Getting the last Error Message

I have a process that runs that pulls data from an Oracle database to SQL
server, when I encounter an error I'd like to write the error description
back to Oracle for the record that failed for reporting purposes. I can get
the code using @.@.ERROR but I'd like to have the message associated with this
error code populated with the field and table name and operation associated
with the error, example: Error Code 515, here's the associated message from
sysmessages: Cannot insert the value NULL into column '%.*ls', table '%.*ls'
;
column does not allow nulls. %ls fails.
I want the completed message that looks like this: Cannot insert the value
NULL into column 'vm2_location', table 'Intergy.dbo.vmailto'; column does no
t
allow nulls. UPDATE fails.
How can I get this...?
Thanks in advance..
DanIt's probably not what you are looking for, but DBCC OUTPUTBUFFER() will
contain the error message that is sent to the client. Unfortunately, I can't
think of any meaningful way of formatting it.
On the bright side, SQL Server 2005 has an ERROR_MESSAGE function which can
be used to capture the error message within a TRY..CATCH block.
"Alien2_51" wrote:

> I have a process that runs that pulls data from an Oracle database to SQL
> server, when I encounter an error I'd like to write the error description
> back to Oracle for the record that failed for reporting purposes. I can ge
t
> the code using @.@.ERROR but I'd like to have the message associated with th
is
> error code populated with the field and table name and operation associate
d
> with the error, example: Error Code 515, here's the associated message fro
m
> sysmessages: Cannot insert the value NULL into column '%.*ls', table '%.*l
s';
> column does not allow nulls. %ls fails.
> I want the completed message that looks like this: Cannot insert the value
> NULL into column 'vm2_location', table 'Intergy.dbo.vmailto'; column does
not
> allow nulls. UPDATE fails.
> How can I get this...?
> Thanks in advance..
> Dan|||The later sounds a lot nicer, nonetheless OUTPUTBUFFER is still an option,
thanks for the reply!!!
Dan
"Mark Williams" wrote:
> It's probably not what you are looking for, but DBCC OUTPUTBUFFER() will
> contain the error message that is sent to the client. Unfortunately, I can
't
> think of any meaningful way of formatting it.
> On the bright side, SQL Server 2005 has an ERROR_MESSAGE function which ca
n
> be used to capture the error message within a TRY..CATCH block.
> "Alien2_51" wrote:
>

Getting system time from SQL

Hi All
Does anyone know how to call the current system time
(using transact) and then write that data into a table?
Thanks
JUPDATE table SET curtime = CURRENT_TIMESTAMP
OR
UPDATE table SET curtime = GETDATE()
Kurt
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:10fc01c3b3a7$32a74840$a001280a@.phx.gbl...
> Hi All
> Does anyone know how to call the current system time
> (using transact) and then write that data into a table?
> Thanks
> J

Getting SCHEMA name from sys views

Hello,
I am trying to write a query that will generate the script to drop a
constraint. The problem is that I need to get the SCHEMA name from one of
the system views to join with the query below.
This query produces the column schema_id
SELECT * FROM sys.tables
Any help would be appreciated.
sck10
SELECT
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
WHERE (Tsyso.type = 'D')Thanks, but I think I figured it out:
SELECT
Tschema.name AS 'Schema Name',
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
INNER JOIN sys.schemas Tschema
ON Tsyst.schema_id = Tschema.schema_id
WHERE (Tsyso.type = 'D')
"sck10" <sck10@.online.nospam> wrote in message
news:eWQDGA$QHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am trying to write a query that will generate the script to drop a
> constraint. The problem is that I need to get the SCHEMA name from one of
> the system views to join with the query below.
> This query produces the column schema_id
> SELECT * FROM sys.tables
> Any help would be appreciated.
> sck10
> SELECT
> Tsyst.schema_id,
> Tsyst.name AS 'TableName',
> Tsyst.object_id AS 'ObjectID',
> Tsyso.name AS 'DefaultName',
> Tsyso.type_desc AS 'TypeDscr',
> 'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
> FROM
> sys.tables Tsyst
> INNER JOIN sys.objects Tsyso
> ON Tsyst.object_id = Tsyso.parent_object_id
> WHERE (Tsyso.type = 'D')
>

Getting SCHEMA name from sys views

Hello,
I am trying to write a query that will generate the script to drop a
constraint. The problem is that I need to get the SCHEMA name from one of
the system views to join with the query below.
This query produces the column schema_id
SELECT * FROM sys.tables
Any help would be appreciated.
sck10
SELECT
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
WHERE (Tsyso.type = 'D')Thanks, but I think I figured it out:
SELECT
Tschema.name AS 'Schema Name',
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
INNER JOIN sys.schemas Tschema
ON Tsyst.schema_id = Tschema.schema_id
WHERE (Tsyso.type = 'D')
"sck10" <sck10@.online.nospam> wrote in message
news:eWQDGA$QHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am trying to write a query that will generate the script to drop a
> constraint. The problem is that I need to get the SCHEMA name from one of
> the system views to join with the query below.
> This query produces the column schema_id
> SELECT * FROM sys.tables
> Any help would be appreciated.
> sck10
> SELECT
> Tsyst.schema_id,
> Tsyst.name AS 'TableName',
> Tsyst.object_id AS 'ObjectID',
> Tsyso.name AS 'DefaultName',
> Tsyso.type_desc AS 'TypeDscr',
> 'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
> FROM
> sys.tables Tsyst
> INNER JOIN sys.objects Tsyso
> ON Tsyst.object_id = Tsyso.parent_object_id
> WHERE (Tsyso.type = 'D')
>

Getting SCHEMA name from sys views

Hello,
I am trying to write a query that will generate the script to drop a
constraint. The problem is that I need to get the SCHEMA name from one of
the system views to join with the query below.
This query produces the column schema_id
SELECT * FROM sys.tables
Any help would be appreciated.
sck10
SELECT
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
WHERE (Tsyso.type = 'D')
Thanks, but I think I figured it out:
SELECT
Tschema.name AS 'Schema Name',
Tsyst.schema_id,
Tsyst.name AS 'TableName',
Tsyst.object_id AS 'ObjectID',
Tsyso.name AS 'DefaultName',
Tsyso.type_desc AS 'TypeDscr',
'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
FROM
sys.tables Tsyst
INNER JOIN sys.objects Tsyso
ON Tsyst.object_id = Tsyso.parent_object_id
INNER JOIN sys.schemas Tschema
ON Tsyst.schema_id = Tschema.schema_id
WHERE (Tsyso.type = 'D')
"sck10" <sck10@.online.nospam> wrote in message
news:eWQDGA$QHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am trying to write a query that will generate the script to drop a
> constraint. The problem is that I need to get the SCHEMA name from one of
> the system views to join with the query below.
> This query produces the column schema_id
> SELECT * FROM sys.tables
> Any help would be appreciated.
> sck10
> SELECT
> Tsyst.schema_id,
> Tsyst.name AS 'TableName',
> Tsyst.object_id AS 'ObjectID',
> Tsyso.name AS 'DefaultName',
> Tsyso.type_desc AS 'TypeDscr',
> 'ALTER TABLE schemaname.' + Tsyst.name + ' DROP CONSTRAINT ' + Tsyso.name
> FROM
> sys.tables Tsyst
> INNER JOIN sys.objects Tsyso
> ON Tsyst.object_id = Tsyso.parent_object_id
> WHERE (Tsyso.type = 'D')
>

Getting Return Value from system SPROC

I'm trying to learn how to write/use system sprocs for the 1st time. SYSTEM
SPROC below is a working sproc I have in the master db that returns a 1 if a
file exists and 0 if it doesn't. If I run QA CODE 1, my SYSTEM SPROC returns
the correct 1 or 0.
However, if I use my sproc as in QA CODE 2 to act on the returned value, I
get syntax errors.
How can I write QA CODE 2 so I can use an IF statement to test and act on
the returned value of my SYSTEM SPROC?
-- SYSTEM SPROC ************
CREATE procedure [dbo].[usp_FileExists]
@.physname nvarchar(260)
as
SET nocount ON
DECLARE @.i int
EXEC master..xp_fileexist @.physname,@.i out
SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END
-- QA CODE 1 ****************
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
-- QA CODE 2 ****************
IF EXEC master.dbo.usp_FileExists @.physname =
'D:\data\sql_data\myData_Data.MDF' = 0
PRINT 'Does not Exist 'You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||I need a little more explanation. I tried your code on my "System Sproc" and
it just returns the correct 1 or 0, not the PRINT message. So why would the
last IF test be ignored?
Should my "System Sproc" be an extended system Sproc?
If you know of any books that teach system and extended sprocs please let me
know. I'd really like to learn things like when I have to use a function vs.
sproc.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a
> function.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You do NOT want a 'extended' stored procedure. That is a different animal to
tally.
---
Here is a slightly revised version of the FileExists function (master..xp_Fi
leExist is an 'UnDocumented' system function. There is no guarantee it will
be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be co
nsistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx
.gbl...
>I need a little more explanation. I tried your code on my "System Sproc" an
d
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
> Should my "System Sproc" be an extended system Sproc?
>
> If you know of any books that teach system and extended sprocs please let
me
> know. I'd really like to learn things like when I have to use a function v
s.
> sproc.
>
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>
>|||thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OeixErImGHA.4696@.TK2MSFTNGP05.phx.gbl...
You do NOT want a 'extended' stored procedure. That is a different animal
totally.
---
Here is a slightly revised version of the FileExists function
(master..xp_FileExist is an 'UnDocumented' system function. There is no
guarantee it will be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be
consistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I need a little more explanation. I tried your code on my "System Sproc"
>and
> it just returns the correct 1 or 0, not the PRINT message. So why would
> the
> last IF test be ignored?
> Should my "System Sproc" be an extended system Sproc?
> If you know of any books that teach system and extended sprocs please let
> me
> know. I'd really like to learn things like when I have to use a function
> vs.
> sproc.
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>|||scott wrote:
> I need a little more explanation. I tried your code on my "System Sproc" a
nd
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
I think you ran all of the code together, so that the print statement is
inside the function. Try this instead:
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF' ,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
Run that, then run this:
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '

> Should my "System Sproc" be an extended system Sproc?
>
NO!!!

Getting Records Inserted in the Last(??)

Hi Everyone,
I am trying to write a stored proceedure, that will return records (from
2 Tables) where the last updated date was within the last hour.
Here is my query so far:
Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
If anyone could help, that would be great.
ThanksHello,
Try the below query:-
Select LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where Datediff(hh,getdate(),ua.LastAction)<=1
Thanks
Hari
"Mick Walker" <Mick.Walker@.privacy.net> wrote in message
news:5cg5qlF30ifdqU1@.mid.individual.net...
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from 2
> Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks|||On 3 Jun, 16:42, Mick Walker <Mick.Wal...@.privacy.net> wrote:
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from
> 2 Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks
...
WHERE ua.LastAction > DATEADD(HOUR,-1,CURRENT_TIMESTAMP);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Getting Records Inserted in the Last(??)

Hi Everyone,
I am trying to write a stored proceedure, that will return records (from
2 Tables) where the last updated date was within the last hour.
Here is my query so far:
Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
If anyone could help, that would be great.
ThanksHello,
Try the below query:-
Select LastAction as LastActivity, Title
From dbo.Access a
Join UserActions ua
on a.ID = ua.ID
Join News n
on a.Username = n.Poster
Where Datediff(hh,getdate(),ua.LastAction)<=1
Thanks
Hari
"Mick Walker" <Mick.Walker@.privacy.net> wrote in message
news:5cg5qlF30ifdqU1@.mid.individual.net...
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from 2
> Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks|||On 3 Jun, 16:42, Mick Walker <Mick.Wal...@.privacy.net> wrote:
> Hi Everyone,
> I am trying to write a stored proceedure, that will return records (from
> 2 Tables) where the last updated date was within the last hour.
> Here is my query so far:
> Select a.ID, UserName, JoinDate, LastAction as LastActivity, Title
> From dbo.Access a
> Join UserActions ua
> on a.ID = ua.ID
> Join News n
> on a.Username = n.Poster
> Where ua.LastAction < DateDiff(hour,GetDATE()-1, GETDATE())
> If anyone could help, that would be great.
> Thanks
...
WHERE ua.LastAction > DATEADD(HOUR,-1,CURRENT_TIMESTAMP);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Getting Network User Names

Can you write a query to get all the network user names?
Thanks
Don
Don wrote:
> Can you write a query to get all the network user names?
> Thanks
> Don
Get's them from where? What version of SQL Server? Do you mean those
network users who are currently logged into SQL Server, or do you mean a
query of Active Directory to query all users?
For SQL 2000: You can query the master..sysprocesses table (nt_username
column) for all connected users that have connected using Windows
Authentication. Otherwise, you can use the loginname for SQL
Authentication.
For SQL 2005: You can query the sys.dm_exec_sessions dmv.
David Gugick - SQL Server MVP
Quest Software
|||If you want to get list of all the accounts, you can do this
by creating a linked server to Active Directory. See books
online topic:
OLE DB Provider for Microsoft Directory Services
The following article has more information and links:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Sun, 12 Mar 2006 08:46:02 -0800, Don
<Don@.discussions.microsoft.com> wrote:

>Can you write a query to get all the network user names?
>Thanks
>Don

Getting Network User Names

Can you write a query to get all the network user names?
Thanks
DonDon wrote:
> Can you write a query to get all the network user names?
> Thanks
> Don
Get's them from where? What version of SQL Server? Do you mean those
network users who are currently logged into SQL Server, or do you mean a
query of Active Directory to query all users?
For SQL 2000: You can query the master..sysprocesses table (nt_username
column) for all connected users that have connected using Windows
Authentication. Otherwise, you can use the loginname for SQL
Authentication.
For SQL 2005: You can query the sys.dm_exec_sessions dmv.
David Gugick - SQL Server MVP
Quest Software|||If you want to get list of all the accounts, you can do this
by creating a linked server to Active Directory. See books
online topic:
OLE DB Provider for Microsoft Directory Services
The following article has more information and links:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Sun, 12 Mar 2006 08:46:02 -0800, Don
<Don@.discussions.microsoft.com> wrote:

>Can you write a query to get all the network user names?
>Thanks
>Don

Getting Network User Names

Can you write a query to get all the network user names?
Thanks
DonDon wrote:
> Can you write a query to get all the network user names?
> Thanks
> Don
Get's them from where? What version of SQL Server? Do you mean those
network users who are currently logged into SQL Server, or do you mean a
query of Active Directory to query all users?
For SQL 2000: You can query the master..sysprocesses table (nt_username
column) for all connected users that have connected using Windows
Authentication. Otherwise, you can use the loginname for SQL
Authentication.
For SQL 2005: You can query the sys.dm_exec_sessions dmv.
David Gugick - SQL Server MVP
Quest Software|||If you want to get list of all the accounts, you can do this
by creating a linked server to Active Directory. See books
online topic:
OLE DB Provider for Microsoft Directory Services
The following article has more information and links:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Sun, 12 Mar 2006 08:46:02 -0800, Don
<Don@.discussions.microsoft.com> wrote:
>Can you write a query to get all the network user names?
>Thanks
>Don

Getting errors I dont understand within Create Function

Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@.mNumInput as charvar(100))
RETURNS numeric
AS
BEGIN
declare @.x as tinyint
declare @.x1 as tinyint
SET @.x = 1
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1))
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE
END
If @.x1 > 0
BEGIN
RETURN CAST(LEFT(@.mNumInput, @.x1), Numeric
END
END
Here are the two error messages I'm getting from this function.Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 12
Incorrect syntax near the keyword 'BEGIN'.
Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 20
Incorrect syntax near the keyword 'END'. I have no idea what these two error messages mean.
TIA,Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@.mNumInput as charvar(100))
RETURNS numeric
AS
BEGIN
declare @.x as tinyint
declare @.x1 as tinyint
SET @.x = 1
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1))
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE
END
If @.x1 > 0
BEGIN
RETURN CAST(LEFT(@.mNumInput, @.x1), Numeric
END
END
Here are the two error messages I'm getting from this function. I have no idea what these two error messages mean.
TIA,

The error messages mean you have syntax errors in your function definition.

You've got a few issues:

1. there's no such type as charvar. I think you mean varchar
2. a while condition needs to evalulate to a boolean. but isnumeric returns 1 or 0 (an int). so you need this: WHILE IsNumeric(SubString(@.mNumInput, @.x, 1)) = 1
3. your cast at the end has syntax errors.
4. you don't return a value at the end if the IF isn't satisfied. all functions must return a value. Here's your corrected function:

create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@.mNumInput as varchar(100))
RETURNS numeric
AS
BEGIN
declare @.x as tinyint
declare @.x1 as tinyint
SET @.x = 1
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1)) = 1
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE -- continue isn't necessary here, but whatever.
END
If @.x1 > 0
BEGIN
RETURN CAST(LEFT(@.mNumInput, @.x1) as Numeric)
END
return -1 -- if no digits found at the beginning of the string
END

sample usage:

select dbo.NumValue('asdf') -- returns -1
select dbo.NumValue('555asdf') -- returns 555|||jezemine,
Thank you VERY much for your reply. I'm a VBA programmer, so this T-SQL coding language is still a little awkward, therefore, this question:
WHILE IsNumeric(SubString(@.mNumInput, @.x, 1)) = 1
BEGIN
SET @.x1 = @.x
SET @.x = @.x + 1
CONTINUE -- continue isn't necessary here, but whatever.
END
You said the Continue is not necessary here, but I thought the Continue was the "Loop back to the Where statement" command. How else do you get the "Where" to loop?
Thanks for your patience!|||it will loop from the point where you have END.

you only use continue if you want to jump back to the beginnign of the loop before END, that is, if you have statements between continue and end that you don't want to be executed on a particular pass thru the loop.

Certainly they have while loops in VB, don't they?|||Of course there are loops in VB, but with differing syntax. Within VB, there is no equivilent to "Continue" in T-SQL. Rather than using a command statement such as "Continue", in VB if there are lines after a certain point that should not be executed, that would be done with an If statement. There is only one Loop point in VB. So, this was not a conceptual question (what is a loop?) but a syntax question because of a new language. And, this is the first language for me that uses the concept of a Begin/End I have seen enough of Java and C to know that both of them have that same concept, but as far as writing in a language that uses this concept, this is new and different.
Thanks a ton for your help and I will be asking some more questions, soon.|||so in VB they have while loops, but no way to go back to the beginning of the loop early?

I know nothing of VB, my question was an honest one. :)|||Sample VB While Loop:
While TestIsTrue
i = i + 1
If i > 5 then
j = j + 2
End If
WEnd 'End of While, therefore loop until TestIsTrue becomes equal to False.
Same While loop in T-SQL (as I understand it)
While @.TestIsTrue
Begin
@.i = @.i + 1
If @.i <= 5 Continue
@.j = @.j + 2
End

I'm not sure I got the T-SQL syntax correct, but I tried. :)
And yes, I know there is no way out of my loop, but I really don't think it was that important.|||i see.

In T-SQL, it's more similar to what you have in C/C++ (you can use continue to jump to the top, or break to break out)

For flow control in sql, a good resource is BOL:

http://msdn2.microsoft.com/en-us/library/ms189826.aspx
http://msdn2.microsoft.com/en-us/library/ms178642.aspx (funny thing about the first example on this page is they have a noop continue just like you had :))