|
Hi,
I am working on MSSQL 2005
I want query help
Suppose in table t1, I have field f1 and f2
now the details in the tables are as folows:
f1, f2
1 a
1 b
1 c
1 d
1 e
I want in my select query I should get the information as follows:
f1 f2
1 a,b,c,d,e
Can I get this
Please help
|
|
|
|
|
So if the a,b,c,d,e is a comma separated list in one column, you could write a function to concatenate the result and return it to the query. For details, see: CREATE FUNCTION[^]
|
|
|
|
|
hi!!!!!!! maybe you can use
PIVOT[^] statements in SQL server! It's a very useful
tool! good luck! 
|
|
|
|
|
Hi,
Is there any way to write a parameterless constructor for DataContext class generated for SSCE using SQLMetal
I know the constructor needs connectionString to SSCE.
Veena
|
|
|
|
|
Dear All,
I have a table that has a huge number of data, this table has no primary key.
I need to create a replication that replicate this table and any new transactions applied to this table.
And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions", but in both options it does not accept to create this replication because the source table has no primary key.
How can I solve this problem to create a good replication? 
Kind Regards
OBarahmeh
|
|
|
|
|
obarahmeh wrote: in both options it does not accept to create this replication because the source table has no primary key
Why not create a primary key??? Just add an IDENTITY column to your table and you have a valid primary key to use in replication.
Although I'm wondering also why don't you have any primary key. In my opinion it's a very strange situtation.
obarahmeh wrote: And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions"
I would be very careful with Transactional publication with updatable subscriptions. Technically it works fine, but you have to do a good data modeling so that you don't run into update conflicts (same data is updated in different places). This can be quite challenging sometimes.
Also note that this feature is going to be removed in future versions so if using SQL Server 2008, you shouldn't use "transactional publication with updatable subscriptions".
|
|
|
|
|
Ok I will adda an Identity column.
But are you sure that this method (Transactional publication with updatable subscriptions) makes update conflict???
So, why SQL 2008 contains this method if there is a problems when we use it?
And then what is the ideal solution for making replication in SQL server 2008?
Kind Regards
OBarahmeh
|
|
|
|
|
obarahmeh wrote: But are you sure that this method (Transactional publication with updatable subscriptions) makes update conflict
The method doesn't make them, your application (modifications) may cause conflicts.
obarahmeh wrote: why SQL 2008 contains this method if there is a problems when we use it
For example: you have the same row on two different SQL Servers which then replicate the modifications to each other. The replication interval is, let's say 15 minutes. Now you update the same record on both SQL Server. Next time the modifications are replicated, SQL Server sees that the exact same row has been modified on both ends. Now the question is, which modification is correct? There's no simple answer to that. In some cases it may be the earliest or the latest or perhaps it's both modifications combined.
So the problem isn't in the technology. It is something that you as a designer must be aware of and model the data and the applications so that conflicts do not occur or that they are rare.
The same conflict situation concerns all modifications (update, insert and delete).
obarahmeh wrote: what is the ideal solution for making replication in SQL server 2008?
This depends fully on the requirements.
|
|
|
|
|
Hi,
I am working on SSIS to perform following job.
I am copying data from AS400. Now I want to modify the data So, for each record, if the claim number is 123456789..., change it to AAA456789... (you will overlay the first 3 characters). After this transformation, I need to push this data into SQL server.
Please advise me how can I do this using SSIS.
thanks in advance
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
Use split transformation to split data in two types: one where claim number is 123456789 and everything else. Then use derived column transformation to make necessary changes.
|
|
|
|
|
hi
plz help me
how can i able to protect the sql server database using password.so that user can not able to open that database without password.i m using sql server 2000.
|
|
|
|
|
|
Hi Any body.
I Create a table for location that contains city, state and country in tree structure(parent child).
User add Post in "post" table and ech post has a location id thac can be a city or state or country.
Here is my questions:
1-If I use correct Solution for save location or I should separate city and state and country in another tables?
2-Whats best solution with high performance to show city,state and country for each post.Example:
post id city state country
---- ---- ---- ------ ---------
some text 4 waco txas us
some text 4 null txas us
some text 4 null null us
I use a view with 3 join to show all location here is my view :
SELECT Citeis.name AS city, States.name AS state, C***ries.name AS country, C***ries.id AS CountryID, States.id AS StateID, Citeis.id AS cityID
FROM dbo.Geo AS Citeis INNER JOIN
dbo.Geo AS States ON Citeis.parentID = States.id INNER JOIN
dbo.Geo AS C***ries ON States.parentID = C***ries.id
And then I join it to post table:
SELECT
dbo.Post.*, dbo.ViewStates.city, dbo.ViewStates.state, dbo.ViewStates.country
FROM dbo.Post INNER JOIN
dbo.ViewStates ON dbo.Post.geoID = dbo.ViewStates.cityID
But my host don't allow me to execute this query because of it performance.It say that ths query consume some resource.
Should I use function to retrieve state and city and country ?
should I use inline function or table function?
Thanks for any idea.
sepel
|
|
|
|
|
You have two problems here, first off storing the city, state & country on the post table. What happens if you find you have data issues and have to move a city to another state - will require update to the post table. Second problem is performance of your query.
I would suggest three tables, Country, State, City, with city having a foreign key of state etc.
Then just keep the city location id on the post table and join to the other two tables to get your state and country.
Either that or keep the parent child relationship in location and use a CTE (common table expression) to recursively query the table to get the parent relationships for a particular location. Then just store a single location id as the foreign key on the post table. This has the possible advantage of a post being able to be logged against a country or a state instead of just a city.
|
|
|
|
|
The first question is that is your indexing correctly (on all ID columns and ParentID columns).
The other question is that do you really want to retrieve all rows. You propably have somekind of where clause when you query the data. You should also build indexes to support that query.
Just a sidenote, you don't have to have 3 table. If the columns in all tables are (almost) the same, yuo can combine the data into 1 table. However, I think that's not where you should start from.
|
|
|
|
|
Hi all,
i'm going to install SqlServer on Windows Vista SP1 , i will use it with Visual Studio 2008 SP1 and develop project with WPF and Linq To Sql so i will ask which is the better version of SqlServer that can be compatible/stable with VisualStudio 2008 SP1 and Vista SP1?
Thanks in anticipate.
Have a good day.
Nice Regards.
Bye
|
|
|
|
|
There is no direct link between Visual Studio and Sql Server, so if you're developping something new, why not use SQL Server 2008. Visual Studio connects to SQL Server basically just as any application.
|
|
|
|
|
Hi Mika,
it is my first approach to use Windows Vista SP1 with SqlServer 2008 so with my poor knowledge i ask you some advice before to make my step...i heard some news about some version of SQl Server 2005 is not compatible with Vista so i would remove this doubt from my mind.
Thanks for your support.
Nice Regards,
Bye
|
|
|
|
|
|
Hi Mika,
the link above is useful for my doubt ,thanks  .
I will make last one question if i create a database with SqlServer 2008 this one can to be compatible with SqlServer 2005? ...example i will use the database ,create with SqlServer 2008, in SqlServer 2005 so can SqlServer 2005 read it without trouble?
Have a nice day.
Nice Regards
Bye
|
|
|
|
|
ascotravel wrote: i will use the database ,create with SqlServer 2008, in SqlServer 2005 so can SqlServer 2005 read it without trouble?
I'm not sure what you mean. Do you mean backups? If you take a backup from SQL Server 2008 it cannot be read by 2005.
|
|
|
|
|
Yes it mean if i can do a downgrade from SqlServer 2008 to SqlServer 2005 ...
but already you anticipate my question...
If i can do the last question : Which is the difference between SqlServer 2005 Enterprise Edition and Developer Edition?
I decide to use SqlServer 2005 because i will do backup on a machine that get SqlServer 2005.
However thanks a lot for your kind support.
Have a nice day.
best regards,
Bye
|
|
|
|
|
ascotravel wrote: Which is the difference between SqlServer 2005 Enterprise Edition and Developer Edition
They have the same features but licensing is different:
"SQL Server 2005 Developer Edition allows developers to build and test any type of application with SQL Server on 32-bit, ia64, and x64 platforms. Developer Edition includes all of the functionality of Enterprise Edition, but is licensed only for development, test, and demo use"
See, SQL Server 2005 Developer Edition[^]
|
|
|
|
|
HI Mika,
your information satisfy my doubt  ,
now i understand better about which version of Sql Server is right for my work.
Thanks a lot my friend.
Have a happy work.
Best Regards.
Bye
|
|
|
|
|
Hi,
I want to use audit trail for my application.In my view i shd create one new table with same structure of original table and insert old record of original table into new table at the time of update record.But how to know that there is any updation or not in this record b'cas if any user click on update button then update query will fire either you r updating record or not.
Second thing is that i have 40 fields in my table so it is not feasible to me to check for each column of that record for change.
Please suggest me way to implement audit trail in my application
N!dh!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
