Monday, May 13, 2013

[T-SQL] facing problem in configration of database

[T-SQL] facing problem in configration of database

facing problem in configration of database

Posted: 13 May 2013 12:53 AM PDT

Hey!I have been using my local database for dev Purpose . Now i m trying to connect my application with to server database . for that purpose changed webconfig,appconfig and settings.cs . but it is still taking the older connection string , Please help :(

Extracting files from "binary" stored in TEXT datatype

Posted: 12 May 2013 06:25 AM PDT

I've been asked to look into extracting files (mostly jpg or pdf) from a database where the files are stored in the TEXT data type. There isn't a supporting column in the ole binaries table indicating the file type or extension. The vendor's database currently sits on a SQL 2008 R2 instance, but I do know that it was originally developed and ran on SQL 2000, if not earlier.Since it was originally developed prior to SQL 2005, and since I'm assuming the software vendor didn't subsequently rewrite their original ole extraction to varbinary bulk extraction, I'm left guessing what legacy method the original developers might have used to import and export embedded files to binary in their database. Their front-end is black box, so I can't see the source code they use.I do know the data resides on a TEXT data field, and the column value size is typically something like 5637935 characters. Selecting the field value in SSMS yields something like ÿØÿáN&Exif .Does any of this ring a bell for the group, and is there anyone who might be able to point me in the right direction that I might extract the files directly from the database?Thanks in advance for your consideration.

update MySQL from SQL Server

Posted: 12 May 2013 09:14 PM PDT

Hi All,I have managed to set up a linked server to a MySQL database and i want to update the MySQL database using a table within SQL Server. I tried the below code:[code="sql"]UPDATE OPENQUERY(DQ_TESTDATA,'select webmaster_id, dupe_master_id, dupe_flag from webmaster')SET dupe_master_id = r.MID,dupe_flag = 'M'FROM (Select * from dbo.DQResults) as r INNER JOINOPENQUERY(DQ_TESTDATA,'select webmaster_id from webmaster') AS wON r.MID = w.webmaster_id[/code]but it updated every record in the database (just over 2 million).it should only be updating around 662510.Can anyone see why this is happening and what the SQL update query should be?Thanks

Writing to local variable from CTE

Posted: 12 May 2013 06:12 AM PDT

Can I write to a local variable from inside a CTE?I am trying to get a comma delimited string of monthly names from inside a CTE to a varchar variable:This works fine:[code];WITH CTE1 AS( SELECT DATENAME(MM, CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName , 1 AS MonthPart, 1 AS CTEPart UNION ALL SELECT DATENAME(MM, CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' + '2013' AS datetime)) AS MonthName , MonthPart + 1 AS MonthPart, 2 AS CTEPart FROM CTE1 WHERE MonthPart <= 12)SELECT [MonthName] , MonthList = SUBSTRING( ( SELECT ( ', ' + MonthName ) FROM CTE1 FOR XML PATH('')), 3, 1000)FROM CTE1[/code]But this is creating the comma delimited string for each row.I wanted to create the string once and use it in the query final query. I tried this:[code];WITH CTE1 AS( SELECT DATENAME(MM, CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName , 1 AS MonthPart, 1 AS CTEPart UNION ALL SELECT DATENAME(MM, CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' + '2013' AS datetime)) AS MonthName , MonthPart + 1 AS MonthPart, 2 AS CTEPart FROM CTE1 WHERE MonthPart <= 12)SELECT [MonthName] , @MonthListFROM CTE1[/code]This works OK, so I know I can access the variable (but there is nothing in it yet).This doesn't work:[code];WITH CTE1 AS( SELECT DATENAME(MM, CAST('1' + '/1' + '/' + '2013' AS datetime)) AS MonthName , 1 AS MonthPart, 1 AS CTEPart UNION ALL SELECT DATENAME(MM, CAST(CAST(MonthPart AS varchar(2)) + '/1' + '/' + '2013' AS datetime)) AS MonthName , MonthPart + 1 AS MonthPart, 2 AS CTEPart FROM CTE1 WHERE MonthPart <= 12),CTE2 AS ( SELECT @MonthList = SUBSTRING( ( SELECT ( ', ' + MonthName ) FROM CTE1 FOR XML PATH('')), 3, 1000))SELECT [MonthName] , @MonthListFROM CTE1[/code]It won't let me assign the result to the @MonthList variable. But it would work if I took the "@" off, so I know the syntax is correct.Is there a way to get this to work to write to variable and then use it at the end?Thanks,Tom
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog