Easy SQL Server Version Identification

I know about @.@.version and SERVERPROPERTY, but I am looking for a query that will determine between SQL Server 2000 and 2005. I need to write an if/then statement and don't want to have to add all of the 9.xxx.xx versions for 2005 and keep that updated with each hotfix that comes out.
-Kyle

Use the following query..

Tested in all the versions..

Code Snippet

Declare @.version as Varchar(10);

Set @.version = Cast(serverproperty('ProductVersion') as Varchar)

if charindex('10.',@.version) = 1

Print 'It is SQL Server 2008'

if charindex('9.',@.version) =1

Print 'It is SQL Server 2005'

if charindex('8.',@.version) =1

Print 'It is SQL Server 2000'

|||The final query looked like this...thanks for your help.

IF CONVERT(char(1),SERVERPROPERTY('ProductVersion'))>='9' --pre SQL 2005
BEGIN
DECLARE @.Server [nchar] (100)
SET @.Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))

INSERT INTO [tempdb].[dbo].[User_Auditing] (Server, UserName, WinAuth, SQL_Auth_UserName, PassPolicyOn)
SELECT @.Server, s.name, isntuser, q.name, is_policy_checked
FROM sys.syslogins s FULL OUTER JOIN sys.sql_logins q
ON (s.name = q.name)
END

-Kyle

E Manager port changes

Is there a way when you try to do a server registration in Enterprize Manage
r
(SQL 2000 version) to set which port it will connect on? I know the default
is 1433 but the one I want to register is on 1210 and can't be changed so I
need the Manager registration to try to connect on that port number. I know
the ODBC allows you to set the port to connect on but don't see that in EM.
Thanks!!You will need to use the Server Network Utility to change ports.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Transam388" <Transam388@.discussions.microsoft.com> wrote in message
news:FFEB71DE-5705-48D2-A23D-AF72A2E9B824@.microsoft.com...
> Is there a way when you try to do a server registration in Enterprize
> Manager
> (SQL 2000 version) to set which port it will connect on? I know the
> default
> is 1433 but the one I want to register is on 1210 and can't be changed so
> I
> need the Manager registration to try to connect on that port number. I
> know
> the ODBC allows you to set the port to connect on but don't see that in
> EM.
> Thanks!!|||If you are trying to register a server use servername, port
ServerA,1210
Or are you trying to change the port the server listens on?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Transam388" <Transam388@.discussions.microsoft.com> wrote in message
news:FFEB71DE-5705-48D2-A23D-AF72A2E9B824@.microsoft.com...
> Is there a way when you try to do a server registration in Enterprize
> Manager
> (SQL 2000 version) to set which port it will connect on? I know the
> default
> is 1433 but the one I want to register is on 1210 and can't be changed so
> I
> need the Manager registration to try to connect on that port number. I
> know
> the ODBC allows you to set the port to connect on but don't see that in
> EM.
> Thanks!!|||OK, that was it. I tried doing the old telnet style IP : Port instead of a
comma. Thank you!! This is the best place for quick and USEFUL
information!! Thanks to both of you and merry Christmas!
"Kevin3NF" wrote:

> If you are trying to register a server use servername, port
> ServerA,1210
> Or are you trying to change the port the server listens on?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Transam388" <Transam388@.discussions.microsoft.com> wrote in message
> news:FFEB71DE-5705-48D2-A23D-AF72A2E9B824@.microsoft.com...
>
>

E Manager port changes

Is there a way when you try to do a server registration in Enterprize Manager
(SQL 2000 version) to set which port it will connect on? I know the default
is 1433 but the one I want to register is on 1210 and can't be changed so I
need the Manager registration to try to connect on that port number. I know
the ODBC allows you to set the port to connect on but don't see that in EM.
Thanks!!
If you are trying to register a server use servername, port
ServerA,1210
Or are you trying to change the port the server listens on?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Transam388" <Transam388@.discussions.microsoft.com> wrote in message
news:FFEB71DE-5705-48D2-A23D-AF72A2E9B824@.microsoft.com...
> Is there a way when you try to do a server registration in Enterprize
> Manager
> (SQL 2000 version) to set which port it will connect on? I know the
> default
> is 1433 but the one I want to register is on 1210 and can't be changed so
> I
> need the Manager registration to try to connect on that port number. I
> know
> the ODBC allows you to set the port to connect on but don't see that in
> EM.
> Thanks!!
|||OK, that was it. I tried doing the old telnet style IP : Port instead of a
comma. Thank you!! This is the best place for quick and USEFUL
information!! Thanks to both of you and merry Christmas!
"Kevin3NF" wrote:

> If you are trying to register a server use servername, port
> ServerA,1210
> Or are you trying to change the port the server listens on?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Transam388" <Transam388@.discussions.microsoft.com> wrote in message
> news:FFEB71DE-5705-48D2-A23D-AF72A2E9B824@.microsoft.com...
>
>

Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan

dynamic sql query syntax

Hi friends
I need ur help with syntax of following dynamic sql.actually it is simple version of what am doing but i know for sure this line actually causing the error.

DECLARE @.p_taskentrydtfilter nvarchar(50),@.taskentrydttag nvarchar(100)
declare @.p_taskentrydt DATETIME

set @.p_taskentrydtfilter= '>='
set @.p_taskentrydt = '20050609'

select @.taskentrydttag=case when (@.p_taskentrydt='' OR @.p_taskentrydt is null) then '' else
' and task.entrydt '+
@.p_taskentrydtfilter+''+@.p_taskentrydt+''' and task.entrydt <'''+dateadd(d,1,@.p_taskentrydt)+'' end

as u can see am storing a CASE stmt in a variable but it gives me error saying
"Syntax error converting datetime from character string."
any ideas .Thanks

Cheers

Hi,

I think its your date format. try setting your @.p_taskentrydt to other date date formats such as mm/dd/yyyy

cheers,

Paul June A. Domag

|||@.p_taskentrydtfilter+''+@.p_taskentrydt + '''

should be:

@.p_taskentrydtfilter+''''+@.p_taskentrydt + '''

But you should be using sp_executesql instead of dynamically concatenating variables that can be set by caller for example. See BOL for more details on how to use sp_executesql to execute parameterized SQL statements.

|||Thanks Umachandar
That worked nicely