Sql Create error: failed

Showing posts with label failed. Show all posts

Thursday, March 22, 2012

Create subscription failed.

Hi
I am using merge replication to sync sqlserver 2000 sp3 database and sql
server ce 2.0 sp3 via PPC 2003. I received the following error.
"Sql CE Exception: Create subscription failed:
system.Data.Sqlserverce.sqlceException"
"Create subscription failed (27750 - 8004005)"
Please help.
80004005 is a generic access denied. Are you sure the account you are using
to pull the subscription is in the PAL of your merge publication?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pcherlop" <Pcherlop@.discussions.microsoft.com> wrote in message
news:930ED762-1874-4513-B675-0FFC27A418E0@.microsoft.com...
> Hi
> I am using merge replication to sync sqlserver 2000 sp3 database and sql
> server ce 2.0 sp3 via PPC 2003. I received the following error.
> "Sql CE Exception: Create subscription failed:
> system.Data.Sqlserverce.sqlceException"
> "Create subscription failed (27750 - 8004005)"
> Please help.

create storedproc with smo

hi,
i try to create storeproc with smo but i 've an exception
"Create failed for StoredProcedure 'dbo.TEST'"
{"Cannot create StoredProcedure '[dbo].[TEST]' if parent is not yet created."}
but sp.parent is created
please help me

attache my work
StoredProcedure sp = new StoredProcedure();
sp.Schema = "dbo";
sp.Name = "TEST";
sp.Parent = new Database(new Server("serveur"), "db_TEST");
sp.IgnoreForScripting = false;
sp.TextMode = false;
sp.ImplementationType = ImplementationType.TransactSql;

