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 incorrect. Show all posts
Showing posts with label incorrect. 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
>
Subscribe to:
Posts (Atom)