Login failed for user '(null)'

I have set up a vary basic report that connects to a SQL Server database,
executes a stored procedure that takes two parameters (start date and end
date). In the report I created in VS, I create the data source in VS using a
trusted connection and test the connection and it works fine. I can preview
the report with no problems. Reporting Services is installed on one server
and the database is on another server.
Once I have deployed the report and go to run it, it prompts me for the
start date and end date and then I go to view the report and I get the
following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'winoasis'.
(rsErrorOpeningConnection) Get Online Help
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
I have tracing turned on to verbose and I can see my user name in the log,
but it still shows the Login failed for user '(null)'. Reason: Not associated
with a trusted SQL Server connection error.
Does the reporting services server need to have the trusted for delegation?
Any ideas on what is causing this problem?
Thanks
MichaelSounds like a datasource connection problem. Is the Authentication mode for
your SQL server Windows Only? If so, and your datasource is passing in UID
and PWD, you will need to set it to SQL Server and Windows.
Create a shared datasource in Report Manager, select the "Credentials stored
securely in the report server" and specify the User Name and Password so RS
can connect to get the data. Assign your report(s) to use the datasource.
Adrian M.
"MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
>I have set up a vary basic report that connects to a SQL Server database,
> executes a stored procedure that takes two parameters (start date and end
> date). In the report I created in VS, I create the data source in VS using
> a
> trusted connection and test the connection and it works fine. I can
> preview
> the report with no problems. Reporting Services is installed on one server
> and the database is on another server.
> Once I have deployed the report and go to run it, it prompts me for the
> start date and end date and then I go to view the report and I get the
> following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'winoasis'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have tracing turned on to verbose and I can see my user name in the log,
> but it still shows the Login failed for user '(null)'. Reason: Not
> associated
> with a trusted SQL Server connection error.
> Does the reporting services server need to have the trusted for
> delegation?
> Any ideas on what is causing this problem?
> Thanks
> Michael|||if you really want to use integrated security in this scenario (client ->
web server (RS) -> database server) than you have to set up kerberos ticket
delegation. (the web server service account needs to be trusted for
delegation)
"MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
>I have set up a vary basic report that connects to a SQL Server database,
> executes a stored procedure that takes two parameters (start date and end
> date). In the report I created in VS, I create the data source in VS using
> a
> trusted connection and test the connection and it works fine. I can
> preview
> the report with no problems. Reporting Services is installed on one server
> and the database is on another server.
> Once I have deployed the report and go to run it, it prompts me for the
> start date and end date and then I go to view the report and I get the
> following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'winoasis'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> I have tracing turned on to verbose and I can see my user name in the log,
> but it still shows the Login failed for user '(null)'. Reason: Not
> associated
> with a trusted SQL Server connection error.
> Does the reporting services server need to have the trusted for
> delegation?
> Any ideas on what is causing this problem?
> Thanks
> Michael|||The server is set for SQL Server and Windows. I have a login on the SQL
Server that uses windows and I connect with no problems through Enterprise
Manager and Query Analyzer. When I look at the SQL Error logs, I can see the
same error. Any other thoughts?
Michael
"Adrian M." wrote:
> Sounds like a datasource connection problem. Is the Authentication mode for
> your SQL server Windows Only? If so, and your datasource is passing in UID
> and PWD, you will need to set it to SQL Server and Windows.
> Create a shared datasource in Report Manager, select the "Credentials stored
> securely in the report server" and specify the User Name and Password so RS
> can connect to get the data. Assign your report(s) to use the datasource.
> Adrian M.
>
> "MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
> news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
> >I have set up a vary basic report that connects to a SQL Server database,
> > executes a stored procedure that takes two parameters (start date and end
> > date). In the report I created in VS, I create the data source in VS using
> > a
> > trusted connection and test the connection and it works fine. I can
> > preview
> > the report with no problems. Reporting Services is installed on one server
> > and the database is on another server.
> >
> > Once I have deployed the report and go to run it, it prompts me for the
> > start date and end date and then I go to view the report and I get the
> > following error:
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'winoasis'.
> > (rsErrorOpeningConnection) Get Online Help
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I have tracing turned on to verbose and I can see my user name in the log,
> > but it still shows the Login failed for user '(null)'. Reason: Not
> > associated
> > with a trusted SQL Server connection error.
> >
> > Does the reporting services server need to have the trusted for
> > delegation?
> > Any ideas on what is causing this problem?
> >
> > Thanks
> >
> > Michael
>
>|||My only other thoughts is the Connection String in the shared data source.
What option do you have selected under Connect Using on the datasource?
"MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
news:B490773D-B0EC-4B80-A6F1-AF36F2597CF0@.microsoft.com...
> The server is set for SQL Server and Windows. I have a login on the SQL
> Server that uses windows and I connect with no problems through Enterprise
> Manager and Query Analyzer. When I look at the SQL Error logs, I can see
> the
> same error. Any other thoughts?
> Michael
>
> "Adrian M." wrote:
>> Sounds like a datasource connection problem. Is the Authentication mode
>> for
>> your SQL server Windows Only? If so, and your datasource is passing in
>> UID
>> and PWD, you will need to set it to SQL Server and Windows.
>> Create a shared datasource in Report Manager, select the "Credentials
>> stored
>> securely in the report server" and specify the User Name and Password so
>> RS
>> can connect to get the data. Assign your report(s) to use the
>> datasource.
>> Adrian M.
>>
>> "MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
>> news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
>> >I have set up a vary basic report that connects to a SQL Server
>> >database,
>> > executes a stored procedure that takes two parameters (start date and
>> > end
>> > date). In the report I created in VS, I create the data source in VS
>> > using
>> > a
>> > trusted connection and test the connection and it works fine. I can
>> > preview
>> > the report with no problems. Reporting Services is installed on one
>> > server
>> > and the database is on another server.
>> >
>> > Once I have deployed the report and go to run it, it prompts me for the
>> > start date and end date and then I go to view the report and I get the
>> > following error:
>> >
>> > An error has occurred during report processing. (rsProcessingAborted)
>> > Get
>> > Online Help
>> > Cannot create a connection to data source 'winoasis'.
>> > (rsErrorOpeningConnection) Get Online Help
>> > Login failed for user '(null)'. Reason: Not associated with a trusted
>> > SQL
>> > Server connection.
>> >
>> > I have tracing turned on to verbose and I can see my user name in the
>> > log,
>> > but it still shows the Login failed for user '(null)'. Reason: Not
>> > associated
>> > with a trusted SQL Server connection error.
>> >
>> > Does the reporting services server need to have the trusted for
>> > delegation?
>> > Any ideas on what is causing this problem?
>> >
>> > Thanks
>> >
>> > Michael
>>|||I don't have the same connection auth setup that you are using but (after a
little digging) maybe this will help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_datasources_v1_87e9.asp
Adrian M.
"MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
news:B490773D-B0EC-4B80-A6F1-AF36F2597CF0@.microsoft.com...
> The server is set for SQL Server and Windows. I have a login on the SQL
> Server that uses windows and I connect with no problems through Enterprise
> Manager and Query Analyzer. When I look at the SQL Error logs, I can see
> the
> same error. Any other thoughts?
> Michael
>
> "Adrian M." wrote:
>> Sounds like a datasource connection problem. Is the Authentication mode
>> for
>> your SQL server Windows Only? If so, and your datasource is passing in
>> UID
>> and PWD, you will need to set it to SQL Server and Windows.
>> Create a shared datasource in Report Manager, select the "Credentials
>> stored
>> securely in the report server" and specify the User Name and Password so
>> RS
>> can connect to get the data. Assign your report(s) to use the
>> datasource.
>> Adrian M.
>>
>> "MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
>> news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
>> >I have set up a vary basic report that connects to a SQL Server
>> >database,
>> > executes a stored procedure that takes two parameters (start date and
>> > end
>> > date). In the report I created in VS, I create the data source in VS
>> > using
>> > a
>> > trusted connection and test the connection and it works fine. I can
>> > preview
>> > the report with no problems. Reporting Services is installed on one
>> > server
>> > and the database is on another server.
>> >
>> > Once I have deployed the report and go to run it, it prompts me for the
>> > start date and end date and then I go to view the report and I get the
>> > following error:
>> >
>> > An error has occurred during report processing. (rsProcessingAborted)
>> > Get
>> > Online Help
>> > Cannot create a connection to data source 'winoasis'.
>> > (rsErrorOpeningConnection) Get Online Help
>> > Login failed for user '(null)'. Reason: Not associated with a trusted
>> > SQL
>> > Server connection.
>> >
>> > I have tracing turned on to verbose and I can see my user name in the
>> > log,
>> > but it still shows the Login failed for user '(null)'. Reason: Not
>> > associated
>> > with a trusted SQL Server connection error.
>> >
>> > Does the reporting services server need to have the trusted for
>> > delegation?
>> > Any ideas on what is causing this problem?
>> >
>> > Thanks
>> >
>> > Michael
>>|||Hi, I'm using Reporting Services and Everest by Outlooksoft and in the last
days sometimes I'm getting the same error message...don't know why because I
haven't modified anything in the reports I've been succesfully using in the
last months...
The only way I found to fix the problem is to restart the server...
Let me know if u find a valid solution!
thanks!
"Thomas Kern" wrote:
> if you really want to use integrated security in this scenario (client ->
> web server (RS) -> database server) than you have to set up kerberos ticket
> delegation. (the web server service account needs to be trusted for
> delegation)
>
> "MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
> news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
> >I have set up a vary basic report that connects to a SQL Server database,
> > executes a stored procedure that takes two parameters (start date and end
> > date). In the report I created in VS, I create the data source in VS using
> > a
> > trusted connection and test the connection and it works fine. I can
> > preview
> > the report with no problems. Reporting Services is installed on one server
> > and the database is on another server.
> >
> > Once I have deployed the report and go to run it, it prompts me for the
> > start date and end date and then I go to view the report and I get the
> > following error:
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'winoasis'.
> > (rsErrorOpeningConnection) Get Online Help
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > I have tracing turned on to verbose and I can see my user name in the log,
> > but it still shows the Login failed for user '(null)'. Reason: Not
> > associated
> > with a trusted SQL Server connection error.
> >
> > Does the reporting services server need to have the trusted for
> > delegation?
> > Any ideas on what is causing this problem?
> >
> > Thanks
> >
> > Michael
>
>|||I ran into this problem yesterday and eventually solved it with the help of
the responses to your thread.
In my scenario, I was working with a Mixed security model SQL Server. I had
created a SQL Login for use with the report, and embedding the credentials
in the report. This failed with the same error you're recieving.
I finally realized that NT security was taking precedence, and changed the
login credentials to be a domain login (domain\loginname format) and the
report began to render properly.
In brief:
If working with a mixed security model, provide the Domain login info
instead of a valid SQL login.
"MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
news:B490773D-B0EC-4B80-A6F1-AF36F2597CF0@.microsoft.com...
> The server is set for SQL Server and Windows. I have a login on the SQL
> Server that uses windows and I connect with no problems through Enterprise
> Manager and Query Analyzer. When I look at the SQL Error logs, I can see
the
> same error. Any other thoughts?
> Michael
>
> "Adrian M." wrote:
> > Sounds like a datasource connection problem. Is the Authentication mode
for
> > your SQL server Windows Only? If so, and your datasource is passing in
UID
> > and PWD, you will need to set it to SQL Server and Windows.
> >
> > Create a shared datasource in Report Manager, select the "Credentials
stored
> > securely in the report server" and specify the User Name and Password so
RS
> > can connect to get the data. Assign your report(s) to use the
datasource.
> >
> > Adrian M.
> >
> >
> > "MAGrimsley" <MAGrimsley@.discussions.microsoft.com> wrote in message
> > news:F9FC955B-1EF5-4670-95A8-B5BC8993A327@.microsoft.com...
> > >I have set up a vary basic report that connects to a SQL Server
database,
> > > executes a stored procedure that takes two parameters (start date and
end
> > > date). In the report I created in VS, I create the data source in VS
using
> > > a
> > > trusted connection and test the connection and it works fine. I can
> > > preview
> > > the report with no problems. Reporting Services is installed on one
server
> > > and the database is on another server.
> > >
> > > Once I have deployed the report and go to run it, it prompts me for
the
> > > start date and end date and then I go to view the report and I get the
> > > following error:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
Get
> > > Online Help
> > > Cannot create a connection to data source 'winoasis'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > Login failed for user '(null)'. Reason: Not associated with a trusted
SQL
> > > Server connection.
> > >
> > > I have tracing turned on to verbose and I can see my user name in the
log,
> > > but it still shows the Login failed for user '(null)'. Reason: Not
> > > associated
> > > with a trusted SQL Server connection error.
> > >
> > > Does the reporting services server need to have the trusted for
> > > delegation?
> > > Any ideas on what is causing this problem?
> > >
> > > Thanks
> > >
> > > Michael
> >
> >
> >

