check if there is an existing SQL server installation

is there a way to check an SQL Server installation, i want to get the version and what edition the installed sql server is. thanks

Hi,

Try this.Hope this works for you.

select @.@.Version

(or)

exec xp_msver

both will get you about the details of installed product.

Regards,

Prashanthi

|||can you do this.. without actually connecting in the sql server?|||you can do by navigating through the Help-> About|||

You can check the Errorlog to know whats the version and edition of sql server if you dont want to connect to sql server......hope this helps

|||

Hi,

You can know about the details of your SQL Server Installation detais, like version, etc.. in registry editor. To locate the verion information navigate to the following path in your registry editor:

1. Click Start button select "Run" command and type "regedit"

2. Search the following key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\CurrentVersion"

Or

You can also know your version by using "sqlcmd" utility.

1. In the command prompt, type "sqlcmd".

2. Type the following command exactly:

:exit(SELECT @.@.VERSION)

or

:exit(EXEC xp_msver)

I Hope it'll work. Please let me know If I m wrong.

Regards,

Kiran.Y

|||

Inaddition to what Kiran has said, you can execute the below query in sqlcmd to get the desired results...

select serverproperty('servername'),serverproperty('productversion'),serverproperty('edition'),serverproperty('productlevel')

Check Foreign Key integrity of existing data

Hi,
I'm in the process of writing a script that inserts or updates default data
for a database.
I came to the conclusion that I have to temporarily disable certain foreign
keys.
At the end of the script however, I'd like to check existing data to verify
that everything is still ok.
Basically something like this:
ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
-- Insert data here
ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
existing data, right?
-- Process other tables here
-- At this point I'd like to check that the FK_MyForeignKey constaint is
valid for existing data
Now I know that I could do something like this:
ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
However, is there a way to check existing data without dropping and
re-adding the constraint?
Or is dropping and re-adding not that costly?
Thanks,
Erik> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
Right.

> However, is there a way to check existing data without dropping and
> re-adding the constraint?
Use an IF EXISTS (or similar query)...test prior to running the ALTER
TABLE...CHECK statement.
HTH
Jerry
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message
news:eGZJSnZ1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm in the process of writing a script that inserts or updates default
> data
> for a database.
> I came to the conclusion that I have to temporarily disable certain
> foreign
> keys.
> At the end of the script however, I'd like to check existing data to
> verify
> that everything is still ok.
> Basically something like this:
> ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
> -- Insert data here
> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
> -- Process other tables here
> -- At this point I'd like to check that the FK_MyForeignKey constaint is
> valid for existing data
>
> Now I know that I could do something like this:
> ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
> ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
>
> However, is there a way to check existing data without dropping and
> re-adding the constraint?
> Or is dropping and re-adding not that costly?
> Thanks,
> Erik
>|||OK, so manually run a check.
Didn't even think of that. :)
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
check
> Right.
>
> Use an IF EXISTS (or similar query)...test prior to running the ALTER
> TABLE...CHECK statement.
> HTH
> Jerry|||You can also check out DBCC CHECKCONSTRAINTS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message news:OVnT3Gb1FHA.1212@.TK2MSFTNGP10.phx
.gbl...
> OK, so manually run a check.
> Didn't even think of that. :)
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
> check
>sql

Check Constraint

I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.Don't have access to bol and my machine...I'll figure it out tomorrow, unless you get it tonight

BUT

Look at LIKE [0-9][0-9][0-9][0-9][0-9]|||I guess u r trying to check a single digit as zip.
existing data in the table conflicts with the constraint u r trying to add
so try to alter table with nocheck option.
or else drop the column and add it in which ever way u want.
------------------------
alter table test
with nocheck
add constraint ck_test
check (zip between '0' and '9')
------------------------

Originally posted by ttkh
I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.

Check all existing collations in one database.

Hello,
Can you tell me, how can i get all collations that i have in one database. I
get the database collation but i need to verify if any table or field in the
current database have one different collation.
I've got one job that is returning the following error:
Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
Implicit conversion of char value to char cannot be performed because the
collation of the value is unresolved due to a collation conflict.
Thanks and best regars.
The following will list columns in tables and views that have a collation
other than the current database default:
SELECT
o.type,
USER_NAME(o.uid),
o.name,
c.name
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE
o.type IN('U', 'V') AND
c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> Hello,
> Can you tell me, how can i get all collations that i have in one database.
> I
> get the database collation but i need to verify if any table or field in
> the
> current database have one different collation.
> I've got one job that is returning the following error:
> Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> Implicit conversion of char value to char cannot be performed because the
> collation of the value is unresolved due to a collation conflict.
> Thanks and best regars.
|||Thanks Dan,
Best regards
"Dan Guzman" wrote:

> The following will list columns in tables and views that have a collation
> other than the current database default:
> SELECT
> o.type,
> USER_NAME(o.uid),
> o.name,
> c.name
> FROM sysobjects o
> JOIN syscolumns c ON c.id = o.id
> WHERE
> o.type IN('U', 'V') AND
> c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
>
>

Check all existing collations in one database.

Hello,
Can you tell me, how can i get all collations that i have in one database. I
get the database collation but i need to verify if any table or field in the
current database have one different collation.
I've got one job that is returning the following error:
Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
Implicit conversion of char value to char cannot be performed because the
collation of the value is unresolved due to a collation conflict.
Thanks and best regars.The following will list columns in tables and views that have a collation
other than the current database default:
SELECT
o.type,
USER_NAME(o.uid),
o.name,
c.name
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE
o.type IN('U', 'V') AND
c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> Hello,
> Can you tell me, how can i get all collations that i have in one database.
> I
> get the database collation but i need to verify if any table or field in
> the
> current database have one different collation.
> I've got one job that is returning the following error:
> Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> Implicit conversion of char value to char cannot be performed because the
> collation of the value is unresolved due to a collation conflict.
> Thanks and best regars.|||Thanks Dan,
Best regards
"Dan Guzman" wrote:
> The following will list columns in tables and views that have a collation
> other than the current database default:
> SELECT
> o.type,
> USER_NAME(o.uid),
> o.name,
> c.name
> FROM sysobjects o
> JOIN syscolumns c ON c.id = o.id
> WHERE
> o.type IN('U', 'V') AND
> c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> > Hello,
> >
> > Can you tell me, how can i get all collations that i have in one database.
> > I
> > get the database collation but i need to verify if any table or field in
> > the
> > current database have one different collation.
> >
> > I've got one job that is returning the following error:
> > Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> > Implicit conversion of char value to char cannot be performed because the
> > collation of the value is unresolved due to a collation conflict.
> >
> > Thanks and best regars.
>
>

Check all existing collations in one database.

Hello,
Can you tell me, how can i get all collations that i have in one database. I
get the database collation but i need to verify if any table or field in the
current database have one different collation.
I've got one job that is returning the following error:
Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
Implicit conversion of char value to char cannot be performed because the
collation of the value is unresolved due to a collation conflict.
Thanks and best regars.The following will list columns in tables and views that have a collation
other than the current database default:
SELECT
o.type,
USER_NAME(o.uid),
o.name,
c.name
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE
o.type IN('U', 'V') AND
c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
> Hello,
> Can you tell me, how can i get all collations that i have in one database.
> I
> get the database collation but i need to verify if any table or field in
> the
> current database have one different collation.
> I've got one job that is returning the following error:
> Server: Msg 457, Level 16, State 1, Procedure WsIBTLoadBatch, Line 20
> Implicit conversion of char value to char cannot be performed because the
> collation of the value is unresolved due to a collation conflict.
> Thanks and best regars.|||Thanks Dan,
Best regards
"Dan Guzman" wrote:

> The following will list columns in tables and views that have a collation
> other than the current database default:
> SELECT
> o.type,
> USER_NAME(o.uid),
> o.name,
> c.name
> FROM sysobjects o
> JOIN syscolumns c ON c.id = o.id
> WHERE
> o.type IN('U', 'V') AND
> c.collation <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:31B6222F-B334-4612-853E-D27298405704@.microsoft.com...
>
>