Proprties not imported with Table

I am developing a DB with others in my group. When I import tables created on other servers to my server, the primary key and other properties do not import with the tables. Can anyone explain why this is happening? Is there a setting I have over looked?If you use DTS, make usre you use the Copy object(s) instead of Copy table(s) option.|||Nope...what method are you using to migrate the Data

Look at DTS transfer Database Task option

Or better yet, script the objects then build them...I prefer this method, and use bcp|||Originally posted by joejcheng
If you use DTS, make usre you use the Copy object(s) instead of Copy table(s) option.
The copy object worked ... Thanks

Programatically finding if a row has a foreign key constraint

Hello,

Does anyone know a way from within a .net application to find out if a row has a foreign key constraint before updating it? Or possibly in a SQL Stored Procedure if necessary.

What I am trying to do is basicly simulate a delete of a row in a SQL 2005 database from a .net application. Instead of actually deleting the row I want to set a bool column as deleted. The problem is that I can't orphan records that have a relationship to the one deleted.

This is normaly taken care of automaticly with refrential integrity working properly, but not actually calling delete comands I can't seem to find that out without writing a lot of code to check manually in other tables for that key.

Any help would be appreciated greatly,

Thanks,

Patrick

The following statement will bring you the references tables:

Select TC2.Table_Name from INFORMATION_SCHEMA.Table_constraints TC

INNER JOIN INFORMATION_SCHEMA.Referential_constraints RC

ON TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.Table_constraints TC2

ON TC2.CONSTRAINT_NAME = RC.Constraint_NAME

WHERE TC.TABLE_NAME ='Category' AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks. That worked

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current installed SQL Server Service pack via either a registry key or a WMI class entry...
I've had a good look, but can't seem to find anything that matches or is useful. I know you can retrieve this via a SQL query (with Select @.@.version), but I'm after a way that uses our existing WMI/registry reading process as we are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL permissions issues & have to use a different tool in order to run a SQL query...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
Iain
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain
|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>
|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday for me to have missed it. The other method I found was to check the event log via WMI for the event that SQL records on startup, which give the version of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message text from a range of
other event log informational messages from SQLServer, and the LIKE operator for WQL is only suppported in XP/Server2003.....not w2k which the majority of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection tool they retrieve the version from SQL statement, but I assume that it is to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is al
so stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and what the productIDs are for the various editions, that would be great, otherwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain
|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
Regards
|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'll also work when SP4 i released!
Any ideas?
|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current installed SQL Server Service pack via either a registry key or a WMI class entry..
I've had a good look, but can't seem to find anything that matches or is useful. I know you can retrieve this via a SQL query (with Select @.@.version), but I'm after a way that uses our existing WMI/registry reading process as we are collecting info from servers all over the world, so I don't want to have to deal with all the SQL permissions issues & have to use a different tool in order to run a SQL query..
Any info or advice that people can offer would be greatfully accepte
Thanks in Advanc
Cheer
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex..
This is exactly what I nee
Can't believe I didn't spot this, must have been due to a long day on friday for me to have missed it. The other method I found was to check the event log via WMI for the event that SQL records on startup, which give the version of SQL, and also the version of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message text from a range of other event log informational messages from SQLServer, and the LIKE operator for WQL is only suppported in XP/Server2003.....not w2k which the majority of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection tool they retrieve the version from SQL statement, but I assume that it is to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is also stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identify the product type..
if you already know if productID correlates with the different editions, and what the productIDs are for the various editions, that would be great, otherwise I'll post the results of what I find out back here for your referenc
Thanks agai
cheer
Iain|||Rasmus,
This should help:
How do I know which version of SQL Server I'm running?
http://aspfaq.com/show.asp?id=2160
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Rasmus wrote:
> Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
>
> Regards|||Thanks Mark
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'll also work when SP4 i released
Any ideas?|||... and on a related node
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current in
stalled SQL Server Service pack via either a registry key or a WMI class ent
ry...
I've had a good look, but can't seem to find anything that matches or is use
ful. I know you can retrieve this via a SQL query (with Select @.@.version), b
ut I'm after a way that uses our existing WMI/registry reading process as we
are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL
permissions issues & have to use a different tool in order to run a SQL quer
y...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday
for me to have missed it. The other method I found was to check the event l
og via WMI for the event that SQL records on startup, which give the version
of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...th
e only limitation on this is that you need a relatively specific WQL (WMI Qu
ery Language) query - as its an information event that only differs in the m
essage text from a range of
other event log informational messages from SQLServer, and the LIKE operator
for WQL is only suppported in XP/Server2003.....not w2k which the majority
of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection
tool they retrieve the version from SQL statement, but I assume that it is t
o grab the SQL product type (enterprise edition etc) at the same time.....i
s this something that is al
so stored in the registry, as the only reference that might give this info i
s the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL Server\
80\Registration), which I'll check on some different version machines to see
if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and
what the productIDs are for the various editions, that would be great, othe
rwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Servic
e Pack installed?
Regards|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding th
e SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - tha
t'll also work when SP4 i released!
Any ideas?|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?

Proglem creating index

I am having a problem creating an index. When I check the table for dupes I don't find any. When I try to create the key I get an error that there are duplicated values. I think that this may be because the key is too long? I don't see any other reason. The data looks OK.

Each client should only have one record for a date (the 1st of the month) and a paysource.

create unique index UniqueArId on AR(CLIENTID, [DATE], PaysourceUUID)

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '400007'.
The statement has been terminated.

Int datetime uniqueid
400007 2005-04-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-05-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-06-27 00:00:00.000 163BD2F6-C61F-45A1-8015-CDE4D2B3F29C
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760

402161 2004-01-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
402161 2004-02-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64
402161 2004-03-01 00:00:00.000 F533399B-FB34-4DC4-AB9E-12D46A908F64

The rows below seem to be the duplicates:

400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760
400007 2005-04-27 00:00:00.000 C9A28832-C5E1-48A1-8575-1EB7D7EA2760|||Sorry, thats a typo in the email, it is not that way in the database.|||

Can you post a repro script that shows the problem? What is the output of the query below?

select CLIENTID, [DATE], PaysourceUUID, count(*) as dupes
from AR
group by CLIENTID, [DATE], PaysourceUUID
having count(*) > 1
order by dupes desc;