Login Access

I want to give a login the ability to see all database objects, insert and
and delete data, run stored procedures, and execute objects within a specifi
c
database. I do not want them to create or delete any database objects or
adjust security. How do I accomplish that by using the database roles?
>I want to give a login the ability to see all database objects, insert and
> and delete data, run stored procedures, and execute objects within a
> specific
> database. I do not want them to create or delete any database objects or
> adjust security. How do I accomplish that by using the database roles?
With a user-defined role. There is no predefined role for executing
procedures. Check the permissions of the fixed db oles in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a08108a3-f1fb-43ac-a264-3f2f
9749db5d.htm.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

Logic problem in cursor/SPROC

SQL Server 2000

I have a stored procedure that uses an extended SPROC to send an email
notification to my customers when a document is distributed.

However, the SPROC has an unexpected side effect.

If I run it in its current incarnation, it only sends one email and
then exits. However, if I remove or comment out the block

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @.JobID)

then it runs through the whole list as anticipated. Conceptually, it
seems that the records in the cursor are changed if the underlying
table is updated.

Here is pseudo-code for what the SPROC does - whole SPROC below (with
part of the "WHERE" clause removed for readability). I haven't
included any table schemae but I don't think they're relevant.

1. Open a cursor and fetch a list of all companies that need email
notification for pending jobs.

2. While records in the cursor...

a) Format and send email from the cursor
b) Write a record to the audit table
c) Update the jobs table for the current record

3) Fetch next from cursor

There is an update trigger on the tblJobs table thus:

CREATE TRIGGER "tblJobs_UTrig" ON dbo.tblJobs FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tblDistributionMaster' */
IF UPDATE(fldDistributionID)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDistributionMaster, inserted WHERE
(tblDistributionMaster.fldDistributionID = inserted.fldDistributionID))
BEGIN
RAISERROR 44446 'The record can''t be added or changed.
Referential integrity rules require a related record in table
''tblDistributionMaster''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblPrinterJobs' */
IF UPDATE(fldJobID)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tblPrinterJobs WHERE
(deleted.fldJobID = tblPrinterJobs.fldJobID)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or
changed. Since related records exist in table ''tblPrinterJobs'',
referential integrity rules would be violated.'
ROLLBACK TRANSACTION
END
END

I can't see that this is relevant - I think it's something to do with
where I'm updating the fldEmailProcessed field, but I need to do this
here, as outside the loop the fldJobID might be lost.

Sorry it's all such a mess. Hope someone can help!

Thanks

Edward

/*
Checks all Jobs that were set to Despatched more than 24 hours ago,
selects those that the companies elected to get email notification, and
sends them emails.
*/

CREATE PROCEDURE stpSendEmailNotification

AS

DECLARE @.rc int
DECLARE @.JobID int
DECLARE @.CompanyID int
DECLARE @.DocumentNumber varchar(50)
DECLARE @.Email varchar(50)
DECLARE @.DocumentURL varchar(750)
DECLARE @.Dat varchar(20)
DECLARE @.Subject varchar(100)

