Login failed for user Admin.

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Admin;Initial
Catalog=Northwind;Data Source=LocalNet;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=LocalNet;Use Encryption for
Data=False;Tag with column collation when possible=False

Any ideas on how to solve this problem?

Cheersi could find out

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Admin;password=******;Initial
Catalog=Northwind;Data Source=LocalNet;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=LocalNet;Use Encryption for
Data=False;Tag with column collation when possible=False

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

what is the login procedure for sql server?usually i just enter my username and password into the appropriate fields and press enter

:)|||usually i just enter my username and password into the appropriate fields and press enter

:)And you claim not to be a DBA :)|||There are literally dozens of ways to log in to SQL Server, depending on how the security has been set up. When you add the complexity of user developed front ends, the number of ways to log in goes into the tens of thousands.

Considering only Microsoft's Query Analyzer, I can go for the short list. Keep in mind that if your configuration requires C2 hardware tokens, you must have them recognized BEFORE you can launch Query Analyzer in C2 mode.

1. In a simple, Windows authenticated and AD managed configuration, you double click the Query Analyzer icon and you're done. Nothing more is needed.

2. In a conventional Windows Authenticated configuration, double click query analyser, then enter or select your server name and press Ok.

3. In a SQL Authenticated configuration, enter your server, login name, and password, then click Ok.

4. In any of the "developer" configurations, contact the appropriate developer. These choices are endless!

You really need to discuss this whole process in more detail with the administrator(s) of the SQL Server you are trying to access. Depending on how they've configured SQL Server's security, there could be lots of issues preventing you from connecting, some of which have nothing at all to do with security!

-PatP

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.

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 Sql error to a table

CREATE PROCEDURE GetData
AS
SELECT * FROM Bogus
When I am executing above procedure I got following error.
Server: Msg 208, Level 16, State 1, Procedure GetData, Line 3
Invalid object name 'Bogus'.
I want to Insert above sql generated error message to a table from a
procedure (Ver.SQl 2000)
How can I do it ?You can't. Such errors are batch-aborting and need to be handled by the clie
nt application. Read the
error handling articles at www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sha" <Sha@.discussions.microsoft.com> wrote in message
news:D0E50949-4A04-4DF3-AD58-D0140D3A3B87@.microsoft.com...
> CREATE PROCEDURE GetData
> AS
> SELECT * FROM Bogus
> When I am executing above procedure I got following error.
> Server: Msg 208, Level 16, State 1, Procedure GetData, Line 3
> Invalid object name 'Bogus'.
> I want to Insert above sql generated error message to a table from a
> procedure (Ver.SQl 2000)
> How can I do it ?
>