When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?
The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:
> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>
sql
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Friday, March 23, 2012
Problem with sp_grantdbaccess
When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:
> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:
> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>
Problem with sp_grantdbaccess
When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:
> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:
> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>
Tuesday, March 20, 2012
Problem with Select Top ....(incorrect syntax near '@p')
hello,
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:
> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:
> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>
Friday, March 9, 2012
Problem with replication
Hi
Using SQL 7.0
I get this error :
>> Line 81: Incorrect syntax near '@.c1'.
when this SP is running :
{call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
Does anyone have an idea ?
Where is @.c1 ? What is in it ?
Thanks !
DonTry checking out the stored proc 'sp_addsynctriggers ' for '@.c1' (which is a
variable) and see what is going on in that area. One note, when SQL says
that an error is 'near' something, it means exactly that; somewhere in the
area of whatever it references (in this case '@.c1') it objects to something.
"Don" wrote:
> Hi
> Using SQL 7.0
> I get this error :
> >> Line 81: Incorrect syntax near '@.c1'.
> when this SP is running :
> {call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
> N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
> N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
> N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
> Does anyone have an idea ?
> Where is @.c1 ? What is in it ?
> Thanks !
> Don
>
Using SQL 7.0
I get this error :
>> Line 81: Incorrect syntax near '@.c1'.
when this SP is running :
{call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
Does anyone have an idea ?
Where is @.c1 ? What is in it ?
Thanks !
DonTry checking out the stored proc 'sp_addsynctriggers ' for '@.c1' (which is a
variable) and see what is going on in that area. One note, when SQL says
that an error is 'near' something, it means exactly that; somewhere in the
area of whatever it references (in this case '@.c1') it objects to something.
"Don" wrote:
> Hi
> Using SQL 7.0
> I get this error :
> >> Line 81: Incorrect syntax near '@.c1'.
> when this SP is running :
> {call sp_addsynctriggers (N'CstAdr', N'null', N'SRV-ERP',
> N'OlympicProd', N'transwu1', N'sp_MSsync_ins_CstAdr_31',
> N'sp_MSsync_upd_CstAdr_31', N'sp_MSsync_del_CstAdr_31',
> N'dbo', N'null', N'ReplicRowVersion', N'null', 0x010000)}
> Does anyone have an idea ?
> Where is @.c1 ? What is in it ?
> Thanks !
> Don
>
Saturday, February 25, 2012
Problem with profiling datetime
Hi,
SQL Server 2005 x64
I have a weird problem when analysing a trace log with DTA.
The problem is that the trace log include syntax error for datetime!!!
Ex. A date time parameter would appear like this in the trace window.
@.ClaimDate=''2006-03-27 00:00:00:000''
-- Note the two single quotes at the end and start...
Whe analysing, DTA give me this error
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
I can't figue out how to change this so I can't profile any statement that
include date.
Please help!!!!
I will add a sample...since my initial post is not that clear...
That simple .NET code
SqlCommand cmd = new SqlCommand("select @.TestDate");
cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
cmd.Connection = conn;
DateTime dt = (DateTime) cmd.ExecuteScalar();
The resulting trace log item is as follow
exec sp_executesql N'select @.TestDate',N'@.TestDate
datetime',@.TestDate=''2006-04-02 18:44:22:687''
Wich will fail when analyzed since there is a syntax error in the command
e.g. two single quote at the end and start of the date.
Please help!
"Martin Masse" wrote:
> Hi,
> SQL Server 2005 x64
> I have a weird problem when analysing a trace log with DTA.
> The problem is that the trace log include syntax error for datetime!!!
> Ex. A date time parameter would appear like this in the trace window.
> @.ClaimDate=''2006-03-27 00:00:00:000''
> -- Note the two single quotes at the end and start...
> Whe analysing, DTA give me this error
> [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
> I can't figue out how to change this so I can't profile any statement that
> include date.
> Please help!!!!
>
>
>
|||Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I will add a sample...since my initial post is not that clear...
> That simple .NET code
> SqlCommand cmd = new SqlCommand("select @.TestDate");
> cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
> cmd.Connection = conn;
> DateTime dt = (DateTime) cmd.ExecuteScalar();
> The resulting trace log item is as follow
> exec sp_executesql N'select @.TestDate',N'@.TestDate
> datetime',@.TestDate=''2006-04-02 18:44:22:687''
> Wich will fail when analyzed since there is a syntax error in the command
> e.g. two single quote at the end and start of the date.
> Please help!
I don't really have a suggestion how to work around this, as I have not
worked much with the DTA.
However, it is obviously a bug somewhere, although I can't tell whether
it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
DBK48153
What I noticed was that if I changed DateTime.Now to "DateTime.Now",
that is a string, I got the correct output in Profiler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks Erland.
"Erland Sommarskog" wrote:
> Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I don't really have a suggestion how to work around this, as I have not
> worked much with the DTA.
> However, it is obviously a bug somewhere, although I can't tell whether
> it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
> the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
> http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
> DBK48153
> What I noticed was that if I changed DateTime.Now to "DateTime.Now",
> that is a string, I got the correct output in Profiler.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
SQL Server 2005 x64
I have a weird problem when analysing a trace log with DTA.
The problem is that the trace log include syntax error for datetime!!!
Ex. A date time parameter would appear like this in the trace window.
@.ClaimDate=''2006-03-27 00:00:00:000''
-- Note the two single quotes at the end and start...
Whe analysing, DTA give me this error
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
I can't figue out how to change this so I can't profile any statement that
include date.
Please help!!!!
I will add a sample...since my initial post is not that clear...
That simple .NET code
SqlCommand cmd = new SqlCommand("select @.TestDate");
cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
cmd.Connection = conn;
DateTime dt = (DateTime) cmd.ExecuteScalar();
The resulting trace log item is as follow
exec sp_executesql N'select @.TestDate',N'@.TestDate
datetime',@.TestDate=''2006-04-02 18:44:22:687''
Wich will fail when analyzed since there is a syntax error in the command
e.g. two single quote at the end and start of the date.
Please help!
"Martin Masse" wrote:
> Hi,
> SQL Server 2005 x64
> I have a weird problem when analysing a trace log with DTA.
> The problem is that the trace log include syntax error for datetime!!!
> Ex. A date time parameter would appear like this in the trace window.
> @.ClaimDate=''2006-03-27 00:00:00:000''
> -- Note the two single quotes at the end and start...
> Whe analysing, DTA give me this error
> [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
> I can't figue out how to change this so I can't profile any statement that
> include date.
> Please help!!!!
>
>
>
|||Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I will add a sample...since my initial post is not that clear...
> That simple .NET code
> SqlCommand cmd = new SqlCommand("select @.TestDate");
> cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
> cmd.Connection = conn;
> DateTime dt = (DateTime) cmd.ExecuteScalar();
> The resulting trace log item is as follow
> exec sp_executesql N'select @.TestDate',N'@.TestDate
> datetime',@.TestDate=''2006-04-02 18:44:22:687''
> Wich will fail when analyzed since there is a syntax error in the command
> e.g. two single quote at the end and start of the date.
> Please help!
I don't really have a suggestion how to work around this, as I have not
worked much with the DTA.
However, it is obviously a bug somewhere, although I can't tell whether
it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
DBK48153
What I noticed was that if I changed DateTime.Now to "DateTime.Now",
that is a string, I got the correct output in Profiler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks Erland.
"Erland Sommarskog" wrote:
> Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I don't really have a suggestion how to work around this, as I have not
> worked much with the DTA.
> However, it is obviously a bug somewhere, although I can't tell whether
> it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
> the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
> http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
> DBK48153
> What I noticed was that if I changed DateTime.Now to "DateTime.Now",
> that is a string, I got the correct output in Profiler.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
Subscribe to:
Posts (Atom)