SET @.Dat = LEFT((CONVERT(varchar, GETDATE(), 100)), 11)

DECLARE MailList CURSOR FOR

SELECT
tblJobs.fldJobID,
tblJobs.fldDocumentNumber,
tblCompany.fldEmail,
tblCompany.fldCompanyID,
tblJobHistory.fldDocumentURL
FROM
tblJobHistory INNER JOIN
tblJobs ON tblJobHistory.fldJobID = tblJobs.fldJobID
INNER JOIN
tblDistributionMaster ON tblJobHistory.fldDistributionID =
tblDistributionMaster.fldDistributionID INNER JOIN
tblCompany ON tblJobHistory.fldCompanyID =
tblCompany.fldCompanyID
WHERE
(tblJobs.fldEmailProcessed = 0)

OPEN MailList

FETCH NEXT FROM MailList INTO
@.JobID,
@.DocumentNumber,
@.Email,
@.CompanyID,
@.DocumentURL

WHILE @.@.FETCH_STATUS = 0
BEGIN

/* Format and send the email to the customer here */
SET @.Subject = N'Document Distribution No: ' + @.DocumentNumber +
N' - Date: ' + @.Dat

exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'techlib@.myco.co.uk',
@.FROM_NAME = N'Edward Collier',
@.replyto = N'techlib@.myco.co.uk',
@.TO = @.Email,
@.CC = N'',
@.BCC = N'',
@.priority = N'NORMAL',
@.subject = @.Subject,
@.type = N'text/plain',
@.message = @.DocumentURL,
@.messagefile = N'',
@.attachment = N'',
@.attachments = N'',
@.codepage = 0,
@.server = N'12.34.5.67',
@.timeout = 10000
select RC = @.rc

/* Write result to audit table */
INSERT INTO tblEmailAudit
(
fldRCNo,
fldEmail,
fldDocumentNumber,
fldDate,
fldCompanyID
)
VALUES
(
@.rc,
@.Email,
@.DocumentNumber,
GETDATE(),
@.CompanyID
)

/* Set Job to processed */
UPDATE
tblJobs
SET
fldEmailProcessed = 1
WHERE
(fldJobID = @.JobID)

FETCH NEXT FROM MailList INTO
@.JobID,
@.DocumentNumber,
@.Email,
@.CompanyID,
@.DocumentURL
END

CLOSE MailList
DEALLOCATE MailList
GO
teddysnips@.hotmail.com wrote:
> SQL Server 2000
> I have a stored procedure that uses an extended SPROC to send an email
> notification to my customers when a document is distributed.
> However, the SPROC has an unexpected side effect.

[...]

I've sorted it. The cursor needed to be declared STATIC. Panic over.

Edward

Logic for Stored Procedure(s)

I have a table containing a 'queue' of rows waiting to be processed by my application.

Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?

If not, what is the best logic for doing it with two stored procedures ?

The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.

Any suggestions appreciated.

Steve.A single stored prodedure can have many lines of code, so sure, what you are asking is possible.

I'd do it like this:

SET NOCOUNT ON
BEGIN TRANSACTION
 SELECT TOP 1 @.myID = uniqueID FROM myTable ORDER BY datetime DESC
 SELECT someFields FROM myTable WHERE uniqueID = @.myID
 DELETE FROM myTable WHERE uniqueID = @.myID
COMMIT

I'd also put some error checking code in there and perform a ROLLBACK if there's an error.

Terri|||I might create a flag on myTable indicating that the record has processed, move the tran to the client, and update the flag once the process has sucessfully completed.

If the server commits the tran before the client finishes processing and the client error you will have no "easy" way of recovering a deleted record.|||Thanks guys - that helps a lot.

Steve.

Logic Behind Object_ID

I have 2 basic requirements as below,

1) Comparing 2 versions of same database.

2) Keeping track of schema versions of Tables & Stored Procedures so that I can rollback to previous schema.

I have started with Sys.Objects, Sys.Tables & Sys.Columns and I kept things rely on Object_ID but the problem started whenever I change some part of Table. It assigns new Object_ID to the table whenever I change it; moreover creationdate of object is also changed. So I assume that it drops and creates new table.

Please throw some light on following questions,

1) When and How Object_ID Changes?

2) Is there any other thing in database which is Unique & Not Changing with alteration of object?

3) What should be the preferred way to Compare 2 Database?

I hate readymade tools as they charge per user and my requirements are very small.

Thanking you,

Object_ID is internal, and can definitely change when you call the ALTER TABLE / ALTER INDEX statements when the engine decides it is easier to create a new object than to modify the existing table.

The best way to keep track of different version of tables and stored procedures is to use a source control system (like VSTS/SourceSafe). Each time you load a new version in the database, you store the database script in the source control system. If you need to get back to an old version, you simply get the version from source control.

To compore 2 versions of the same database, you can use VSTS for database professionals, which contains this functionality.

Thanks,|||

Versioning is OK. We can easily maintain with Source Control

But Real Headache is DataBase Comparsion, We cannot afford to go for VSTS so there should be some other alternative.

Can you give a brick (clue) for object_id or anything else using which I can build building?

Thanks

|||Would it be possible to use the table name to compare, or do you need to do compares of tables with different names?

Thanks,|||I would definitely use objectname instead of id. Since it's not possible to have to objects with the same name for the same schema. You're guarantee to find the object you're looking for to compare.

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank Wijten
You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:

> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure without
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>
|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank WijtenAfraid not - You would need to collect the data via a profiling session and
then take a look at the results to see your procedure usage.
"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>|||You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:

> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure withou
t
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank WijtenAfraid not - You would need to collect the data via a profiling session and
then take a look at the results to see your procedure usage.
"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>|||You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure without
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Logging stored procedure use with MSDE (2000)

I have a app connected to MSDE that creates a report. The DB has over 300
stored procedures, with various args. I'm 9.9% sure the report must call a
SP. Can I somehow start a trace or log and have MSDE capture everything?
I'm hoping this way, I can discover the SP being run, along with the args.
I need to be able to run the report from the command line to get the output
into VBScript. (I know how to run a SP from VBScript, and massaging the
output shouldn't be a problem.)
Marco
You should be able to capture this using a server-side trace. Read about Profiler in Books Online,
and also read about, for instance sp_trace_create (etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:u14RnmN4GHA.2420@.TK2MSFTNGP02.phx.gbl...
>I have a app connected to MSDE that creates a report. The DB has over 300
> stored procedures, with various args. I'm 9.9% sure the report must call a
> SP. Can I somehow start a trace or log and have MSDE capture everything?
> I'm hoping this way, I can discover the SP being run, along with the args.
> I need to be able to run the report from the command line to get the output
> into VBScript. (I know how to run a SP from VBScript, and massaging the
> output shouldn't be a problem.)
> Marco
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You should be able to capture this using a server-side trace. Read about
Profiler in Books Online,
> and also read about, for instance sp_trace_create (etc).
Am just using MSDE which doesn't seem to have Profiler. Looking in to
sp_trace_create.
Thanks.
|||I just replied to your other post. Try to get hold of a "full" SQL Server, if only for a minute, and
script a trace from there. Here's an example, btw:
/************************************************** **/
/* Created by: SQL Server Profiler 2005 */
/* Date: 2006/09/26 16:21:52 */
/************************************************** **/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere', @.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 15, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 1, @.on
exec sp_trace_setevent @.TraceID, 17, 9, @.on
exec sp_trace_setevent @.TraceID, 17, 6, @.on
exec sp_trace_setevent @.TraceID, 17, 10, @.on
exec sp_trace_setevent @.TraceID, 17, 14, @.on
exec sp_trace_setevent @.TraceID, 17, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 16, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 17, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 18, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 6, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 16, @.on
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 17, @.on
exec sp_trace_setevent @.TraceID, 12, 6, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 18, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 13, 12, @.on
exec sp_trace_setevent @.TraceID, 13, 1, @.on
exec sp_trace_setevent @.TraceID, 13, 9, @.on
exec sp_trace_setevent @.TraceID, 13, 6, @.on
exec sp_trace_setevent @.TraceID, 13, 10, @.on
exec sp_trace_setevent @.TraceID, 13, 14, @.on
exec sp_trace_setevent @.TraceID, 13, 11, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Server Profiler -
74e4bb26-8675-4e74-90d0-5793d57489d1'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:e2RVkeX4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Profiler in Books Online,
> Am just using MSDE which doesn't seem to have Profiler. Looking in to
> sp_trace_create.
> Thanks.
>

Logging stored procedure use with MSDE (2000)

I have a app connected to MSDE that creates a report. The DB has over 300
stored procedures, with various args. I'm 9.9% sure the report must call a
SP. Can I somehow start a trace or log and have MSDE capture everything?
I'm hoping this way, I can discover the SP being run, along with the args.
I need to be able to run the report from the command line to get the output
into VBScript. (I know how to run a SP from VBScript, and massaging the
output shouldn't be a problem.)
MarcoYou should be able to capture this using a server-side trace. Read about Pro
filer in Books Online,
and also read about, for instance sp_trace_create (etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:u14RnmN4GHA.2420@.TK2MSFTNGP02.phx.gb
l...
>I have a app connected to MSDE that creates a report. The DB has over 300
> stored procedures, with various args. I'm 9.9% sure the report must call
a
> SP. Can I somehow start a trace or log and have MSDE capture everything?
> I'm hoping this way, I can discover the SP being run, along with the args.
> I need to be able to run the report from the command line to get the outpu
t
> into VBScript. (I know how to run a SP from VBScript, and massaging the
> output shouldn't be a problem.)
> Marco
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You should be able to capture this using a server-side trace. Read about
Profiler in Books Online,
> and also read about, for instance sp_trace_create (etc).
Am just using MSDE which doesn't seem to have Profiler. Looking in to
sp_trace_create.
Thanks.|||I just replied to your other post. Try to get hold of a "full" SQL Server, i
f only for a minute, and
script a trace from there. Here's an example, btw:
/ ****************************************
************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 2006/09/26 16:21:52 */
/ ****************************************
************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extensio
n
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server ha
s
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere', @.maxfi
lesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 15, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 1, @.on
exec sp_trace_setevent @.TraceID, 17, 9, @.on
exec sp_trace_setevent @.TraceID, 17, 6, @.on
exec sp_trace_setevent @.TraceID, 17, 10, @.on
exec sp_trace_setevent @.TraceID, 17, 14, @.on
exec sp_trace_setevent @.TraceID, 17, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 16, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 17, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 18, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 6, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 16, @.on
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 17, @.on
exec sp_trace_setevent @.TraceID, 12, 6, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 18, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 13, 12, @.on
exec sp_trace_setevent @.TraceID, 13, 1, @.on
exec sp_trace_setevent @.TraceID, 13, 9, @.on
exec sp_trace_setevent @.TraceID, 13, 6, @.on
exec sp_trace_setevent @.TraceID, 13, 10, @.on
exec sp_trace_setevent @.TraceID, 13, 14, @.on
exec sp_trace_setevent @.TraceID, 13, 11, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Server Profiler -
74e4bb26-8675-4e74-90d0-5793d57489d1'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:e2RVkeX4GHA.4352@.TK2MSFTNGP03.phx.gb
l...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Profiler in Books Online,
> Am just using MSDE which doesn't seem to have Profiler. Looking in to
> sp_trace_create.
> Thanks.
>

Logging stored procedure use with MSDE (2000)

I have a app connected to MSDE that creates a report. The DB has over 300
stored procedures, with various args. I'm 9.9% sure the report must call a
SP. Can I somehow start a trace or log and have MSDE capture everything?
I'm hoping this way, I can discover the SP being run, along with the args.
I need to be able to run the report from the command line to get the output
into VBScript. (I know how to run a SP from VBScript, and massaging the
output shouldn't be a problem.)
MarcoYou should be able to capture this using a server-side trace. Read about Profiler in Books Online,
and also read about, for instance sp_trace_create (etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:u14RnmN4GHA.2420@.TK2MSFTNGP02.phx.gbl...
>I have a app connected to MSDE that creates a report. The DB has over 300
> stored procedures, with various args. I'm 9.9% sure the report must call a
> SP. Can I somehow start a trace or log and have MSDE capture everything?
> I'm hoping this way, I can discover the SP being run, along with the args.
> I need to be able to run the report from the command line to get the output
> into VBScript. (I know how to run a SP from VBScript, and massaging the
> output shouldn't be a problem.)
> Marco
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You should be able to capture this using a server-side trace. Read about
Profiler in Books Online,
> and also read about, for instance sp_trace_create (etc).
Am just using MSDE which doesn't seem to have Profiler. Looking in to
sp_trace_create.
Thanks.|||I just replied to your other post. Try to get hold of a "full" SQL Server, if only for a minute, and
script a trace from there. Here's an example, btw:
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 2006/09/26 16:21:52 */
/****************************************************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere', @.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 15, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 12, @.on
exec sp_trace_setevent @.TraceID, 17, 1, @.on
exec sp_trace_setevent @.TraceID, 17, 9, @.on
exec sp_trace_setevent @.TraceID, 17, 6, @.on
exec sp_trace_setevent @.TraceID, 17, 10, @.on
exec sp_trace_setevent @.TraceID, 17, 14, @.on
exec sp_trace_setevent @.TraceID, 17, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 16, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 17, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 18, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 6, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 16, @.on
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 17, @.on
exec sp_trace_setevent @.TraceID, 12, 6, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 18, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 13, 12, @.on
exec sp_trace_setevent @.TraceID, 13, 1, @.on
exec sp_trace_setevent @.TraceID, 13, 9, @.on
exec sp_trace_setevent @.TraceID, 13, 6, @.on
exec sp_trace_setevent @.TraceID, 13, 10, @.on
exec sp_trace_setevent @.TraceID, 13, 14, @.on
exec sp_trace_setevent @.TraceID, 13, 11, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Server Profiler -
74e4bb26-8675-4e74-90d0-5793d57489d1'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:e2RVkeX4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uGNTi%23U4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>> You should be able to capture this using a server-side trace. Read about
> Profiler in Books Online,
>> and also read about, for instance sp_trace_create (etc).
> Am just using MSDE which doesn't seem to have Profiler. Looking in to
> sp_trace_create.
> Thanks.
>

Logging stored Procedure changes into file

Hello all,

I have a big stored procedure which is going to alter many tables,insert data, basically lot of changes.

So, i want to have a text file (or) any log file which will display, what all the changes does the stored procedure has done ( They dont want profiler output )

Can anybody know how to log the results of execution of stored procedure to a text file.

Thanks.

Is your stored just going to run once, or many times?

If it is just a one-time batch job type thing, you could create one or more logging tables, and then have your SP do inserts into the logging table that record what was done (like before and after values, etc.)

|||

Unless you use your own logging logic within the procedure or use the profiler or the trace procedure (like the profiler) you won′t be able to do such a tracing.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Logging Query Messages From ExecuteSQL Task

The ExecuteSQL task makes it easy to capture the resultset of a query or stored proc in a variable. But what about the messages that would normally appear in the Messages tab when running queries in Management Studio or Query Analyzer? There must be a way to display and log rowcounts and other messages that a query normally produces.

Various package/task logging configrations haven't helped. I've read discussions about logging rowcounts in data flow tasks, but what about capturing messages in ExecuteSQL and other control flow tasks that run T-SQL?

I only have enough time to dabble in this technology so I may be missing something obvious or approaching this incorrectly. Any suggestion, reference, constructive criticism or reponse would be appreciated.

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

|||

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.|||

Phil Brammer wrote:

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.

Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.|||

Duane Douglas wrote:

Phil Brammer wrote:

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.

Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.

Duane is correct. As a matter of course I generally finish off my Execute SQL Tasks with:

SELECT @.@.ROWCOUNT AS RowCnt

and store it in a package-scoped variable.

-Jamie

|||

Duane Douglas wrote:

Phil Brammer wrote:

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.

Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.

What about other messages thrown by SQL? I don't know what they are, but "Command(s) completed successfully." is a valid message. Can it be captured somehow? What about other messages that might be generated? (I don't know what they are, as I'm just speaking for the original poster.)|||

Phil Brammer wrote:

What about other messages thrown by SQL? I don't know what they are, but "Command(s) completed successfully." is a valid message. Can it be captured somehow? What about other messages that might be generated? (I don't know what they are, as I'm just speaking for the original poster.)

I may be completely wrong about this but messages such as "Command(s) completed successfully." are messages generated by SSMS, not the database engine. Hence, they wouldn't be accessible from SSIS.

-Jamie

|||

The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.

It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.

|||

M.Glenn wrote:

The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.

It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.

Mike,

No problem.

If you think that BOL is short somewhere then tell them. The option to provide feedback is taken very seriously (I know from experience). In this case its more SQL engine or T-SQL stuff rather than SSIS - but provide it anyway.

-Jamie

|||

M.Glenn wrote:

The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.

It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.

SSMS is merely a GUI for SQL Server. Having said that, all of SQL Server's functionality can be accessed via T-SQL. For example, when a query executes without errors (in other words, when it completes successfully), SQL Server sets the @.@.ERROR variable to "0". This value can be returned to the client via a stored proceedure output parameter.

One should bear in mind that SSIS is a very advanced feature of SQL Server. As such, a mastery of T-SQL is extremely helpful when using SSIS.

|||

Duane Douglas wrote:

SSMS is merely a GUI for SQL Server. Having said that, all of SQL Server's functionality can be accessed via T-SQL. For example, when a query executes without errors (in other words, when it completes successfully), SQL Server sets the @.@.ERROR variable to "0". This value can be returned to the client via a stored proceedure output parameter.

One should bear in mind that SSIS is a very advanced feature of SQL Server. As such, a mastery of T-SQL is extremely helpful when using SSIS.

Agreed, but mastery of T-SQL is not the central issue in this case. I've used @.@.ERROR, @.@.ROWCOUNT, etc. in scripts, stored procs and triggers plenty of times. DTS/SSIS is where I'm on less familiar ground--only recently finding time to delve deeper. My frustration, which Phil Brammer's questions helped to tease out, was explained when Jamie Thompson stated "...messages such as "Command(s) completed successfully." are messages generated by SSMS, not the database engine. Hence, they wouldn't be accessible from SSIS." This together with your posts made it clear that the message stream provided by SSMS or QA is not available in SSIS and rolling your own, so to speak, is the only solution. Okay, I can take it from there.

However, this is disappointing. If you're building an SSIS solution from scratch it's not a problem. But in this case I'm using SSIS to automate more than a dozen vendor developed DDL and DML scripts that otherwise have to be run manually in SSMS. One script would have to be manually rerun more than 40 times, entering a different "batch number" each time. I let them know I wasn't too happy about that one. Anyway, instead of someone sitting there babysitting this convoluted upgrade/cleanup process for hours (or in this case days), SSIS makes it easy to automate the entire process--run the scripts in proper order, manage precedence constraints, enumerate the batch numbers in a forEach loop, etc.

Everything is working beautifully except for logging. A few of the scripts contain 20 or more individual queries. Running them in SSMS or QA would automatically give me a nice message log of the number of records affected, output from print statements embedded in the scripts, error messages, etc. If I understand you guys correctly, there's no built-in functionality to generate this kind of message stream in SSIS. I'd have to break the big scripts down, create ExecuteSQL tasks for each individual query (around 50 to 60 altogether) add and map output parameters for each and configure logging to capture all this. Not a happy prospect.

Is there any reason Microsoft couldn't duplicate SSMS/QA message stream functionality in a future release of SSIS? It would save a lot of work in cases like this. I'll suggest it unless someone has a good reason why it couldn't be done or (preferably) knows of a workaround for this situation.

Happy holiday!

|||

OK, so then how do you write the contents of the package-scoped variable to the log?

I think I've got my rowCnt into a variable but I can see what value it is.

I've tried having the package log 'OnVariableValueChanged' events, but it just tells me the name of the variable, not the value.

(I'm using SQL2005 SP2)

Thanks

|||

Andy Abel wrote:

OK, so then how do you write the contents of the package-scoped variable to the log?

I think I've got my rowCnt into a variable but I can see what value it is.

I've tried having the package log 'OnVariableValueChanged' events, but it just tells me the name of the variable, not the value.

(I'm using SQL2005 SP2)

Thanks

If you are just trying to see the value of the variable, add a breakpoint and use the Watch window to view it. If you need to record the value, you could add an event handler for the OnVariableValueChanged event, and use an Execute SQL task to write it to the database.

Logging Query Messages From ExecuteSQL Task

The ExecuteSQL task makes it easy to capture the resultset of a query or stored proc in a variable. But what about the messages that would normally appear in the Messages tab when running queries in Management Studio or Query Analyzer? There must be a way to display and log rowcounts and other messages that a query normally produces.

Various package/task logging configrations haven't helped. I've read discussions about logging rowcounts in data flow tasks, but what about capturing messages in ExecuteSQL and other control flow tasks that run T-SQL?

I only have enough time to dabble in this technology so I may be missing something obvious or approaching this incorrectly. Any suggestion, reference, constructive criticism or reponse would be appreciated.

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

|||

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.|||

Phil Brammer wrote:

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.

Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.|||

Duane Douglas wrote:

Phil Brammer wrote:

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.

Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.

Duane is correct. As a matter of course I generally finish off my Execute SQL Tasks with:

SELECT @.@.ROWCOUNT AS RowCnt

and store it in a package-scoped variable.

-Jamie

|||

Duane Douglas wrote:

Phil Brammer wrote:

Duane Douglas wrote:

It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.

I hope this helps.

Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.

Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.

What about other messages thrown by SQL? I don't know what they are, but "Command(s) completed successfully." is a valid message. Can it be captured somehow? What about other messages that might be generated? (I don't know what they are, as I'm just speaking for the original poster.)|||

Phil Brammer wrote:

What about other messages thrown by SQL? I don't know what they are, but "Command(s) completed successfully." is a valid message. Can it be captured somehow? What about other messages that might be generated? (I don't know what they are, as I'm just speaking for the original poster.)

I may be completely wrong about this but messages such as "Command(s) completed successfully." are messages generated by SSMS, not the database engine. Hence, they wouldn't be accessible from SSIS.

-Jamie

|||

The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.

It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.

|||

M.Glenn wrote:

The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.

It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.

Mike,

No problem.

If you think that BOL is short somewhere then tell them. The option to provide feedback is taken very seriously (I know from experience). In this case its more SQL engine or T-SQL stuff rather than SSIS - but provide it anyway.

-Jamie

|||

M.Glenn wrote:

The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.

It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.

SSMS is merely a GUI for SQL Server. Having said that, all of SQL Server's functionality can be accessed via T-SQL. For example, when a query executes without errors (in other words, when it completes successfully), SQL Server sets the @.@.ERROR variable to "0". This value can be returned to the client via a stored proceedure output parameter.

One should bear in mind that SSIS is a very advanced feature of SQL Server. As such, a mastery of T-SQL is extremely helpful when using SSIS.

|||

Duane Douglas wrote:

SSMS is merely a GUI for SQL Server. Having said that, all of SQL Server's functionality can be accessed via T-SQL. For example, when a query executes without errors (in other words, when it completes successfully), SQL Server sets the @.@.ERROR variable to "0". This value can be returned to the client via a stored proceedure output parameter.

One should bear in mind that SSIS is a very advanced feature of SQL Server. As such, a mastery of T-SQL is extremely helpful when using SSIS.

Agreed, but mastery of T-SQL is not the central issue in this case. I've used @.@.ERROR, @.@.ROWCOUNT, etc. in scripts, stored procs and triggers plenty of times. DTS/SSIS is where I'm on less familiar ground--only recently finding time to delve deeper. My frustration, which Phil Brammer's questions helped to tease out, was explained when Jamie Thompson stated "...messages such as "Command(s) completed successfully." are messages generated by SSMS, not the database engine. Hence, they wouldn't be accessible from SSIS." This together with your posts made it clear that the message stream provided by SSMS or QA is not available in SSIS and rolling your own, so to speak, is the only solution. Okay, I can take it from there.

However, this is disappointing. If you're building an SSIS solution from scratch it's not a problem. But in this case I'm using SSIS to automate more than a dozen vendor developed DDL and DML scripts that otherwise have to be run manually in SSMS. One script would have to be manually rerun more than 40 times, entering a different "batch number" each time. I let them know I wasn't too happy about that one. Anyway, instead of someone sitting there babysitting this convoluted upgrade/cleanup process for hours (or in this case days), SSIS makes it easy to automate the entire process--run the scripts in proper order, manage precedence constraints, enumerate the batch numbers in a forEach loop, etc.

Everything is working beautifully except for logging. A few of the scripts contain 20 or more individual queries. Running them in SSMS or QA would automatically give me a nice message log of the number of records affected, output from print statements embedded in the scripts, error messages, etc. If I understand you guys correctly, there's no built-in functionality to generate this kind of message stream in SSIS. I'd have to break the big scripts down, create ExecuteSQL tasks for each individual query (around 50 to 60 altogether) add and map output parameters for each and configure logging to capture all this. Not a happy prospect.

Is there any reason Microsoft couldn't duplicate SSMS/QA message stream functionality in a future release of SSIS? It would save a lot of work in cases like this. I'll suggest it unless someone has a good reason why it couldn't be done or (preferably) knows of a workaround for this situation.

Happy holiday!

|||

OK, so then how do you write the contents of the package-scoped variable to the log?

I think I've got my rowCnt into a variable but I can see what value it is.

I've tried having the package log 'OnVariableValueChanged' events, but it just tells me the name of the variable, not the value.

(I'm using SQL2005 SP2)

Thanks

|||

Andy Abel wrote:

OK, so then how do you write the contents of the package-scoped variable to the log?

I think I've got my rowCnt into a variable but I can see what value it is.

I've tried having the package log 'OnVariableValueChanged' events, but it just tells me the name of the variable, not the value.

(I'm using SQL2005 SP2)

Thanks

If you are just trying to see the value of the variable, add a breakpoint and use the Watch window to view it. If you need to record the value, you could add an event handler for the OnVariableValueChanged event, and use an Execute SQL task to write it to the database.