Hi,
I've a SQL2000 server with an instance.
I wanna replicate this istance in another server on my lan.
How can I do this?
Thanks
We'd need to know a lot more to fully understand the requirements, but for
creating and maintaining a copy of user databases, have a look in BOL for log
shipping.
Cheers,
Paul Ibison
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Monday, March 19, 2012
Thursday, March 8, 2012
create only sp's during schema initialization
Hey, I've created the indexes and tables and stuff manually. I just need SQL
to create the 3 sp's on each table during Trans Repl. Does it do that or it's
all or nothing kind of situation?
Right now, I manually edited about 20 schema files from snapshot to not drop
and recreate the table. And, it takes time.
Tejas,
for a nosync initialization, if you run sp_scriptpublicationcustomprocs
'publicationname' at the publisher, the results (in text format) are 3
stored procedure creation scripts. These are then run on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||see, when you say, 'no, the subscriber already has the schema and data' even
the data doesn't get transferred over. How would I be able to do it through
replication process? or i have to sue bcp or dts or something EXTERNALLY?
Thank you.
|||Tejas,
now I'm confused
I thought you were trying to achieve a nosync
initialization? If not, then the normal initialization process will take
care of the data transfer. Please can you clarify a bit more for me exactly
what you want to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
What had happened is this.
I was asked to script the tables and indexes from the publisher and run them
on the sub. Now, I cannot use the 'yes,initialize the schema' option as it
would overwrite all that. But at the same time, I could not use 'no, the sub
already has the schema and data' option cuz that would not transfer over the
data. That's what I was asking you about.
|||Tejas,
what I don't understand is the point of putting just the schema on the
subscriber. It's standard practice to do a full initialization (schema and
data) or a nosync one (neither). So, if there is no reason for creating the
shema on the subscriber, then I'd do a standard initialization and let it
drop the articles on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I do it the way you asked me to, the replication finishes doing the
schema and most of the data and when it starts doing the indexes, the log
file grows like crazy an i dont have that much space to fulfill the logspace
need. Is there a work around this? And i think it evetually times out. I read
somewhere to increase the querytimeout for this. But still, how about the log
file space?
thank you.
by the way, I tried it the way you had said first
|||Tejas,
if you're struggling for space to host the log file, there's no simple fix
Options include creating a separate log file on another disk, trying
simple recovery mode etc
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
to create the 3 sp's on each table during Trans Repl. Does it do that or it's
all or nothing kind of situation?
Right now, I manually edited about 20 schema files from snapshot to not drop
and recreate the table. And, it takes time.
Tejas,
for a nosync initialization, if you run sp_scriptpublicationcustomprocs
'publicationname' at the publisher, the results (in text format) are 3
stored procedure creation scripts. These are then run on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||see, when you say, 'no, the subscriber already has the schema and data' even
the data doesn't get transferred over. How would I be able to do it through
replication process? or i have to sue bcp or dts or something EXTERNALLY?
Thank you.
|||Tejas,
now I'm confused
I thought you were trying to achieve a nosyncinitialization? If not, then the normal initialization process will take
care of the data transfer. Please can you clarify a bit more for me exactly
what you want to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
What had happened is this.
I was asked to script the tables and indexes from the publisher and run them
on the sub. Now, I cannot use the 'yes,initialize the schema' option as it
would overwrite all that. But at the same time, I could not use 'no, the sub
already has the schema and data' option cuz that would not transfer over the
data. That's what I was asking you about.
|||Tejas,
what I don't understand is the point of putting just the schema on the
subscriber. It's standard practice to do a full initialization (schema and
data) or a nosync one (neither). So, if there is no reason for creating the
shema on the subscriber, then I'd do a standard initialization and let it
drop the articles on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I do it the way you asked me to, the replication finishes doing the
schema and most of the data and when it starts doing the indexes, the log
file grows like crazy an i dont have that much space to fulfill the logspace
need. Is there a work around this? And i think it evetually times out. I read
somewhere to increase the querytimeout for this. But still, how about the log
file space?
thank you.
by the way, I tried it the way you had said first
|||Tejas,
if you're struggling for space to host the log file, there's no simple fix
Options include creating a separate log file on another disk, tryingsimple recovery mode etc
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 7, 2012
create multiple views
Hi,
I want to create multiple views in one run in the query analyzer, but it
won't work. I've the following sqlcode in the analyzer:
create view test1 as select.......
create view test2 as select......
etc.
What is wrong with this code?
On Thu, 5 Aug 2004 08:09:02 -0700, Ezekil wrote:
>Hi,
>I want to create multiple views in one run in the query analyzer, but it
>won't work. I've the following sqlcode in the analyzer:
>create view test1 as select.......
>create view test2 as select......
>etc.
>What is wrong with this code?
Hi Ezekil,
The CREATE VIEW statement must be the first in a batch. So your code will
run if you add batch seperators:
create view test1 as select.......
GO
create view test2 as select......
GO
etc.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
I want to create multiple views in one run in the query analyzer, but it
won't work. I've the following sqlcode in the analyzer:
create view test1 as select.......
create view test2 as select......
etc.
What is wrong with this code?
On Thu, 5 Aug 2004 08:09:02 -0700, Ezekil wrote:
>Hi,
>I want to create multiple views in one run in the query analyzer, but it
>won't work. I've the following sqlcode in the analyzer:
>create view test1 as select.......
>create view test2 as select......
>etc.
>What is wrong with this code?
Hi Ezekil,
The CREATE VIEW statement must be the first in a batch. So your code will
run if you add batch seperators:
create view test1 as select.......
GO
create view test2 as select......
GO
etc.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
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...
>
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 login for existing database user
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:
> Hello,
> I've this problem with a database user that was restored from a backup. If i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else.
> Is it possible to work around it.
> THX!
>
>
|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
If i[vbcol=seagreen]
else.[vbcol=seagreen]
|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:
> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:
> Hello,
> I've this problem with a database user that was restored from a backup. If i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else.
> Is it possible to work around it.
> THX!
>
>
|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
If i[vbcol=seagreen]
else.[vbcol=seagreen]
|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:
> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>
Tuesday, February 14, 2012
Create database from code
Hi, I want to create a database setup on a server. I've scripted my database from sql server express. I've tested the code in the query window and it worked. When i pasted the same code in a sqlcommand command text...the debuger threw an sql exception...saying the sintax is wrong near keywords GO, USE, some forgein keys, and so on. Here is a chunk of the script.
USE [master]
GO
/****** Object: Database [estate_management] Script Date: 09/13/2006 09:19:32 ******/
CREATE DATABASE [estate_management] ON PRIMARY
( NAME = N'estate_management', FILENAME = N'D:\MSSQL\estate_management.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'estate_management_log', FILENAME = N'D:\MSSQL\estate_management_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[admin](
[admin_id] [int] IDENTITY(1,1) NOT NULL,
[log_in_id] [varchar](20) NOT NULL,
[password] [varchar](50) NOT NULL,
CONSTRAINT [PK__admin__07020F21] PRIMARY KEY CLUSTERED
(
[admin_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 1) ON [PRIMARY],
CONSTRAINT [IX_admin] UNIQUE NONCLUSTERED
(
[admin_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
Thanks for your time.
You'd better set the database file size to larger value, to host a copy of the model database. Except this, your script works fine when I tested it under sqlcmd, both on SQL2000 and SQL2005 the database can be created successfully.
Subscribe to:
Posts (Atom)