Automation On SQL Server

Nov 30, 2004

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

View 1 Replies


ADVERTISEMENT

Access-like Automation In SQL Server 2005?

Jul 8, 2006

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

Automation Of Excel To SQL Server 2005

Aug 11, 2007



Hi all,

I have a task which needs to be automated.

1) Excel spreadsheet load into sql server database as a table.
2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.
3) Then, output should be back into excel spreadsheet.

All these steps 1,2,3 need to be automated.

Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.

Thanks for all your help..





View 1 Replies View Related

Analysis :: Overview Of Automation With Server

Jun 4, 2015

Overview of Automation with SQL server.

View 2 Replies View Related

Sql Server Agent Problem With Excel Automation

Mar 19, 2008

H...
I have a problem to run a package with a scrip task containing the code in this page. I try to create a new excel workbook, change some cells, and save it to the harddrive. When designing the package in BI dev studio, I can execute it without a problem. I can even run it with DTexec.exe. The problem is that when I run this package from the SQL server agent, then it breaks. It breaks on a machine running Windows server 2008 with SQL server 2008 on it and it also breaks on a machine running Vista with sql server 2005, but it runs fine on a windows server 2003 machine with sql server 2005 on it.
I can also mention that if I dim out the one line after the comment in the code, then It runs fine on any one of my machines. The problem seems to be with saving or opening an excel file. I am also making use of a proxy with a credential using my login name. I am part of the administrators on these machines.

Imports eXcel = Microsoft.Office.Interop.Excel
Public Sub Main()
Dim objExcel As New eXcel.Application
Dim objSheet As eXcel.Worksheet
Dim strFname As String = "c: empBook3.xls"
objExcel.DisplayAlerts = False
objExcel.Application.Interactive = False
objExcel.Workbooks.Add()
objExcel.Cells(1, 1) = "0"
'I dim out the next line and it works from sql server agent on any machine
objExcel.ActiveWorkbook.SaveAs(strFname)
objExcel.DisplayAlerts = True
objExcel.Application.Quit()
objSheet = Nothing
objExcel = Nothing


Dts.TaskResult = ScriptResults.Success
End Sub

The error that I receive is this one:
Executed as user: BS-PROCESS1dlangec. ...0.0.1300.13 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:25:16 PM Error: 2008-03-18 22:25:22.40 Code: 0x00000001 Source: Script Task Description: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC Server stack trace: at Microsoft.VisualStudio.Tools.Applications.RemoteTypeAdapter.System.AddIn.Contract.Automation.IRemoteTypeContract.InvokeMember(String name, BindingFlags bindingFlags, IRemoteObjectContract target, IRemoteArgumentArrayContract remoteArgs, Boolean[] remoteArgModifiers, Int32 lcid) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 metho... Process Exit Code 1. The step failed.

View 9 Replies View Related

SQL Server Admin 2014 :: Read Only DNS Listener Failover Automation From Primary To Secondary

Mar 23, 2015

I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.

Env details
xa (current primary)
y2a (secondary)

client access point (resource group): aglreadtest

View 2 Replies View Related

Automation Error, SQL Server Error-MSSqlserver-urgent

Oct 13, 2000

I have three problems related to Sql Server

a.I am developing a database application using VB5/VB6, SQL server 7 and Crystal reports.When I try to generate reports I get the message 'Automation error'. Please help-urgent.

b. I am using temperorary tables to generate reports.ie Table is created by user.Tables are generated runtime ie created as well as deleted. Sometimes
Sometimes i get the message 'SQLServer error' What should I do.What are the rights to be assigned to LOGINS?.

c.Is there precaution while installing SQLserver in WIN98.

View 1 Replies View Related

Automation

Dec 1, 2007

 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 

View 6 Replies View Related

DTS Automation

Feb 5, 2001

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?

View 2 Replies View Related

Ole Automation.

Apr 30, 2008

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

View 5 Replies View Related

Automation

Dec 3, 2007



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

View 11 Replies View Related

Automation Error ?

Mar 18, 2001

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

View 2 Replies View Related

DR Scripting Automation

Jul 9, 2007

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

View 15 Replies View Related

Excel Automation

Jul 20, 2005

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 Related

Automation Error

Feb 2, 2007

Hello,

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!

View 2 Replies View Related

SSIS And OLE Automation

Nov 17, 2006

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

View 1 Replies View Related

Report Automation

Jun 18, 2007

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)



================================================================





View 1 Replies View Related

PDF Creation Automation

Jan 11, 2007

hello

is it possible to generate PDF files from reports programmatically ?

View 7 Replies View Related