sp.Parameters.Add(new StoredProcedureParameter(sp,"@.toto, DataType.DateTime));

sp.TextBody = "Select 1";
sp.Create();

You need to create the database first before creating the SP.

Database db = new Database(new Server(), "db_TEST");

db.Create();

StoredProcedure sp = new StoredProcedure();

sp.Schema = "dbo";

sp.Name = "TEST";

sp.Parent = db;

sp.TextMode = false;

sp.ImplementationType = ImplementationType.TransactSql;

sp.Parameters.Add(new StoredProcedureParameter(sp, "@.toto", DataType.DateTime));

sp.TextBody = "Select 1";

sp.Create();

Wednesday, March 21, 2012

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

What do you think? Am i on the right way?

MariJo
|||

Hi,

Peng

|||

hi,

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

What do you think? Am i on the right way?

MariJo
|||

Hi,

Peng

|||

hi,

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

What do you think? Am i on the right way?

MariJo
|||

Hi,

Peng

|||

hi,

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJosql

create secondary for getting property set extention failed

Using xp pro.
Had to reinstal sound drivers.
Now getting message
create secondary for getting property set extention failed.--
Thanks for any help you can give.
RoySorry, but I don't understand what this has to do with SQL Server.
(Same with your rundll post.) If it is SQL related, could you elaborate
on the problem?
*mike hodgson*
http://sqlnerd.blogspot.com
Roy Chapman wrote:

>Using xp pro.
>Had to reinstal sound drivers.
>Now getting message
>create secondary for getting property set extention failed.--
>Thanks for any help you can give.
>Roy
>|||Thanks. Did not finish statement.
Working ok now.
--
Roy
"Mike Hodgson" wrote:

> Sorry, but I don't understand what this has to do with SQL Server.
> (Same with your rundll post.) If it is SQL related, could you elaborate
> on the problem?
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Roy Chapman wrote:
>
>sql

Sunday, March 11, 2012

create procedure for 'search agent'

Hey all,
Just when I thought I was starting to get the hang of T-SQL, I try a
project that now (after several failed attempts) has me thoroughly

. I'm trying to create a procedure for a 'search agent' that will
execute users' saved searches and generate email notifications via
xp_smtp_sendmail when matches are found. I want to schedule the procedure
to run as a job; and the resulting brief email to users should be in html
format as it will need to contain a hyperlink to the appropriate search
results. I think I have the basic pieces in place that I need but I could
sure use some help.
I (think) that I need to follow this basic concept to accomplish what I'm
after, but after working on this for several hours to no avail I've
reverted back to square one to try a fresh look. As a beginner this is
sometimes helpful for me in identifying the specific SQL tasks that need to
be carried out, though not yet in this case <g>. So the (very) beginnings
of the procedure, with several commented lines:
CREATE PROCEDURE dbo.usp_SearchAgent
AS
-- Need to do this for each t1.SearchID
-- in order to get the parameters to search but
-- need to then execute
SELECT t2.ParameterID, t2.FieldName, t2.SearchFor, t2.SearchOption,
t2.SearchFor2
FROM dbo.tblSavedSearch t1 INNER JOIN
dbo.tblSavedSearchParameters t2 ON t1.SearchID = t2.SearchID INNER
JOIN
dbo.tblSearchNotifications t3 ON t1.NotifyID = t3.NotifyID
WHERE (t1.NotifyUser = 1) AND (t1.NotifyExpires >= GETDATE()) AND
(DATEDIFF(d, t1.LastNotification, GETDATE())) >= t3.NotifyValue)
-- After we've executed each search
-- are there any matches?
IF ( SELECT COUNT(*)
FROM testVehicle t
WHERE ? ) > 0 -- Use the search parameters here
-- Or a better way to test..?
-- If we have matches then we need to generate an email via
xp_smtp_sendmail
-- Beyond a generic 'matches found' message the email needs to
include a link to the results (similar to, but with proper syntax):
-- '<A HREF=http://www.domain.com/searchpage.asp?
Action=AdvancedSearch&SearchID=' + SearchID + ">" + SearchName
-- Generate just a single email to the user for each SearchID with
matches
DECLARE @.TO varchar(100)
SET @.TO = ''
EXEC @.R = dbo.xp_smtp_sendmail
@.FROM = N'searchagent@.domain.com'
@.TO = N''
@.SUBJECT = N'Search Results'
@.MESSAGE = N'Matches found. Click the link below to view results.'
@.TYPE = N''
@.SERVER = N'mail.domain.com'
-- If we've sent an email then we'll need to update the
LastNotification in t1
Users' saved searches are stored in two tables (search, parameters);
another table contains the 'notification elements'; and I've included a
snipped version of the User and Vehicle tables:
CREATE TABLE [tblSavedSearch] (
[SearchID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchName] [varchar] (100) NOT NULL ,
[UserID] [int] NOT NULL ,
[DateSaved] [datetime] NOT NULL CONSTRAINT [DF_tblSavedSearch_DateSaved]
DEFAULT (getdate()),
[NotifyUser] [bit] NULL ,
[NotifyID] [int] NULL ,
[NotifyExpires] [datetime] NULL CONSTRAINT
[DF_tblSavedSearch_NotifyExpires] DEFAULT (dateadd(day,7,getdate())),
[LastNotification] [datetime] NULL ,
CONSTRAINT [PK_tblSavedSearch] PRIMARY KEY CLUSTERED
(
[SearchID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, SampleSearch1, 1, 5/24/2005 00:00:00, 1, 1, NULL
2, SampleSearch2, 1, 5/23/2005 00:00:00, 1, 2, NULL
3, SampleSearch3, 2, 5/22/2005 00:00:00, 1, 3, NULL
#####
CREATE TABLE [tblSavedSearchParameters] (
[ParameterID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchID] [int] NOT NULL ,
[FieldName] [varchar] (100) NULL ,
[SearchFor] [varchar] (100) NULL ,
[SearchOption] [varchar] (100) NULL ,
[SearchFor2] [varchar] (100) NULL ,
[DateSaved2] [datetime] NULL CONSTRAINT
[DF_tblSavedSearchParameters_DateSaved2]
DEFAULT (getdate()),
CONSTRAINT [PK_tblSavedSearchParameters] PRIMARY KEY CLUSTERED
(
[ParameterID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, 1, Make, Honda, Equals, NULL, 5/24/2005 00:00:00
2, 2, Model, Accord, Equals, NULL, 5/23/2005 00:00:00
3, 2, Year, 2003, Equals, NULL, 5/24/2005 00:00:00
4, 3, Make, Chev, Contains, NULL, 5/24/2005 00:00:00
#####
CREATE TABLE [tblSearchNotifications] (
[NotifyID] [int] IDENTITY (1, 1) NOT NULL ,
[NotifyFrequency] [varchar] (25) NOT NULL ,
[NotifyValue] [varchar] (3) NULL ,
CONSTRAINT [PK_tblSearchNotifications] PRIMARY KEY CLUSTERED
(
[NotifyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, Daily, 1
2, W

ly, 7
3, Monthly, 30
4, Yearly, 365
#####
CREATE TABLE [testUsers] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (100) NOT NULL ,
CONSTRAINT [PK_testUsers] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, any@.where.com
2, any@.where.com
3, any@.where.com
#####
CREATE TABLE [testVehicle] (
[VehicleID] [int] IDENTITY (1, 1) NOT NULL ,
[Year] [int] NOT NULL ,
[Make] [varchar] (50) NOT NULL ,
[Model] [varchar] (100) NOT NULL ,
[VIN] [varchar] (17) NOT NULL
CONSTRAINT [PK_testVehicle] PRIMARY KEY CLUSTERED
(
[VehicleID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, 2003, Honda, Accord, 12345678901234567
2, 2002, Honda, Civic, 23456789012345678
3, 2005, Ford, Mustang, 34567890123456789
4, 2002, Chevrolet, Malibu, 45678901234567890
#####
Thanks in advance for any and all assistance.
Message posted via http://www.webservertalk.comHi
Look at using sp_executesql, the following will also give you the basics to
implement a solution:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"The Gekkster via webservertalk.com" wrote:

> Hey all,
> Just when I thought I was starting to get the hang of T-SQL, I try a
> project that now (after several failed attempts) has me thoroughly
>

. I'm trying to create a procedure for a 'search agent' that will
> execute users' saved searches and generate email notifications via
> xp_smtp_sendmail when matches are found. I want to schedule the procedure
> to run as a job; and the resulting brief email to users should be in html
> format as it will need to contain a hyperlink to the appropriate search
> results. I think I have the basic pieces in place that I need but I could
> sure use some help.
> I (think) that I need to follow this basic concept to accomplish what I'm
> after, but after working on this for several hours to no avail I've
> reverted back to square one to try a fresh look. As a beginner this is
> sometimes helpful for me in identifying the specific SQL tasks that need t
o
> be carried out, though not yet in this case <g>. So the (very) beginnings
> of the procedure, with several commented lines:
>
> CREATE PROCEDURE dbo.usp_SearchAgent
> AS
> -- Need to do this for each t1.SearchID
> -- in order to get the parameters to search but
> -- need to then execute
> SELECT t2.ParameterID, t2.FieldName, t2.SearchFor, t2.SearchOption,
> t2.SearchFor2
> FROM dbo.tblSavedSearch t1 INNER JOIN
> dbo.tblSavedSearchParameters t2 ON t1.SearchID = t2.SearchID INNE
R
> JOIN
> dbo.tblSearchNotifications t3 ON t1.NotifyID = t3.NotifyID
> WHERE (t1.NotifyUser = 1) AND (t1.NotifyExpires >= GETDATE()) AND
> (DATEDIFF(d, t1.LastNotification, GETDATE())) >= t3.NotifyValue)
> -- After we've executed each search
> -- are there any matches?
> IF ( SELECT COUNT(*)
> FROM testVehicle t
> WHERE ? ) > 0 -- Use the search parameters here
> -- Or a better way to test..?
> -- If we have matches then we need to generate an email via
> xp_smtp_sendmail
> -- Beyond a generic 'matches found' message the email needs to
> include a link to the results (similar to, but with proper syntax):
> -- '<A HREF=http://www.domain.com/searchpage.asp?
> Action=AdvancedSearch&SearchID=' + SearchID + ">" + SearchName
> -- Generate just a single email to the user for each SearchID with
> matches
> DECLARE @.TO varchar(100)
> SET @.TO = ''
> EXEC @.R = dbo.xp_smtp_sendmail
> @.FROM = N'searchagent@.domain.com'
> @.TO = N''
> @.SUBJECT = N'Search Results'
> @.MESSAGE = N'Matches found. Click the link below to view results.'
> @.TYPE = N''
> @.SERVER = N'mail.domain.com'
> -- If we've sent an email then we'll need to update the
> LastNotification in t1
>
> Users' saved searches are stored in two tables (search, parameters);
> another table contains the 'notification elements'; and I've included a
> snipped version of the User and Vehicle tables:
>
> CREATE TABLE [tblSavedSearch] (
> [SearchID] [int] IDENTITY (1, 1) NOT NULL ,
> [SearchName] [varchar] (100) NOT NULL ,
> [UserID] [int] NOT NULL ,
> [DateSaved] [datetime] NOT NULL CONSTRAINT [DF_tblSavedSearch_DateSaved]
> DEFAULT (getdate()),
> [NotifyUser] [bit] NULL ,
> [NotifyID] [int] NULL ,
> [NotifyExpires] [datetime] NULL CONSTRAINT
> [DF_tblSavedSearch_NotifyExpires] DEFAULT (dateadd(day,7,getdate())),
> [LastNotification] [datetime] NULL ,
> CONSTRAINT [PK_tblSavedSearch] PRIMARY KEY CLUSTERED
> (
> [SearchID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, SampleSearch1, 1, 5/24/2005 00:00:00, 1, 1, NULL
> 2, SampleSearch2, 1, 5/23/2005 00:00:00, 1, 2, NULL
> 3, SampleSearch3, 2, 5/22/2005 00:00:00, 1, 3, NULL
> #####
>
> CREATE TABLE [tblSavedSearchParameters] (
> [ParameterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SearchID] [int] NOT NULL ,
> [FieldName] [varchar] (100) NULL ,
> [SearchFor] [varchar] (100) NULL ,
> [SearchOption] [varchar] (100) NULL ,
> [SearchFor2] [varchar] (100) NULL ,
> [DateSaved2] [datetime] NULL CONSTRAINT
> [DF_tblSavedSearchParameters_DateSaved2]
DEFAULT (getdate()),
> CONSTRAINT [PK_tblSavedSearchParameters] PRIMARY KEY CLUSTERED
> (
> [ParameterID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, 1, Make, Honda, Equals, NULL, 5/24/2005 00:00:00
> 2, 2, Model, Accord, Equals, NULL, 5/23/2005 00:00:
00
> 3, 2, Year, 2003, Equals, NULL, 5/24/2005 00:00:00
> 4, 3, Make, Chev, Contains, NULL, 5/24/2005 00:00:0
0
> #####
>
> CREATE TABLE [tblSearchNotifications] (
> [NotifyID] [int] IDENTITY (1, 1) NOT NULL ,
> [NotifyFrequency] [varchar] (25) NOT NULL ,
> [NotifyValue] [varchar] (3) NULL ,
> CONSTRAINT [PK_tblSearchNotifications] PRIMARY KEY CLUSTERED
> (
> [NotifyID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, Daily, 1
> 2, W

ly, 7
> 3, Monthly, 30
> 4, Yearly, 365
> #####
>
> CREATE TABLE [testUsers] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmailAddress] [varchar] (100) NOT NULL ,
> CONSTRAINT [PK_testUsers] PRIMARY KEY CLUSTERED
> (
> [UserID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, any@.where.com
> 2, any@.where.com
> 3, any@.where.com
> #####
>
> CREATE TABLE [testVehicle] (
> [VehicleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Year] [int] NOT NULL ,
> [Make] [varchar] (50) NOT NULL ,
> [Model] [varchar] (100) NOT NULL ,
> [VIN] [varchar] (17) NOT NULL
> CONSTRAINT [PK_testVehicle] PRIMARY KEY CLUSTERED
> (
> [VehicleID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, 2003, Honda, Accord, 12345678901234567
> 2, 2002, Honda, Civic, 23456789012345678
> 3, 2005, Ford, Mustang, 34567890123456789
> 4, 2002, Chevrolet, Malibu, 45678901234567890
> #####
>
> Thanks in advance for any and all assistance.
> --
> Message posted via http://www.webservertalk.com
>|||Still struggling quite a bit with this one. For some reason things just
aren't clicking with me.
Beyond the DDL from the OP, I tried to create a view that retrieves the
correct rows (i.e. those saved searches that require a notification) which
is working properly:
#######
CREATE VIEW dbo.vw_SearchAgent
AS
SELECT t2.UserID, t2.SearchID, t3.ParameterID, t3.FieldName,
t3.SearchFor, t3.SearchOption, t3.SearchFor2, t2.LastNotification
FROM dbo.tblSavedSearchParameters t3 INNER JOIN
dbo.tblSavedSearch t2 ON t3.SearchID = t2.SearchID
LEFT OUTER JOIN
dbo.tblSearchNotifications t1 ON t2.NotifyID =
t1.NotifyID AND DATEDIFF(d, t2.LastNotification, GETDATE()) > t1.NotifyValue
WHERE (t2.NotifyUser = 1) AND (t2.NotifyExpires >= GETDATE())
GO
#######
I'm also trying to utilize a CASE expression when building the dynamic SQL,
like in this snippet:
CASE @.SearchOption
WHEN 'Contains' THEN ' AND FieldName LIKE ''%'' @.SearchFor + ''%'''
WHEN 'Starts with ...' THEN ' AND FieldName LIKE @.SearchFor + ''%'''
WHEN 'More than ...' THEN ' AND FieldName > @.SearchFor'
WHEN 'Less than ...' THEN ' AND FieldName < @.SearchFor'
WHEN 'Equal or more than ...' THEN ' AND FieldName >= @.SearchFor'
WHEN 'Equal or less than ...' THEN ' AND FieldName <= @.SearchFor'
WHEN 'Between' THEN ' AND FieldName >= @.SearchFor AND FieldName <=
@.SearchFor2'
ELSE ' AND FieldName = @.SearchFor'
END
This seems so simple: Execute each saved search against Table1 using the
appropriate saved search parameters; count the matches (if any); and
generate an email (if needed). But I can't seem to get it quite right. My
problem is with generating the correct SQL string when more than one search
parameter exists for a given SearchID.
I'm really stuck on this one, could anyone help out? Thanks.

Wednesday, March 7, 2012

create maintenance plan failed

I am running Windows Small Business server 2003 R2 and I've upgraded the
Sharepoint from MSDE to SQL 2005. I'm trying to create a Database
maintenance plan to backup the Sharepoint database but I'm getting an error
message. I've pasted the messaged below:Create maintenance plan failed.
TITLE: Maintenance Plan Wizard Progress
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
BUTTONS:
OK
===================================
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
Program Location:
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan.AddAgentJob(ServerConnecti on localConnObj, String proxyName)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlanSubPlan..ctor(String
subplanName, String proxyAccount, Package package, ServerConnection
localConnObj)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintenancePlan.AddSubPlan(String subplanName, String proxyAccount)
at
Microsoft.SqlServer.Management.MaintenancePlanWiza rd.MaintenancePlanWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQuery(StringCollection queries)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ex ecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImplFinish(StringCollection createQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Cr eateImpl()
===================================
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
Server Name: SERVER1\SHAREPOINT
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 28
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.SqlServer.Management.Common.ServerConnec tion.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType)
no, I didn't. I did some more searches and found some fixes for it.
Actually what fixed it for me was SQL 2005 SP1.
"Tibor Karaszi" wrote:

> Did you install Integration Services?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:07FDD247-34D0-4F03-8BFB-E669E8E51829@.microsoft.com...
>
|||Hi Tibor
I have an issue on a newly buildt SQL2005 cluster. I didn't install the SSIS
at first but then added the service. However I still have the same problems
and I wonder if I need to reinstall the Management Tools in order to get it
to work?
I have a case with support and we verified that both nodes are running the
SSIS service but the creation/saving of Maintenance plans fails. Any
experience with this?
Rune
"Tibor Karaszi" wrote:

> Interesting. A Maint Plan in 2005 is an SSIS package, so I would expect a requirement for using 2005
> Maint Plans is to have SSIS installed. Perhaps they did some special handling of Maint Plans SSIS
> packages...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:BDC49606-300E-4BEA-8CE1-083026C6C00F@.microsoft.com...
>

Create local snapshot replication for sql 2005 failed

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you make the admin account( 'DB4\Administrator' ) part of the sysadmin
role on the publisher?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168789205.120731.163890@.51g2000cwl.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
> Is there a detailed step-by=step guide to create a snapshot
> replication?
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>

Friday, February 24, 2012

Create failed for User Computername\Username

Hello,

After creating a new SSMSExpress Login username account, Iuse it as the Database User of the attached database (aspnetdb.mdf), but Ireceive this error.

...
Additional information:
->An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)

->The login already has an account under a different user name. (Microsoft SQL Server,
Error: 15063)
...

I am sure such username account is not yet members of that database(aspnetdb.mdf) for this are the users present
-dbo
-guest
-INFORMATION_SCHEMA
-sys
-COMPUTERNAME\ASPNET
-CONPUTERNAME\IUSR_COMPUTERNAME

cheers,
imperialx

Logins and users are two different things.

Logins are defined at global level. Users are simply a mapping from a login to a username.

Try opening SSMSE and open the security folder. Under logins you will see all the "logins" (users) that have already been created. I bet the user you are trying to create is in that list.

Hope it points you in the right direction

Friday, February 17, 2012

CREATE DATABASE permission denied in database ''master''. (Microsoft SQL Server, Error: 262)

How exactly do I correct this problem on Vista?

TITLE: Microsoft SQL Server Management Studio Express

Create failed for Database 'kkl'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

I have ensured that I am logged into the system as Admin but cannot find where on the Server, I assign my login to the dbcreator group.

I have gone into Security >> Server Roles >> dbcreator. of which I am not a member. On a different system, where all works, however, I am not a member of this group either.

Any suggestions on how to kill this one?

Thanks in Advance.

Klaus

and this fixed my problem:

http://msdn2.microsoft.com/en-us/library/bb326612.aspx

Tuesday, February 14, 2012

Create Database on a Virtual Drive (Created with subst) Failed

When I try to add a (SQL Server 2005 Express Edition) database to my project (I'v tried a windows application and an ASP.NET application) in Visual Studio 2005 Professional Edition; It fails with an error like this :
"create database failed. some file names listed could not be created (...)"
but when I open the project from the real path It works.
Is this a bug? Is there any solution? (In many situations there is a need for working with virtual drives. There must be some work around ...)

Thanks

SQL Server does not recognize OS level 'mapped' drives.

You must use a 'actual' drive, or a SAN/NAS lun(drive).

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

Hi!

First of all: I don't really know a lot about MSSQL-Servers, so this question might sound a bit silly ;)

Today I wanted to test an application, that uses a MSSQL-Server for the databases. As they said, that a MSSQL7-Server is required, I thought that a MSSQL-Express 2005-Server would also do the job.

But when it's time to set up the database, I get the following error:

Procdure:CreateDatabase Message:[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.

With searching the net, I didn't found a solution to my problem...

Does this in the end mean, that I need to install (and buy!) a MSSQL7-Server, because the application won't run with a MSSQL-Express2005-Server? Or is there any way to get this thing up'n'running?

Thanks a lot for your answers!

Martin
Hi,

thats usally a problem of obsolete functions in the procedures in your database. THe best thing is to install the database first on a SQL Server 7.0 (Perhaps you can get somewhere a Developer Edition or another edition which shouldn′t be expensive because they are right now two editions *old*). If you want to leave it on the 7.0 Machine your are fine. If you want to migrate it, do a backup of the database and a restore on the SQl Server 2005 Express Machine. Then search after the *now* buggy procedures and try to eliminate them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Hi Jens!

Thanks a lot for your answer.

OK, if it's like this, I'll try to find a MSSQL7-Server...

Martin

Create Database Failed - Primary file must be at least 3 MB ...

I am trying to create a database with the following command

CREATE DATABASE [db1] ON PRIMARY
( NAME = N'db1', FILENAME = N'C:\Databases\Main\db1.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'db1_log', FILENAME = N'C:\Databases\Main\db1_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

However, I always get the following error:

CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.

I don't have a clue what's going wrong? The very same command is working fine on other machines. Its only one particular machine where it fails with the error message. Strangely enough, it worked on the "problem machine" too until I had to format the hard disk of the machine and re-install everything from scratch.

Any idea what could be going wrong? Please note that I am using SQL Server 2005.

On the server where this fails, verify the size of the Model database. I suspect it has inadvertently become larger than expected.

All newly created databases use the Model database as a 'template'. The new databases will start out no smaller than the Model database.

As this message indicates, since the Model database is about 3 MB in size, you MUST have a minimum size of 3 MB.

And you can reduce the size of the Model database. See Books Online, Topics: DBCC Shrinkdatabase, DBCC Shrinkfile

|||Thanks for your response. I checked the size of model database and found that it has a size of 3MB on that particular server as you said.
Actually I am creating a installation package which has to run on different machines. I am new to SQL Server so have a lesser understanding of many features of SQL Server.
Since, size of model database may just vary from machines to machines, I decided to entirely remove the SIZE=2048KB option from the CREATE DATABASE command. I tried it on my test machines and seems to work fine.
It seems that removing the SIZE option should not be a problem as system seems to pick up the size of model database on the server where is is executing.
Please correct if I am wrong or you see any negative impacts of this.
|||

You are correct, removing the [SIZE] parameter will ensure that you don't run into a similar problem.

I would suggest that immediately after the CREATE DATABASE statement, if your needs dictate a certain size to start with, that your code check the defined size and issue a ALTER DATABASE statement to increase if necessary.

|||I do not have any such requirement as of now so I might not be needing an 'alter database'
Its just that I am new to SQL server and I exported this script through SQL Server management studio. I wasn't aware that SIZE has a dependency on model database.

Anyway, thanks a lot for your suggestions.

CREATE DATABASE failed

Does any body seen this message before?
Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 1807: Could not obtain exclusive lock on database 'MODEL'. Retry the
operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
OK
Hi,
This error will occur if there is a user connected to model database. Run
sp_who to determine what connections are using model.
Identify those connetions and kill the SPID and try to create database
again.
Thanks
Hari
SQL Server MVP
"SQLCluster" <SQLCluster@.discussions.microsoft.com> wrote in message
news:24E3473B-9AD3-43C9-A668-07A7653DF676@.microsoft.com...
> Does any body seen this message before?
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1807: Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
> --
> OK
> --
>

CREATE DATABASE failed

Does any body seen this message before?
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 1807: Could not obtain exclusive lock on database 'MODEL'. Retry the
operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
--
OK
--Hi,
This error will occur if there is a user connected to model database. Run
sp_who to determine what connections are using model.
Identify those connetions and kill the SPID and try to create database
again.
Thanks
Hari
SQL Server MVP
"SQLCluster" <SQLCluster@.discussions.microsoft.com> wrote in message
news:24E3473B-9AD3-43C9-A668-07A7653DF676@.microsoft.com...
> Does any body seen this message before?
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1807: Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
> --
> OK
> --
>

CREATE DATABASE failed

Does any body seen this message before?
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 1807: Could not obtain exclusive lock on database 'MODEL'. Retry the
operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
--
OK
--Hi,
This error will occur if there is a user connected to model database. Run
sp_who to determine what connections are using model.
Identify those connetions and kill the SPID and try to create database
again.
Thanks
Hari
SQL Server MVP
"SQLCluster" <SQLCluster@.discussions.microsoft.com> wrote in message
news:24E3473B-9AD3-43C9-A668-07A7653DF676@.microsoft.com...
> Does any body seen this message before?
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1807: Could not obtain exclusive lock on database 'MODEL'. Retry the
> operation later.
> CREATE DATABASE failed. Some file names listed could not be created. Check
> previous errors.
> --
> OK
> --
>

Thursday, March 22, 2012

Create subscription failed.

create storedproc with smo

Wednesday, March 21, 2012

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

create secondary for getting property set extention failed

Sunday, March 11, 2012

create procedure for 'search agent'

Wednesday, March 7, 2012

create maintenance plan failed

Create local snapshot replication for sql 2005 failed

Friday, February 24, 2012

Create failed for User Computername\Username

Friday, February 17, 2012

CREATE DATABASE permission denied in database ''master''. (Microsoft SQL Server, Error: 262)

Tuesday, February 14, 2012

Create Database on a Virtual Drive (Created with subst) Failed

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

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

Create Database failed.

Create Database Failed - Primary file must be at least 3 MB ...

CREATE DATABASE failed

CREATE DATABASE failed

CREATE DATABASE failed

Sql Create error

Blog Archive

About Me