Hopeing this is something I can accomplish. I use MS-SQL for close to
100 CMS web servers. Everytime I create a new website I have to create
a new database and then use the export from SQL to SQL option and copy
all data / SP's / etc... Just no security information. My question is
can I automate this? Can I create a script that will run all this?
Thanks for any help, and thanks for reading.
Chris Auer
Join Bytes!
Is any tool is available for replicating the Microsoft sql database..
View 1 Replies View RelatedMy environment are Win Vista, Ms sql 2005 express, Ms Sql Server Management studio express. I have a database Mysearchdb.mdf and want to replicate Myseachdb.mdf to another database with exact same features. What is the best way to do it? I don't want to create each tables, functions and store procedures manually. Thanks
View 8 Replies View Relatedwe have a server in our environment, we would like to make a copy of it on the other server in side the company and also do the same on the remote server.
Can u tell me how to do it or any sites where i can find the stratergy's which have been already implemented for review.
Thanks and with reply is appreciated,
harry
i want to replicate the data from one table into another table in the same database,all the transactions in table "a" should be replicated in table "b" and vice versa.
Note:this should be done without using triggers
Very new to the world of databases. Our current database situation is a train wreck, but I'm not at the point I can fix it. So, all I'm trying to do right now is copy our 'live' database so that we can use the copy for development, without worrying about blowing things up.
Anyhow, the problem I have is that when I backup, and then try to restore the 'live' database, I'm told the drive doesn't have enough space, which is accurate (only has 1gig, needs 3gigs). FYI, EVERYTHING that has to do with our databases is currently on a 14gb partition, of which 13gigs are being used. There is an empty partition of 120gb just sitting there.
Can somebody tell me what is the best way to get this 3gb development database up? I tried to route the Data and Transaction Log paths to the empty partition, but it still seemed to want to mess with the 14gb partition and told me there isn't enough space when I tried to restore.
Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!
Thanks for ANY information you can give me...
~Jepadria
My company is considering purchasing MS SQL Server to run anapplication on (SASIxp). I am mainly familiar with Oracle, so I waswondering how long it would take to copy a database. Basically we havedatabase A and each night we want to replace database B with thecontents of A. How long would this take say if we had a 10GB databaseor a 20GB database.What would be the technique to do this nightly, the Copy DatabaseWizard, Snapshot Replication, Attach & Detach...? We need to automatethis process, and the source database can be made unavailable whenthis happens.Thanks,Roger
View 2 Replies View RelatedI have a database called sky and its tables, views, procs and functions owned by sky. I need to replicate the sky database to another server. I had problem because those objects have ownership sky not dbo. I can not change ownership when replicate the database. How do I replicate database objects that are not owned by dbo? Is this possible or I have to change ownership from sky to dbo before replicate the database?
Thank you very much for your input and suggestions.
Dear group,I have to replicate remote data to a SQL Server in the headquarter.The data are on a site which does not have permanent online-connectionto the headquarter.I have written a script which replicates the data and i want to set upa process / batch on the headquarter-machine which roughly does thefollowing1) Connect to the remote site2) start replication script (in fact a stored procedure)3) on success disconnect from the remote siteAll this has to run automatically e.g. during nighttime.Could someone please outline how i set up the connection and keep itup during my database-action and how to automatically disconnect aftersuccess of my replication script.I'd be jolly grateful.Thanks in advance and Greetings from ViennaUli
View 1 Replies View RelatedI have 2 SQL 2008 R2 Servers which are always running (on is Prod/one is Test).
is there a way to sync Prod to Test WITHOUT using the .bak and overriding Test's Security settings?
I have configured replication between Always ON Availability Groups (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR
to
Always ON Availability Groups (Listener) SUBSCRIBER...
We are setting up a test lab environment with 100 machines. We want one master testing db that gets replicated to each to run scripted application tests nightly.
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
Is there a way to automation SQL on an SQL Server to perform
impports and exports at a certain of the night. The server is
always running. Thank Dee
I'm trying to bring down a file from the AS/400 to update a SQL database table. Either thru the configuration limitations on the SQL server or ??? I cannot reach the as/400 from my SQL box so I'm running the data down to a Citrix server to a text file via client access.
I need to automate the DTS package run on the SQL box to pull the data from the Citrix server over to the SQL box.
How do I set up the DTS package to run on a schedule?
One of my developers needs on enable Ole Automation on production? What security loop holes we are creating, by enabling this feature in SQL2K5?
------------------------
I think, therefore I am - Rene Descartes
How can I import an xml file to SQL 2005 at the same time every night? I
will need to create a new database first via the import after that I will be
appending to the dataabase. Then I will need to xport the data into a
different xml file and upload to my hosting system which is yahoo. All so the
original xml file is on the yahoo. store server.
I have SQL 2005 standard install on my personal work stations. When I iron
the bugs out I will be running this from our SQl 2005 Enterprise server.
Thank you
Dee
We've an a VB application connecting to SQL Server 7. Recently the log files show 'Automation error' in one of the routine. I've scan thru the code & pass in some test case & it is ok.
Any idea what could have cause the 'Automation error'?
Thanks in advance.
Ben
Hello Experts,
I am a New Bee in SQL.I would like to know,As a System Admin I would like to automate the task of keeping the SQL Server up to date with latest Service Packs and Patches.
Is there any procedure / Software where I can automatically download the patch / sp's from the site and install on the servers or maybe schedule the task for every month.
I would appreciate a reply from the experts
Best Regards
SunilVKulkarni
Hi,
I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).
Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.
Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"
Regards
Mark
Hi,I tried to add column headers and delete all rows to excel databasewhich is a linked server to my sql server db.I got an error message, "delete data through linked server is notsupported by Isam".I tried to use FSO to write to the excel file, but seems like FSOsupport write to only text file.the last time I tried to create an excel applicaiton object and temperwith its cell, I hang my Excel application and had to reinstall it.Have any ideas,Thank you very much
View 1 Replies View RelatedHello,
Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.
Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!
In MSS 2000, it was possible to have a stored procedure that used OLE Automation to execute a DTS package. E.g.
declare @oPKG int
exec sp_OACreate 'DTS.Package', @oPKG OUTPUT
declare @pkgName varchar(128)
declare @server varchar(20)
declare @cmd varchar(1100)
declare @userid varchar(20)
declare @ServerPWD varchar(20)
set @pkgName = 'PKTestOuter'
set @Server='myServer'
set @userid='myuser'
set @ServerPWD='myPW'
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + @userid + '", "' + @ServerPWD + '", 0, , , , "' + @PkgName + '")'
EXEC sp_OAMethod @oPKG, @Cmd,NULL
exec sp_OAMethod @oPKG, 'Execute'
When I move - but not migrate - packages to 2005, this still works. I don't have a problem with permissions or any of the other problems that I have seen posted in this forum. But if I migrate a package to SSIS, the package is now seen in sysdtspackages90 instead of table sysdtspackages (assuming I remove the old version). But "LoadFromSQLServer(...)" can't find it anymore. So it seems like one of the following is true:
a) There is a different item that I should use in sp_oacreate besides DTS.Package. I tried SSIS.Package or DTS.Package90 which seemed like reasonable possibilities but they don't work. Is there something that will work? Or is "LoadFromSQLServer(...)" not supported for SSIS packages?
b) Or OLE Automation doesn't work with SSIS packages. OLE Automation is enabled and it works on non-migrated packages. Is it true that it was decided by Microsoft that this capability was not needed in SSIS?
I see numerous postings saying not to use the sp_OA routines. I understand there are other ways to execute an SSIS package from a SP and that there may be more secure ways. But I need to know if it is an absolute requirement to use these other methods.
Paul
hi
i have created a rss script for report deployment which i working pretty good but icheckout my all reports on report server and from another machine i mapped that drive from command line and run rss script then i got following error
================================================================
:Automation>rs -i PromotionAutomation.rss -s http://fsaleem/reportserver -v en
v=Dev -v DBServer=fsaleem
Unhandled Exception: System.TypeInitializationException: The type initializer fo
r "Microsoft.ReportingServices.ScriptHost.ScriptHost" threw an exception. ---> S
ystem.Security.SecurityException: Request for the permission of type System.Secu
rity.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0, Culture=neutral
, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet granted
Set, PermissionSet deniedSet, CodeAccessPermission demand, PermissionToken permT
oken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken permToken,
CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 checkFrames, Int32
unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission cap, S
tackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Reflection.Assembly.VerifyCodeBaseDiscovery(String codeBase)
at System.Reflection.Assembly.GetName(Boolean copiedName)
at System.Reflection.Assembly.GetName()
at Microsoft.ReportingServices.ScriptHost.ScriptHost..cctor()
The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission, mscorlib, Vers
ion=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PathDiscovery="Z:AutomationRS.exe"/>
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ScriptHost.ScriptHost.Main(String[] args)
================================================================
hello
is it possible to generate PDF files from reports programmatically ?
Hey,How can I schedule a stored function to run atleast once a day, other than by having it as a scheduled task on the server?Sql Server 2000Thanks
View 3 Replies View RelatedI have spent the last couple of weeks going through about 15-20 different examples/tutorials on running DTS packages from inside a C# Windows app (and even asp.net). I must have a mental block, because I can't seem to make it click in my head. I have hundreds of DTS packages saved as .dtsx files. I can double click on them and run them perfectly. What I need to do is be able to run them conditionally from inside a windows app. Could anyone point me to a tutorial or example that could help me with this? I've even looked at some VB source to see if I could work it out, but I keep running up against a wall. I'd appreciate any help anyone can provide.
View 1 Replies View RelatedI have been trying to execute the following code:
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
--Instantiate a Network Object
EXEC @hr = sp_OACreate 'WScript.Network', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a property using an output parameter.
--EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT
EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
--PRINT @property
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
I get the same behavior stated in microsoft's support site related to BUG
55840 for Sql Server 7.0.
I get an error message when trying to retrieve with @property OUT parameter.
And I get an Empty record set when NO Output param is supplied.
http://support.microsoft.com/support/kb/articles/Q236/4/40.ASP
I am running Sql Server 7.0 SP2 on Windows 2000 AS.
SP2 has this BUG listed as being Fixed. Am I missing something?
Thanks in Advance for your Help,
Chris
Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay
Hello everybody ,
I need to create a automation job for 2005 analysis services database to backup a database every night . i dont have sql server agent to create a job. I was told about a method to create a batch file and than use ssis or something similar to execute the job.
can anyone please help me out in guiding me with proper documentation to perform this procedure so that i can create a automation job without the help of sql server agent to create a backup job every night for anaylysis services 2005 database ......analysis services 2005 only
Thanks in advance
Yukon DBA
I have written quite a complex query in Query Analyser, now need toknow how to automate the running of the query to dump the results intoa flat file at scheduled times. Am an experienced Unix user, where itwould be quite simple but new to sql server, is there a relativelysimple way to do this?
View 1 Replies View Relatedcan any one send me a useful link which is easy to undersatnd Step by Step for Aumaotion of Reports Deployment?
I'm a newbie to SQL Server, trying to make the leap from Access because I'm starting out grow it for my needs. I have a lot of automation stuff in Access where it does a query/code driven process for us via a macro. Sometimes the output is a report, or a form with assembled data that we use. I'm wondering if SQL Server 2005 is capable of doing automation of processes like these as well. Even if it's not necessarily a macro, something I can program, compile and have use off of my SQL Server database to do the tasks that the Access macro did.
View 1 Replies View Related