Copying Store Procedure From One Server To All Other Servers
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 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,'''','''''')
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.
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.
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.
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.
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.
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
I'm new to sql server stored procedures - I have 2 tables tasksTbl and staff_TasksTbl - tasksTbl primary key is an identity column - When inserting a row into tasksTbl I want to get the ID for the new inserted row using SCOPE_IDENTITY() and use it into a new insert command that will add a row into staff_TasksTbl
I need to know whats the problem in this procedure
CREATE PROCEDURE insertTask (@TaskName varchar(20), @TaskDescription varchar(20),@Rating int, @Status varchar(20),@StartAt varchar(20), @EntAt varchar(20),@EventID varchar(20), @StaffID varchar(100) ) AS declare @TaskID int SET NOCOUNT ON insert into TasksTbl(TaskName, TaskDescription, Rating, Status, StartAt, EntAt, EventID)values(@TaskName, @TaskDescription, @Rating, @Status, @StartAt, @EntAt, @EventID) select @TaskID = SCOPE_IDENTITY()
insert into staff_TasksTbl(TaskID,StaffID)values (@TaskID, @StaffID) GO
Dim Conn as new OleDbConnection("Provider=SQLOLEDB;Server=*;Database=WEST1;User ID=*;Password=*;Trusted_Connection=False") Sub Submit(Sender as Object, e as EventArgs) Dim objCmd as OleDbCommand = new OleDbCommand ("vendorPending 10031,85140109", Conn)
hi, i save some text into my database which looks like this.. <ol> <li><strong>adfadsf as </strong> <li><strong>asf as </strong> <li><strong>dfas </strong> <li><strong>df sad </strong> <li><strong>f as</strong> </li> </ol>
how m i gg to display them as numbered list in my report?
i'm using store procedure to retrieve them out into pdf format..
I am pulling my hair out having built my first application using WWF, I have moved the application off the development machine to a machine ready for production for the persistence store will now be on a remote server.
I cannot create a connection string for the persistence store that will allow me to attach to the mdb on the remote server.
the original connection string in development was:
Data Source=.SQLEXPRESS;AttachDbFilename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataWorkflowPersistenceStore.mdf';Integrated Security=True;Connect Timeout=30;User Instance=False
I cannot create an equivalent string that does not return a error like this one:
An attempt to attach an auto-named database for file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataWorkflowPersistenceStore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I did simply copy the WorkFlowPersistenceStore.mdf from the development machine to the server machine (completely independant environments) - It has worked in the past for other non wwf databases - Is there something particular about the wwf database that would have prevented this?
I have tried making the persistence store permanently attached (in the development environment) but this returns a different error that implies that a permanently attached database is not allowed - so I assume that the persistence service HAS to have a detached database - Am I correct?
I have tried a UNC naming that is the same on both the machine with the server and the client machine e.g. \MACHINEwwfWorkflowPersistenceStore.mdb - but that still returns an error - Is that not allowed? (or is that not allowed) - does the file path for an attached database have to be recognisable by the client machine, the server machine or both?
MUST the User Instance be True or False or does it not really matter
It is probably obvious that I am not confident in the whole area of connection strings and the effect of different parameters and the truth is I am not certain that I have not completely confused the SQL Server with the various attempts I have made.
Is there someone who could confirm that I am in the right area and could give me an example of what SHOULD work in a situation I have described so that I know that I am roughly right and to keep trying or I am barking up the wrong tree?
When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?
I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
Hi I am trying to run 4 - 5 store procedure in a strict order. I have created a SSIS package whereby I trying to execute store procedure in a orderly fashion. I have created constraints between them. Yet whenever I run the package - the store procedures run out of sequence. Is there something I can do to force the store procedure in orderly manner? Help will be much appreciated.
How can I create a table identical to another one, in a stored procedure? I need to copy the indexes and constraints too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
I can't script the table because this process must be automatic no manual.
Obviously, in a select statement, I can select * into #temptable. But can I do the equivalent from a stored procedure? I do not want to edit the stored procedure, just take the records it returns and put them into the temporary table. I do not know what records are returned by the stroped procedure until it is executed. Is there a way I can do this? Thanks J
How can I create a table identical to another one, in a stored procedure? I need to copy the indexes and constraints too. Example: I have a table "employee" and I want another table "employee2" with the same indexes and primary key and references.
I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.
I am executing xcopy with xp_cmdshell in a stored procedure and it is not copying all the files. There are about 600 files in the source directory and only around 190 are copied to the destination.
The command in the stored procedure is this N'xcopy c:source*.* c:dest'
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
Hi all, I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters??? Thanks. Kabir
I have asp.net web application which interface with SQL server, I want to run store procedure query of SQL using my asp.net application. How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server. for Instance Dim connections as string, Dim da as dataset, Dim adpt as dataadapter. etc if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application thank you maxmax