Default dates to UTC in SQL Server 2000

Hi all,

I hope this is the correct forum. I have SQL Server 2000 installed on a server located in the USA. The local time zone on the server is EST. Therefore getdate() returns EST time. I now realise that I'd like to be saving all my timestamps in UTC. While I could modify all my stored procedures to use getutcdate() rather than getdate() I wonder if there is a database level setting for time zone that will cause getdate() to return UTC date?

Any ideas?

Des

Unfortunately, there is not a database or server lever 'switch' that would allow us to shift time zones.

However, it would be fairly quick to search syscomments for getdate() to determine a list of procedures needing alteration.

You would also want to change any table definitions where column level defaults are set to getdate().

Or, you could set the OS Server time for GMT timezone and turn automatic DST off -but that would effect all other non database logging on the server. Perhaps not a good idea...

|||

Thanks Arnie. I thought as much but it's always good to double check.

Regards,

Des

Dedicated Administrator Connection

Hi:

I am trying to connect to my local instance of SQL Server 2005 using Dedicated Administrator Connection by using

Admin:Servername\Instancename

and I end up with the following error.

Dedicated administrator connections are not supported.

I enabled the connection in the SQL Configuration Manager and still get the error.

Any ideas on how to resolve this error?.

Note: SQL Browser service is running.

Thanks

Ak

Which SKU of sql server 2005 are you running ?

Also, are the client and the server running on the same machine or different machines ?

|||

Hi Asvin:

I am running SQL Server 2005 Standard Edition and both the client and server are running on the same machine.

Thanks

|||You can check the sqlserver log to see if DAC is enabled/listening (and on which tcp port).

Use sqlcmd -A to test. Also, you cannot use DAC for Object Explorer connection. It only works on query window. So, connect normally first, then open a new query window with "admin:<server>\<instance>"|||

Hi OJ:

Thanks for your reply.

This is what my SQL Server log says.

Date 7/7/2006 4:56:11 PM
Log SQL Server (Current - 7/12/2006 12:00:00 AM)

Source Server

Message
Dedicated admin connection support was established for listening remotely on port 1771.

So DAC is listening on port 1771 right?. I tried your second method of connecting to through object explorer but I got the same error message.

Then I tried the following from command prompt:

sqlcmd -SMyMachine\SQL2K5 -A -E

I got the following error message:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port.
Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Please let me know what I am doing wrong.

Thanks

|||

MS Experts:

Can anyone please help me with this issue. I would really appreciate all your help.

Thanks

AK

|||Sorry for the late reply.

I've seen the same error when the server\instance is incorrect. Since this is a local instance, please try one of the following:

sqlcmd -S".\SQL2K5" -A -E

sqlcmd -S"MyMachine,1771" -A -E

Be sure to start the instance first.

net start mssql$sql2k5

|||

Did you enable remote connections ?

http://www.sqlserver2005.de/SharedFiles/Remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks OJ and Jens. I tried all options that you have suggested (my remote connections are enabled) but still get the same error.

The funny thing is when I try through the command prompt using sqlcmd (with the new commands you gave) it appears to be connected but when I connect using SQL Management Studio while the command window is still open, it allows me to establish a new connection through Management studio. I think once DAC is established i should not be able to make a new connection using management studio. DAC should allow only a single connection. This tells me that a DAC was not establised through SQLCMD from command prompt.

So to make sure that I can indeed connect using DAC I used the SQL Management Studioand tried the following:

ADMIN:.\SQL2K5 and end up with the same error.

Dedicated adminstrator connections are not supported.

This means I am not able to connect using DAC still.

I cant post the attachment or else I would have posted the attachment.

|||There can only be ONE admin connection. If you successfully connect via "sqlcmd -A" then you will not be able to make another admin connection via sqlwb (vice versa).

Admin connection has nothing to do with regular user connection. So, it's normal that you still can connect via sqlwb. However, it will not be possible for you to open another admin connection (i.e. admin:\.sql2k5) via sqlwb if you already connect with sqlcmd.

As I have said before, you need to connect normally (without admin) when you first open up sqlwb. Once that's done, you can open a new query window (File -> New -> Database Engine Query) and connect as an admin connection.

If you still have problem, please post the entire sql log here.|||

Do you obtain this error message:

"Dedicated administrator connections are not supported. (ObjectExplorer)"

If so you try to connect the Object Explorer. Try to open a new query windows as oj told you.

|||

Thanks everyone. I seem to have connected through the cmd prompt through DAC and when I query using @.@.Servername I can see my machine name in the cmd prompt window. I am just wondering if there is a command in SQL Server 2005 that tells me whether I am connected using dedicated admin connection or that my present connection is a DAC. If so, I would appreciate if if anyone lets me know it.

Thanks

AK.

|||

The query below should return 1 row if you are connected using dedicated admin connection. It should not return any rows if you are not connected using DAC.

select * from sys.dm_exec_connections ec join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@.@.spid

|||

Thanks Ashvin. That did exactly confirm that I was connected using DAC. It returned one row. So my million dollar question is why cant I connect to DAC using ADMIN keyword. I tried all that have been suggested in this thread and still it says DAC connections are not supported (Object Explorer). Its a mystery!!!.

However thanks for all the suggestions.

|||

Hi,

DAC is a feature which is disabled by default. Did you enable it so far ? I made a screencast for you showing how to enable this feature. This is the second of a series which I decided to make. So have Fun !

A link with the screencasts taken so far will be on my site as I will have time for that.

use this link here in the meantime: http://www.SQLServer2005.de/ScreenCasts/Enable_DAC.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Dedicated Administrator Connection

Hi:

I am trying to connect to my local instance of SQL Server 2005 using Dedicated Administrator Connection by using

Admin:Servername\Instancename

and I end up with the following error.

Dedicated administrator connections are not supported.

I enabled the connection in the SQL Configuration Manager and still get the error.

Any ideas on how to resolve this error?.

Note: SQL Browser service is running.

Thanks

Ak

Which SKU of sql server 2005 are you running ?

Also, are the client and the server running on the same machine or different machines ?

|||

Hi Asvin:

I am running SQL Server 2005 Standard Edition and both the client and server are running on the same machine.

Thanks

|||You can check the sqlserver log to see if DAC is enabled/listening (and on which tcp port).

Use sqlcmd -A to test. Also, you cannot use DAC for Object Explorer connection. It only works on query window. So, connect normally first, then open a new query window with "admin:<server>\<instance>"|||

Hi OJ:

Thanks for your reply.

This is what my SQL Server log says.

Date 7/7/2006 4:56:11 PM
Log SQL Server (Current - 7/12/2006 12:00:00 AM)

Source Server

Message
Dedicated admin connection support was established for listening remotely on port 1771.

So DAC is listening on port 1771 right?. I tried your second method of connecting to through object explorer but I got the same error message.

Then I tried the following from command prompt:

sqlcmd -SMyMachine\SQL2K5 -A -E

I got the following error message:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port.
Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Please let me know what I am doing wrong.

Thanks

|||

MS Experts:

Can anyone please help me with this issue. I would really appreciate all your help.

Thanks

AK

|||Sorry for the late reply.

I've seen the same error when the server\instance is incorrect. Since this is a local instance, please try one of the following:

sqlcmd -S".\SQL2K5" -A -E

sqlcmd -S"MyMachine,1771" -A -E

Be sure to start the instance first.

net start mssql$sql2k5

|||

Did you enable remote connections ?

http://www.sqlserver2005.de/SharedFiles/Remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks OJ and Jens. I tried all options that you have suggested (my remote connections are enabled) but still get the same error.

The funny thing is when I try through the command prompt using sqlcmd (with the new commands you gave) it appears to be connected but when I connect using SQL Management Studio while the command window is still open, it allows me to establish a new connection through Management studio. I think once DAC is established i should not be able to make a new connection using management studio. DAC should allow only a single connection. This tells me that a DAC was not establised through SQLCMD from command prompt.

So to make sure that I can indeed connect using DAC I used the SQL Management Studioand tried the following:

ADMIN:.\SQL2K5 and end up with the same error.

Dedicated adminstrator connections are not supported.

This means I am not able to connect using DAC still.

I cant post the attachment or else I would have posted the attachment.

|||There can only be ONE admin connection. If you successfully connect via "sqlcmd -A" then you will not be able to make another admin connection via sqlwb (vice versa).

Admin connection has nothing to do with regular user connection. So, it's normal that you still can connect via sqlwb. However, it will not be possible for you to open another admin connection (i.e. admin:\.sql2k5) via sqlwb if you already connect with sqlcmd.

As I have said before, you need to connect normally (without admin) when you first open up sqlwb. Once that's done, you can open a new query window (File -> New -> Database Engine Query) and connect as an admin connection.

If you still have problem, please post the entire sql log here.|||

Do you obtain this error message:

"Dedicated administrator connections are not supported. (ObjectExplorer)"

If so you try to connect the Object Explorer. Try to open a new query windows as oj told you.

|||

Thanks everyone. I seem to have connected through the cmd prompt through DAC and when I query using @.@.Servername I can see my machine name in the cmd prompt window. I am just wondering if there is a command in SQL Server 2005 that tells me whether I am connected using dedicated admin connection or that my present connection is a DAC. If so, I would appreciate if if anyone lets me know it.

Thanks

AK.

|||

The query below should return 1 row if you are connected using dedicated admin connection. It should not return any rows if you are not connected using DAC.

select * from sys.dm_exec_connections ec join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@.@.spid

|||

Thanks Ashvin. That did exactly confirm that I was connected using DAC. It returned one row. So my million dollar question is why cant I connect to DAC using ADMIN keyword. I tried all that have been suggested in this thread and still it says DAC connections are not supported (Object Explorer). Its a mystery!!!.

However thanks for all the suggestions.

|||

Hi,

DAC is a feature which is disabled by default. Did you enable it so far ? I made a screencast for you showing how to enable this feature. This is the second of a series which I decided to make. So have Fun !

A link with the screencasts taken so far will be on my site as I will have time for that.

use this link here in the meantime: http://www.SQLServer2005.de/ScreenCasts/Enable_DAC.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Dedicated Administrator Connection

Hi:

I am trying to connect to my local instance of SQL Server 2005 using Dedicated Administrator Connection by using

Admin:Servername\Instancename

and I end up with the following error.

Dedicated administrator connections are not supported.

I enabled the connection in the SQL Configuration Manager and still get the error.

Any ideas on how to resolve this error?.

Note: SQL Browser service is running.

Thanks

Ak

Which SKU of sql server 2005 are you running ?

Also, are the client and the server running on the same machine or different machines ?

|||

Hi Asvin:

I am running SQL Server 2005 Standard Edition and both the client and server are running on the same machine.

Thanks

|||You can check the sqlserver log to see if DAC is enabled/listening (and on which tcp port).

Use sqlcmd -A to test. Also, you cannot use DAC for Object Explorer connection. It only works on query window. So, connect normally first, then open a new query window with "admin:<server>\<instance>"|||

Hi OJ:

Thanks for your reply.

This is what my SQL Server log says.

Date 7/7/2006 4:56:11 PM
Log SQL Server (Current - 7/12/2006 12:00:00 AM)

Source Server

Message
Dedicated admin connection support was established for listening remotely on port 1771.

So DAC is listening on port 1771 right?. I tried your second method of connecting to through object explorer but I got the same error message.

Then I tried the following from command prompt:

sqlcmd -SMyMachine\SQL2K5 -A -E

I got the following error message:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port.
Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Please let me know what I am doing wrong.

Thanks

|||

MS Experts:

Can anyone please help me with this issue. I would really appreciate all your help.

Thanks

AK

|||Sorry for the late reply.

I've seen the same error when the server\instance is incorrect. Since this is a local instance, please try one of the following:

sqlcmd -S".\SQL2K5" -A -E

sqlcmd -S"MyMachine,1771" -A -E

Be sure to start the instance first.

net start mssql$sql2k5

|||

Did you enable remote connections ?

http://www.sqlserver2005.de/SharedFiles/Remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks OJ and Jens. I tried all options that you have suggested (my remote connections are enabled) but still get the same error.

The funny thing is when I try through the command prompt using sqlcmd (with the new commands you gave) it appears to be connected but when I connect using SQL Management Studio while the command window is still open, it allows me to establish a new connection through Management studio. I think once DAC is established i should not be able to make a new connection using management studio. DAC should allow only a single connection. This tells me that a DAC was not establised through SQLCMD from command prompt.

So to make sure that I can indeed connect using DAC I used the SQL Management Studioand tried the following:

ADMIN:.\SQL2K5 and end up with the same error.

Dedicated adminstrator connections are not supported.

This means I am not able to connect using DAC still.

I cant post the attachment or else I would have posted the attachment.

|||There can only be ONE admin connection. If you successfully connect via "sqlcmd -A" then you will not be able to make another admin connection via sqlwb (vice versa).

