I'm missing SQL Server already. :(
This doesn't work with a Microsoft Access database. The DEFAULT is causing a syntax exception. Trying to find any help with Google has prooved very frustrating and given me no leads, so do any of you know how it is done in a CREATE TABLE statement? (i.e. not seperately).
CREATE TABLE [MyTable] (
[MyField] VARCHAR(50) DEFAULT ""
)
Thanks for reading,
- David
(btw, I posted this in the general SQL forum as there didn't seem to be one for non-application-type MS Access questions. Hope that was right.)When you use the Table Design within Access there is Default Value property for a column.
By the way there is a Microsoft Access section with dbforums.|||I know about that, I want to set it using an SQL statement though. I am creating the database tables through script not using Access itself.
I mentioned why I didn't use the Microsoft Access forum in my edit. I looked at the messages that were on the first few pages and they seemed to all be application-orientated.
Thanks for your reply,
- David|||Ok, but it is just a suggestion to maybe have your question duplicated in the MS Access (you never know who might be popping in there to view stuff).
Also, have you looked at the Access documentation there is a section about Jet SQL Reference (not sure if that is what you need to reference)...although it looks as though there isn't a mention of DEFAULT. I agree with you when you 'downgrade' from a DB engine that has everything to something that lacks, it is frustrating.
Good luck....|||CREATE TABLE [MyTable] (
MyField Text(50) DEFAULT Hello World,
MyID Integer NOT NULL DEFAULT 1
)
Sorry for pulling a Hello world stuff on ya but that should work.|||I tried running that SQL in MS Access itself and got the same error I have been seeing with other attempts:
---------------
Microsoft Access
---------------
Syntax error in CREATE TABLE statement.
---------------
OK Help
---------------
It then selects the CREATE keyword in the SQL window.
I am using Access 2002/XP for this, should I be using something else?|||Just an observation, but MS-Access is a client side program. By default, it ships with the Microsoft-Jet database engine. If you have MS-Access 2002, you have MSDE on the CD, which is a slightly scaled down version of MS-SQL.
It might be worthwhile for you to install MSDE and use that as your database engine. It would put you on much more familiar ground!
-PatP|||Thanks Pat, but it's not for that sort of use. The product gets installed on web servers that don't have SQL Server or MSDE available to them. (if it does, it would use them anyway). :(
Showing posts with label default. Show all posts
Showing posts with label default. Show all posts
Thursday, March 29, 2012
Create table with 15,000,000 default rows
Hi I want to create a table with one column, which is a identity
column.
Let's say like this:
CREATE TABLE DefaultTable(N int identity(0,1))
column.
Let's say like this:
CREATE TABLE DefaultTable(N int identity(0,1))
Then I want to fill this table with 15,000,000 records, so that I have
a table with the numbers 0 to 14,999,999.
How can I do this as fast as possible. A standard INSERT would take a
long time.
(It can be a temp table or a table variable. I just need a list with
numbered 0 to 15,000,000)
Thank you.
Gidonhttp://www.bizdatasolutions.com/tsql/tblnumbers.asp
--
David Portas
SQL Server MVP
--|||Thanks a lot.
Tuesday, March 27, 2012
CREATE Table permission
I gave a user explicit permission to create table, but still getting
this error:
Sounds like you are using some graphical tool in SSMS. Those tools are
of poor quality, and I recommend that you try CREATE TABLE instead.
this error:
Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors
Do I need to create a new schema?
--sharifSharif Islam (mislam@.npspam.uiuc.edu) writes:
Quote:
Originally Posted by
I gave a user explicit permission to create table, but still getting
this error:
>
Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors
Sounds like you are using some graphical tool in SSMS. Those tools are
of poor quality, and I recommend that you try CREATE TABLE instead.
But only CREATE TABLE may not be sufficient. The user may also need ALTER
permission on the schema (for instance dbo).
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, March 25, 2012
Create table - default for column (sql 2000)
When I have a table with two columns, can the second column default to
a value based on the value from the first column on an inserted record?
I read the section below in BOL ALTER TABLE but can't make head nor
toes.
E. Alter a table to add several columns with constraints
...
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
...
Can someone explain what REFERENCES is for?
regards,
Gerard> When I have a table with two columns, can the second column default to
> a value based on the value from the first column on an inserted record?
CREATE TABLE dbo.foo
(
column_a VARCHAR(32),
column_b AS CONVERT(CHAR(8), LEFT(column_a, 8))
);
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo;
DROP TABLE dbo.foo;
However, my suggestion is usually to have this kind of thing in a view,
since you can always calculate it at SELECT time, without having to store it
and without tempting users to try and update it, have it be included in
column lists produced by code generators, etc. etc. For example, this
accomplishes the same thing:
CREATE TABLE dbo.foo
(
column_a VARCHAR(32)
);
GO
CREATE VIEW dbo.foo_view
AS
SELECT
column_a,
column_b = LEFT(column_a, 8)
FROM
dbo.foo
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo_view;
DROP VIEW dbo.foo_view;
DROP TABLE dbo.foo;
> Can someone explain what REFERENCES is for?
A foreign key constraint is completely different from what you are asking
about (computed columns). REFERENCES is indicating a separate table (think
master/detail, child/parent, and just about any type of entity
relationship). If you have an Orders table, a Customers table, a Products
table and an OrderDetails table, it is usually set up something like this
(Celko, you know where you can cram your IDENTITY comments):
CREATE TABLE dbo.Products
(
ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Customers
(
CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Orders
(
OrderID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID BIGINT NOT NULL FOREIGN KEY REFERENCES
dbo.Customers(CustomerID),
/*...other columns...*/
);
GO
CREATE TABLE dbo.OrderDetails
(
OrderID BIGINT FOREIGN KEY REFERENCES dbo.Orders(OrderID),
ProductID INT FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT,
/*...other columns...*/
PRIMARY KEY(OrderID, ProductID)
);
GO|||"References" token as shown here is a method to explain that the new
column contents must conform to the contents of another table/column
before an INSERT or UPDATE is allowed.
No related to what you are asking to get accomplished. Sounds more like
you might be asking for a trigger which should only be used as a last
ditch effort when making the changes at the (each) of the client
interface is not possible.
Example of simple trigger:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tgr_sample_insert_update]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[tgr_sample_insert_update]
GO
CREATE TRIGGER dbo.tgr_sample_insert_update ON dbo.tmp_sample
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE inserted SET colb = cola * tax_percentage
GO
Cheers
http://rickhathaway.blogspot.com/|||Thanks to you both for your replies. I will experiment a little to see
which is best for me.
regards,
Gerard|||The computed column was not an option as it can not be updated, quite
logical really.
A trigger was too much overhead for what I was trying to achieve so I I
have resolved my issue by including the logic to set the value of the
column on the "client side"
The reason I was wondering about REFERENCES was that I hoped that
something like this would be possible:
create table aTest (
col_a int default 0,
col_b as case when col_a = 1 then 1 when col_a = 2 then 2 else 3 end
)
insert into aTest (col_a) values (0)
select * from aTest
update aTest set col_b = 9
drop table aTest
--
But as I noted above, the update cannot be done.
Thanks again for your replies.
regards,
Gerard
a value based on the value from the first column on an inserted record?
I read the section below in BOL ALTER TABLE but can't make head nor
toes.
E. Alter a table to add several columns with constraints
...
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
...
Can someone explain what REFERENCES is for?
regards,
Gerard> When I have a table with two columns, can the second column default to
> a value based on the value from the first column on an inserted record?
CREATE TABLE dbo.foo
(
column_a VARCHAR(32),
column_b AS CONVERT(CHAR(8), LEFT(column_a, 8))
);
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo;
DROP TABLE dbo.foo;
However, my suggestion is usually to have this kind of thing in a view,
since you can always calculate it at SELECT time, without having to store it
and without tempting users to try and update it, have it be included in
column lists produced by code generators, etc. etc. For example, this
accomplishes the same thing:
CREATE TABLE dbo.foo
(
column_a VARCHAR(32)
);
GO
CREATE VIEW dbo.foo_view
AS
SELECT
column_a,
column_b = LEFT(column_a, 8)
FROM
dbo.foo
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo_view;
DROP VIEW dbo.foo_view;
DROP TABLE dbo.foo;
> Can someone explain what REFERENCES is for?
A foreign key constraint is completely different from what you are asking
about (computed columns). REFERENCES is indicating a separate table (think
master/detail, child/parent, and just about any type of entity
relationship). If you have an Orders table, a Customers table, a Products
table and an OrderDetails table, it is usually set up something like this
(Celko, you know where you can cram your IDENTITY comments):
CREATE TABLE dbo.Products
(
ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Customers
(
CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Orders
(
OrderID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID BIGINT NOT NULL FOREIGN KEY REFERENCES
dbo.Customers(CustomerID),
/*...other columns...*/
);
GO
CREATE TABLE dbo.OrderDetails
(
OrderID BIGINT FOREIGN KEY REFERENCES dbo.Orders(OrderID),
ProductID INT FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT,
/*...other columns...*/
PRIMARY KEY(OrderID, ProductID)
);
GO|||"References" token as shown here is a method to explain that the new
column contents must conform to the contents of another table/column
before an INSERT or UPDATE is allowed.
No related to what you are asking to get accomplished. Sounds more like
you might be asking for a trigger which should only be used as a last
ditch effort when making the changes at the (each) of the client
interface is not possible.
Example of simple trigger:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tgr_sample_insert_update]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[tgr_sample_insert_update]
GO
CREATE TRIGGER dbo.tgr_sample_insert_update ON dbo.tmp_sample
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE inserted SET colb = cola * tax_percentage
GO
Cheers
http://rickhathaway.blogspot.com/|||Thanks to you both for your replies. I will experiment a little to see
which is best for me.
regards,
Gerard|||The computed column was not an option as it can not be updated, quite
logical really.
A trigger was too much overhead for what I was trying to achieve so I I
have resolved my issue by including the logic to set the value of the
column on the "client side"
The reason I was wondering about REFERENCES was that I hoped that
something like this would be possible:
create table aTest (
col_a int default 0,
col_b as case when col_a = 1 then 1 when col_a = 2 then 2 else 3 end
)
insert into aTest (col_a) values (0)
select * from aTest
update aTest set col_b = 9
drop table aTest
--
But as I noted above, the update cannot be done.
Thanks again for your replies.
regards,
Gerard
Wednesday, March 21, 2012
Create Snapshot -> FAILED! you dont have sufficient permission to run this command
Hi all,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
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,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
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,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
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,
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.
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,
MariJosql
Monday, March 19, 2012
CREATE RULE for a Default Type
Hi all,
Using: SQL Server 2000 SP3A Enterprise Edition
I have setup a table that holds application information. One of the fields
holds the Applications Version Information.
I have created a default type called Version of nvarchar and length 43.
Version information is made up of 2, 3 or 4 parts, Major, Minor, Build and
Revision (Major.Minor[.Build[.Revision]]). Each part can hold up to 10 digits
up to 2,147,483,647 (int without comas). That makes 4 blocks of 10 plus up t
o
3 seperators (being the .) makes 43 the max length.
What I want to do is create a rule that will only allow a valid version
number to be stored in the field. I had something like this:
@.value LIKE '[0-9].[0-9]' OR @.value LIKE '[0-9].[0-9].[0-9]' OR @.value LIKE
'[0-9].[0-9].[0-9].[0-9]'
This will not allow 1.10.8903.56 as [0-9] specifies single characters only.
Is there quick way to do the validation as a rule without having to type
loads of LIKE statements for every possibility?Use a CHECK constraint rather than a RULE. Rules and user-defined types
are supported for backwards compatibility. Constraints are more much
easier to maintain and code.
In this case I think you'll find it easier to exclude the values you
don't want:
CREATE TABLE YourTable
(... , version VARCHAR(43) NOT NULL
CHECK (version NOT LIKE '[^.0-9]'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Oops. That should be:
CREATE TABLE YourTable
(version NVARCHAR(43) NOT NULL
CHECK (version NOT LIKE '%[^.0-9]%'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Hi,
Thanks for quick response to my question!
All working okay now!
Just 1 other question! Why use a Check instead of a Rule? I was using the
rule on the default type to save me typing the Check for every field as I
have many tables that contain this Version type field. Your code works both
as a Check and Rule.
Cheers
Paul|||Yes it will work as a Check and a Rule. User-defined types, defaults
and rules are designated as backwards compatibility features so they
won't necessarily be fully supported in future versions of SQL Server.
Books Online recommends using the ANSI/ISO standard alternatives, CHECK
and DEFAULT constraints, instead.
User-defined types are difficult to maintain because of the convoluted
syntax and binding - you have to remove all references and unbind
before you can make a change - a big problem if your type is used in
many columns. Constraints are declarative, unbound and much more
flexible.
CHECK constraints can also be used by the optimizer (although that's
unlikely to be useful with the constraint used here). I don't think the
optimizer can take advantage of Rules, although I confess I don't
recall where I've seen that documented so someone may correct me on
that point.
Finally, I suspect fewer SQL Server professionals will continue to use
and remember the old syntax in future so those who inherit your code
will probably be more productive if they don't have to cope with the
legacy stuff.
I think those are enough reasons not to use User-defined Types and
Rules. You want to save yourself some typing? Just cut-and-Paste the
CHECK constraint in Query Analyzer - that's no more work than pasting
the name of a user-defined type.
David Portas
SQL Server MVP
--|||Hi,
Thanks again for your information, very useful.
I have updated to use Check instead of Rule, was just trying to do the easy
way but as you pointed out sometimes the easy way can become problamatic in
the future.
Cheers again for your help.
Paul|||If you use a datamodeling tool (I use ERwin) you probably can do much the
same thing in the model, but generating them out as CHECK constraints. They
have domains that you can use in the model but only generate them as CHECKS.
Not sure if other tools have this, but it is a really
feature of ERwin.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:667A4E1E-64D2-4030-A25E-399B07E3C9B8@.microsoft.com...
> Hi,
> Thanks again for your information, very useful.
> I have updated to use Check instead of Rule, was just trying to do the
> easy
> way but as you pointed out sometimes the easy way can become problamatic
> in
> the future.
> Cheers again for your help.
>
> Paul
Using: SQL Server 2000 SP3A Enterprise Edition
I have setup a table that holds application information. One of the fields
holds the Applications Version Information.
I have created a default type called Version of nvarchar and length 43.
Version information is made up of 2, 3 or 4 parts, Major, Minor, Build and
Revision (Major.Minor[.Build[.Revision]]). Each part can hold up to 10 digits
up to 2,147,483,647 (int without comas). That makes 4 blocks of 10 plus up t
o
3 seperators (being the .) makes 43 the max length.
What I want to do is create a rule that will only allow a valid version
number to be stored in the field. I had something like this:
@.value LIKE '[0-9].[0-9]' OR @.value LIKE '[0-9].[0-9].[0-9]' OR @.value LIKE
'[0-9].[0-9].[0-9].[0-9]'
This will not allow 1.10.8903.56 as [0-9] specifies single characters only.
Is there quick way to do the validation as a rule without having to type
loads of LIKE statements for every possibility?Use a CHECK constraint rather than a RULE. Rules and user-defined types
are supported for backwards compatibility. Constraints are more much
easier to maintain and code.
In this case I think you'll find it easier to exclude the values you
don't want:
CREATE TABLE YourTable
(... , version VARCHAR(43) NOT NULL
CHECK (version NOT LIKE '[^.0-9]'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Oops. That should be:
CREATE TABLE YourTable
(version NVARCHAR(43) NOT NULL
CHECK (version NOT LIKE '%[^.0-9]%'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Hi,
Thanks for quick response to my question!
All working okay now!
Just 1 other question! Why use a Check instead of a Rule? I was using the
rule on the default type to save me typing the Check for every field as I
have many tables that contain this Version type field. Your code works both
as a Check and Rule.
Cheers
Paul|||Yes it will work as a Check and a Rule. User-defined types, defaults
and rules are designated as backwards compatibility features so they
won't necessarily be fully supported in future versions of SQL Server.
Books Online recommends using the ANSI/ISO standard alternatives, CHECK
and DEFAULT constraints, instead.
User-defined types are difficult to maintain because of the convoluted
syntax and binding - you have to remove all references and unbind
before you can make a change - a big problem if your type is used in
many columns. Constraints are declarative, unbound and much more
flexible.
CHECK constraints can also be used by the optimizer (although that's
unlikely to be useful with the constraint used here). I don't think the
optimizer can take advantage of Rules, although I confess I don't
recall where I've seen that documented so someone may correct me on
that point.
Finally, I suspect fewer SQL Server professionals will continue to use
and remember the old syntax in future so those who inherit your code
will probably be more productive if they don't have to cope with the
legacy stuff.
I think those are enough reasons not to use User-defined Types and
Rules. You want to save yourself some typing? Just cut-and-Paste the
CHECK constraint in Query Analyzer - that's no more work than pasting
the name of a user-defined type.
David Portas
SQL Server MVP
--|||Hi,
Thanks again for your information, very useful.
I have updated to use Check instead of Rule, was just trying to do the easy
way but as you pointed out sometimes the easy way can become problamatic in
the future.
Cheers again for your help.
Paul|||If you use a datamodeling tool (I use ERwin) you probably can do much the
same thing in the model, but generating them out as CHECK constraints. They
have domains that you can use in the model but only generate them as CHECKS.
Not sure if other tools have this, but it is a really
feature of ERwin.----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:667A4E1E-64D2-4030-A25E-399B07E3C9B8@.microsoft.com...
> Hi,
> Thanks again for your information, very useful.
> I have updated to use Check instead of Rule, was just trying to do the
> easy
> way but as you pointed out sometimes the easy way can become problamatic
> in
> the future.
> Cheers again for your help.
>
> Paul
Friday, February 24, 2012
CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue
Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.
CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue
Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.
Sunday, February 19, 2012
create default tables, procedures, etc. on newly connected remote SQL server
I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?
-Mathminded
There are many ways to move the database, but the best is Backup and Restore because it moves everything including permissions, you Backup the database locally and put the .bak file in the location below in the remote server and use the Backup and Restore wizard SQL Server will create the file path just make sure the path is correct don't try to create the path because SQL Server gets confused if you do. I have created a FAQ that covers all the ways to move a database, if you use another method remember to also move the permissions. Hope this helps.
C:\Program Files\Microsoft SQL Server\MSSQL\Backup
http://forums.asp.net/thread/1454694.aspx|||Thanks, Caddre. Before I got your message I had just found this link (http://www.c-sharpcorner.com/uploadfile/dsdaf/104012006083052am/1.aspx) and followed those instructions. It created all the tables, views, and stored procedures that had been created on my local SQL Express installation. From what I understand of the Backup and Restore method, I have to be able to load the backup file on the remote server, right? I don't think I have access to do that. I'm planning to call the database administrator tomorrow, anyway, because the Website Administration Tool in VS2005 says I don't have permission to execute 'aspnet_CheckSchemaVersion' so I'm not able to create new users. Crazy. :-)
For future readers of this thread, if you end up using the link in this message, and you have a problem running the tool like I did, you may be interested in this quote fromhttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/management/tools.aspx :
"To configure and install databases on a SQL server to use these ASP.NET features, you can use theaspnet_regsql tool. This tool can be found in the version-specific framework directory, under theMicrosoft.NET\Framework subdirectory of your Windows system folder."
As always, thank you for your help, Caddre.
-Mathminded
|||FYI for anyone who runs into the problem of not having permission to execute that stored procedure. I used the Microsoft SQL Server Management Studio Express to add my database user to the aspnet_Membership_FullAccess role and that took care of it. I think the backup and restore method that Caddre mentioned probably takes care of problems like that, but unfortunately I didn't have access to load files on the remote server. If you can do it her way, I suggest doing that since she has about a million times more experience than I do (that's probably an understatement . . . it's probably closer to a billion or so). But, if you're in the situation I was in, this did work for me.
Happy SQLing!
-Mathminded
|||addition to my previous post:
I had to add my database user to ALL of the roles ending in "_FullAccess" in order to fully use the Website Administration Tool.
Create default
Hi All,
Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?
Cheers Wimmouse getdate() in the field where date field is used in insert.|||Originally posted by nhariharan
use getdate() in the field where date field is used in insert.
I tried it, but i keeps the null value.|||use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go|||Originally posted by Enigma
use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?
Thanx already.
Cheers Wim
I|||Originally posted by Wimmo
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?
Thanx already.
Cheers Wim
I
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)|||Originally posted by harshal_in
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)
I tried this but the result seems strange:
joindate 13-2-2004 11:48:45 enddate Feb 13 200|||Originally posted by Wimmo
I tried this but the result seems strange:
joindate 13-2-2004 11:48:45 enddate Feb 13 200
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
endate datetime default dateadd(yy,1,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go
Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?
Cheers Wimmouse getdate() in the field where date field is used in insert.|||Originally posted by nhariharan
use getdate() in the field where date field is used in insert.
I tried it, but i keeps the null value.|||use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go|||Originally posted by Enigma
use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?
Thanx already.
Cheers Wim
I|||Originally posted by Wimmo
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?
Thanx already.
Cheers Wim
I
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)|||Originally posted by harshal_in
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)
I tried this but the result seems strange:
joindate 13-2-2004 11:48:45 enddate Feb 13 200|||Originally posted by Wimmo
I tried this but the result seems strange:
joindate 13-2-2004 11:48:45 enddate Feb 13 200
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
endate datetime default dateadd(yy,1,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go
Friday, February 17, 2012
Create Database's default ANSI settings are off?
When I create a new database in SQL Server 2005, most of the database
Options settings, including all of the ANSI settings are off (False).
I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
were recommended to be ON(True) by Microsoft. If that is so, then why
aren't they ON by default? I had thought that they were in SQL Server
2000.
Is this the correct behavior or is there a problem with my
installation?
Thanks, R Barry YoungHow are you creating it? Are you running a script? If so what tool are you
using? If it is the query editor then check your options and set them the
way you want them to be.
Andrew J. Kelly SQL MVP
<RBarryYoung@.gmail.com> wrote in message
news:1144864748.605738.232910@.e56g2000cwe.googlegroups.com...
> When I create a new database in SQL Server 2005, most of the database
> Options settings, including all of the ANSI settings are off (False).
> I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
> were recommended to be ON(True) by Microsoft. If that is so, then why
> aren't they ON by default? I had thought that they were in SQL Server
> 2000.
> Is this the correct behavior or is there a problem with my
> installation?
> Thanks, R Barry Young
>
Options settings, including all of the ANSI settings are off (False).
I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
were recommended to be ON(True) by Microsoft. If that is so, then why
aren't they ON by default? I had thought that they were in SQL Server
2000.
Is this the correct behavior or is there a problem with my
installation?
Thanks, R Barry YoungHow are you creating it? Are you running a script? If so what tool are you
using? If it is the query editor then check your options and set them the
way you want them to be.
Andrew J. Kelly SQL MVP
<RBarryYoung@.gmail.com> wrote in message
news:1144864748.605738.232910@.e56g2000cwe.googlegroups.com...
> When I create a new database in SQL Server 2005, most of the database
> Options settings, including all of the ANSI settings are off (False).
> I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
> were recommended to be ON(True) by Microsoft. If that is so, then why
> aren't they ON by default? I had thought that they were in SQL Server
> 2000.
> Is this the correct behavior or is there a problem with my
> installation?
> Thanks, R Barry Young
>
Create Database's default ANSI settings are off?
When I create a new database in SQL Server 2005, most of the database
Options settings, including all of the ANSI settings are off (False).
I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
were recommended to be ON(True) by Microsoft. If that is so, then why
aren't they ON by default? I had thought that they were in SQL Server
2000.
Is this the correct behavior or is there a problem with my
installation?
Thanks, R Barry YoungHow are you creating it? Are you running a script? If so what tool are you
using? If it is the query editor then check your options and set them the
way you want them to be.
--
Andrew J. Kelly SQL MVP
<RBarryYoung@.gmail.com> wrote in message
news:1144864748.605738.232910@.e56g2000cwe.googlegroups.com...
> When I create a new database in SQL Server 2005, most of the database
> Options settings, including all of the ANSI settings are off (False).
> I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
> were recommended to be ON(True) by Microsoft. If that is so, then why
> aren't they ON by default? I had thought that they were in SQL Server
> 2000.
> Is this the correct behavior or is there a problem with my
> installation?
> Thanks, R Barry Young
>
Options settings, including all of the ANSI settings are off (False).
I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
were recommended to be ON(True) by Microsoft. If that is so, then why
aren't they ON by default? I had thought that they were in SQL Server
2000.
Is this the correct behavior or is there a problem with my
installation?
Thanks, R Barry YoungHow are you creating it? Are you running a script? If so what tool are you
using? If it is the query editor then check your options and set them the
way you want them to be.
--
Andrew J. Kelly SQL MVP
<RBarryYoung@.gmail.com> wrote in message
news:1144864748.605738.232910@.e56g2000cwe.googlegroups.com...
> When I create a new database in SQL Server 2005, most of the database
> Options settings, including all of the ANSI settings are off (False).
> I thought that most of the ANSI settings (except NUMERIC_ROUNDABORT)
> were recommended to be ON(True) by Microsoft. If that is so, then why
> aren't they ON by default? I had thought that they were in SQL Server
> 2000.
> Is this the correct behavior or is there a problem with my
> installation?
> Thanks, R Barry Young
>
create database statement
Where does the SQL Server default the "file name" parameter when you create
a database WITHOUT entering your own parameter for "file name".
You can do a simple "create database somename" and it will create the
database with the defaulted "file name" to be where SQL Server is installed
(location of the mdf and ldf). Does SQL Server take the default location
from the model or master databases or some file group setting?
Thanks in advance
I believe it defaults to the location of model. You can change this in EM
by right-clicking on your server and changing the defaults in the server
properties dialog, or by modifying the registry directly. The defaults are
stored at HKLM\Software\Microsoft\MSSQLServer\MSSQLServer. Two keys:
DefaultData and DefaultLog, which should both be REG_SZ. Note that if you
haven't previously modified this setting in EM, the keys will not yet exist.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:66B065D1-287D-485E-B3C5-425852D83EFE@.microsoft.com...
> Where does the SQL Server default the "file name" parameter when you
> create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is
> installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
|||The following article should explain the New Database default locations.
Basically though these values are stored in the registry for each SQL server
instance.
http://www.wardyit.com/blog/blog/arc.../11/10/58.aspx
- Peter Ward
WARDY IT Solutions
"Homer" wrote:
> Where does the SQL Server default the "file name" parameter when you create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
a database WITHOUT entering your own parameter for "file name".
You can do a simple "create database somename" and it will create the
database with the defaulted "file name" to be where SQL Server is installed
(location of the mdf and ldf). Does SQL Server take the default location
from the model or master databases or some file group setting?
Thanks in advance
I believe it defaults to the location of model. You can change this in EM
by right-clicking on your server and changing the defaults in the server
properties dialog, or by modifying the registry directly. The defaults are
stored at HKLM\Software\Microsoft\MSSQLServer\MSSQLServer. Two keys:
DefaultData and DefaultLog, which should both be REG_SZ. Note that if you
haven't previously modified this setting in EM, the keys will not yet exist.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:66B065D1-287D-485E-B3C5-425852D83EFE@.microsoft.com...
> Where does the SQL Server default the "file name" parameter when you
> create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is
> installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
|||The following article should explain the New Database default locations.
Basically though these values are stored in the registry for each SQL server
instance.
http://www.wardyit.com/blog/blog/arc.../11/10/58.aspx
- Peter Ward
WARDY IT Solutions
"Homer" wrote:
> Where does the SQL Server default the "file name" parameter when you create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
create database statement
Where does the SQL Server default the "file name" parameter when you create
a database WITHOUT entering your own parameter for "file name".
You can do a simple "create database somename" and it will create the
database with the defaulted "file name" to be where SQL Server is installed
(location of the mdf and ldf). Does SQL Server take the default location
from the model or master databases or some file group setting?
Thanks in advanceI believe it defaults to the location of model. You can change this in EM
by right-clicking on your server and changing the defaults in the server
properties dialog, or by modifying the registry directly. The defaults are
stored at HKLM\Software\Microsoft\MSSQLServer\MSSQ
LServer. Two keys:
DefaultData and DefaultLog, which should both be REG_SZ. Note that if you
haven't previously modified this setting in EM, the keys will not yet exist.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:66B065D1-287D-485E-B3C5-425852D83EFE@.microsoft.com...
> Where does the SQL Server default the "file name" parameter when you
> create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is
> installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance|||The following article should explain the New Database default locations.
Basically though these values are stored in the registry for each SQL server
instance.
http://www.wardyit.com/blog/blog/ar...5/11/10/58.aspx
- Peter Ward
WARDY IT Solutions
"Homer" wrote:
> Where does the SQL Server default the "file name" parameter when you cre
ate
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is installe
d
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
a database WITHOUT entering your own parameter for "file name".
You can do a simple "create database somename" and it will create the
database with the defaulted "file name" to be where SQL Server is installed
(location of the mdf and ldf). Does SQL Server take the default location
from the model or master databases or some file group setting?
Thanks in advanceI believe it defaults to the location of model. You can change this in EM
by right-clicking on your server and changing the defaults in the server
properties dialog, or by modifying the registry directly. The defaults are
stored at HKLM\Software\Microsoft\MSSQLServer\MSSQ
LServer. Two keys:
DefaultData and DefaultLog, which should both be REG_SZ. Note that if you
haven't previously modified this setting in EM, the keys will not yet exist.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:66B065D1-287D-485E-B3C5-425852D83EFE@.microsoft.com...
> Where does the SQL Server default the "file name" parameter when you
> create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is
> installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance|||The following article should explain the New Database default locations.
Basically though these values are stored in the registry for each SQL server
instance.
http://www.wardyit.com/blog/blog/ar...5/11/10/58.aspx
- Peter Ward
WARDY IT Solutions
"Homer" wrote:
> Where does the SQL Server default the "file name" parameter when you cre
ate
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is installe
d
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
create database statement
Where does the SQL Server default the "file name" parameter when you create
a database WITHOUT entering your own parameter for "file name".
You can do a simple "create database somename" and it will create the
database with the defaulted "file name" to be where SQL Server is installed
(location of the mdf and ldf). Does SQL Server take the default location
from the model or master databases or some file group setting?
Thanks in advanceI believe it defaults to the location of model. You can change this in EM
by right-clicking on your server and changing the defaults in the server
properties dialog, or by modifying the registry directly. The defaults are
stored at HKLM\Software\Microsoft\MSSQLServer\MSSQLServer. Two keys:
DefaultData and DefaultLog, which should both be REG_SZ. Note that if you
haven't previously modified this setting in EM, the keys will not yet exist.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:66B065D1-287D-485E-B3C5-425852D83EFE@.microsoft.com...
> Where does the SQL Server default the "file name" parameter when you
> create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is
> installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance|||The following article should explain the New Database default locations.
Basically though these values are stored in the registry for each SQL server
instance.
http://www.wardyit.com/blog/blog/archive/2005/11/10/58.aspx
- Peter Ward
WARDY IT Solutions
"Homer" wrote:
> Where does the SQL Server default the "file name" parameter when you create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
a database WITHOUT entering your own parameter for "file name".
You can do a simple "create database somename" and it will create the
database with the defaulted "file name" to be where SQL Server is installed
(location of the mdf and ldf). Does SQL Server take the default location
from the model or master databases or some file group setting?
Thanks in advanceI believe it defaults to the location of model. You can change this in EM
by right-clicking on your server and changing the defaults in the server
properties dialog, or by modifying the registry directly. The defaults are
stored at HKLM\Software\Microsoft\MSSQLServer\MSSQLServer. Two keys:
DefaultData and DefaultLog, which should both be REG_SZ. Note that if you
haven't previously modified this setting in EM, the keys will not yet exist.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:66B065D1-287D-485E-B3C5-425852D83EFE@.microsoft.com...
> Where does the SQL Server default the "file name" parameter when you
> create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is
> installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance|||The following article should explain the New Database default locations.
Basically though these values are stored in the registry for each SQL server
instance.
http://www.wardyit.com/blog/blog/archive/2005/11/10/58.aspx
- Peter Ward
WARDY IT Solutions
"Homer" wrote:
> Where does the SQL Server default the "file name" parameter when you create
> a database WITHOUT entering your own parameter for "file name".
> You can do a simple "create database somename" and it will create the
> database with the defaulted "file name" to be where SQL Server is installed
> (location of the mdf and ldf). Does SQL Server take the default location
> from the model or master databases or some file group setting?
> Thanks in advance
Tuesday, February 14, 2012
Create Database in Management Studio
Hi
Does anyone know how to create an empty database in Management Studio. Or how to get rid of tables that are in the default database that the default script creates using a single command.
By deault the database is created with all the tables from Model database, and to get rid of tables individually, one has to go and look at all the dependencies before they can be deleted.
Thanks
Alvin
Hi,once we did that our own. The model database could not be changed for some reason I can′t remember :-) So we wrote a stored procedure which copied a template to a specified folder and attach the database to the server. The template database was clear from the users / objects we did not want to have in the new database.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
CREATE DATABASE
Is there any way to learn default settings for path where new database
will be created on server machine if I do not specify the path explicitly,
just running on client machine:
CREATE DATABASE [testdb]This is decided by either the
Default Data Directory (Right click server | Properties | Database Settings)
If that is empty then you will still be using the default dir (DATA) from
install time
In my case this would be
D:\Microsoft SQL Server\MSSQL\Data
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:OaoBReefDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any way to learn default settings for path where new database
> will be created on server machine if I do not specify the path explicitly,
> just running on client machine:
> CREATE DATABASE [testdb]
>
>
will be created on server machine if I do not specify the path explicitly,
just running on client machine:
CREATE DATABASE [testdb]This is decided by either the
Default Data Directory (Right click server | Properties | Database Settings)
If that is empty then you will still be using the default dir (DATA) from
install time
In my case this would be
D:\Microsoft SQL Server\MSSQL\Data
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:OaoBReefDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Is there any way to learn default settings for path where new database
> will be created on server machine if I do not specify the path explicitly,
> just running on client machine:
> CREATE DATABASE [testdb]
>
>
Subscribe to:
Posts (Atom)