How to capture What time zone by DB Server is using?

I need to capture the time zone of the DB server and adjust some date columns in some of my tables to make sure all the dates are using PST.

My database is distributed into different smaller databases (example into laptops, PDAs etc..) around the world.

End of day I will sync all the databases in PST time zone. Is there a command in SQL to capture the timezone.

Thanks

The query below will give the offset:

select datediff(hour, GETUTCDATE(), CURRENT_TIMESTAMP)

You can map this to a table that contains the various timezones (pre-defined) and use it in your queries.

|||

Is there any way to know if the timezone supports daylight saving time? This gets the current offset, but not the timezone meta data.

|||

Thanks for the quick response. This will help me to start working on my code.

Thanks for your time.

|||Not without reading the registry or writing extended SP or calling some OS utility or SQLCLR code. But it is very easy to build a TimeZone dimension/table that contains all the predefined offsets and attributes. You can then use the obtained offset to query the TimeZone table to get the rest of the details.|||That's what I thought. Thanks!

How to Capture variables values in a Profiler trace

I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.
No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>
|||Don't think so. Have you looked into the SQL Debugger?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>
|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
queries[vbcol=seagreen]
variables
>

How to Capture variables values in a Profiler trace

I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
queries[vbcol=seagreen]
variables[vbcol=seagreen]
>

How to Capture variables values in a Profiler trace

I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > I have the variables that I pass to the SP, but there are several
queries
> > within the SP that get other variables from the tables being used by it.
> >
> > Is there a way to create a SQL 2000 trace that will capture the
variables
> > being used within the stored procedure?
> >
> > Thanks.
> >
> >
>

How to capture the sql query

Hi
can someone explain how the profiler get the queries that are fired against the database. This information will be useful for building a customized trace tool.
ThanksRefer to Inside SQL Server book from Kalen Daleney and also you may get better information from MS Support about designing such a tool.

HTH|||look at the sp_trace_xxxxxx stored procedures

How to capture the only "time" into the database

I've a textbox that displays the current time in this format "hh:mm:ss tt" but when it is save into the database it'll display the date and time together. So how do I save only the time into the database? My codes is as shown below:

txtTime.Text = DateTime.Now.ToLongTimeString()

Dim

parameterDateAs SqlParameter =New SqlParameter("@.Date_5",SqlDbType.DateTime)

parameterDate.Value = txtDate.Text

objCommand.Parameters.Add(parameterDate)

I've tried using Format() but it still get the same results. Can someone help me out? Thanks!

The SQL Datetime datatype will always store a date portion. as effectively all you are doing is storing a time difference since an initial base date/time.

Perhaps change your SQL datatype to varchar and store the time as a string, or else use a constant date along with your time value (IE 1/1/1900).

Dave

How to capture the error message?

Is possible to capture the message of error generated in the execution
of a command SQL?
Thanks.DECLARE@.Error

..sql statement

SELECT @.Error = @.@.ERROR

SELECT *
FROM master..sysmessages
WHERE error = @.Error|||I thank its reply, but I want to capture the specific message of the error and not it generic message. For example:

"Cannot insert the value NULL into column 'field1', table 'DB.dbo.Tab'; column does not allow nulls. INSERT fails."

instead of :

"Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails."

DECLARE@.Error

..sql statement

SELECT @.Error = @.@.ERROR

SELECT *
FROM master..sysmessages
WHERE error = @.Error

How to capture the entire DML statement in a DML trigger?

Hi,

In Yukon, is it possible to capture the entire DML statement with the parameter values that triggered the DML trigger inside the trigger body?

Basically trying to see the equivalent as the eventdata() in a DDL trigger, that provides the CommandText()

Rgds

No. This is not possible.|||Thanks for the quick reply UJ.
Was just trying to create a detailed Audit trace. (DDL, DML, App Login & Logout)

Can a CLR Trigger extract such data from the SQL Profiler - say using SMO in Yukon etc. - i'm not sure.
Though this would make it a very heavy trigger, but then we can run it async.

Rgds|||SMO cannot be used within CLR trigger right now. It is not supported. Also, using profiler to do these type of actions from trigger code is not ideal. Triggers should usually be very light weight and efficient. The more complex logic that you put inside your trigger the harder it is in terms of performance, development and management. Also, I am not sure how useful it will be to know which statement actually caused the trigger to fire. Note that this might not be very straight-forward too. For example, the update trigger might be fired by UPDATE statement from different SPs or triggers even. In this case, it might be useful to know the SP call that fired the trigger indirectly. What you are asking for is a call / stack trace which is not available in T-SQL now.|||Thanks UJ.
Shall keep you posted on this.

Can we expect SMO in triggers/ Call stack in Yukon?

How to capture the correct identity value

I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.

blumonde

Have you tried this?

SELECT SCOPE_IDENTITY()

|||

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

|||

Thanks for your response, gentlemen.

I tried 'Select @.getKEY = @.@.IDENTITY' right after the first insert and it seems to work pretty good so far. I hope I am doing it the right way. However, I am not sure my method is consistant when several users inserting at the same time.

I think it is better to use 'scope_identity' and follow Motley's method above. I think it can handle multi-tasking better than my method.

Motley, what is the Select below for? I have to use it after the second insert?

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||

Motley:

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

Motley, what is the Select below for? I have to use it after the second insert? Thanks.

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.|||

Motley:

The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.

Thank you.

blumonde

How to capture return value from 'execute'

declare @.table varchar(100);

declare @.q varchar(100);

declare @.key bigint;

select @.table = 'key_table';

select @.q = 'select key from ' + @.table;

select @.key = exec(@.q); -> not working.

Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||

In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.

Thanks,

|||

create a temp table and use this type

insert into #tempTable

exec ( @.yourExecString )

Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.

|||

Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.

declare @.table sysname;

declare @.q nvarchar(100);

declare @.key bigint;

select @.table = N'key_table';

select @.q = 'select @.key = key from dbo.[' + @.table + N']';

exec sp_executesql @.q, N'@.key bigint output', @.key output;

select @.key

go

Be careful with sql injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

How to capture result from READTEXT?

In the continuing saga of dealing with blobs . . .
READTEXT is a fine function, but useless by itself. I need to return the
results of READTEXT into a varchar column but have been unable to do so or
even find a similar example. Displaying the results in Query Analyzer is all
the example MS provides.
I need something like the following:
Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
@.chunkindex @.chunksize
Any suggestions?
MichaelPlease don't start new threads, there is already a thread about this you
started half an hour ago.
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:DE98D97E-C3CF-44DB-8C13-1747CD3194EF@.microsoft.com...
> In the continuing saga of dealing with blobs . . .
> READTEXT is a fine function, but useless by itself. I need to return the
> results of READTEXT into a varchar column but have been unable to do so or
> even find a similar example. Displaying the results in Query Analyzer is
> all
> the example MS provides.
> I need something like the following:
> Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
> @.chunkindex @.chunksize
> Any suggestions?
> Michael|||> READTEXT is a fine function, but useless by itself. I need to return the
> results of READTEXT into a varchar column
I'm not sure I understand this. What exactly are you trying to accomplish
(e.g. forget about READTEXT for a minute, what are you going to actually DO
with each of these chunks)?|||Snake wrote:
> In the continuing saga of dealing with blobs . . .
> READTEXT is a fine function, but useless by itself. I need to return
> the results of READTEXT into a varchar column but have been unable to
> do so or even find a similar example. Displaying the results in Query
> Analyzer is all the example MS provides.
> I need something like the following:
> Select @.string = READTEXT tblFLC_Catalog.longDescription @.textptr
> @.chunkindex @.chunksize
> Any suggestions?
> Michael
How about querying the column directly using a SELECT statement?
Declare @.MyText VARCHAR(1000)
Select
@.MyText = SUBSTRING(pr_info, 1, 100)
From
pubs.dbo.pub_info
Where
pub_id = '0736'
Select @.MyText
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Aaron,
If you really read my orignal posting you will see that while these postings
are related, this one is more specific and focused on how to use the
READTEXT. Since your own responses to each of my postings is different, at
least on some level even you see them differently.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Please don't start new threads, there is already a thread about this you
> started half an hour ago.
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:DE98D97E-C3CF-44DB-8C13-1747CD3194EF@.microsoft.com...
>
>|||David,
Thanks for your response. I believe I read in BOL that for ntext columns
over 4000 characters one must use the READTEXT function. My data is longer
than 4000, therefore I must use READTEXT to parse my data and return it to m
y
program in, say, 1k chunks. Is this an incorrect approach?
I am warry of the pubs examples and sample code because they always assume
the most simplistic situations and from my point-of-view ignore much of the
real-world. One must always read the associated text in BOL to find the
gotchas!
Have a very nice 3-day wend.
"David Gugick" wrote:

> Snake wrote:
> How about querying the column directly using a SELECT statement?
> Declare @.MyText VARCHAR(1000)
> Select
> @.MyText = SUBSTRING(pr_info, 1, 100)
> From
> pubs.dbo.pub_info
> Where
> pub_id = '0736'
> Select @.MyText
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Actually, my reply here was meant for the original thread, and applies
equally to both. They are still both dealing with how to get a chunk of
text from readtext into a varchar variable. There is no reason to carry on
multiple distinct conversations about the same thing.

> Aaron,
> If you really read my orignal posting you will see that while these
> postings
> are related, this one is more specific and focused on how to use the
> READTEXT. Since your own responses to each of my postings is different, at
> least on some level even you see them differently.|||> Thanks for your response. I believe I read in BOL that for ntext columns
> over 4000 characters one must use the READTEXT function. My data is
> longer
> than 4000, therefore I must use READTEXT to parse my data and return it to
> my
> program in, say, 1k chunks. Is this an incorrect approach?
Well, did you TRY using SUBSTRING? Did you try the code samples that have
been posted?
Also, why would you want to use 1k chunks? Didn't you say you wanted to
stuff it into an NVARCHAR variable or column? If so, then why not use 4k
chunks? And if all you are doing is returning the data to your program, why
not just say SELECT NTextColumn FROM table, the program shouldn't have
display limitations like Query Analyzer (8,192 characters).
A

How to capture Package Validation Error?

Hi,

I have a package which has 5 connection managers. One of the Connection Manager has incorrect server name, which results in Package Validation error. Which event handler should be used to run on such errors for OnError Event handler doesnt work @. all.

Thanks

Prasad

I may be wrong; but I think if the validation step fails; the package logging nor event handler would work as they need a valid package. What you can do is to capture the output of the package execution command line. Eg if you are using sql server agent; and run the package as CmdExec step; you will see the validation error in the job log.|||

i dont see any errors in the job log which point out to invalid packages.

Is there any metadata table like sysdtspackages90 which can be queried to find out if the generated packages are valid or not?

|||You need to run the package through a CmdExec step, not from a SSIS step, as Rafael mentioned. This is the same as running it from the command line.|||using dtexec /validate we can validate the packages|||

2lazydba wrote:

using dtexec /validate we can validate the packages

Well now THERE is something I never knew. Thanks 2lazydba.

-Jamie

How to capture out param?

I'd like to execute a stored procedure that needs 5 parameters. One of
them is an OUTPUT parameter. What does the
exec sp_name...
code, executed in QA SQL Server 2000, look like for this? Also, if the
OUTPUT parameter is declared last in the spoc, how can I call it by
name as the first parameter in my exec code?
Thanks,
BrettAssuming you have 5 parameters & the 5th one is an OUTPUT paramter, you can
call the procedure like:
EXEC usp @.p1, @.p2, @.p3, @.p4, @.p5 OUT
SELECT @.p5 ;
I know what you posted is just a sample, but in case hadn't noted, avoid
using sp_ prefix for stored procedures sicen they have certain unfavorable
implications.
Anith

how to capture o/p of SELECT .. FOR XML AUTO.

Hi,
Can u please tell me how can i store o/p of following query into a variable
and process it.
"SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
Thanks
Gopinath M.
"Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can u please tell me how can i store o/p of following query into a
variable
> and process it.
> "SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
You can't store the results of a for xml query just within SQL. The XML is
created by the provider.
Bryant
|||There are some ugly workarounds using the sp_OA stored procedures.
In SQL Server 2005 (beta2 comming soon, watch this space for nomination
registration), you will be able to do so.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:Ogsvi70EEHA.3568@.tk2msftngp13.phx.gbl...
> "Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
> news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> variable
> You can't store the results of a for xml query just within SQL. The XML is
> created by the provider.
> --
> Bryant
>

How to capture error upon connect error?

I would like to continue processing when a connect error occurs.

Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect oName

Would like to continue if the connect fails rather than have the script fail. How do I do that?

Thanks!

Add a Try-Catch construct to your code:

Try
oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect(oName)
Catch ex As Exception
MsgBox(ex.Message)
End Try

How to capture error upon connect error?

I would like to continue processing when a connect error occurs.

Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect oName

Would like to continue if the connect fails rather than have the script fail. How do I do that?

Thanks!

Add a Try-Catch construct to your code:

Try
oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect(oName)
Catch ex As Exception
MsgBox(ex.Message)
End Try

How to capture Error Messages in script

I know how to capture the error number using @.@.ERROR, but I don't know how
to capture the actual text. It is easy to do so via an application that uses
a database driver (BDE, ADO .NET etc...), but I just want to capture the
error text in SQL script and log it to a table.
Please Note:
'select description from master.dbo.sysmessages' alone is not sufficientThe current version of SQL Server does not provide a method to get error
message text in Transact-SQL. This functionality is planned for SQL 2005,
though.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <nospam@.nospam.com> wrote in message
news:eRXqGcYjFHA.3448@.TK2MSFTNGP10.phx.gbl...
>I know how to capture the error number using @.@.ERROR, but I don't know how
> to capture the actual text. It is easy to do so via an application that
> uses
> a database driver (BDE, ADO .NET etc...), but I just want to capture the
> error text in SQL script and log it to a table.
> Please Note:
> 'select description from master.dbo.sysmessages' alone is not sufficient
>

How to Capture data from server side trace to file?

Hi,
I want to automate tracing for SQL server Profiler. I have written a set of
stored procedures using the SQL Server in-built sp's for the very purpose.
A trace is also being successfully created with trace id and a
file(MyTrace.trc) is also created however the trace is not writing anything
into the file MyTrace.trc
I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
and when accessing info of the trace using
SELECT *
FROM ::fn_trace_getinfo(@.TraceId)
it shows the correct file path and status as 1
Could some please help me out in this.
What else am i required to do for enabling server side trace and so that the
capture sql traces are written into the file.
Regards
Shajee
The easiest way to make sure you're on the right track is to use the
Profiler tool, get a trace running that is capturing what you want, stop it
and the script it (File>Script Trace). Then run the generated script and
stop it and check you have results. If this is all working okay then check
your code and make sure you don't have any filters present that might be
interfering with the capture. In SQL2000 you need to both stop the
serverside trace and delete its definition from the server in order to open
it correctly using Profiler or fn_trace_gettable. In SQL2005 this is no
lonfer the case and you can access a running server side trace. Are you
actually getting an error or is it simply that the trace file is empty. If
it is truly empty then it's likely an issue with your code - double check it
against the script generated by Profiler
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"msnews.microsoft.com" <ahmadshajee@.hotmail.com> wrote in message
news:OT5zGaS6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to automate tracing for SQL server Profiler. I have written a set
> of
> stored procedures using the SQL Server in-built sp's for the very purpose.
> A trace is also being successfully created with trace id and a
> file(MyTrace.trc) is also created however the trace is not writing
> anything
> into the file MyTrace.trc
> I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
> and when accessing info of the trace using
> SELECT *
> FROM ::fn_trace_getinfo(@.TraceId)
> it shows the correct file path and status as 1
> Could some please help me out in this.
> What else am i required to do for enabling server side trace and so that
> the
> capture sql traces are written into the file.
> Regards
> Shajee
>
|||Writes to the trace file happen in 128K chunks. Are you sure you've
generated that many events?
Try stopping and closing the trace (this takes to calls to
sp_trace_setstatus, first stop and then close the trace.
Or try generating lots of events to get 128K worth of data generated.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <ahmadshajee@.hotmail.com> wrote in message
news:OT5zGaS6FHA.3684@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to automate tracing for SQL server Profiler. I have written a set
> of
> stored procedures using the SQL Server in-built sp's for the very purpose.
> A trace is also being successfully created with trace id and a
> file(MyTrace.trc) is also created however the trace is not writing
> anything
> into the file MyTrace.trc
> I've started the trace using the sp sp_trace_setstatus @.TraceId, 1
> and when accessing info of the trace using
> SELECT *
> FROM ::fn_trace_getinfo(@.TraceId)
> it shows the correct file path and status as 1
> Could some please help me out in this.
> What else am i required to do for enabling server side trace and so that
> the
> capture sql traces are written into the file.
> Regards
> Shajee
>
>
|||Thanks for the help. I really appreciate that.
The problem is resolved. Actually i was stopping the trace but not
closing it. So the captured events were not being written into
file(.trc). I'm using SQL 2000
Now after deleting the definition of trace by using
sp_trace_setstatus @.TraceId,2 it works and the specified file gets
update which's then accessed by function
::fn_trace_gettable(@.filepath,default)
Thanks for the help once again
Regards
Shajee
*** Sent via Developersdex http://www.codecomments.com ***

how to capture custom made error message into table

Dear all,
I want to know how to custom made error message into table, as I illustrated
T-SQL below :
if objectproperty(object_id('DateTable'),'I
sUserTable')=1
drop table DateTable
if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
drop table InputFromFlatFile1
if objectproperty(object_id('errorLog'),'Is
UserTable')=1
drop table errorLog
if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
drop proc usp_testData
create table DateTable
(datetimestamp datetime)
go
create table errorLog
(
data varchar(200),
errmsg varchar(2000)
)
go
create proc usp_testData
(@.dateinfo datetime)
as
begin
if convert(varchar(8),@.dateinfo,112)<'20060328'
begin
raiserror('Date input invalid because it''s entered before 28 Mar
2006',16,1)
return
end
else
insert into DateTable (datetimestamp) values (@.dateinfo)
end
go
create table InputFromFlatFile1
(
id int identity (1,1) not null,
linestring varchar(100)
)
go
insert into InputFromFlatFile1 (linestring) values ('20060328');
insert into InputFromFlatFile1 (linestring) values ('20060212');
insert into InputFromFlatFile1 (linestring) values ('20060115');
declare @.linestring varchar(200), @.errmsg varchar(2000)
declare @.cnt int, @.recnum int, @.error int
set @.cnt = 1
select @.recnum = count(*) from InputFromFlatFile1
while @.cnt <= @.recnum
begin
select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
print @.linestring
exec @.error = usp_testData @.linestring;
if @.error<>0 or @.@.error <> 0
begin
if @.@.error <> 0
begin
set @.error = @.@.error
select @.errmsg = description from master.dbo.sysmessages where error =
@.error
end
/*
How can I capture user-made error in stored procedure usp_testData to put
into ErrorLog table
'
*/
insert into errorLog values (@.linestring, @.errmsg)
end
set @.cnt = @.cnt + 1;
end
select * from DateTable
select * from errorLog
from query analyzer I get error message below :
Date input invalid because it is entered before 28 Mar 2006
but I cannot get the error message as above in errorLog table
How can I capture error message like in query analyzer in errorLog table?
Regards,
Koronx
SQL HobbistHi
Check out http://www.sommarskog.se/error-handling-II.html and
http://www.sommarskog.se/error-handling-I.html You will need to add the
insert statement into your code or possibly do it through the client
application by trapping the error message returned.
John
"Kornx Koronx" wrote:

> Dear all,
> I want to know how to custom made error message into table, as I illustrat
ed
> T-SQL below :
> if objectproperty(object_id('DateTable'),'I
sUserTable')=1
> drop table DateTable
> if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
> drop table InputFromFlatFile1
> if objectproperty(object_id('errorLog'),'Is
UserTable')=1
> drop table errorLog
> if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
> drop proc usp_testData
> create table DateTable
> (datetimestamp datetime)
> go
> create table errorLog
> (
> data varchar(200),
> errmsg varchar(2000)
> )
> go
> create proc usp_testData
> (@.dateinfo datetime)
> as
> begin
> if convert(varchar(8),@.dateinfo,112)<'20060328'
> begin
> raiserror('Date input invalid because it''s entered before 28 Mar
> 2006',16,1)
> return
> end
> else
> insert into DateTable (datetimestamp) values (@.dateinfo)
> end
> go
> create table InputFromFlatFile1
> (
> id int identity (1,1) not null,
> linestring varchar(100)
> )
> go
> insert into InputFromFlatFile1 (linestring) values ('20060328');
> insert into InputFromFlatFile1 (linestring) values ('20060212');
> insert into InputFromFlatFile1 (linestring) values ('20060115');
> declare @.linestring varchar(200), @.errmsg varchar(2000)
> declare @.cnt int, @.recnum int, @.error int
> set @.cnt = 1
> select @.recnum = count(*) from InputFromFlatFile1
> while @.cnt <= @.recnum
> begin
> select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
> print @.linestring
> exec @.error = usp_testData @.linestring;
> if @.error<>0 or @.@.error <> 0
> begin
> if @.@.error <> 0
> begin
> set @.error = @.@.error
> select @.errmsg = description from master.dbo.sysmessages where error =
> @.error
> end
> /*
> How can I capture user-made error in stored procedure usp_testData to put
> into ErrorLog table
> '
> */
> insert into errorLog values (@.linestring, @.errmsg)
> end
> set @.cnt = @.cnt + 1;
> end
> select * from DateTable
> select * from errorLog
> from query analyzer I get error message below :
> Date input invalid because it is entered before 28 Mar 2006
>
> but I cannot get the error message as above in errorLog table
> How can I capture error message like in query analyzer in errorLog table?
> Regards,
> Koronx
> SQL Hobbist

How to capture changes in a table

Hi Freinds,
SQL 2000
I would like to capture all the changes ( updates. insert, delete) in my
tables. this is a type of audit and capturing the changes of data.
we can't do this in GUI section, so I need to develop smt to capture the
changes.
Does anybody did smt like this before? any help? hint?
Thank in advance,
Pathttp://www.aspfaq.com/2496
http://www.aspfaq.com/2448
"Patrick" <patriarck@.gmail.com> wrote in message
news:eK9IhW0VGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi Freinds,
> SQL 2000
> I would like to capture all the changes ( updates. insert, delete) in my
> tables. this is a type of audit and capturing the changes of data.
> we can't do this in GUI section, so I need to develop smt to capture the
> changes.
> Does anybody did smt like this before? any help? hint?
> Thank in advance,
> Pat
>|||Based on what you are describing, you can try using a trigger, the option FO
R
INSERT, DELETE, UPDATE. Check Books Online for details. You can also capture
the username of anyone who performs an INSERT, DELETE or UPDATE. You may nee
d
to create another table to store all the changes captured by the trigger.
Please be aware that depending on how often this table is being accessed,
you may experience a impact in performance, since every operation will fire
the trigger.
"Patrick" wrote:

> Hi Freinds,
> SQL 2000
> I would like to capture all the changes ( updates. insert, delete) in my
> tables. this is a type of audit and capturing the changes of data.
> we can't do this in GUI section, so I need to develop smt to capture the
> changes.
> Does anybody did smt like this before? any help? hint?
> Thank in advance,
> Pat
>
>|||This is exacly what I want to do.
I am trying to develope a function or sp to use it in my trigger for same
exact purpose.
does anybody have done this before?
Thanks again,
Pat
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:A4AC389F-D7BA-4D6A-BEFB-769F2131A625@.microsoft.com...
> Based on what you are describing, you can try using a trigger, the option
> FOR
> INSERT, DELETE, UPDATE. Check Books Online for details. You can also
> capture
> the username of anyone who performs an INSERT, DELETE or UPDATE. You may
> need
> to create another table to store all the changes captured by the trigger.
> Please be aware that depending on how often this table is being accessed,
> you may experience a impact in performance, since every operation will
> fire
> the trigger.
> "Patrick" wrote:
>|||> I am trying to develope a function or sp to use it in my trigger for same
> exact purpose.
> does anybody have done this before?
YES! Did you see the links I posted?|||there were no links in your message !!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrD0wz0VGHA.4740@.TK2MSFTNGP14.phx.gbl...
> YES! Did you see the links I posted?
>|||Yes, there were !!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ud7qqb0VGHA.4336@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2496
> http://www.aspfaq.com/2448
"Patrick" <patriarck@.gmail.com> wrote in message
news:Omyf$70VGHA.4424@.TK2MSFTNGP15.phx.gbl...
> there were no links in your message !!|||ok , I got it and thanks a lot :)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23WiTIA1VGHA.5664@.TK2MSFTNGP15.phx.gbl...
> Yes, there were !!
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:ud7qqb0VGHA.4336@.TK2MSFTNGP14.phx.gbl...
>
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:Omyf$70VGHA.4424@.TK2MSFTNGP15.phx.gbl...
>