Stored Function Automation

Aug 16, 2006

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 Related

DTS Packages (.dtsx) And Automation Through C#

Jul 23, 2007

I 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 Related

COM Automation Procs Failure

Oct 24, 2000

I 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

View 3 Replies View Related

DTS File To Table Automation

Apr 8, 2002

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

View 11 Replies View Related

Backup Method Automation Job

Sep 18, 2007

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

View 1 Replies View Related

Replicate A Database Automation

Jul 20, 2005

Hopeing this is something I can accomplish. I use MS-SQL for close to100 CMS web servers. Everytime I create a new website I have to createa new database and then use the export from SQL to SQL option and copyall data / SP's / etc... Just no security information. My question iscan I automate this? Can I create a script that will run all this?Thanks for any help, and thanks for reading.Chris AuerJoin Bytes!

View 2 Replies View Related

Automation Of Running Queries

Jul 20, 2005

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 Related

Reports Automation -Urgent

Mar 17, 2008

can any one send me a useful link which is easy to undersatnd Step by Step for Aumaotion of Reports Deployment?

View 4 Replies View Related

Automation Tool For Migrating From Dev To Prod

Jan 16, 2008

Hello,

I have recently become a release manager for SRSS in our company.
Since then I've been swamped with requests to migrate reports, permissions and subscription lists from development environment to production.

Each time I have to do it manually with a lot of clicks. It is a real pain...

So, may be... may be there is an automation tool out there to help me? Does anybody know?

This tool or s/w package should move a report file along with its permissions and subscription lists from one server to another.

Please advise!

thanks,

View 1 Replies View Related

DB Design :: Backup And Restore Automation

Jul 1, 2015

I need to perform a backup from prod and restore the backup onto Staging server.

This entire task needs to be automated using sqlcmd and staging server is configured with always-on.

What is the best possible way to automate the entire tasks?

View 4 Replies View Related

Client Installation &&amp; Configuration Automation

Jul 16, 2007

Hello!



I am looking to automate the rollout of the SQL Native client to our workstations. What I would like to do is use our login script to initiate an unattended install on the workstation. I will create the unattended file using the one that comes with SQL Server 2005 as a guide. This seems pretty straightforward.



More importantly, the other task I want to tackle is adding server aliases to the workstations. I am thinking of creating an alias for each of our applications and pushing the changes out by merging a registry key(maybe WMI? I dont know...). This way, should we need to move application databases to a different instance, we can just push an updated list of aliases to the workstations. The actual storage location remains transparent to the applications and users, (ie no configuration changes).



Has anyone done this or have any ideas? Are there any pros/cons that I have not thought of?



Thanks for any input!



Chris G

View 2 Replies View Related

Using SSIS As A Workflow Automation Tool

May 12, 2008

Hi all,
I am looking for any information about using SSIS as a workflow automation (job engine) tool. My company is looking into buying a 3rd party app to do our job scheduling and I think that SSIS could do all that we need. The only issue they found with SSIS is the lack of a GUI/dashboard to view all jobs at once. We need more flexibility than the job scheduler in SQL Server Agent will allow. I have heard that it is possible to build a C# GUI app that can serve as a job engine front page.

What we need is a way to view all jobs in the system and be able to start, stop, pause all jobs manually in a graphical interface. I know of a few companies that are doing this but I am unable to find anything on line about it. My bosses are ready to give SSIS a shot if I can prove that we can build such an interface. Does anyone have any first hand knowledge of such an application or have any tips on where I should look?

Thanks in advance!
-- Craig ***

View 4 Replies View Related

Scaling Model For Sales Force Automation

Jun 21, 2006

Read only transacted replication to about 60 clients. 100 or so transactions a day (8 hours), transaction (record) is probably about 8K/transaction. Reliable connection over 100base-T. Latency - 15 minutes would be great.



This doable? -- I couldn't find a timing/ sizing model any place.

View 1 Replies View Related

Data Warehousing :: Generating STATISTICS (Automation)

Jul 21, 2015

Is it possible to write a SP (Automate) to generate STATISTICS on any database and then use the output to create the stats on that database.

I ran the tuning adviser and it suggested indexes with lot of STATISTICS on the dev environment. This dev environment is replicated in several other environment with data size in these environment varying. I would  like to know if I can create a SP which generates STATISTICS information pertaining to specific database environment for the query in question for tuning. 

View 9 Replies View Related

Memory Configurations For SQL Server 2005 EE X64 With SQL Server 2000 EE X32 On Windows Server EE X64 Server

Apr 20, 2007

My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.

I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved