Copying Sprocs, DTS To Multiple DBs And Servers
May 3, 2007
We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.
We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.
View 4 Replies
ADVERTISEMENT
Dec 22, 1999
I have two servers running SQLserver 7.0. I have a number of jobs that are configured to import files from an AS/400 each day. I do not want to recreate these JOBS on the second server, for it took quite sometime to set up only one. I know that I can set up multi server jobs, but I want these jobs to run independant of the Master server. In other words I just want to move a copy of the job over to the next server.
View 1 Replies
View Related
May 6, 2004
HI All,
Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.
I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.
moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.
Any assistance here is greatly appreciated.
Cheers
Troy
View 3 Replies
View Related
Apr 10, 2008
I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am"
I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now.
Please help.
Thanks,
JC
View 11 Replies
View Related
May 18, 2006
I'm trying to copy files between 2 servers on a local network from within aSQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an accessdenied message returned.I'm able to successfully do the copy from within a command window so thinkthe problem has something to do with using the default SQL Server accountbut as yet I don't know how to resolve.Any help/suggestions would be much appreciated.
View 1 Replies
View Related
May 22, 2008
HI,
Actally we have different server and we work on only one server and end of the day i have to copy the list of newly or modified procedure to other databases of other server. i have create the following Sp. but i am hving problem int it. please any one can have a look..
Declare @ServerName nvarchar(max)
Declare @DatabaseName nvarchar(max)
Declare @Prod nvarchar(max)
declare @m nvarchar(max)
DECLARE @SERVER nvarchar(max)
Declare @String nvarchar(max)
DECLARE @sp VARCHAR(MAX)
SELECT @sp = OBJECT_DEFINITION(OBJECT_ID('SPNAMEMOD'))
SET @Prod = 'SPNAMEMOD'
DECLARE ProcedureScripingCursor CURSOR FOR
SELECT SQLServer,DatabaseName FROM databaseListName
-- where sqlserver is ip address and databasename is databasename on that server
OPEN ProcedureScripingCursor
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp = REPLACE(@sp,'''','''''')
Set @String = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
Set @M = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
SELECT @M = @M + ' IF EXISTS (SELECT * FROM sys.objects where NAME = '''''+ @Prod + ''''') DROP PROCEDURE ' + @Prod +' '''
select @string = @string + ' ' + @sp + ' '''
SELECT @M
select @string
PRINT @M
-- Print @string
EXEC sp_Executesql @M
exec sp_Executesql @string
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
END
CLOSE ProcedureScripingCursor
DEALLOCATE ProcedureScripingCursor
View 2 Replies
View Related
Feb 23, 2007
hi
I've got a job which copy tables between different servers .
I am feeding the tables one by one and the process of copying is in a loop so I have cotrol over the copying process.
it works fine but sometimes randomly I am getting
Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
and the process fails and this might happen for in any point and on any table and sometimes it runs all the way successfully!!
any idea what the problem might be!
Thanks
View 5 Replies
View Related
Apr 23, 2006
I am wondering what the advantages of using CRL Sprocs over T-SQL sprocs and what not.
Looking for such comparison and articles on websites resulted in only "how to create CRL sprocs" but none of them were talking about what they are used for in what situations.
I would really appreciate it if you guys can post comments, links and external articles.
Thank you in advance.
View 1 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Aug 1, 2005
I am totally stumped. In SQL
Server 2000, I would fire up my EM and right-click on multiple SPs and then
Generate Scripts. I then would start QA and run the script on a different DB.
This was a very convenient feature to copy SPs over from one DB to other as well
as from one machine to other. I can’t seem to do the same in SQL Management
Studio. Is it possible? If yes, how?
Now this may not be the right place to ask but since VS and SS go hand-in-hand, I thought I would ask.
View 2 Replies
View Related
Sep 20, 2007
Hi,
I have 3 tables with the follwing schema
Table <Category>
{
UniqueID,
LastDate DateTime
}
Assume the follwing tables with data following the above schema
Table Cat1
{
1, D1
2, D2
3, D3
}
Table Cat2
{
2, D4
3,D5
4, D6
}
Table Cat3
{
1, D7
3,D8
5,D9
}
I have a Master and the schema is as follows
Table master
{
UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{
UniqueId cat1 cat2 Cat3
------------ --------- ------- -----------
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}
Please remember the column names will be same as that of table names
can any one pelase let me know the query t o acheive this
Thanks for your quick response
~Mohan Babu
View 1 Replies
View Related
Mar 27, 2008
Hello-
I need to know what would be the best way to perform a task I have been assigned. I have read multiple post online, and I came to the conclusion that the Import/Export wizard was my best choice. I'm trying to copy at least 80 tables from a SQL 2000 server to a SQL 2005 server. Currently I have these tables over on the destination server (SQL 2005) but this data is outdated and needs to be updated. The ulitimate goal would be to set up a SSIS process so that I can schedule this process to copy over once the data has passed QA. I followed through the Import/Export Wizard inside of the BID and I manually highlighted all of the tables and performed a edit "delete rows in destination table" . But to my alarm this did not occurr and now I have duplicate records in all of my 80 tables. I'm going to go through this process again, but I wanted to make sure this was going to be my best option.
Any suggestion ?
Shanon
View 4 Replies
View Related
Sep 20, 2007
Hi,
I have 3 tables with the follwing schema
Table <Category>
{
UniqueID,
LastDate DateTime
}
Assume the follwing tables with data following the above schema
Table Cat1
{
1, D1
2, D2
3, D3
}
Table Cat2
{
2, D4
3,D5
4, D6
}
Table Cat3
{
1, D7
3,D8
5,D9
}
I have a Master and the schema is as follows
Table master
{
UniqueId,
Cat1 DateTime, -- This is same as the Table name
Cat2 DateTime, -- This is same as the Table name
Cat3 DateTime -- This is same as the Table name
}
After inserting the data from all these 3 tables, I want the my master table to look like this
Table Master
{
UniqueId cat1 cat2 Cat3
------------ --------- ------- -----------
1 D1 NULL D7
2 D2 D4 NULL
3 D3 D5 D8
4 NULL D6 NULL
5 NULL NULL D9
}
Please remember the column names will be same as that of table names
can any one pelase let me know the query t o acheive this
Thanks for your quick response
~Mohan Babu
View 8 Replies
View Related
Dec 25, 2007
Hi,
Can anyone tell how to copy multiple tables from from one db to another using SSIS object model.
I am confused with things like. Do I need to add multiple source and destination in a dataflow task or do I need to add multiple dataflow task?
What is the difference between these two? Is there any other (easy way) to achieve this?
Thanks.
View 7 Replies
View Related
Nov 15, 2007
Hi All,I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?ThanksBryan
View 3 Replies
View Related
Mar 2, 2015
What's the easiest way to run the same db across multiple servers?
View 4 Replies
View Related
Jun 3, 2008
hi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)
View 2 Replies
View Related
Jul 12, 2000
suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you
View 2 Replies
View Related
Feb 6, 2001
Question:
How to prevent a user/admin on one SQL 7 server, who knows the system admin password that is common to both servers, from accessing a second SQL 7 server via the SQL Server Group setup process.
Is there a way from shutting off remote access to a SQL server without limiting RPC?
All reading points towards 1) Remote Access Options, 2) Store User Independent, 3) Control Store as options to locking down a server. Would like to hear from someone that ran into this type of issue. Third party software available?
I've tried the following without any success: 1) I have deleted the second SQL server registration from the first SQL server group - but this is too easy to delete and recreate, 2) edit the SQL server registration for the remote server and force authentication - almost there if SQL prevented deletion of the registration without verifying the logon/password of the person who created it (again able to delete and recreate), 3) played around with the client utility to remove the TCP/IP pipe entry.
Anyone else pulling their hair out on this?
TIA!
View 1 Replies
View Related
Nov 12, 1999
Please excuse my niavety on this subject, But I have a simple question. When you have more than one SQl server 7 installation on the same network, each using different machines, does one automatically have the ability/authority to be able to stop the other server at will??
Thanks in advance for any help
Simon
View 2 Replies
View Related
Mar 12, 2007
Joe writes "I am trying to find whether I can have multiple SQL 2005 servers but having same security profiles and maintain them grom one place only. I prefer not to have windows authentication.
Thanks a lot"
View 1 Replies
View Related
Apr 13, 2007
I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.
Any help on this would certainly be appreciated.
Thanks in advance.
View 2 Replies
View Related
Nov 15, 2006
Hi,
I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...
Is there a way to do that...
I need to connect to two servers from the same procedure to get data..
Please get back
Thanks,
View 3 Replies
View Related
Nov 27, 2007
Hi,
I want to perform a join on two tables from two separate database servers.
Code Block
SELECT *
FROM tbl1 AS t1
INNER JOIN
"SERVERASQL2000".Production.tbl2 AS t2
ON t2.UserID = t1.UserID
Why do I get this error...
Invalid object name 'SERVERASQL2000.Production.tbl2'
thanks.
View 10 Replies
View Related
Aug 24, 2000
I have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?
Thanks!
Lia
View 1 Replies
View Related
Feb 29, 2008
Hi,
I have zero experience running any databases that spread further than 1 machine, so I have a few theory questions here that hopefully someone can help with. Hopefully this is the right forum, I'm not sure if it classifies as 'clustering'.
Anyways, we are launching a web app that is going to start with just 1 webserver/db server. For speed reasons, after some growth we might have to have a load balanced setup with a webserver in europe and one in north america. Basically the webservers are going to be serving 100,000's of files and each time a file is served it needs to be recorded in the database.
I think that if I'm connecting my european webserver across the internet to my db server, thats killing the purpose of having a webserver in europe to make for faster responses.
I am thinking that this european web server/db serving is only going to be logging the files served. Is there a way to import them into north american database everynight ?
I'm not sure what the best approach would be for something like this, but any suggestions are greatly appreciated.
Thanks very much,
mike123
View 2 Replies
View Related
Nov 14, 2007
Is it possible to get an aggregate list of databases from multiple servers, without using SMO?
All target servers are running SQL Server 2005.
Ben Aminnia
View 2 Replies
View Related
Oct 17, 2007
Hi, I was wondering what the licensing requirement would be for following scenario:
1 active sql server installation installed on a windows failover cluster.
1 passive mirror sql server (single server).
1 passive log-shipped sql server (single server).
In the microsoft white paper it says:
When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license, provided that the number of processors in the passive server is equal or less than those of the active server. The passive server can take the duties of the active server for 30 days. Afterward, it must be licensed accordingly.
So do I need to buy an enterprise license for each of the additional passive servers...in the above example meaning I would need 3 licenses in total?
Thanks,
Paul
View 3 Replies
View Related
Dec 1, 2006
Apologies if this question has been asked before. I am about to upgrade a multiuser application based on Access97 and VB6 to C# and possibly SQL Server Express.
My customer does not want to use a server based database solution ie a single instance of SQL Server, but prefers to have a common networked dataset which is accessed via user's own individual database engines. This is because of restrictions on the installation of server based apps.
Does this scenario completely rule out the use of SQL Server Express?
Thanks
View 3 Replies
View Related
Jul 19, 2007
Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?
Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.
The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.
Any advice is appreciated.
View 7 Replies
View Related
Feb 18, 2007
I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.
When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).
--To create my cert and key:
USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO
CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO
To encrypt:
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert
WITH PASSWORD = 'same password as above';
Insert my record, use scope_identity to return primary key into @CustomerID.
INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @DataToEncrypt, 1, CONVERT( varbinary, @CustomerID)))
CLOSE SYMMETRIC KEY MyKey
To decrypt:
SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @CustomerID))) as PlainTextData
FROM Customers
WHERE (CustomerID= @CustomerID)
Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.
Thanks in advance for your help!
View 5 Replies
View Related
Jul 4, 2007
I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.
I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.
Any help would be great.
Thanks!
Eric Elliston
SWFLParent.com
http://www.swflparent.com
View 1 Replies
View Related
Nov 9, 2001
I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).
For example, I retrieve data from two linked servers using the following:
select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere')
select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere')
.
.
lots of manipulation of the temporary tables to get what I want, etc.
.
.
The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?
Bill
View 1 Replies
View Related