Admin connection has nothing to do with regular user connection. So, it's normal that you still can connect via sqlwb. However, it will not be possible for you to open another admin connection (i.e. admin:\.sql2k5) via sqlwb if you already connect with sqlcmd.

As I have said before, you need to connect normally (without admin) when you first open up sqlwb. Once that's done, you can open a new query window (File -> New -> Database Engine Query) and connect as an admin connection.

If you still have problem, please post the entire sql log here.|||

Do you obtain this error message:

"Dedicated administrator connections are not supported. (ObjectExplorer)"

If so you try to connect the Object Explorer. Try to open a new query windows as oj told you.

|||

Thanks everyone. I seem to have connected through the cmd prompt through DAC and when I query using @.@.Servername I can see my machine name in the cmd prompt window. I am just wondering if there is a command in SQL Server 2005 that tells me whether I am connected using dedicated admin connection or that my present connection is a DAC. If so, I would appreciate if if anyone lets me know it.

Thanks

AK.

|||

The query below should return 1 row if you are connected using dedicated admin connection. It should not return any rows if you are not connected using DAC.

select * from sys.dm_exec_connections ec join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@.@.spid

|||

Thanks Ashvin. That did exactly confirm that I was connected using DAC. It returned one row. So my million dollar question is why cant I connect to DAC using ADMIN keyword. I tried all that have been suggested in this thread and still it says DAC connections are not supported (Object Explorer). Its a mystery!!!.

However thanks for all the suggestions.

|||

Hi,

DAC is a feature which is disabled by default. Did you enable it so far ? I made a screencast for you showing how to enable this feature. This is the second of a series which I decided to make. So have Fun !

A link with the screencasts taken so far will be on my site as I will have time for that.

use this link here in the meantime: http://www.SQLServer2005.de/ScreenCasts/Enable_DAC.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Dedicated Administrator Connection

Hi:

I am trying to connect to my local instance of SQL Server 2005 using Dedicated Administrator Connection by using

Admin:Servername\Instancename

and I end up with the following error.

Dedicated administrator connections are not supported.

I enabled the connection in the SQL Configuration Manager and still get the error.

Any ideas on how to resolve this error?.

Note: SQL Browser service is running.

Thanks

Ak

Which SKU of sql server 2005 are you running ?

Also, are the client and the server running on the same machine or different machines ?

|||

Hi Asvin:

I am running SQL Server 2005 Standard Edition and both the client and server are running on the same machine.

Thanks

|||You can check the sqlserver log to see if DAC is enabled/listening (and on which tcp port).

Use sqlcmd -A to test. Also, you cannot use DAC for Object Explorer connection. It only works on query window. So, connect normally first, then open a new query window with "admin:<server>\<instance>"|||

Hi OJ:

Thanks for your reply.

This is what my SQL Server log says.

Date 7/7/2006 4:56:11 PM
Log SQL Server (Current - 7/12/2006 12:00:00 AM)

Source Server

Message
Dedicated admin connection support was established for listening remotely on port 1771.

So DAC is listening on port 1771 right?. I tried your second method of connecting to through object explorer but I got the same error message.

Then I tried the following from command prompt:

sqlcmd -SMyMachine\SQL2K5 -A -E

I got the following error message:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port.
Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Please let me know what I am doing wrong.

Thanks

|||

MS Experts:

Can anyone please help me with this issue. I would really appreciate all your help.

Thanks

AK

|||Sorry for the late reply.

I've seen the same error when the server\instance is incorrect. Since this is a local instance, please try one of the following:

sqlcmd -S".\SQL2K5" -A -E

sqlcmd -S"MyMachine,1771" -A -E

Be sure to start the instance first.

net start mssql$sql2k5

|||

Did you enable remote connections ?

http://www.sqlserver2005.de/SharedFiles/Remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks OJ and Jens. I tried all options that you have suggested (my remote connections are enabled) but still get the same error.

The funny thing is when I try through the command prompt using sqlcmd (with the new commands you gave) it appears to be connected but when I connect using SQL Management Studio while the command window is still open, it allows me to establish a new connection through Management studio. I think once DAC is established i should not be able to make a new connection using management studio. DAC should allow only a single connection. This tells me that a DAC was not establised through SQLCMD from command prompt.

So to make sure that I can indeed connect using DAC I used the SQL Management Studioand tried the following:

ADMIN:.\SQL2K5 and end up with the same error.

Dedicated adminstrator connections are not supported.

This means I am not able to connect using DAC still.

I cant post the attachment or else I would have posted the attachment.

|||There can only be ONE admin connection. If you successfully connect via "sqlcmd -A" then you will not be able to make another admin connection via sqlwb (vice versa).

Admin connection has nothing to do with regular user connection. So, it's normal that you still can connect via sqlwb. However, it will not be possible for you to open another admin connection (i.e. admin:\.sql2k5) via sqlwb if you already connect with sqlcmd.

As I have said before, you need to connect normally (without admin) when you first open up sqlwb. Once that's done, you can open a new query window (File -> New -> Database Engine Query) and connect as an admin connection.

If you still have problem, please post the entire sql log here.|||

Do you obtain this error message:

"Dedicated administrator connections are not supported. (ObjectExplorer)"

If so you try to connect the Object Explorer. Try to open a new query windows as oj told you.

|||

Thanks everyone. I seem to have connected through the cmd prompt through DAC and when I query using @.@.Servername I can see my machine name in the cmd prompt window. I am just wondering if there is a command in SQL Server 2005 that tells me whether I am connected using dedicated admin connection or that my present connection is a DAC. If so, I would appreciate if if anyone lets me know it.

Thanks

AK.

|||

The query below should return 1 row if you are connected using dedicated admin connection. It should not return any rows if you are not connected using DAC.

select * from sys.dm_exec_connections ec join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@.@.spid

|||

Thanks Ashvin. That did exactly confirm that I was connected using DAC. It returned one row. So my million dollar question is why cant I connect to DAC using ADMIN keyword. I tried all that have been suggested in this thread and still it says DAC connections are not supported (Object Explorer). Its a mystery!!!.

However thanks for all the suggestions.

|||

Hi,

DAC is a feature which is disabled by default. Did you enable it so far ? I made a screencast for you showing how to enable this feature. This is the second of a series which I decided to make. So have Fun !

A link with the screencasts taken so far will be on my site as I will have time for that.

use this link here in the meantime: http://www.SQLServer2005.de/ScreenCasts/Enable_DAC.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Declaring USER_NAME() as SQL Variable

Hi,

I have a User-defined function "Concatenate_NoteTexts" which I use in a
query (SQL Server 2000). On my local development machine it is called like
this:

SELECT
dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
FROM tblIntroducers

I want to run the same code on a shared remote server where I am user "JON"
instead of "dbo". I don't want to hard-code the User Name into the SQL, but
when I tried to put the user name into a variable as here:

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

I get the following error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'

Any advice?

TIA,

JON

PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
1"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?

Beg for your own database.
Development as a non-dbo is really a hastle.

You can have your own database without being a SystemAdministrator. Just
have a SystemAdministrator to run this code:

create database jon_dev
go
use jon_dev
go
sp_addalias 'jon', 'dbo'

David|||Hi David,

Thanks, nice idea, I'll have to see if the webhosts will do that.

But there must also be a way to code what I want *without* being a SysAd!

JON|||What are you trying to do with the variable? Concatenate? Or return it in
the select statement as a column? If the latter, change the period to a
comma.

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?
> TIA,
> JON
>
> PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> 1
>
>
>|||Sorry... misread your message - you need access to the function.

"Morgan" <mfears@.spamcop.net> wrote in message
news:OCNLY9icDHA.1280@.tk2msftngp13.phx.gbl...
> What are you trying to do with the variable? Concatenate? Or return it in
> the select statement as a column? If the latter, change the period to a
> comma.
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> "Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
> news:bj4s3n$kh5$1@.online.de...
> > Hi,
> > I have a User-defined function "Concatenate_NoteTexts" which I use in a
> > query (SQL Server 2000). On my local development machine it is called
> like
> > this:
> > SELECT
> > dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> > FROM tblIntroducers
> > I want to run the same code on a shared remote server where I am user
> "JON"
> > instead of "dbo". I don't want to hard-code the User Name into the SQL,
> but
> > when I tried to put the user name into a variable as here:
> > DECLARE @.USER_NAME VarChar(30)
> > SET @.USER_NAME = USER_NAME()
> > SELECT
> > @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as
NoteTexts
> > FROM tblIntroducers
> > I get the following error:
> > Server: Msg 170, Level 15, State 1, Line 4
> > Line 4: Incorrect syntax near '.'
> > Any advice?
> > TIA,
> > JON
> > PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> > 1|||Jon Maz (jonmaz@.NOSPAM.surfeu.de) writes:
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
> like this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
> "JON" instead of "dbo". I don't want to hard-code the User Name into
> the SQL, but when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers

The question is slightly more interesting than it may look like.

Say that you instead had had a stored procedure, call it notetext_sp.
This would not have constituted any problem, because you could have
called it as:

EXEC notetext_sp

When you are logged in as JON on the remote server, SQL Server would
have found the notetext_sp owned by you. This works for any other
SQL Server object as well. Except scalar user-defined functions, because
you must refer to them with a two-part name. The reason for this is
syntactical, so that the parser can distinguish between UDF and built-in
functions.

However, there is an exception to the exception. This works:

ALTER FUNCTION nisse_fun (@.a int) returns varchar(90) as
BEGIN
RETURN (SELECT replicate('nisse', @.a))
END
go
declare @.g varchar(90)
exec @.g = nisse_fun 8
select @.g

That is you can invoke a scalar UDF with EXEC as well, and in this case
you don't need the two-part name. Whether this actually helps you, I
don't know.

However, as noted by David Browne, getting your database makes life a
lot easier.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

declare syntax in a UDF

Hi, I'm trying to create a function that returns a table, however I want
to use a local variable in there and enterprise manager ain't liking it!

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in
the wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
nvarchar(20))

RETURNS TABLE AS
RETURN

declare @.set nvarchar(150)

select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)

Cheers for any help,
Chris"Not Me" <Noone.is.home@.here.com> wrote in message
news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
> Hi, I'm trying to create a function that returns a table, however I want
> to use a local variable in there and enterprise manager ain't liking it!
> The error I get is number 156 'incorrect syntax near the keyword
> 'declare'.. hopefully this is just a simple thing where I've put it in the
> wrong place.
> The code follows:
> CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
> smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
> nvarchar(20))
> RETURNS TABLE AS
> RETURN
> declare @.set nvarchar(150)
> select "Total Pledged" as info, sum(total) as tot
> FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
>
> Cheers for any help,
> Chris

You seem to be mixing inline and multi-statement syntax. If you just say
RETURN TABLE, then the rest of the function can only be a single SELECT
statement; if you want to use multiple statements in the function, then you
must define the structure of the table you're returning. See the examples in
Books Online under CREATE FUNCTION.

In your function, you haven't defined the structure of the returned table,
so the only thing you can have in the body of the function is a single
SELECT.

Simon|||Simon Hayes wrote:
> "Not Me" <Noone.is.home@.here.com> wrote in message
> news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
>>The error I get is number 156 'incorrect syntax near the keyword
>>'declare'.. hopefully this is just a simple thing where I've put it in the
>>wrong place.
>>
>>The code follows:
>>
>>CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
>>smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
>>nvarchar(20))
>>RETURNS TABLE AS
>>RETURN
>>declare @.set nvarchar(150)
>>select "Total Pledged" as info, sum(total) as tot
>>FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
> You seem to be mixing inline and multi-statement syntax. If you just say
> RETURN TABLE, then the rest of the function can only be a single SELECT
> statement; if you want to use multiple statements in the function, then you
> must define the structure of the table you're returning. See the examples in
> Books Online under CREATE FUNCTION.

Aha! sounds about right, just needed a little shunt in the right
direction.. gonna have nightmares about BOL :p

cheers,
Chris

Declare a local var

Hi,
I am trying to do the following but I am stucked. I have a table
workdb..a (column num), I want to get the maximum number of column num
to store it as a local variable.
TABLE WORKDB..A
num
1
2
3
4
5
DECLARE @.max
Thanks a lot!!!
Michael
Michael,
DECLARE @.max
set @.max = (select max(num) from dbo.a)
-- or
select @.max = max(num) from dbo.a
AMB
"Michael" wrote:

> Hi,
> I am trying to do the following but I am stucked. I have a table
> workdb..a (column num), I want to get the maximum number of column num
> to store it as a local variable.
> TABLE WORKDB..A
> num
> 1
> 2
> 3
> 4
> 5
>
> DECLARE @.max
> Thanks a lot!!!
> Michael
>

Declare a local var

Hi,
I am trying to do the following but I am stucked. I have a table
workdb..a (column num), I want to get the maximum number of column num
to store it as a local variable.
TABLE WORKDB..A
num
1
2
3
4
5
DECLARE @.max
Thanks a lot!!!
MichaelMichael,
DECLARE @.max
set @.max = (select max(num) from dbo.a)
-- or
select @.max = max(num) from dbo.a
AMB
"Michael" wrote:

> Hi,
> I am trying to do the following but I am stucked. I have a table
> workdb..a (column num), I want to get the maximum number of column num
> to store it as a local variable.
> TABLE WORKDB..A
> num
> 1
> 2
> 3
> 4
> 5
>
> DECLARE @.max
> Thanks a lot!!!
> Michael
>|||On Apr 9, 12:10 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> Michael,
> DECLARE @.max
> set @.max = (select max(num) from dbo.a)
> -- or
> select @.max = max(num) from dbo.a
> AMB
>
> "Michael" wrote:
>
>
>
>
> - Show quoted text -
Great!! Thank you!!!

Declare a local var

Hi,
I am trying to do the following but I am stucked. I have a table
workdb..a (column num), I want to get the maximum number of column num
to store it as a local variable.
TABLE WORKDB..A
num
1
2
3
4
5
DECLARE @.max
Thanks a lot!!!
MichaelMichael,
DECLARE @.max
set @.max = (select max(num) from dbo.a)
-- or
select @.max = max(num) from dbo.a
AMB
"Michael" wrote:
> Hi,
> I am trying to do the following but I am stucked. I have a table
> workdb..a (column num), I want to get the maximum number of column num
> to store it as a local variable.
> TABLE WORKDB..A
> num
> 1
> 2
> 3
> 4
> 5
>
> DECLARE @.max
> Thanks a lot!!!
> Michael
>

Debugging a stored procedure

When I try to debug stored procedure in SQL Server 2000 Query Analyzer I got error message

SP debugging may not work propertly if you log as 'Local system account' while SQL Server is configured to run as service. You can open Event Viwer to see details ...

After this msgbox I press F5 to start and got error

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on COMPAQ-NX6325\PRERADA_MESA (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on COMPAQ-NX6325. Debugging disabled for connection 55.

What I have to do to work with debugerWhen I try to debug stored procedure in SQL Server 2000 Query Analyzer I got error message

SP debugging may not work propertly if you log as 'Local system account' while SQL Server is configured to run as service. You can open Event Viwer to see details ...

After this msgbox I press F5 to start and got error

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on COMPAQ-NX6325\PRERADA_MESA (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on COMPAQ-NX6325. Debugging disabled for connection 55.

What I have to do to work with debuger|||try this code in sql new query editor & execute

sp_Configure 'clr enabled',1
Go
Reconfigure
Go|||This belongs in the SQL Server forum, so this thread will be moved.

moderator|||Please do not double post.


Changed title and merged duplicate threads.

Debuggin in 2005

Can you debug on a local install in 2005?

NO, you must use Visual Studio 2005 w/SQL Server 2005 to obtain debugging capabilities for tsql, clr, SSIS, and others.

you should be able to use just the Business Intelligence Development Studio (comes with sql server 2005) to debug locally though w/o the full blown VS 2005 product. Select View/Server Explorer, connect to your local sql server, expand stored procs and select step into etc.

|||i doubt (never tried it) that the new advanced Express edition's BI studio has debugging but you could try it. If your not using Express your "golden" as you have BI studio allready. Regardless, you can navigate to the below link for http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx on the BI Studio and other features per edition of SQL Server 2005.|||did this help?

Debug SProcs

I have both SQL2005 and Express installed on my local development machine. I
am able to step in / debug stored procedures in dbs on Express just fine.
Stepping into a stored procedure from within Visual Studio 2005 under the
full version of SQL on the local machine pops up the "run stored procedure"
dialog box under which I enter my parameters but then things just seem to
hang, never reaching my breakpoint. When I click on the "Stop Debugging"
button, control is returned to VS.
Any ideas?
The output window contains the following:
Auto-attach to process '[1168] [SQL] webdev1' on machine 'webdev1'
succeeded.
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
Running [dbo].[CartToOrder] ( @.BrowserID = 5).
(Click on the stop debugging button).
The thread 'webdev1 [56]' (0xdd0) has exited with code 0 (0x0).
The program '[1168] [SQL] webdev1: webdev1' has exited with code 0 (0x0).
Batch execution is terminated because of debugger request.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[CartToOrder].
Andrew RobinsonHello Andrew,
You may want to run the SP directly in SSMS to see if it hangs. Also, you
may try to create a new simple SP on a new database to test the situation.
To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroup. I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.