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 SCHEMA fails Inside an If Block

Hello All,

The below "CREATE SCHEMA" sql statement fails if it is inside an IF block. It runs fine if i run it without the IF block...

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')

BEGIN

CREATE SCHEMA Customer AUTHORIZATION [sys]

END

-

Did anyone encountered this issue before....

Thanks..

Make this as dynamic SQL.|||

Thanks, Bushan.

As the DDL scripts can grow bigger, I feel that it is hard to maintain dynamic sql. But, I was just trying to figure out why this is not possible in this "create schema" scenario alone. It even works for "drop schema".

|||

Like the error message you get back says, CREATE SCHEMA must be the first command in the batch. So to do a CREATE SCHEMA in a script like this, that one statement has to be done dynamically.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customer')
BEGIN
exec ('CREATE SCHEMA Customer AUTHORIZATION [sys]')
END
Annoying? Yes? But it is not that much more work than doing it in the way that you (and I) originally expected :)

|||This is such an OBVIOUS shortcoming of TransactSQL. Why hasn't Microsoft corrected this flaw. As far as I know (with the exception of entering the exec ('sneak the CREATE in as a text string') there is no way to conditionally create a SCHEMA or a FUNCTION for that matter.

If it's disallowed for security reasons then why can we sneak it in with an EXEC?

The problem this causes for me (over and over again) is I write a sample function for the user and include it in my upgrade script. If they've already run the script for an earlier version, they already have my example and may have customized it to their specific application. If they have, I don't really want to replace it with my example again. So I'm stuck sneaking it in through the string route. This has been a flaw in T-SQL for a long time....

Sorry for the rant... Sure wish the T-SQL gods were listening |||
Thanks for the clarification..

Create Proc Parameter Issue

Hi All,
I have created a stored proc that is set to accept @.username =
varchar(40)...it fails when the username is FULLY qualified with Domain name
...ex: 'MyDomain\Username'...how do I get my procedure to except this FULL
name?
Thanks...M.Please post some sample code on how you execute that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michelle" <smiley2211@.yahoo.com> schrieb im Newsbeitrag
news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have created a stored proc that is set to accept @.username =
> varchar(40)...it fails when the username is FULLY qualified with Domain
> name ...ex: 'MyDomain\Username'...how do I get my procedure to except this
> FULL name?
> Thanks...M.
>|||The datatype for usernames in SQL (as used in the system tables) is sysname,
which is equivalent to nvarchar(128). Use that instead of varchar(40).
Jacco Schalkwijk
SQL Server MVP
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have created a stored proc that is set to accept @.username =
> varchar(40)...it fails when the username is FULLY qualified with Domain
> name ...ex: 'MyDomain\Username'...how do I get my procedure to except this
> FULL name?
> Thanks...M.
>|||Yes, I tried sysname as well...still errors: "Associated statement is not
prepared"
************snippet********
CREATE PROCEDURE sp_getprivs (@.username sysname = null) AS
set nocount on
declare @.dbn varchar(30)
declare test cursor for
select name from master..sysdatabases
etc....
*****************
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23$f5sirZFHA.3780@.tk2msftngp13.phx.gbl...
> The datatype for usernames in SQL (as used in the system tables) is
> sysname, which is equivalent to nvarchar(128). Use that instead of
> varchar(40).
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Sorry...I execute this as such...
sp_getprivs 'MyDomain\Username'
Thanks...M
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:ukE3YorZFHA.2496@.TK2MSFTNGP14.phx.gbl...
> Yes, I tried sysname as well...still errors: "Associated statement is not
> prepared"
> ************snippet********
> CREATE PROCEDURE sp_getprivs (@.username sysname = null) AS
> set nocount on
> declare @.dbn varchar(30)
> declare test cursor for
> select name from master..sysdatabases
> etc....
> *****************
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23$f5sirZFHA.3780@.tk2msftngp13.phx.gbl...
>|||Try using delimiters:
sp_getprivs '[MyDomain\Username]'
Jacco Schalkwijk
SQL Server MVP
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:OhhR1qrZFHA.2412@.TK2MSFTNGP10.phx.gbl...
> Sorry...I execute this as such...
> sp_getprivs 'MyDomain\Username'
> Thanks...M
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:ukE3YorZFHA.2496@.TK2MSFTNGP14.phx.gbl...
>|||Thanks, that worked...
...M
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ui3WLisZFHA.2884@.tk2msftngp13.phx.gbl...
> Try using delimiters:
> sp_getprivs '[MyDomain\Username]'
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:OhhR1qrZFHA.2412@.TK2MSFTNGP10.phx.gbl...
>

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:

> Hello,
> I've this problem with a database user that was restored from a backup. If i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else.
> Is it possible to work around it.
> THX!
>
>
|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
If i[vbcol=seagreen]
else.[vbcol=seagreen]
|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:

> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.
>
> "Ezekiël" wrote:
> > Hello,
> >
> > I've this problem with a database user that was restored from a backup.
If i
> > want to re-create the same username for sql login, it fails. This also
> > happens when i try to delete the user to set the ownership to someone
else.
> >
> > Is it possible to work around it.
> >
> > THX!
> >
> >
> >

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:

> Hello,
> I've this problem with a database user that was restored from a backup. If
i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else
.
> Is it possible to work around it.
> THX!
>
>|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
>
If i[vbcol=seagreen]
else.[vbcol=seagreen]|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:

> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>

create endpoint fails with error 0x800704be

I am trying to create an endpoint with the code below and get the following error

An error ('0x800704be') occurred while attempting to register the endpoint 'getFormsEP'.

Anyone any ideas on what this error means?

Please help if you can.

Regards, Major (that is my Christian name ;-)

Code

create endpoint getFormsEP
state = started
as http
(
site='ictdwt10mt',
path='/EndPoints',
authentication=(integrated),
ports=(clear),
clear_port=84
)
for soap
(
webmethod 'getForms'
(
name='EForms21.dbo.getForms',
schema=standard,
format=rowsets_only
),
wsdl=default,
login_type=windows,
database='EForms21',
schema=none
)

Additionl information.

I have now setup a server with .NET 3.0 and a development PC with .NET 3.0. I have applied the lastest OS fixes to both.

I have installed SQL 2005 Service Pack 2 on the server.

I am still getting this error.

Anyone from MS watching?

Please help if you can.

Regards, Major.

|||

Hi Major,

You need to set the namespace in order for this endpoint to bind because it serves as the final location or address of the bound endpoint.

Hope this helps,

John Gordon (MSFT)

<edit>

Sorry about that, I misunderstood the requirements. I will continue to look into this for you but Matt is probably going to beat me to a solution to your problem. I hope we get this fixed for you soon.

|||Hi Major,

What operating system are you running on?

Error code: (HRESULT) 0x800704be (2147943614) - The format of the specified network name is invalid. Or the constant error code is ERROR_INVALID_NETNAME.

Note I wasn’t able to replicate it by putting all sorts of invalid names as the site name when creating the endpoint.

This will come up when SQL tries to register the endpoint using httpaddurl API and it means the network name you specified in the site='zzz' tag is not valid from http.sys perspective. Try running ipconfig /all and verifying the site name you are using is correct.

Create database fails because of lack of disk space...

Hi,
I'm getting strange thing here.
CREATE DATABASE fails ("CREATE DATABASE failed. Could not
allocate enough disk space for a new database on the
named disks. Total space allocated must be at least 2 MB
to accommodate a copy of the model database."), and I
have almost 8GB of free space on that drive.
CREATE DATABASE [GTO] ON (NAME = N'GTO_Data', FILENAME =
N'E:\mssql\data\GTO_Data.MDF' , SIZE = 1, FILEGROWTH =
10%) LOG ON (NAME = N'GTO_Log', FILENAME =
N'E:\mssql\data\GTO_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
Output od xp_fixeddrives[MB]:
C2312
D5539
E7801
F10228
G11130
I38801
Does anybody have an idea what's going on?
Thanks,
OJ
Is it possible you have disk quotas enabled, or that E:\ is a compressed
volume?
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2201c470ca$8990a800$a601280a@.phx.gbl...
> Hi,
> I'm getting strange thing here.
> CREATE DATABASE fails ("CREATE DATABASE failed. Could not
> allocate enough disk space for a new database on the
> named disks. Total space allocated must be at least 2 MB
> to accommodate a copy of the model database."), and I
> have almost 8GB of free space on that drive.
> CREATE DATABASE [GTO] ON (NAME = N'GTO_Data', FILENAME =
> N'E:\mssql\data\GTO_Data.MDF' , SIZE = 1, FILEGROWTH =
> 10%) LOG ON (NAME = N'GTO_Log', FILENAME =
> N'E:\mssql\data\GTO_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
> Output od xp_fixeddrives[MB]:
> --
> C 2312
> D 5539
> E 7801
> F 10228
> G 11130
> I 38801
> Does anybody have an idea what's going on?
> Thanks,
> OJ
|||No and no.
OJ
>--Original Message--
>Is it possible you have disk quotas enabled, or that E:\
is a compressed
>volume?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2d2201c470ca$8990a800$a601280a@.phx.gbl...
not[vbcol=seagreen]
MB[vbcol=seagreen]
FILENAME =[vbcol=seagreen]
10%)
>
>.
>
|||Well, I don't have any other suggestions, maybe you should call PSS. To
avoid embarrassment, make sure that the xp_fixeddrives call is truly being
made on the same server.
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:30f201c470dd$b01efa40$a501280a@.phx.gbl...[vbcol=seagreen]
> No and no.
> OJ
> is a compressed
|||Can you delete something on that E: drive that's larger than 2 MB and run
the script again? I've never seen things like that so just shooting in the
dark. Is it possible that SQL server gets really funky and doesn't read OS
level the same as xp does? Or a backup that took 8 GB or so got currupted
halfway, causing SQL server to still think the disk drive is full?
I'd just give the server a clean reboot before making a PSS call.
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2201c470ca$8990a800$a601280a@.phx.gbl...
> Hi,
> I'm getting strange thing here.
> CREATE DATABASE fails ("CREATE DATABASE failed. Could not
> allocate enough disk space for a new database on the
> named disks. Total space allocated must be at least 2 MB
> to accommodate a copy of the model database."), and I
> have almost 8GB of free space on that drive.
> CREATE DATABASE [GTO] ON (NAME = N'GTO_Data', FILENAME =
> N'E:\mssql\data\GTO_Data.MDF' , SIZE = 1, FILEGROWTH =
> 10%) LOG ON (NAME = N'GTO_Log', FILENAME =
> N'E:\mssql\data\GTO_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
> Output od xp_fixeddrives[MB]:
> --
> C 2312
> D 5539
> E 7801
> F 10228
> G 11130
> I 38801
> Does anybody have an idea what's going on?
> Thanks,
> OJ
|||It's embarrasing but not from that reason.
Somebody changed MODEL database size to 2MB. My "create
database" statement has 1MB as data file size. As soon as
I changed that to 3MB, everything worked out OK...
OJ
>--Original Message--
>Well, I don't have any other suggestions, maybe you
should call PSS. To
>avoid embarrassment, make sure that the xp_fixeddrives
call is truly being
>made on the same server.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:30f201c470dd$b01efa40$a501280a@.phx.gbl...
E:\
>
>.
>

Create database fails because of lack of disk space...

Hi,
I'm getting strange thing here.
CREATE DATABASE fails ("CREATE DATABASE failed. Could not
allocate enough disk space for a new database on the
named disks. Total space allocated must be at least 2 MB
to accommodate a copy of the model database."), and I
have almost 8GB of free space on that drive.
CREATE DATABASE [GTO] ON (NAME = N'GTO_Data', FILENAME =
N'E:\mssql\data\GTO_Data.MDF' , SIZE = 1, FILEGROWTH =
10%) LOG ON (NAME = N'GTO_Log', FILENAME =
N'E:\mssql\data\GTO_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
Output od xp_fixeddrives[MB]:
--
C 2312
D 5539
E 7801
F 10228
G 11130
I 38801
Does anybody have an idea what's going on?
Thanks,
OJIs it possible you have disk quotas enabled, or that E:\ is a compressed
volume?
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2201c470ca$8990a800$a601280a@.phx.gbl...
> Hi,
> I'm getting strange thing here.
> CREATE DATABASE fails ("CREATE DATABASE failed. Could not
> allocate enough disk space for a new database on the
> named disks. Total space allocated must be at least 2 MB
> to accommodate a copy of the model database."), and I
> have almost 8GB of free space on that drive.
> CREATE DATABASE [GTO] ON (NAME = N'GTO_Data', FILENAME =
> N'E:\mssql\data\GTO_Data.MDF' , SIZE = 1, FILEGROWTH =
> 10%) LOG ON (NAME = N'GTO_Log', FILENAME =
> N'E:\mssql\data\GTO_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
> Output od xp_fixeddrives[MB]:
> --
> C 2312
> D 5539
> E 7801
> F 10228
> G 11130
> I 38801
> Does anybody have an idea what's going on?
> Thanks,
> OJ|||No and no.
OJ
>--Original Message--
>Is it possible you have disk quotas enabled, or that E:\
is a compressed
>volume?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2d2201c470ca$8990a800$a601280a@.phx.gbl...
not[vbcol=seagreen]
MB[vbcol=seagreen]
FILENAME =[vbcol=seagreen]
10%)[vbcol=seagreen]
>
>.
>|||Well, I don't have any other suggestions, maybe you should call PSS. To
avoid embarrassment, make sure that the xp_fixeddrives call is truly being
made on the same server.
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:30f201c470dd$b01efa40$a501280a@.phx.gbl...[vbcol=seagreen]
> No and no.
> OJ
> is a compressed|||Can you delete something on that E: drive that's larger than 2 MB and run
the script again? I've never seen things like that so just shooting in the
dark. Is it possible that SQL server gets really funky and doesn't read OS
level the same as xp does? Or a backup that took 8 GB or so got currupted
halfway, causing SQL server to still think the disk drive is full?
I'd just give the server a clean reboot before making a PSS call.
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2201c470ca$8990a800$a601280a@.phx.gbl...
> Hi,
> I'm getting strange thing here.
> CREATE DATABASE fails ("CREATE DATABASE failed. Could not
> allocate enough disk space for a new database on the
> named disks. Total space allocated must be at least 2 MB
> to accommodate a copy of the model database."), and I
> have almost 8GB of free space on that drive.
> CREATE DATABASE [GTO] ON (NAME = N'GTO_Data', FILENAME =
> N'E:\mssql\data\GTO_Data.MDF' , SIZE = 1, FILEGROWTH =
> 10%) LOG ON (NAME = N'GTO_Log', FILENAME =
> N'E:\mssql\data\GTO_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
> Output od xp_fixeddrives[MB]:
> --
> C 2312
> D 5539
> E 7801
> F 10228
> G 11130
> I 38801
> Does anybody have an idea what's going on?
> Thanks,
> OJ|||It's embarrasing but not from that reason.
Somebody changed MODEL database size to 2MB. My "create
database" statement has 1MB as data file size. As soon as
I changed that to 3MB, everything worked out OK...
OJ
>--Original Message--
>Well, I don't have any other suggestions, maybe you
should call PSS. To
>avoid embarrassment, make sure that the xp_fixeddrives
call is truly being
>made on the same server.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:30f201c470dd$b01efa40$a501280a@.phx.gbl...
E:\[vbcol=seagreen]
>
>.
>

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\MS SQLServer\'DefaultData'
and
HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\SQLDataRoot
are having same value ( E:\data )......
Can any one tell me how to resolve this?
Thanks,
Suchi
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\MS SQLServer\'DefaultData'
> and
> HKEY_LOCAL_MACHINE\FTWARE\Microsoft\MSSQLServer\Se tup\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:
[vbcol=seagreen]
> 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:

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
> >