Hi all,
I get the error message Login failed for user 'computername\ASPNET'
when trying to log to MSDE 2000 SP 3 with .NET application.
My computer is Windows 2000 + SP4 with latest security patches.
I have tried to set the permissions to the database I am connecting
(mydatabase) with script:
sp_grantlogin 'mycomputer\ASPNET'
go
use mydatabase
go
sp_grantdbaccess 'mycomputer\ASPNET'
go
sp_addrolemember 'db_owner', 'mycomputer\ASPNET'
go
Still the error comes.
Does anybody have ideas what to try next.
PS. Software reinstall has been tried (.NET framework 1.1, MSDE).
Regards
Jarmo
Hi,
Did you logged into your machine using 'mycomputer\ASPNET' ? If not login to
the machine using 'mycomputer\ASPNET' and try
connecting using OSQL and confirm.
Thanks
Hari
MCDBA
"jarmopy" <jarmo.pyorny@.luukku.com> wrote in message
news:5d70b654.0408152138.6ca4d0d8@.posting.google.c om...
> Hi all,
> I get the error message Login failed for user 'computername\ASPNET'
> when trying to log to MSDE 2000 SP 3 with .NET application.
> My computer is Windows 2000 + SP4 with latest security patches.
> I have tried to set the permissions to the database I am connecting
> (mydatabase) with script:
>
> sp_grantlogin 'mycomputer\ASPNET'
> go
> use mydatabase
> go
> sp_grantdbaccess 'mycomputer\ASPNET'
> go
> sp_addrolemember 'db_owner', 'mycomputer\ASPNET'
> go
> Still the error comes.
> Does anybody have ideas what to try next.
> PS. Software reinstall has been tried (.NET framework 1.1, MSDE).
> Regards
> Jarmo
|||Hi Hari,
Local policy does not allow to log in as aspnet:
"The local policy of this system does not permit you to logon interactively."
It is only permitted to log on as a batch job.
Regards
Jarmo
"Hari Prasad" wrote:
> Hi,
> Did you logged into your machine using 'mycomputer\ASPNET' ? If not login to
> the machine using 'mycomputer\ASPNET' and try
> connecting using OSQL and confirm.
> Thanks
> Hari
> MCDBA
>
|||Hi again,
I changed the policy to be able to log in.
When giving the command as mycomputer\aspnet user
osql -E -S mycomputer\myinstance
I get the same error message:
Login failed.
Regards
Jarmo
|||There is no need to log in as ASPNET account
Go to MSDN site and look in SQL SERVER 2000 SDK Documentation (it is valid
for MSDE)
There is explanation how to use ASP .NET applications with SQL Server
"jarmopy" <jarmo.pyorny@.luukku.com> wrote in message
news:5d70b654.0408152138.6ca4d0d8@.posting.google.c om...
> Hi all,
> I get the error message Login failed for user 'computername\ASPNET'
> when trying to log to MSDE 2000 SP 3 with .NET application.
> My computer is Windows 2000 + SP4 with latest security patches.
> I have tried to set the permissions to the database I am connecting
> (mydatabase) with script:
>
> sp_grantlogin 'mycomputer\ASPNET'
> go
> use mydatabase
> go
> sp_grantdbaccess 'mycomputer\ASPNET'
> go
> sp_addrolemember 'db_owner', 'mycomputer\ASPNET'
> go
> Still the error comes.
> Does anybody have ideas what to try next.
> PS. Software reinstall has been tried (.NET framework 1.1, MSDE).
> Regards
> Jarmo
|||Hi,
thanks for the answers.
This seems to be a problem only in one computer.
In other machines this is working OK.
Showing posts with label msde. Show all posts
Showing posts with label msde. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Login failed for user
Hi All
I am using MSDE2K and an application written in VB6
After installing MSDE and my program on to a new computer I am having
strange problems when saving data which I didn't get on the old computer
My connection string is...
cn.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;User
ID=Tramcars;Password=ga711bm;Initial Catalog=Tramcars;Data Source=(local)"
(User ID Tramcars exists in MSDE and is included in the db_owner role)
This connects me OK and allows me to enter new data and save
On creating a new entry and attempting to save again I get the message
'Login failed for user Tramcars'
If I set 'Persist Security Info=True' in the connection string, all works
fine
I am completely lost
Any ideas?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am using MSDE2K and an application written in VB6
> After installing MSDE and my program on to a new computer I am having
> strange problems when saving data which I didn't get on the old
> computer
> My connection string is...
> cn.ConnectionString = "Provider=SQLOLEDB;Persist Security
> Info=False;User ID=Tramcars;Password=ga711bm;Initial
> Catalog=Tramcars;Data Source=(local)"
> (User ID Tramcars exists in MSDE and is included in the db_owner role)
> This connects me OK and allows me to enter new data and save
> On creating a new entry and attempting to save again I get the message
> 'Login failed for user Tramcars'
> If I set 'Persist Security Info=True' in the connection string, all
> works fine
>
MSDE installs by default disabling standard SQL Server authenticated
connections..
thus only WinNT (trusted) connections will work..
you can modify this behavior both at install time, providing the
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer, or later, at "run-time",
hacking the windows registry as described in
http://support.microsoft.com/default...b;en-us;285097
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
MSDE is set to mixed mode when this happens
Strange thing is it allows me to save once, but fails with message on second
save?
Regards
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n8lalFcjubU1@.individual.net...
> hi Steve,
> steve wrote:
> MSDE installs by default disabling standard SQL Server authenticated
> connections..
> thus only WinNT (trusted) connections will work..
> you can modify this behavior both at install time, providing the
> SECURITYMODE=SQL
> parameter to the setup.exe boostrap installer, or later, at "run-time",
> hacking the windows registry as described in
> http://support.microsoft.com/default...b;en-us;285097
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Steve,
steve wrote:
> Hi Andrea
> MSDE is set to mixed mode when this happens
> Strange thing is it allows me to save once, but fails with message on
> second save?
>
you mean you can connect with the provided connection string once, but if
you try a second time you get that exception?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Yes thats exactly what I mean
I can connect to msde when my program starts, to populate comboboxes etc
I always close the connection when not needed (con.close)
I can then create 1 invoice and save it OK, which requires..
con.open
code to save data....
con.close
If I then try to save another invoice
con.open
etc
con.close
Error raised at con.open (login failed for user Tramcarslogin)
It is like the connectionstring forgets the login password on the second
attempt
If I use persist security info=true in the connectionstring I don't get the
error and everything is OK
Regards
Steve
"Andrea Montanari" wrote:
> hi Steve,
> steve wrote:
> you mean you can connect with the provided connection string once, but if
> you try a second time you get that exception?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Steve,
Steve wrote:
> Hi Andrea
> Yes thats exactly what I mean
> I can connect to msde when my program starts, to populate comboboxes
> etc
> I always close the connection when not needed (con.close)
> I can then create 1 invoice and save it OK, which requires..
> con.open
> code to save data....
> con.close
> If I then try to save another invoice
> con.open
> etc
> con.close
> Error raised at con.open (login failed for user Tramcarslogin)
> It is like the connectionstring forgets the login password on the
> second attempt
> If I use persist security info=true in the connectionstring I don't
> get the error and everything is OK
ok, I think I understand... or hope so...
you do something like
Dim sCon As String
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
sCon = "Provider=sqloledb;Data Source=(Local);Initial Catalog=pubs;User
Id=sa;Password=xxx;persist security info=false"
oCon.Open sCon
DoSomethingWithConnection oCon ' here you consume a recordset or command or
the like
oCon.Close
oCon.Open
DoSomethingWithConnection oCon ' here you consume a recordset or command or
the like
oCon.Close
Set oCon = Nothing
that's to say you open the connection the second time without providing a
connection string and relying on the old provided one..
the specific property, "Persist Security Info" is there to specify whether
or not the data source object is allowed to persist sensitive authentication
information (such as pwd) along with other info.. as it has been set to
false, there's no way for the 2nd open method to know such a kind of info,
and the provider will refuse to pass the connection on..
if this is the case, you should not rely on such a mechanism for 2 reason..
1st you always should completely destroy the ADODB.Connection object when
not needed,
oCon.Close
Set oCon = Nothing
so that all finalizers of the object will be properly called, and the conn
will return to the connection pool...
2nd, (and also becouse of the preceding one) you always should provide a
full qualified connection string...
is a comparable/compatible one can be found in the connection pool, it will
be picked up from there, or a new one will be createad ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Thanks again for a great explanation
Explains everything
Regards
Steve
"Andrea Montanari" wrote:
> hi Steve,
> Steve wrote:
> ok, I think I understand... or hope so...
> you do something like
> Dim sCon As String
> Dim oCon As ADODB.Connection
> Set oCon = New ADODB.Connection
> sCon = "Provider=sqloledb;Data Source=(Local);Initial Catalog=pubs;User
> Id=sa;Password=xxx;persist security info=false"
> oCon.Open sCon
> DoSomethingWithConnection oCon ' here you consume a recordset or command or
> the like
> oCon.Close
> oCon.Open
> DoSomethingWithConnection oCon ' here you consume a recordset or command or
> the like
> oCon.Close
> Set oCon = Nothing
> that's to say you open the connection the second time without providing a
> connection string and relying on the old provided one..
> the specific property, "Persist Security Info" is there to specify whether
> or not the data source object is allowed to persist sensitive authentication
> information (such as pwd) along with other info.. as it has been set to
> false, there's no way for the 2nd open method to know such a kind of info,
> and the provider will refuse to pass the connection on..
> if this is the case, you should not rely on such a mechanism for 2 reason..
> 1st you always should completely destroy the ADODB.Connection object when
> not needed,
> oCon.Close
> Set oCon = Nothing
> so that all finalizers of the object will be properly called, and the conn
> will return to the connection pool...
> 2nd, (and also becouse of the preceding one) you always should provide a
> full qualified connection string...
> is a comparable/compatible one can be found in the connection pool, it will
> be picked up from there, or a new one will be createad ...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
I am using MSDE2K and an application written in VB6
After installing MSDE and my program on to a new computer I am having
strange problems when saving data which I didn't get on the old computer
My connection string is...
cn.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;User
ID=Tramcars;Password=ga711bm;Initial Catalog=Tramcars;Data Source=(local)"
(User ID Tramcars exists in MSDE and is included in the db_owner role)
This connects me OK and allows me to enter new data and save
On creating a new entry and attempting to save again I get the message
'Login failed for user Tramcars'
If I set 'Persist Security Info=True' in the connection string, all works
fine
I am completely lost
Any ideas?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am using MSDE2K and an application written in VB6
> After installing MSDE and my program on to a new computer I am having
> strange problems when saving data which I didn't get on the old
> computer
> My connection string is...
> cn.ConnectionString = "Provider=SQLOLEDB;Persist Security
> Info=False;User ID=Tramcars;Password=ga711bm;Initial
> Catalog=Tramcars;Data Source=(local)"
> (User ID Tramcars exists in MSDE and is included in the db_owner role)
> This connects me OK and allows me to enter new data and save
> On creating a new entry and attempting to save again I get the message
> 'Login failed for user Tramcars'
> If I set 'Persist Security Info=True' in the connection string, all
> works fine
>
MSDE installs by default disabling standard SQL Server authenticated
connections..
thus only WinNT (trusted) connections will work..
you can modify this behavior both at install time, providing the
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer, or later, at "run-time",
hacking the windows registry as described in
http://support.microsoft.com/default...b;en-us;285097
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
MSDE is set to mixed mode when this happens
Strange thing is it allows me to save once, but fails with message on second
save?
Regards
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3n8lalFcjubU1@.individual.net...
> hi Steve,
> steve wrote:
> MSDE installs by default disabling standard SQL Server authenticated
> connections..
> thus only WinNT (trusted) connections will work..
> you can modify this behavior both at install time, providing the
> SECURITYMODE=SQL
> parameter to the setup.exe boostrap installer, or later, at "run-time",
> hacking the windows registry as described in
> http://support.microsoft.com/default...b;en-us;285097
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Steve,
steve wrote:
> Hi Andrea
> MSDE is set to mixed mode when this happens
> Strange thing is it allows me to save once, but fails with message on
> second save?
>
you mean you can connect with the provided connection string once, but if
you try a second time you get that exception?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Yes thats exactly what I mean
I can connect to msde when my program starts, to populate comboboxes etc
I always close the connection when not needed (con.close)
I can then create 1 invoice and save it OK, which requires..
con.open
code to save data....
con.close
If I then try to save another invoice
con.open
etc
con.close
Error raised at con.open (login failed for user Tramcarslogin)
It is like the connectionstring forgets the login password on the second
attempt
If I use persist security info=true in the connectionstring I don't get the
error and everything is OK
Regards
Steve
"Andrea Montanari" wrote:
> hi Steve,
> steve wrote:
> you mean you can connect with the provided connection string once, but if
> you try a second time you get that exception?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Steve,
Steve wrote:
> Hi Andrea
> Yes thats exactly what I mean
> I can connect to msde when my program starts, to populate comboboxes
> etc
> I always close the connection when not needed (con.close)
> I can then create 1 invoice and save it OK, which requires..
> con.open
> code to save data....
> con.close
> If I then try to save another invoice
> con.open
> etc
> con.close
> Error raised at con.open (login failed for user Tramcarslogin)
> It is like the connectionstring forgets the login password on the
> second attempt
> If I use persist security info=true in the connectionstring I don't
> get the error and everything is OK
ok, I think I understand... or hope so...
you do something like
Dim sCon As String
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
sCon = "Provider=sqloledb;Data Source=(Local);Initial Catalog=pubs;User
Id=sa;Password=xxx;persist security info=false"
oCon.Open sCon
DoSomethingWithConnection oCon ' here you consume a recordset or command or
the like
oCon.Close
oCon.Open
DoSomethingWithConnection oCon ' here you consume a recordset or command or
the like
oCon.Close
Set oCon = Nothing
that's to say you open the connection the second time without providing a
connection string and relying on the old provided one..
the specific property, "Persist Security Info" is there to specify whether
or not the data source object is allowed to persist sensitive authentication
information (such as pwd) along with other info.. as it has been set to
false, there's no way for the 2nd open method to know such a kind of info,
and the provider will refuse to pass the connection on..
if this is the case, you should not rely on such a mechanism for 2 reason..
1st you always should completely destroy the ADODB.Connection object when
not needed,
oCon.Close
Set oCon = Nothing
so that all finalizers of the object will be properly called, and the conn
will return to the connection pool...
2nd, (and also becouse of the preceding one) you always should provide a
full qualified connection string...
is a comparable/compatible one can be found in the connection pool, it will
be picked up from there, or a new one will be createad ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
Thanks again for a great explanation
Explains everything
Regards
Steve
"Andrea Montanari" wrote:
> hi Steve,
> Steve wrote:
> ok, I think I understand... or hope so...
> you do something like
> Dim sCon As String
> Dim oCon As ADODB.Connection
> Set oCon = New ADODB.Connection
> sCon = "Provider=sqloledb;Data Source=(Local);Initial Catalog=pubs;User
> Id=sa;Password=xxx;persist security info=false"
> oCon.Open sCon
> DoSomethingWithConnection oCon ' here you consume a recordset or command or
> the like
> oCon.Close
> oCon.Open
> DoSomethingWithConnection oCon ' here you consume a recordset or command or
> the like
> oCon.Close
> Set oCon = Nothing
> that's to say you open the connection the second time without providing a
> connection string and relying on the old provided one..
> the specific property, "Persist Security Info" is there to specify whether
> or not the data source object is allowed to persist sensitive authentication
> information (such as pwd) along with other info.. as it has been set to
> false, there's no way for the 2nd open method to know such a kind of info,
> and the provider will refuse to pass the connection on..
> if this is the case, you should not rely on such a mechanism for 2 reason..
> 1st you always should completely destroy the ADODB.Connection object when
> not needed,
> oCon.Close
> Set oCon = Nothing
> so that all finalizers of the object will be properly called, and the conn
> will return to the connection pool...
> 2nd, (and also becouse of the preceding one) you always should provide a
> full qualified connection string...
> is a comparable/compatible one can be found in the connection pool, it will
> be picked up from there, or a new one will be createad ...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
Login failed for user
I installed Sales Logix (a database program) which uses
SQL Server MSDE. When you install Sales Logix, it
installs SQL Server; however, I was having trouble getting
SQL to install so worked with a Microsoft tech and we got
a default instance of SQL Server MSDE installed. Now,
when I try to attach my SLX database using the SalesLogix
Attach Remote utility, I get the following error:
[Microsoft][ODBC SQL Driver][SQL Server Driver][SQL Server]
Login failed for user 'sa'. Reason: Not associated with
a trusted SQL Server connection.
How would I associate this with a trusted SQL Server
connection? Any help would be greatly appreciated!
Leesa
hi Leesa,
"Leesa" <leesaforeman@.fccef.com> ha scritto nel messaggio
news:2f55001c46c41$0fa2ad50$a301280a@.phx.gbl...
> I installed Sales Logix (a database program) which uses
> SQL Server MSDE. When you install Sales Logix, it
> installs SQL Server; however, I was having trouble getting
> SQL to install so worked with a Microsoft tech and we got
> a default instance of SQL Server MSDE installed. Now,
> when I try to attach my SLX database using the SalesLogix
> Attach Remote utility, I get the following error:
> [Microsoft][ODBC SQL Driver][SQL Server Driver][SQL Server]
> Login failed for user 'sa'. Reason: Not associated with
> a trusted SQL Server connection.
> How would I associate this with a trusted SQL Server
> connection? Any help would be greatly appreciated!
> Leesa
MSDE install by default granting only Windows NT trusted connections, so SQL
Server authenticated connections, using "user" + "pwd" credential are not
allowed...
you can change this behaviour both at install time and later, using the
advices provided at
http://support.microsoft.com/default...b;en-us;285097
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
SQL Server MSDE. When you install Sales Logix, it
installs SQL Server; however, I was having trouble getting
SQL to install so worked with a Microsoft tech and we got
a default instance of SQL Server MSDE installed. Now,
when I try to attach my SLX database using the SalesLogix
Attach Remote utility, I get the following error:
[Microsoft][ODBC SQL Driver][SQL Server Driver][SQL Server]
Login failed for user 'sa'. Reason: Not associated with
a trusted SQL Server connection.
How would I associate this with a trusted SQL Server
connection? Any help would be greatly appreciated!
Leesa
hi Leesa,
"Leesa" <leesaforeman@.fccef.com> ha scritto nel messaggio
news:2f55001c46c41$0fa2ad50$a301280a@.phx.gbl...
> I installed Sales Logix (a database program) which uses
> SQL Server MSDE. When you install Sales Logix, it
> installs SQL Server; however, I was having trouble getting
> SQL to install so worked with a Microsoft tech and we got
> a default instance of SQL Server MSDE installed. Now,
> when I try to attach my SLX database using the SalesLogix
> Attach Remote utility, I get the following error:
> [Microsoft][ODBC SQL Driver][SQL Server Driver][SQL Server]
> Login failed for user 'sa'. Reason: Not associated with
> a trusted SQL Server connection.
> How would I associate this with a trusted SQL Server
> connection? Any help would be greatly appreciated!
> Leesa
MSDE install by default granting only Windows NT trusted connections, so SQL
Server authenticated connections, using "user" + "pwd" credential are not
allowed...
you can change this behaviour both at install time and later, using the
advices provided at
http://support.microsoft.com/default...b;en-us;285097
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Monday, March 19, 2012
Login failed
I have VS2005 Beta 2
I have a Global.asax file containing:Cannot open database "myDataBase" requested by the login. The login failed.
You use integrated security to connect to the database and when testing local in VS2005 it will use the currently logged on user running VS2005. Most likely that is your user account and it has access to the database.
I created a very simple Web Site that has a Login page and a Default Page
I installed MSDE so that I can use a SQL 2000 database
I ran C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\aspnet_regsql.exe
to create a database in the MSSQLSERVER for user info.
My Web.config file looks like:
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=myDataBase;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilation debug="false">
<assemblies>
<add assembly="System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation>
<authorization>
<deny users="?"></deny>
</authorization>
<authentication mode="Forms"/>
</system.web>
</configuration>
I have a Global.asax file containing:
void Application_Start(Object sender, EventArgs e) {
// always create bob and alice as users if they are not present
// so you can login and we can avoid dealing with admin
//
MembershipCreateStatus status;
if (Membership.FindUsersByName("bob").Count == 0)
Membership.CreateUser("bob", "pass@.word1", "bob@.wingtip.com", "no question", "no answer", true, out status);
if (Membership.FindUsersByName("alice").Count == 0)
Membership.CreateUser("alice", "pass@.word1", "alice@.wingtip.com", "no question", "no answer", true, out status);
}
When I copy the Web site to a virtual directory set up in IIS and the open the web site using Local IIS in VS2005 I get:
Cannot open database "myDataBase" requested by the login. The login failed.
Login failed for user 'BARBARA\ASPNET'.
You use integrated security to connect to the database and when testing local in VS2005 it will use the currently logged on user running VS2005. Most likely that is your user account and it has access to the database.
The error message indicates you are trying to logon to the database with BARBARA\ASPNET user and that is the user setup to run the application in IIS. You could either change this to be another user that has access to the database needed, under directory security in IIS, or give the user BARBARA\ASPNET access to the database.|||Thanks for your reply - I don't have that much experience with this stuff -
Could you please tell me how I can go about giving BARBARA\ASPNET access to the database? I don't know about changing the user that is accessing the database in IIS. BARBARA is the name of the computer and ASPNET is the user that was created on the computer when all this stuff was loaded. Do I have to change the database server to use mixed security?
Is it a user that has to be set up for the server or for the database? In any case, I don't really know how to do this. Any direction would be appreciated.
thanks|||You might want to have a look at 3rd party tools to adminstrate MSDE.
You can do it directly in a query also.
exec sp_grantlogin 'BARBARA\ASPNET'
go
use myDataBase
go
exec sp_grantdbaccess 'BARBARA\ASPNET', 'ASPNET'
go
exec sp_addrolemember 'public', 'ASPNET'
This assumes that the group public has the proper permissions in the database. You could use dbo instead of public then the ASPNET user will be acting as database owner.
|||okay thank you very much - that gives me a direction
Login failed
I have VS2005 Beta 2
I have a Global.asax file containing:Cannot open database "myDataBase" requested by the login. The login failed.
You use integrated security to connect to the database and when testing local in VS2005 it will use the currently logged on user running VS2005. Most likely that is your user account and it has access to the database.
I created a very simple Web Site that has a Login page and a Default Page
I installed MSDE so that I can use a SQL 2000 database
I ranC:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\aspnet_regsql.exe
to create a database in the MSSQLSERVER for user info.
My Web.config file looks like:
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=myDataBase;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilation debug="false">
<assemblies>
<add assembly="System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation>
<authorization>
<deny users="?"></deny>
</authorization>
<authentication mode="Forms"/>
</system.web>
</configuration>
I have a Global.asax file containing:
void Application_Start(Object sender, EventArgs e) {
// always create bob and alice as users if they are not present
// so you can login and we can avoid dealing with admin
//
MembershipCreateStatus status;
if (Membership.FindUsersByName("bob").Count == 0)
Membership.CreateUser("bob", "pass@.word1", "bob@.wingtip.com", "no question", "no answer", true, out status);
if (Membership.FindUsersByName("alice").Count == 0)
Membership.CreateUser("alice", "pass@.word1", "alice@.wingtip.com", "no question", "no answer", true, out status);
}
When I copy the Web site to a virtual directory set up in IIS and the open the web site using Local IIS in VS2005 I get:
Cannot open database "myDataBase" requested by the login. The login failed.
Login failed for user 'BARBARA\ASPNET'.
You use integrated security to connect to the database and when testing local in VS2005 it will use the currently logged on user running VS2005. Most likely that is your user account and it has access to the database.
The error message indicates you are trying to logon to the database with BARBARA\ASPNET user and that is the user setup to run the application in IIS. You could either change this to be another user that has access to the database needed, under directory security in IIS, or give the user BARBARA\ASPNET access to the database.|||Thanks for your reply - I don't have that much experience with this stuff -
Could you please tell me how I can go about giving BARBARA\ASPNET access to the database? I don't know about changing the user that is accessing the database in IIS. BARBARA is the name of the computer and ASPNET is the user that was created on the computer when all this stuff was loaded. Do I have to change the database server to use mixed security?
Is it a user that has to be set up for the server or for the database? In any case, I don't really know how to do this. Any direction would be appreciated.
thanks|||You might want to have a look at 3rd party tools to adminstrate MSDE.
You can do it directly in a query also.
exec sp_grantlogin 'BARBARA\ASPNET'
go
use myDataBase
go
exec sp_grantdbaccess 'BARBARA\ASPNET', 'ASPNET'
go
exec sp_addrolemember 'public', 'ASPNET'
This assumes that the group public has the proper permissions in the database. You could use dbo instead of public then the ASPNET user will be acting as database owner.|||okay thank you very much - that gives me a direction
Login Failed
I have installed MSDE on a w/s and when logged in as administrator I can
access the server using OSQL -E however if I am logged in as a user and try
to access the server I am denied access.
I have the SQL tools installed on a different machine and would like to
access this server using EM across the LAN, on the machine running EM do I
need to login as administrator in order to connect the MSDE server machine ?
Thanks
Murphy
"Murphy" <Murphy@.murphy.com> wrote in message
news:u3stcLpLEHA.1052@.TK2MSFTNGP12.phx.gbl...
> I have installed MSDE on a w/s and when logged in as administrator I can
> access the server using OSQL -E however if I am logged in as a user and
try
> to access the server I am denied access.
> I have the SQL tools installed on a different machine and would like to
> access this server using EM across the LAN, on the machine running EM do I
> need to login as administrator in order to connect the MSDE server machine
?
> Thanks
> Murphy
>
I have just logged in ad administrator on the machine running EM and tried
to connect to the MSDE server using:
C:\>osql -S MachineA -E
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
C:\>
This command works perfectly when run on the machine runnng MSDE when logged
in as adminsitrator...
What am I missing ?
Thanks
Murphy
|||Is there anything which could be blocking the connection?
Are the machines part of a workgroup or domain?
Some Links:
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&Product=sql2k
Cheers,
James Goodman
"Murphy" <murphy@.murphy.com> wrote in message
news:evoz1NpLEHA.1388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "Murphy" <Murphy@.murphy.com> wrote in message
> news:u3stcLpLEHA.1052@.TK2MSFTNGP12.phx.gbl...
> try
I[vbcol=seagreen]
machine
> ?
> I have just logged in ad administrator on the machine running EM and tried
> to connect to the MSDE server using:
> C:\>osql -S MachineA -E
> [DBNETLIB]SQL Server does not exist or access denied.
> [DBNETLIB]ConnectionOpen (Connect()).
> C:\>
> This command works perfectly when run on the machine runnng MSDE when
logged
> in as adminsitrator...
> What am I missing ?
> Thanks
> Murphy
>
|||There's no local firewall running etc and the machines are part of a domain.
Thanks
Murphy
"James" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:uqnZ0cpLEHA.556@.tk2msftngp13.phx.gbl...
> Is there anything which could be blocking the connection?
> Are the machines part of a workgroup or domain?
>
> Some Links:
>
http://support.microsoft.com/default...&Product=sql2k
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
>
>
> --
> Cheers,
> James Goodman
> "Murphy" <murphy@.murphy.com> wrote in message
> news:evoz1NpLEHA.1388@.TK2MSFTNGP09.phx.gbl...
can[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
do[vbcol=seagreen]
> I
> machine
tried
> logged
>
access the server using OSQL -E however if I am logged in as a user and try
to access the server I am denied access.
I have the SQL tools installed on a different machine and would like to
access this server using EM across the LAN, on the machine running EM do I
need to login as administrator in order to connect the MSDE server machine ?
Thanks
Murphy
"Murphy" <Murphy@.murphy.com> wrote in message
news:u3stcLpLEHA.1052@.TK2MSFTNGP12.phx.gbl...
> I have installed MSDE on a w/s and when logged in as administrator I can
> access the server using OSQL -E however if I am logged in as a user and
try
> to access the server I am denied access.
> I have the SQL tools installed on a different machine and would like to
> access this server using EM across the LAN, on the machine running EM do I
> need to login as administrator in order to connect the MSDE server machine
?
> Thanks
> Murphy
>
I have just logged in ad administrator on the machine running EM and tried
to connect to the MSDE server using:
C:\>osql -S MachineA -E
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
C:\>
This command works perfectly when run on the machine runnng MSDE when logged
in as adminsitrator...
What am I missing ?
Thanks
Murphy
|||Is there anything which could be blocking the connection?
Are the machines part of a workgroup or domain?
Some Links:
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&Product=sql2k
Cheers,
James Goodman
"Murphy" <murphy@.murphy.com> wrote in message
news:evoz1NpLEHA.1388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "Murphy" <Murphy@.murphy.com> wrote in message
> news:u3stcLpLEHA.1052@.TK2MSFTNGP12.phx.gbl...
> try
I[vbcol=seagreen]
machine
> ?
> I have just logged in ad administrator on the machine running EM and tried
> to connect to the MSDE server using:
> C:\>osql -S MachineA -E
> [DBNETLIB]SQL Server does not exist or access denied.
> [DBNETLIB]ConnectionOpen (Connect()).
> C:\>
> This command works perfectly when run on the machine runnng MSDE when
logged
> in as adminsitrator...
> What am I missing ?
> Thanks
> Murphy
>
|||There's no local firewall running etc and the machines are part of a domain.
Thanks
Murphy
"James" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:uqnZ0cpLEHA.556@.tk2msftngp13.phx.gbl...
> Is there anything which could be blocking the connection?
> Are the machines part of a workgroup or domain?
>
> Some Links:
>
http://support.microsoft.com/default...&Product=sql2k
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
>
>
> --
> Cheers,
> James Goodman
> "Murphy" <murphy@.murphy.com> wrote in message
> news:evoz1NpLEHA.1388@.TK2MSFTNGP09.phx.gbl...
can[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
do[vbcol=seagreen]
> I
> machine
tried
> logged
>
Monday, March 12, 2012
login error
when i display the contents of a MS Access database, it displays fine. but when i use SQL/MSDE database, the contents can by displayed well when i run the aspx locally using webmatrix's local server. but when i access the same aspx file online, i get the following error message:
************* ERROR MESSAGE *************
Server Error in '/' Application.
------------------------
Login failed for user 'PC-MARVINE\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'PC-MARVINE\ASPNET'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
************* ERROR MESSAGE *************
the following is the code:
<%@. Page Language="VB" %>
<%@. Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<script runat="server"
' Insert page code here
'</script>
<html>
<head>
</head>
<body>
<form runat="server">
<p>
<wmx:SqlDataSourceControl id="SqlDataSourceControl1" runat="server" SelectCommand="SELECT * FROM [Employees]" ConnectionString="server='(local)'; trusted_connection=true; database='Northwind'" UpdateCommand="" DeleteCommand=""></wmx:SqlDataSourceControl>
<wmx:MxDataGrid id="MxDataGrid2" runat="server" DataSourceControlID="SqlDataSourceControl1" BorderColor="#CCCCCC" AllowSorting="True" DataMember="Employees" AllowPaging="True" BackColor="White" CellPadding="3" DataKeyField="EmployeeID" BorderWidth="1px" BorderStyle="None">
<PagerStyle horizontalalign="Center" forecolor="#000066" backcolor="White" mode="NumericPages"></PagerStyle>
<FooterStyle forecolor="#000066" backcolor="White"></FooterStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#669999"></SelectedItemStyle>
<ItemStyle forecolor="#000066"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#006699"></HeaderStyle>
</wmx:MxDataGrid>
</p>
<!-- Insert content here -->
</form>
</body>
</html
do i need to insert some code to do this properly? obviously running the aspx file to display the SQL/MSDE database contents locally works perfectly. i have a webserver setup at home. but when another computer tries to access that file online, i get the error message shown above.
regards,
marvineMy instinct say that the user id under which ASP.NET is running (PC-MARVINE\ASPNET) doesn't have access rights to the SQL Server database. There are a couple of solutions to this problem, the correct one depending on the type of environment you're running and the level of security (read that as risk) that you'd like to have. One of the simplest solutions is to give the ASPNET user access to the SQL Server database. Another is to change the user under which ASP.NET is running to someone that has access to the database. A third is to turn impersonation on for the web site, which means that the person accessing the page will need to have access to the database. There are other options, but I'm sure you get the point.
HTH
Friday, March 9, 2012
login 101..
What is the difference between 'Windows Authentication' login and 'SQL
login'? I have IIS, MSDE installed on my home computer and I don't log
into windows. So how does 'Windows Authentication' apply in this case?
Thanks,
RABMissouriIt depends on what operating system you are running at home.
In some cases, even though you don't explicitly log in, you
are being logged in when the computer starts up. It's just
transparent to the user.
What OS are you running at home?
-Sue
On 22 May 2006 08:31:41 -0700, "RAB" <rabmissouri@.yahoo.com>
wrote:
>What is the difference between 'Windows Authentication' login and 'SQL
>login'? I have IIS, MSDE installed on my home computer and I don't log
>into windows. So how does 'Windows Authentication' apply in this case?
>
>Thanks,
>RABMissouri|||On 22 May 2006 08:31:41 -0700, RAB wrote:
>What is the difference between 'Windows Authentication' login and 'SQL
>login'?
Hi RABMissouri,
Windows authentication - SQL Server uses a special protocol to ask
Windows for the details of the currently logged-in user. If the Windows
user is in the list of allowed logins, the login succeeds wiithout the
user having to enter anything. If the Windows login is not registered as
a SQL Server login, an error message follows.
Advantages: no extra login prompts for user; SQL Server security
benefits from Windows login policies (such as password aging, etc).
Disadvantages: doesn't lend itself for testing; Windows users with admin
rights are automatically logged in as SQL Server admin.
SQL Server login - SQL Server prompts user for loginname and password,
then compares this to list of loginnames and (encrypted) passwords in
system table master..syslogins. On a match, you are logged in.
Advantages: easy when you have to test seecurity for different roles.
Disadvantages: no password expiration, password complexity checks, etc.
> I have IIS, MSDE installed on my home computer and I don't log
>into windows. So how does 'Windows Authentication' apply in this case?
What version of Windows are yoou using?
Hugo Kornelis, SQL Server MVP|||Windows XP Professional...
Thanks,
RABMissouri|||Just a correction for the SQL Authentication description:
On Windows 2003, SQL Server 2005 can enforce the Windows password complexity
and expiration policy for SQL logins.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:bsf472h1m7vuhpjlhfi55eekqe47r7nte3@.
4ax.com...
> On 22 May 2006 08:31:41 -0700, RAB wrote:
>
> Hi RABMissouri,
> Windows authentication - SQL Server uses a special protocol to ask
> Windows for the details of the currently logged-in user. If the Windows
> user is in the list of allowed logins, the login succeeds wiithout the
> user having to enter anything. If the Windows login is not registered as
> a SQL Server login, an error message follows.
> Advantages: no extra login prompts for user; SQL Server security
> benefits from Windows login policies (such as password aging, etc).
> Disadvantages: doesn't lend itself for testing; Windows users with admin
> rights are automatically logged in as SQL Server admin.
> SQL Server login - SQL Server prompts user for loginname and password,
> then compares this to list of loginnames and (encrypted) passwords in
> system table master..syslogins. On a match, you are logged in.
> Advantages: easy when you have to test seecurity for different roles.
> Disadvantages: no password expiration, password complexity checks, etc.
>
> What version of Windows are yoou using?
> --
> Hugo Kornelis, SQL Server MVP|||If you aren't logging in then I would guess you are using
the Welcome screen login option. The user you select is the
user that is logged into windows for you. That is the login
that would access MSDE on your computer if you were using
osql, admin tools, etc. Depending on other settings but
generally if your web site is using Windows Authentication,
it will log into SQL Server using the ComputerName\ASPNET
login.
That's basically how it would work. There is a login
associated with you as well as IIS that can be used as
windows logins in SQL Server.
-Sue
On 22 May 2006 18:40:57 -0700, "RAB" <rabmissouri@.yahoo.com>
wrote:
>Windows XP Professional...
>Thanks,
>RABMissouri
login'? I have IIS, MSDE installed on my home computer and I don't log
into windows. So how does 'Windows Authentication' apply in this case?
Thanks,
RABMissouriIt depends on what operating system you are running at home.
In some cases, even though you don't explicitly log in, you
are being logged in when the computer starts up. It's just
transparent to the user.
What OS are you running at home?
-Sue
On 22 May 2006 08:31:41 -0700, "RAB" <rabmissouri@.yahoo.com>
wrote:
>What is the difference between 'Windows Authentication' login and 'SQL
>login'? I have IIS, MSDE installed on my home computer and I don't log
>into windows. So how does 'Windows Authentication' apply in this case?
>
>Thanks,
>RABMissouri|||On 22 May 2006 08:31:41 -0700, RAB wrote:
>What is the difference between 'Windows Authentication' login and 'SQL
>login'?
Hi RABMissouri,
Windows authentication - SQL Server uses a special protocol to ask
Windows for the details of the currently logged-in user. If the Windows
user is in the list of allowed logins, the login succeeds wiithout the
user having to enter anything. If the Windows login is not registered as
a SQL Server login, an error message follows.
Advantages: no extra login prompts for user; SQL Server security
benefits from Windows login policies (such as password aging, etc).
Disadvantages: doesn't lend itself for testing; Windows users with admin
rights are automatically logged in as SQL Server admin.
SQL Server login - SQL Server prompts user for loginname and password,
then compares this to list of loginnames and (encrypted) passwords in
system table master..syslogins. On a match, you are logged in.
Advantages: easy when you have to test seecurity for different roles.
Disadvantages: no password expiration, password complexity checks, etc.
> I have IIS, MSDE installed on my home computer and I don't log
>into windows. So how does 'Windows Authentication' apply in this case?
What version of Windows are yoou using?
Hugo Kornelis, SQL Server MVP|||Windows XP Professional...
Thanks,
RABMissouri|||Just a correction for the SQL Authentication description:
On Windows 2003, SQL Server 2005 can enforce the Windows password complexity
and expiration policy for SQL logins.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:bsf472h1m7vuhpjlhfi55eekqe47r7nte3@.
4ax.com...
> On 22 May 2006 08:31:41 -0700, RAB wrote:
>
> Hi RABMissouri,
> Windows authentication - SQL Server uses a special protocol to ask
> Windows for the details of the currently logged-in user. If the Windows
> user is in the list of allowed logins, the login succeeds wiithout the
> user having to enter anything. If the Windows login is not registered as
> a SQL Server login, an error message follows.
> Advantages: no extra login prompts for user; SQL Server security
> benefits from Windows login policies (such as password aging, etc).
> Disadvantages: doesn't lend itself for testing; Windows users with admin
> rights are automatically logged in as SQL Server admin.
> SQL Server login - SQL Server prompts user for loginname and password,
> then compares this to list of loginnames and (encrypted) passwords in
> system table master..syslogins. On a match, you are logged in.
> Advantages: easy when you have to test seecurity for different roles.
> Disadvantages: no password expiration, password complexity checks, etc.
>
> What version of Windows are yoou using?
> --
> Hugo Kornelis, SQL Server MVP|||If you aren't logging in then I would guess you are using
the Welcome screen login option. The user you select is the
user that is logged into windows for you. That is the login
that would access MSDE on your computer if you were using
osql, admin tools, etc. Depending on other settings but
generally if your web site is using Windows Authentication,
it will log into SQL Server using the ComputerName\ASPNET
login.
That's basically how it would work. There is a login
associated with you as well as IIS that can be used as
windows logins in SQL Server.
-Sue
On 22 May 2006 18:40:57 -0700, "RAB" <rabmissouri@.yahoo.com>
wrote:
>Windows XP Professional...
>Thanks,
>RABMissouri
Monday, February 20, 2012
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.
>
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.
>
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.
>
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.
>
Subscribe to:
Posts (Atom)