Tuesday, July 30, 2013

[SQL Server 2008 issues] SSIS transferring unprintable characters between systems

[SQL Server 2008 issues] SSIS transferring unprintable characters between systems

SSIS transferring unprintable characters between systems

Posted: 29 Jul 2013 07:46 AM PDT

We are using SSIS, Transfer SQL Server Objects Task Editor to move data from one SQL Server system to another.A few of the records on the source system have unprintable characters in them. Yes, it would be a goal to clean that up, but since that involves application changes, our current objective is to have the data transferred so it matches exactly between the systems.It appears on these records that the unprintable characters are not transferring to the target system. Then when something like Data Dude (or whatever they call it now) is used to compare the systems, these records appear as mismatches.Is there a way using SSIS, to get the data to copy from the source to the target exactly as the data exists?TIA,Doug

Analytical function for my Q

Posted: 29 Jul 2013 09:13 AM PDT

Hi,Is there any analytical function in sql server for achieve results like below:I did this with 2 temp tables, but might be something already there to look it nicer.I also need this group by DateIn, and display % for each delDays categoryThanks allM[code]create table #log (shipID int, datein datetime, delDays int)INSERT INTO #LOG (shipid , datein, delDays) select 1001, '2012-01-06', 9 union allselect 1002, '2012-01-06', 11 union allselect 1002 , '2012-01-06', 11 union allselect 1003 , '2012-01-06', 11 union allselect 1004 , '2012-01-06', 11 union allselect 1005 , '2012-01-06', 11 union allselect 1006 , '2012-01-06', 11 union allselect 1007 , '2012-01-06', 11 union allselect 1008 , '2012-01-06', 11 union allselect 1009 , '2012-01-06', 11 union allselect 1010 , '2012-01-06', 22 union allselect 2001 , '2012-02-06', 15 union allselect 2002 , '2012-02-06', 16 union allselect 2003 , '2012-02-06', 33 union allselect 2004 , '2012-02-06', 22 -- Need this result result:DateIn | <20days | <30days | >30days |----------|----------|-----------------------2012-01-06| 90% | 10% | | 2012-02-06| 50% | 25% | 25% | [/code]

Shrink database file with EMPTYFILE option

Posted: 29 Jul 2013 04:13 PM PDT

I want to know that can we shrink database file with EMPTYFILE option while currently accepting data or we just need to do some other thing to shrink database file with EMPTYFILE option..

indentifing the spid

Posted: 28 Jul 2013 10:11 PM PDT

i would like to find the spid of the long running job with a query ? can any one send me the query

Handy and Simple tool for SSMS 2008 R2???

Posted: 30 Sep 2012 10:04 PM PDT

Hello,I was wondering why SSMS does not have the standard builtIn plugin Quick Math (for example: sum, avg, max, min). I have been searching on google but couldn't found any tool which will add this possibility to SSMS. Do you guys know any tool or tips how to added such a extension?Br, EidjazSee my attach for example.

New Quick Search Addin for SQL Server Management Studio 2008/2012

Posted: 29 Jul 2013 03:05 PM PDT

I have built a free Addin for SQL Server Management Studio 2008/2012 [url=http://sql-hunting-dog.com/]. It is a an instant search on tables/procedures etc. with quick access to most usable actions (select from table, design table, see stored procedure body etc..).It is completely free product and very easy to use you can see it here [url=http://sql-hunting-dog.com/how/][img]http://sql-hunting-dog.com/img/how/image005.png[/img]As a .Net and SQL Developer I myself use it everyday but I want to share it with the world so it will help other Developers in their every day tasks.Just wanted to share with SQL Community.Again, any feedback will be appreciated.Regards, Alex

Question about user and login

Posted: 28 Jul 2013 08:11 PM PDT

HelloI created new login and new user to exists login. Next step is log to database with created username database and login password??Regards

Incorrect length being returned for FLOAT datatype field

Posted: 29 Jul 2013 01:16 PM PDT

Hello, is somebody able to point out where I am going wrong?We have a table that is of float datatype which holds the current balance.Initially the data is like this: 45.56 or 3.56But something is updating the balance making it end up like 45.56000000000087 or 3.56000000000003.I am trying to determine if this is an Application error or a FLOAT error / misuse.So to check troubleshoot I am trying to identify the long values but as you can see by the example below the LENGTH is not what I would expect.DROP TABLE #testCREATE TABLE #test ( float1 FLOAT ,float2 FLOAT ,float3 FLOAT ,float4 FLOAT ,float5 FLOAT )INSERT INTO #test ( float1 ,float2 ,float3 ,float4 ,float5 )VALUES ( 6.7893001777 ,6.78 ,6.7893001 ,6.78931777 ,6.7893178 ) SELECT float1,LEN(float1),float2,LEN(float2),float3,LEN(float3),float4,LEN(float4),float1,LEN(float5) FROM #test

A way to shred an XML Deadlock report for easier reading...

Posted: 26 Jun 2013 02:46 AM PDT

I'm not taking credit for this, I just took some code WayneS posted a couple years back here: [url=http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx]http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx[/url] and updated it / modified it some to work with the XML that SQL2008 spits out when you query the Extended Events...[code="sql"]declare @deadlock xmlset @deadlock = 'put your deadlock graph here'select [PagelockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'), [KeyLockObject] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@objectname', 'varchar(200)'), [KeyLockIndex] = @deadlock.value('/deadlock[1]/resource-list[1]/keylock[1]/@indexname', 'varchar(200)'), [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock[1]/victim-list[1]/victimProcess[1]/@id', 'varchar(50)') then 1 else 0 end, [ProcessID] = Deadlock.Process.value('@id', 'varchar(50)'), [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(5)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), --[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'), [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [BatchTime] = Deadlock.Process.value('@lastbatchstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') from @deadlock.nodes('/deadlock/process-list/process') as Deadlock(Process)[/code]Just had to change some little things, SQL no longer wraps the XML in "<deadlock-list>" blocks, and the victim information is now a couple more layers deep.And yes, I'm working with a dev right now trying to troubleshoot deadlocks in an application...Thanks WayneS!

Index Fill Factor

Posted: 28 Jul 2013 11:25 PM PDT

We have a database is isnt performing as it should. A while back I had a database which grew quite rapidly and I was advised to change the index fill factor from the default 0 to 80. For mainly static databases 0 is fine but should be altered for databases with constant deletes and inserts.Is this advise correct? Will it help with performance? And are there any adverse effects which could occur as a result of changing it to 80?

Email to Mutiple users with different subject i.e Subject variable

Posted: 28 Jul 2013 10:36 PM PDT

Hi,I stuck in a weekly report which i have to send to every user for their access.i want to send it through Database Mail.I have a table like thisREQID firstname lastname accessenddate Mail ID1234 Yunus Parvez 8-Aug-13 best_yunus@yahoo.com12345 ABC xyz 9-Aug-13 best_yunus@live.comNow i want to send mail to these user with edited subject line and body.for ex. subject line: RequestID-ReqID accessenddate namebody of mail:Hi <first name>,We have received a notification informing your access is due to expire on <accessenddate>Kindly use this as the subject line for renewal access request: Renew: <Old access - Request#ReqID>Please help to get rid off this report in quick way.Response appreciated.Regards,Yunus

SSIS Import - Delimited File with Fields &gt; 8k Characters

Posted: 29 Jul 2013 05:46 AM PDT

Hello all,I'm attempting to import a text delimited file with a few fields that have greater than 8k characters. I'm running into this error on import, "Text was truncated or one or more characters had no match in the target code page.". So, how do I get around this? I've attempted to import those columns as text, varchar(8000), and varchar (max) with no luck so far. I'm hoping that I don't have to split those fields into multiple rows if they are larger than 8k characters.

Dynamic SQL Unpivoting and re - Pivoting seems overly complicated...

Posted: 29 Jul 2013 02:42 AM PDT

Hi All,It is fairly common where we work to have a questionnaire output in the form of two tables, one that contains a set of questions and another that contains a matching set of answers, along with a UserId and Timestamp. In order to display these nicely, it is useful for us to display these two as one table, where the questions become the column names and the answers become the values, all in one row per questionnaire. I have written some code which does this fine (see the example below, where the output tables correspond to #Questions and #Answers), but it seems to me that this is a lot of code, a lot of dynamic SQL and generally pretty involved looking.Is there a simpler way to do the same thing? Do you have any ideas of things that MIGHT make it all simpler and (maybe) quicker?Cheers,Mark[code="sql"]CREATE TABLE #Questions( s01q01 VARCHAR(50) ,s01q02 VARCHAR(50) ,s02q01 VARCHAR(50) ,s02q02 VARCHAR(50) ,s02q03 VARCHAR(50))CREATE TABLE #Answers( CompletingUser VARCHAR(50) ,CompletingTime VARCHAR(50) ,s01q01 VARCHAR(50) ,s01q02 VARCHAR(50) ,s02q01 VARCHAR(50) ,s02q02 VARCHAR(50) ,s02q03 VARCHAR(50))INSERT #QuestionsSELECT 'Question One','Question Two','Question One B','Question Two B','Question Three B'INSERT #AnswersSELECT 'Darth Vader',GETDATE(),'definitiely 1','There are Two','I started a new section','£22.00',NULL----------------------------------------------------------------------------------------------------------------------------------------------- Enter an empty string so that this is no longer NULL, which means we can easily add to itDECLARE @ColumnList VARCHAR(MAX) = ''DECLARE @SQL VARCHAR(MAX)CREATE TABLE #AnswerList (Label VARCHAR(10), Wording VARCHAR(MAX))CREATE TABLE #QuestionList (Label VARCHAR(10), Wording VARCHAR(MAX))-- The columnlist here is our input list of columns to Pivot aboutSELECT @ColumnList = @ColumnList + ',' + COLUMN_NAMEFROM tempdb.INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME LIKE '#Questions%'-- lose the extaneous comma from our concatenated list of columnsSELECT @ColumnList = SUBSTRING(@ColumnList,2,LEN(@ColumnList))-- make our dynamic SQL with the column list in it and any changes will be incorporated into our querySET @SQL =-- First make the list of answers, with the labels in one column and the wording in the other'INSERT #AnswerListSELECT Label,Answer FROM (SELECT CompletingUser, CompletingTime,' + @ColumnList + ' FROM #Answers) AS PVT UNPIVOT (Answer FOR Label IN (' + @ColumnList +')) AS UnPvt' +-- Now make a similar list of questions, with the same values in the labels row as the answers (this is always true)'INSERT #QuestionListSELECT Label,Question FROM (SELECT ' + @ColumnList + ' FROM #Questions) AS PVT UNPIVOT (Question FOR Label IN (' + @ColumnList +')) AS UnPvt'EXEC (@SQL)-- Make a new column list - this is now the output column list, taken as the list of the label of al the questionsSET @ColumnList = ''-- Put it all nicely in square bracketsSELECT @ColumnList = @ColumnList + ',[' + Wording + ']'FROM #QuestionList-- Lose the comma off the frontSET @ColumnList = SUBSTRING(@ColumnList,2,LEN(@ColumnList))-- Now get the one-off info from the Answers and all the dynamic questions and answersSET @SQL = 'SELECT (SELECT CompletingUser FROM #Answers) AS CompletingUser ,(SELECT CompletingTime FROM #Answers) AS CompletingTime ,*FROM (SELECT Q.Wording AS Question, A.Wording AS Answer FROM #QuestionList AS Q' + -- Left outer join as some questions may be unanswered, so having NULL values' LEFT OUTER JOIN #AnswerList AS A ON Q.Label = A.Label) AS QA ' +-- it doesn't know that we have only one row, so we have to put a MAX in, but it makes no different because we DO have only one row.'PIVOT (MAX([Answer]) FOR Question IN ('+ @ColumnList +')) as Pvt'EXEC (@SQL)DROP TABLE #AnswersDROP TABLE #QuestionsDROP TABLE #AnswerListDROP TABLE #QuestionList[/code]All suggestions most welcome!

Alerts for Database Mirroring

Posted: 15 Jul 2013 01:30 AM PDT

HiI'm trying to get SQL Server to alert me via email when the Primary instance loses connection with Mirrored instance (i.e. mirroring becomes suspended/inactive)So far, the methods I've found require the Service Broker to be enabled on the Mirrored DB, unfortunately, this cannot be enabled in this scenario.Is there any other simple way that I can simply set MSSQL to notify me (via email) when Mirroring isn't active?Thanks.

Update table values if corresponding values change in another table

Posted: 23 Jul 2013 10:03 PM PDT

Hi,I am very new to SQL and really dont know how to phrase my question. There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?The values in the table will be changed through a webpage.Any ideas would be appreciated.Thanks.

How to update data in a table having primary key - foreign key link with another table

Posted: 29 Jul 2013 12:27 AM PDT

Hello,Could someone please tell how can data be updated (refreshed) in one table if the data is refreshed on another table and both tables are linked through a primary key - foreign key ?I just need to know what logic I need to apply to update the values in a table if the same value has been refreshed in another table. Thanks.

How can I avoid Eager Spool on my Update Statement which consumes 56% of total cost

Posted: 28 Jul 2013 11:58 PM PDT

Hi Guys,I found my Update Query is getting the usage of Eager Spool, and the temp table (implicit) cost about 56% percentage of the Update Statement. Kindly Advice how to over come this scenario.Here is the Query.--table structure:create table mytable(tableid nchar(25), element1 int, element2 smallint, col4 nvarchar(255));Merge [dbo].[mytable] as tr --having a 10 million records using #mytable as tmp --carries the data to be updated and about 2500 rows.on(tr.tableid = tmp.tableid and tr.element1 =tmp.element1 and tr.element2 = tmp.element2) When Matched Then update set tr.tableid = tmp.tableid, tr.element1 = tmp.element1, tr.element2 = tmp.element2 , tr.col4 = tmp.col4, tr.modifiedby = @userid,tr.modifieddate = getdate() when not matched then insert (tableid,element1,element2,col4,createdby,createddate) values (tmp.tableid,tmp.element1,tmp.element2,tmp.col4,@userid,getdate()); the attached showplanxml_mytable.txt file could help you in better way.Looking forward to hear from you soon Guys.. Thanks,Prabhu

Using like over charindex to search for pattern

Posted: 28 Jul 2013 09:19 PM PDT

We where using charindex to search for a string as shown below[code="sql"]SELECT *FROM tablenameWHERE ( Charindex('47a%$.abc',CAST(columnname AS VARCHAR(MAX)))>0 ) [/code]I am planning to replace charindex with LIKE as i require to search only whether the pattern exists not the position.So i want to know is these any disadvantage in using LIKE over charindex for my case.I am using charindex in several cases. So i want it to work for all sql datatypes ,all characters including special characters(including % (as like already uses %pattern%).So please tell me is there any disadvantage in using like over charindex

Email Stop - SQL 2k8?

Posted: 28 Jul 2013 08:07 PM PDT

Hi,Email alert stopped automatically due to as below error messages, what could be reason?The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-07-27T11:42:13). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 4.7.1 Client host rejected: cannot find your hostname
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog