Converting problem: windows

Showing posts with label windows. Show all posts

Thursday, March 29, 2012

Copy of RS??

Hi there

We are running SQL server 2000 on a windows server 2000 OS, we are also using the Evaluation edition of RS, can you tell me if the full version is on the SQL server 2000 cd, or the Windows Server 2000 cd?

thanks

tim

SQL RS is a part of SQL Server, so you'll find the install bits on the SQL Server CD. If you bought the CD before Reporting Services was released - you'll have to order a CD from Microsoft.|||

Hi there, thanks for the reply

How do I order from Microsoft and will there be a charge?

Thanks

Tim

Copy just the Database Structure not the Data

I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.

I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.

How would I take just one table from my test database and add it to my production database without retyping the design?

Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?

hi,

GrahamY wrote:

I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.

you can script out the metadata using SQL Server Management Studio Express, but this still works for 1 object at the time...

or you can have a look at this toolkit for a more comprehensive generation "wizard"..

or, you can have a look at 2 free prjs of mines, amScript and amInsert that can produce DDL scripts as long as INSERT INTO scripts to populate existing tables...

then you can execute the DDL script to create a db "clone"....

I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.

backup/restore feature can be handy, but not if you want to only have an "empty" database..

How would I take just one table from my test database and add it to my production database without retyping the design?

see above..

Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?

the full blown editions offer other "features" missing in Express editions as the complete SSIS integration, BI tools and features, mirroring features, more cpu support, more ram support ... http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

Sunday, March 25, 2012

Copy databases from SQL 2005 server to SQL Express

Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.

Is it possible to copy databases from SQL 2005 to SQL Express?

Thanks.Thanks (Thanks@.work.com) writes:

Quote:

Originally Posted by

Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.
>
Is it possible to copy databases from SQL 2005 to SQL Express?

Yes, but I don't think you can use the Copy Database Wizard, but you will
have to do it by hand. Which is not very difficult anyway.

On source machine backup database, and make note of the logical
filenames. (You see these with sp_helpdb).

On target machine to do:

RESTORE db FROM DISK = 'pathgoeshere'
WITH MOVE 'logicalfilename1'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf',
WITH MOVE 'logicalfilename2' TO
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf',
REPLACE

I've used MSSQL.2 for the target, but you use the number for your
Express instance. You find out which is which by looking around in
the directories.

This does not migrate logins, and mappings user and logins are likely
to be out of whack. You can use sp_change_users_login to fix.

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

Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express

Is it possible to connect the SQL Express in B from the A using the SQL
2005?

I tried it, but failed.

If I can do this, that means I can use Import function in SQL 2005 to import
Access databases into SQL Express.|||Am_I_right (Am_I_right@.world.com) writes:

Quote:

Originally Posted by

Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express
>
Is it possible to connect the SQL Express in B from the A using the SQL
2005?
>
I tried it, but failed.

How did you try? How did it fail?

You can always set up a linked server, but it's unclear to me if that
is what you tried.

Quote:

Originally Posted by

If I can do this, that means I can use Import function in SQL 2005 to
import Access databases into SQL Express.

No, that is what you can do with a linked server. You could however
import you Access database into SQL 2005 non-Express, and then
copy the database with backup/restore as I described in my previous
post.

Quote:

Originally Posted by

Am_I_right (Am_I_right@.world.com) writes:

Quote:

Originally Posted by

>Same system setup as before.
>Computer A with SQL 2005
>Computer B with SQL Express
>>
>Is it possible to connect the SQL Express in B from the A using the SQL
>2005?
>>
>I tried it, but failed.

>
How did you try? How did it fail?
>

Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.
Thanks.

Quote:

Originally Posted by

You can always set up a linked server, but it's unclear to me if that
is what you tried.
>

Quote:

Originally Posted by

>If I can do this, that means I can use Import function in SQL 2005 to
>import Access databases into SQL Express.

>
No, that is what you can do with a linked server. You could however
import you Access database into SQL 2005 non-Express, and then
copy the database with backup/restore as I described in my previous
post.
>
>
--
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

|||(Thanks@.work.com) writes:

Quote:

Originally Posted by

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9874F08E810F0Yazorman@.127.0.0.1...

Quote:

Originally Posted by

>Am_I_right (Am_I_right@.world.com) writes:

Quote:

Originally Posted by

>>Same system setup as before.
>>Computer A with SQL 2005
>>Computer B with SQL Express
>>>
>>Is it possible to connect the SQL Express in B from the A using the SQL
>>2005?
>>>
>>I tried it, but failed.

>>
>How did you try? How did it fail?

>
Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.

Just because you connect the computers with a network cables does not
mean that the two SQL Servers are connected. You must also set up a
linked server. Although it depends on what you want to do. Linked servers
are needed only if you want to access tables on server from the other.

Set up SQL sever is ok, import Access to SQL is also oK for me.
but couldn't figure out how to import Accees into SQL Express.
either directly or through another SQL 2005 server.

Next I am going to try import Access into MySQL by using Premiumsoft
Navicat.
If that work, I will decide to use MySQL, because it is open source and
lower cost.

Thursday, March 22, 2012

Copy Database Wizard needs what?

I am trying to launch Copy database wizard from SS2005 SP2 Developer Ed. (on Windows XP SP2) to get databases from SS2005 SP1 Enterprise Ed. (on Windows 2003 Server Enterprise Ed.)

The same collation, default language, SQL Server Agent running on both

I use Windows Authentication with the same sysadmin, part of Administrators on both machines, with the same password. It is in workgroup

I am getting various errors from various attempts.

Ошибка DCOM "Указанная служба не может быть запущена, поскольку она отключена или все связанные с ней устройства отключены. " при попытке запуска службы MsDtsServer с аргументами "" для запуска сервера:

{ABF05265-635E-44B0-A28F-AEA45247ACA0}
(i.e. service MsDtsServer with arguments is switched off)

Таймаут (30000 мс) ожидания для ответа при транзакции от службы clr_optimization_v2.0.50727_32.

Copy Database Wizard shows "The wizard will create a Integration Services package with the properties yo specify below". And it is not enabled on remote SS2005 SP1

So, my questions are what exactly should I have and where in order to run CopyDatabase Wizard to get things from remote server:

1)
Is it possible to have Integration Services running only at one of the SS2005? from which I run the wizard

Should CLR integration be enabled? only at one of the SS2005 (from which I ran wizard)?

3)MsDts Services? only at one of the SS2005 (from which I run wizard)?

What else? firewall, etc.

You need the Integration Services Service to be running on the destination machine and also SQL Agent if you are scheduling the package to run.

You do not need SQLCLR.

The firewall needs to to support the SQL Server and Integration Services ports (you need to look up IS but SQL Server defaults to 1433)

|||

Are you trying port the SSIS package on any of the client machine.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm and ensure all the XML configuration files are copied across teh server & client.

Also http://aspalliance.com/articleviewer.aspx?aId=947a good run on DTS - SSIS understanding.

|||

No, I do not have SSIS packages on source machine and I do not intend to port any.

As I understood, the Copy Database Wizard always ends up with
"The wizard will create a Integration Services package with the properties you specify below
Package name:
"

And why do I get MSDtsServer error?
Can it be connected with the mirroring on source server?

|||Have you attempted Service pack 2 for SQL SErver?|||

Now I have both with SP2 (90.3054) with postSP2 hotfix
http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
http://support.microsoft.com/?kbid=934458

(BTW it is very confusing that 3159, as I understood, is the same as 3054?)

I manage to run it between different instances on the same machine but not from remote one.

Both machines is in workgroup, I use Windows Auth with the same login fro
I use SQL Management Object method
I mark one db to copy to a new one at destination. It is just to pass forward (CDW doesn’t advance with no databases marked) to screen offering me to copy:
- Stored procedures from master database
- SQL Server Agent jobs
- User-defined error messages
- Endpoints
- SSIS packages
- Logins

I choose to copy only Logins(stored procedures from master db. I tried other options too, but this one is the most innocuous from all)

Integration Services Proxy account: SQL Service Account . Run immediately

I cannot change it or have any other choice

Performing operation…
- Add log for package Success
- Add task for transferring database objects Success
- Create package Success
- Start SQL Server Agent Job Error

TITLE: Copy Database Wizard

The job failed. Check the event log on the destination server for details.

Event log shows:

Package "CDW_<source_server>_<target_server>_<target_instance_name>_6" failed.

Дополнительные сведения можно найти в центре справки и поддержки, в http://go.microsoft.com/fwlink/events.asp.

Eveent Log shows various erros:
1)

Package "target_server>_<target_instance_name>_6" failed.

Дополнительные сведения можно найти в центре справки и поддержки, в http://go.microsoft.com/fwlink/events.asp.
2)

Event Name: OnWarning

Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_SERVER310_CELLO_C310_4

Source ID: {037E2C87-3F49-432C-8D67-04485FCD0C7D}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: -2147381246

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

Event Name: OnTaskFailed

Message:

Operator: NT AUTHORITY\SYSTEM

Source Name: SERVER310_CELLO_C310_Transfer Objects Task

Source ID: {DAF81427-B701-4329-BAB2-CFDB1B573E25}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: 0

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

4)
Event Name: OnError

Message: Failed to connect to server SERVER310.

StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)

InnerException-->Login failed for user 'NT AUTHORITY\АНОНИМНЫЙ ВХОД'.

StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_SERVER310_CELLO_C310_4

Source ID: {037E2C87-3F49-432C-8D67-04485FCD0C7D}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: 0

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

5)
Event Name: OnError