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 Multi-server environment in the SQL 2005- need HELP

I tried to setup a master-target server for the multi-server
environment
I tried the step in the microsoft support page already, but I keep
getting stucked with Create master wizard's third and fourth step.
This is my scenario: I have computer A and B connected in my network.
All of the SQLServer and SQL Agent in both machines uses a windows
authentications.
Both machines can communicate well already.
So, in the Create Master Server Wizard at Computer A I do:
-> Next
-> Next (leave all values blank for the email, pager and net send
address)
-> Add Connection to Computer B with windows authentication
-> Check compatibility success (meaning that the login successful as
well), then close
-> I check the "create a new login if necessary and assign it rights to
the MSX", then next
-> Finish
In the 3rd step: "Ensure the agent startup account for 'Computer B' has
rights to login as a target server", I got this error message:
===================================================================================== TITLE: Microsoft.SqlServer.Smo
--
Create failed for Login '.\TargetAdmin'.
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
--
Windows NT user or group '.\TargetAdmin' not found. Check the name
again. (Microsoft SQL Server, Error: 15401)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
--
BUTTONS:
OK
--
==========================================================================================================
In the fourth step: "Enlist 'Computer B' to 'Computer A', I got this
error message:
========================================================================================================== TITLE: Microsoft.SqlServer.Smo
--
MSX enlist failed for JobServer '155.64.154.169'.
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
--
The enlist operation failed (reason: SQLServerAgent Error: Unable to
connect to MSX 'SYMANTEC-6VBB5C'.) (Microsoft SQL Server, Error: 22026)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
--
BUTTONS:
OK
--
==========================================================================================================
Need help deperately >,<Are your servers domain members?
It looks like you have a problem with login from one server to other.
Accordint to the error messge '.\TargetAdmin' is a local account.
"ipramono@.gmail.com" wrote:
> I tried to setup a master-target server for the multi-server
> environment
> I tried the step in the microsoft support page already, but I keep
> getting stucked with Create master wizard's third and fourth step.
> This is my scenario: I have computer A and B connected in my network.
> All of the SQLServer and SQL Agent in both machines uses a windows
> authentications.
> Both machines can communicate well already.
> So, in the Create Master Server Wizard at Computer A I do:
> -> Next
> -> Next (leave all values blank for the email, pager and net send
> address)
> -> Add Connection to Computer B with windows authentication
> -> Check compatibility success (meaning that the login successful as
> well), then close
> -> I check the "create a new login if necessary and assign it rights to
> the MSX", then next
> -> Finish
> In the 3rd step: "Ensure the agent startup account for 'Computer B' has
> rights to login as a target server", I got this error message:
> =====================================================================================> TITLE: Microsoft.SqlServer.Smo
> --
> Create failed for Login '.\TargetAdmin'.
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Windows NT user or group '.\TargetAdmin' not found. Check the name
> again. (Microsoft SQL Server, Error: 15401)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
> --
> BUTTONS:
> OK
> --
> ==========================================================================================================> In the fourth step: "Enlist 'Computer B' to 'Computer A', I got this
> error message:
> ==========================================================================================================> TITLE: Microsoft.SqlServer.Smo
> --
> MSX enlist failed for JobServer '155.64.154.169'.
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The enlist operation failed (reason: SQLServerAgent Error: Unable to
> connect to MSX 'SYMANTEC-6VBB5C'.) (Microsoft SQL Server, Error: 22026)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
> --
> BUTTONS:
> OK
> --
> ==========================================================================================================> Need help deperately >,<
>

Create Multi-server environment in the SQL 2005- need HELP

I tried to setup a master-target server for the multi-server
environment
I tried the step in the microsoft support page already, but I keep
getting stucked with Create master wizard's third and fourth step.
This is my scenario: I have computer A and B connected in my network.
All of the SQLServer and SQL Agent in both machines uses a windows
authentications.
Both machines can communicate well already.
So, in the Create Master Server Wizard at Computer A I do:
-> Next
-> Next (leave all values blank for the email, pager and net send
address)
-> Add Connection to Computer B with windows authentication
-> Check compatibility success (meaning that the login successful as
well), then close
-> I check the "create a new login if necessary and assign it rights to
the MSX", then next
-> Finish
In the 3rd step: "Ensure the agent startup account for 'Computer B' has
rights to login as a target server", I got this error message:
========================================
====================================
=========
TITLE: Microsoft.SqlServer.Smo
--
Create failed for Login '.\TargetAdmin'.
For help, click:
http://go.microsoft.com/fwlink?Prod...in&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Windows NT user or group '.\TargetAdmin' not found. Check the name
again. (Microsoft SQL Server, Error: 15401)
For help, click:
http://go.microsoft.com/fwlink?Prod...01&LinkId=20476
BUTTONS:
OK
--
========================================
====================================
==============================
In the fourth step: "Enlist 'Computer B' to 'Computer A', I got this
error message:
========================================
====================================
==============================
TITLE: Microsoft.SqlServer.Smo
--
MSX enlist failed for JobServer '155.64.154.169'.
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
The enlist operation failed (reason: SQLServerAgent Error: Unable to
connect to MSX 'SYMANTEC-6VBB5C'.) (Microsoft SQL Server, Error: 22026)
For help, click:
http://go.microsoft.com/fwlink?Prod...26&LinkId=20476
BUTTONS:
OK
--
========================================
====================================
==============================
Need help deperately >,<Are your servers domain members?
It looks like you have a problem with login from one server to other.
Accordint to the error messge '.\TargetAdmin' is a local account.
"ipramono@.gmail.com" wrote:

> I tried to setup a master-target server for the multi-server
> environment
> I tried the step in the microsoft support page already, but I keep
> getting stucked with Create master wizard's third and fourth step.
> This is my scenario: I have computer A and B connected in my network.
> All of the SQLServer and SQL Agent in both machines uses a windows
> authentications.
> Both machines can communicate well already.
> So, in the Create Master Server Wizard at Computer A I do:
> -> Next
> -> Next (leave all values blank for the email, pager and net send
> address)
> -> Add Connection to Computer B with windows authentication
> -> Check compatibility success (meaning that the login successful as
> well), then close
> -> I check the "create a new login if necessary and assign it rights to
> the MSX", then next
> -> Finish
> In the 3rd step: "Ensure the agent startup account for 'Computer B' has
> rights to login as a target server", I got this error message:
> ========================================
==================================
===========
> TITLE: Microsoft.SqlServer.Smo
> --
> Create failed for Login '.\TargetAdmin'.
> For help, click:
> http://go.microsoft.com/fwlink?Prod...in&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> Windows NT user or group '.\TargetAdmin' not found. Check the name
> again. (Microsoft SQL Server, Error: 15401)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...01&LinkId=20476
> --
> BUTTONS:
> OK
> --
> ========================================
==================================
================================
> In the fourth step: "Enlist 'Computer B' to 'Computer A', I got this
> error message:
> ========================================
==================================
================================
> TITLE: Microsoft.SqlServer.Smo
> --
> MSX enlist failed for JobServer '155.64.154.169'.
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch. (Microsoft.SqlServer.ConnectionInfo)
> --
> The enlist operation failed (reason: SQLServerAgent Error: Unable to
> connect to MSX 'SYMANTEC-6VBB5C'.) (Microsoft SQL Server, Error: 22026)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...26&LinkId=20476
> --
> BUTTONS:
> OK
> --
> ========================================
==================================
================================
> Need help deperately >,<
>

Create database fails

HI All,
Environment:
Windows2003,
SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
When I execute below simple create database command without specifying the
file name, i got below error. If i explicitly specify the file ( just mdf
file), the database is getting created.
CREATE DATABASE TESTDB
Error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '\TestDB.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
The
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
SuchiAre these same values listed in Enterprise Manager when you right-click your
server, select Properties, Database Settings tab and see the current values
for Default data and log directory?
Ben Nevarez
"Suchi" wrote:
> HI All,
> Environment:
> Windows2003,
> SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> When I execute below simple create database command without specifying the
> file name, i got below error. If i explicitly specify the file ( just mdf
> file), the database is getting created.
>
> CREATE DATABASE TESTDB
> Error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name '\TestDB.mdf' may be
> incorrect.
> Server: Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
>
> The
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> are having same value ( E:\data )......
> Can any one tell me how to resolve this?
> Thanks,
> Suchi
>|||YES. Same values listed out there.
It is taking path as \test.mdf .. not starting with e:\data\testdb.mdf as
it supposed to ...
Thanks,
Suchi
"Ben Nevarez" wrote:
> Are these same values listed in Enterprise Manager when you right-click your
> server, select Properties, Database Settings tab and see the current values
> for Default data and log directory?
> Ben Nevarez
>
>
> "Suchi" wrote:
> > HI All,
> >
> > Environment:
> >
> > Windows2003,
> >
> > SQL : Microsoft SQL Server 2000 - 8.00.818 (sp3+hotfix)
> >
> > When I execute below simple create database command without specifying the
> > file name, i got below error. If i explicitly specify the file ( just mdf
> > file), the database is getting created.
> >
> >
> > CREATE DATABASE TESTDB
> >
> > Error:
> >
> > Server: Msg 5105, Level 16, State 2, Line 1
> > Device activation error. The physical file name '\TestDB.mdf' may be
> > incorrect.
> > Server: Msg 1802, Level 16, State 1, Line 1
> > CREATE DATABASE failed. Some file names listed could not be created. Check
> > previous errors.
> >
> >
> > The
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\MSSQLServer\'DefaultData'
> > and
> > HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot
> > are having same value ( E:\data )......
> >
> > Can any one tell me how to resolve this?
> >
> > Thanks,
> > Suchi
> >