Create Store Procedure Fails "Incorrect syntax near the keyword 'ON'."

Hi All!

I'm really new to SQL environment in general so, sorry if this is a stupid question.

I'm trying to create a Stored Procedure on my BD with SQL Server Management Studio Express.

I receive this error:
Msg 156, Level 15, State 1, Procedure sprocBlogEntrySelectListByCategory, Line 18
Incorrect syntax near the keyword 'ON'.

This is the sp:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories ON BlogPosts.bp_ID = PostInCategories.bp_ID INNER JOIN

Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

GO

I really don't understand this error and what does this means in my case.

Any suggestion is appreciated.

alan

It seems to me that the error is in your FROM clause. FROM must be followed with a table name, derived table, or view.

In your case, you have a FROM clause followed by a JOIN condition without the JOIN clause.

|||I am guessing you mean this:

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories

INNER JOIN BlogPosts ON BlogPosts.bp_ID = PostInCategories.bp_ID

INNER JOIN Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

hth.

http://www.elsasoft.org|||

I tryed to re-write the sp in SQL Mgm Studio from scratch.
Identical to that one I posted earlier in my opening, and it was accepted without problem.

I think there was same TAB, SPACE or Comma character wrong.

I think my "issue" is resoved.

Thanks for yuor help anyway

Alan.

Create Route

Hi

I have a very simple and probably stupid question. I am new to SB. My question is when I create a service and queue do I have to create a route always?. whats the purpose of creating a route?. what happens if i dont create a route. As I understand creating a route creates a routing table in the database but i am perplexed as to what is the actual use of this routing table and in what way it helps.

Thanks

AK

In the BEGIN DIALOG statement you provide a string as the TO service name:

begin dialog conversation @.handle

from service [initiator]

to service 'target'

on contract [contract]

with encryption = off;

The [initiator] and [contract] are names of actual objects in the database (identifiers), but the 'target' is just any name, a string. What a route does it instructs Service Broker where the service named 'target' is actually located. You create a route like this:

create route [route_to_target]

with service_name = 'target',

address 'tcp://someserver:4022';

which maps the name 'target' to the address 'tcp://someserver:4022'. So a route is always required when you want to reach a service that is located on a different SQL Server instance. You'll see that normally you never have to create a route for cases when both [initiator] and 'target' services are in the same database or the same SQL Server instance. This is because all databases by default contain a route named AutoCreatedLocal that allows any service within the local SQL Server instance to be addressed.

A secondary role for routes is indirection between logical named and physical location. An alternative approach would had been to specify somehow the location in the BEGIN DIALOG, like this:

begin dialog conversation @.handle

from service [initiator]

to service 'tcp://someserver:4022/target'

on contract [contract]

with encryption = off;

but in this moment the application has hardcoded the location of the 'target' service in it's code. In practice once an application gets deployed the physical location of machines hosting services often changes, say the machine 'someserver' gets upgraded to a machine named 'superdome', so the location of the the service 'target' has in fact moved to 'tcp://superdome:4022'. In this case, the administrator can change the route in the database, w/o an application code change beeing necessary (the application still initiates the dialogs with the service 'target', not knowing the actual physical location). Same is true for the case when the database hosting the 'target' service is moved (sp_detach/sp_attach) to a different SQL Server instance.

A third role for routes is to provide support for scale-out load balancing scenarios, by allowing multiple routes for the same service.

A fourth role for routes is to provide support for services hosted in mirrored database (the MIRROR_ADDRESS parameter of CREATE/ALTER ROUTE).

Create Proc

Hello
I have one probably stupid qestion
I want to create procedure that will create user then create database with the same name as user name and the give that user db_owner permision to that data base i created procedure that creat a user and creat a database but i couldn't do last part of it
becous USE statment can't be use in a procedur
Do you have some idea of how to create something like this ?
e.g.
create proc usp
@.login sysname,
@.pwd sysname=''
as
set nocount on
declare @.sql nvarchar(1000)
set @.sql = '--create database
if db_id('+quotename(@.login,char(39)+char(39))+') is null
create database '+quotename(@.login)
print(@.sql)
exec(@.sql)
set @.sql = '--create login
if suser_sid('+quotename(@.login,char(39)+char(39))+') is null
exec sp_addlogin
'+quotename(@.login,char(39)+char(39))+','+quotenam e(@.pwd,char(39)+char(39))+
','+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
set @.sql = '--grantdbaccess
exec '+quotename(@.login)+'..sp_grantdbaccess
'+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
set @.sql = '--add db_owner
exec '+quotename(@.login)+'..sp_addrolemember
''db_owner'','+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
go
"Nikon" <anonymous@.discussions.microsoft.com> wrote in message
news:1B54E87B-2788-4711-B0F2-31D8AEEA085B@.microsoft.com...
> Hello
> I have one probably stupid qestion
> I want to create procedure that will create user then create database with
the same name as user name and the give that user db_owner permision to that
data base i created procedure that creat a user and creat a database but i
couldn't do last part of it becous USE statment can't be use in a procedur
> Do you have some idea of how to create something like this ?
>
|||THX YOU HELPED ME VERY MUCH I now just must analyz it and understend it =)

Create Proc

Hello
I have one probably stupid qestion
I want to create procedure that will create user then create database with t
he same name as user name and the give that user db_owner permision to that
data base i created procedure that creat a user and creat a database but i c
ouldn't do last part of it
becous USE statment can't be use in a procedur
Do you have some idea of how to create something like this 'e.g.
create proc usp
@.login sysname,
@.pwd sysname=''
as
set nocount on
declare @.sql nvarchar(1000)
set @.sql = '--create database
if db_id('+quotename(@.login,char(39)+char(3
9))+') is null
create database '+quotename(@.login)
print(@.sql)
exec(@.sql)
set @.sql = '--create login
if suser_sid('+quotename(@.login,char(39)+ch
ar(39))+') is null
exec sp_addlogin
'+quotename(@.login,char(39)+char(39))+',
'+quotename(@.pwd,char(39)+char(39))+
','+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
set @.sql = '--grantdbaccess
exec '+quotename(@.login)+'..sp_grantdbaccess
'+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
set @.sql = '--add db_owner
exec '+quotename(@.login)+'..sp_addrolemember
''db_owner'','+quotename(@.login,char(39)
+char(39))
print(@.sql)
exec(@.sql)
go
"Nikon" <anonymous@.discussions.microsoft.com> wrote in message
news:1B54E87B-2788-4711-B0F2-31D8AEEA085B@.microsoft.com...
> Hello
> I have one probably stupid qestion
> I want to create procedure that will create user then create database with
the same name as user name and the give that user db_owner permision to that
data base i created procedure that creat a user and creat a database but i
couldn't do last part of it becous USE statment can't be use in a procedur
> Do you have some idea of how to create something like this '
>|||THX YOU HELPED ME VERY MUCH I now just must analyz it and understend it =)

Create Proc

Hello
I have one probably stupid qestion
I want to create procedure that will create user then create database with the same name as user name and the give that user db_owner permision to that data base i created procedure that creat a user and creat a database but i couldn't do last part of it becous USE statment can't be use in a procedu
Do you have some idea of how to create something like this 'e.g.
create proc usp
@.login sysname,
@.pwd sysname=''
as
set nocount on
declare @.sql nvarchar(1000)
set @.sql = '--create database
if db_id('+quotename(@.login,char(39)+char(39))+') is null
create database '+quotename(@.login)
print(@.sql)
exec(@.sql)
set @.sql = '--create login
if suser_sid('+quotename(@.login,char(39)+char(39))+') is null
exec sp_addlogin
'+quotename(@.login,char(39)+char(39))+','+quotename(@.pwd,char(39)+char(39))+
','+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
set @.sql = '--grantdbaccess
exec '+quotename(@.login)+'..sp_grantdbaccess
'+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
set @.sql = '--add db_owner
exec '+quotename(@.login)+'..sp_addrolemember
''db_owner'','+quotename(@.login,char(39)+char(39))
print(@.sql)
exec(@.sql)
go
"Nikon" <anonymous@.discussions.microsoft.com> wrote in message
news:1B54E87B-2788-4711-B0F2-31D8AEEA085B@.microsoft.com...
> Hello
> I have one probably stupid qestion
> I want to create procedure that will create user then create database with
the same name as user name and the give that user db_owner permision to that
data base i created procedure that creat a user and creat a database but i
couldn't do last part of it becous USE statment can't be use in a procedur
> Do you have some idea of how to create something like this '
>|||THX YOU HELPED ME VERY MUCH I now just must analyz it and understend it =)

CREATE LOGIN problem

I know this is a really stupid question but I can't figure out how to make this work. It just doesn't seem to want variables. What is the proper syntax?

CREATE LOGIN [@.GUserName] FROM WINDOWS WITH DEFAULT_DATABASE=@.DBName, DEFAULT_LANGUAGE=[@.LoginLanguage]

You could put the command into a variable, and then execute the variable:

declare @.Command varchar(2000)

Select @.Command = 'CREATE LOGIN [' + @.GUserName + '] FROM WINDOWS WITH DEFAULT_DATABASE= ' + @.DBName + ' , DEFAULT_LANGUAGE=['+@.LoginLanguage +']'

exec (@.command)

BobP

|||

That didn't work...but this did....

declare @.tmp varchar(2000)

SET @.tmp = 'CREATE LOGIN [' + @.GUserName + '] FROM WINDOWS WITH DEFAULT_DATABASE= ' + @.DBName + ' , DEFAULT_LANGUAGE=['+@.LoginLanguage +']'

execute sp_executesql @.tmp

|||

Actually, this is the SET command that finally worked...

SET @.tmp = 'CREATE LOGIN ['+@.SQLLoginName+'] WITH PASSWORD = '''+@.Password+''', DEFAULT_DATABASE='+@.DBName+', DEFAULT_LANGUAGE='+@.LoginLanguage+''

|||

Actually, to make your code more robust, you should use:

SET @.tmp = 'CREATE LOGIN ' + quotename(@.SQLLoginName)
+ ' WITH PASSWORD = ' + quotename(@.Password, '''')
+ ', DEFAULT_DATABASE = ' + quotename(@.DBName)
+ ', DEFAULT_LANGUAGE= ' + quotename(@.LoginLanguage)

Otherwise, the code is open to injection. quotename will do the proper quoting and escaping, so you should always use it when building dynamic SQL commands.

Thanks
Laurentiu