Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.
Hence, I wanted to write a script, Here the code below.
-- These two variables must contain a valid database name. DECLARE @vchSearch VarChar(15), @vchReplacement VarChar(15)
SET @vchSearch = 'Search' SET @vchReplacement = 'Replacement' /* -- Select the Kaplan Database Names in the Current Server */
DECLARE @tblDBNames TABLE (vchDBName VarChar(30)) INSERT INTO @tblDBNames SELECT Name FROM MASTER.DBO.SYSDATABASES WHERE Has_DBAccess(Name)=1 And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')
--SELECT * FROM @DBNames
IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames) BEGIN PRINT 'Not a Valid Search DB Name' GOTO Terminate END IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames) BEGIN PRINT 'Not a Valid Replacement DB Name' GOTO Terminate END
-- We have Valid DB Names, lets proceed... --USE @vchReplacement
SET @vchSearch = '%' + @vchSearch + '..%' SET @vchReplacement = '%' + @vchReplacement + '..%'
-- Get Names of Stored Procedures to be altered DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))
INSERT INTO @tblSProcNames SELECT DISTINCT so.Name FROM SYSOBJECTS so INNER JOIN SYSCOMMENTS sc ON sc.Id = so.Id WHERE so.XType='P' AND sc.Text LIKE @vchSearch ORDER BY so.name
-- Now, the table @tblSprocNames has the names of stored procedures to be updated. -- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to -- update the database reference -- Then, use cursors to loop through each stored proc and upate the reference
Now, I have got stuck how to extract the body of a stored procedure into a variable.
Please Help.... I dont want spend weeks of time in the future to do this work manually.
Ok, now that you understant the table layout... i need get a list of ProductId's with there respective inventory count
So what i need to do is select all the product id's from the "Individuale_Products" table, and the based apon there "SizeGroupCode", and "SizeCode"... I need to look up in the "Size_Groups" table... on the row that is equal to SizeGroupCode... the colum name that has the value of the "SizeCode"... So that then i can look up in the "Product_Group_Inventory" table the correct inventory column.
Please feel free to ASK any furthur Questions... or e-mail them to me at gregmoser@forwarddev.com
I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help. What I want to do is give a column in a table the same value as another column from the same table. For example: Table:Requests A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)
select * from A where ReceiptTS > '2014-09-30 00:00:00.000'
select * from A where ReceiptTS > (select ReferenceTS from Reference)
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True. If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null App1 ||| Linux |||UDB App1 ||| AIX |||UDB App1 ||| Linux |||Sql
We are experiencing problems inserting or updating image fields fromone table to another in SQL Server.When we do this what ever size of file we insert is doubled in sizewhen it is inserted into the destination table.This happens in insert and update queries, and if we use DTS.Any help would be greatly appreciated
Hi all, I have one table with a column of type 'image'. There are manytypes of files saved in that column (i.e. .Doc,Xls,Pdf,jpg,gif etc.). What I want is, read that files from database and save it in temp folder on d drive of server. Can anyone help me in my problem? Thanx in advance
I need to update a large table, about 55 million rows, without filling the transaction log, in the shortest time as possible. The goal is to alter the table and change the data type for Text column from VARCHAR(7900) to NVARCHAR(MAX).
Since I cannot do it with an ALTER TABLE statement (it would fill up the transaction log) I'm thinking to:
- rename column Text in Text_OLD - add Text column of type NVARCHAR(MAX) - copy values in batches from Text_OLD to Text
The table is defined like:
create table DATATEXT( rID INTEGER NOT NULL, sID INTEGER NOT NULL, pID INTEGER NOT NULL, cID INTEGER NOT NULL, err TINYINT NOT NULL,
[Code] ....
I've thought about a stored procedure doing this but how to copy values in batch from Text_OLD to Text.
The code I would start with (doing just this part) is the following, but maybe there are more efficient ways to do it, or at least there's a better way to select @startSeq in the WHILE loop (avoiding to select a bunch of 100000 sequences and later selecting the max).
declare @startSeq timestamp declare @lastSeq timestamp select @lastSeq = MAX(sequence) from [DATATEXT] where [Text] is null select @startSeq = MIN(Sequence) FROM [DATATEXT] where [Text]is null BEGIN TRANSACTION T1 WHILE @startSeq < @lastSeq
I have some code (C#) that runs an SQL update query that sets thevalue of a column to what the user passes. So, this causes an errorwhen anything the user passes in has a ' character in it. I'm surethere's other characters that'll break it too. So, I was wondering,how do I get around this? Is there some commonly accepted regexpattern that will make the value safe to run in an SQL query? How canI take care of any values that need to be escaped?I'm not using any fancy ado.net objects:string sql= [whatever the user passes in]SqlConnection connection = newSqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());connection.Open();SqlCommand command = connection.CreateCommand();command.CommandType = CommandType.Text;command.CommandText = sql;try{int result = command.ExecuteNonQuery();if (result != 1){Response.StatusCode = 500;Response.Write("The file has been uploaded, but wecould not update the DB");Response.End();}}catch (InvalidOperationException){Response.Clear();Response.Write("error");Response.StatusCode = 500;Response.End();}connection.Close();
I'm a bit of a newby to creating update queries so I was hoping those moreexperienced could help with what should be a simple query. I do know tocreate backups and test on a test database first.I need to copy an items' cost from location 'CA' to the cost in location'OH' when the cost in 'CA' is not zeroTable name is ITEMSLocation column is LOCCost column is COSTHere's the Select statement from Enterprise Manager's Open Table, Query toolSELECT loc AS Expr1, cost AS Expr2FROM ITEMSWHERE (loc = 'CA') AND (cost <> 0)I just can't seem to get this into a working Update query.Thanks in advance...
SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact
Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information and description is non editable.Now the new requirement has come user can update the description information and save information in a new column say 'Description1'. that means new column needs to be added in the db and also necessary changes needs to be done at the application side
For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact
Now when user views the contact table it should display description info by default from 'description' table.If he edits he should see edited data from 'description1' table.the logic should if updated data is there display that data from 'description1' table other wise display from 'description' table
I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!
Example would be:
Player Minutes Adam 287
Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
I am trying to learn XQuery and Xpath in SQL Server. I created a sample file and uploaded it to a Table with 2 columns ID, XMLDoc. The below code is within the document in the XMLDoc column so it is the only record in the column.
I am trying to query the file so it will show all the results in a table like a normal select statement would. How would you construct the select statement to select all the information like a select * ? How would you select one field like all suppliers? I would like to select the supplier, requestor for each item.
const_cd const_name count inner query 122 a 205 4 123 b 205 4 124 c 235 2 125 d 191 3 126 e 226 1 127 f 159 6 128 g 165 11 129 h 175 9 130 i 225 2 131 j 213
Hi All I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion? I use asp.net + msde
