Sunday, March 3, 2013

[SQL Server 2008 issues] error in bulk copy

[SQL Server 2008 issues] error in bulk copy

error in bulk copy

Posted: 19 Feb 2013 08:27 PM PST

HiI want to test some changes in the structure of a table and see the plan,I exported the data in a text file using bulk copy,the table is about 400G and 120000000 records.bcp DB.dbo.[Table] out "G:\sqlscript.txt" -c -T after that I create the table in another test server ,using the primary structure of the table from Main server and the same collationI imported the data in new table with this commandbcp Test.dbo.[Table2] in "G:\sqlscript.txt" -c -T -e G:\error.txtbut from 120000000 records ,it inserted just 1000000 records and in command prompt I have :sqlstate =22005 , nativeerror=0 error = [microsoft][sql server native client 10.0]invalid character value for cast specificationand in error.txt file :#@ Row 951577, Column 8: Invalid character value for cast specification @#please help me

SSRS. Parameters mdx script doesn't work with multi-value

Posted: 02 Mar 2013 06:03 PM PST

Hi ALL,I need your your help!!I want to create a report with multi-value parameters with SSRS.So, I wrote this mdx script: WITH -- Geography metadataMEMBER [Measures].[Geographie]AS StrToValue ( @SelectionGeographie + ".Hierarchy.Currentmember.Uniquename" )MEMBER [Measures].[Geographie_Label]AS StrToValue( @SelectionGeographie + ".Hierarchy.CurrentMember.Member_Caption" )-- Activity metadataMEMBER [Measures].[Activite]AS StrToValue( @SelectionActivite + ".Hierarchy.Currentmember.Uniquename" )MEMBER [Measures].[Activite_Label]AS StrToValue( @SelectionActivite + ".Hierarchy.CurrentMember.Member_Caption" )-- Date metadataMEMBER [Measures].[Temps]AS StrToValue( @Annee + ".Hierarchy.Currentmember.Uniquename" )MEMBER [Measures].[Temps_Label]AS StrToValue( @Annee + ".Hierarchy.CurrentMember.Member_Caption" )-- Perimetre metadataMEMBER [Measures].[Perimetre]AS StrToValue( @Perimetre + ".Hierarchy.Currentmember.Uniquename" )MEMBER [Measures].[Perimetre_Label]AS StrToValue( @Perimetre + ".Hierarchy.CurrentMember.Member_Caption" )SELECT NON EMPTY {-- display the parameters attributes on columns[Measures].[Geographie],[Measures].[Geographie_Label],[Measures].[Activite],[Measures].[Activite_Label],[Measures].[Temps],[Measures].[Temps_Label],[Measures].[Perimetre],[Measures].[Perimetre_Label],[Measures].[11 VA]} ON COLUMNS, ( STRTOSET ( "{" + @SelectionGeographie + "}") ,STRTOSET ("{" + @SelectionActivite + "}" ))ON ROWSFROM [MyCube]WHERE STRTOTUPLE ( "(" +@Annee + "," + @Perimetre + ")" )But It works with one value in parameter and not with muti-value parameters.I have null result in my metadata members when I have multi value.Any idea ?Thank

even and odd records

Posted: 01 Mar 2013 11:03 PM PST

The sql code to find odd number of records is:[code]select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);[/code]But if I do the foll code for odd nos:[code]select * from student where mod(rownum,2)=1;[/code]Then it displays only the first row!Why is that so because I think the logic is true for odd numbers.Can someone please give solution to this?

unexplained deadlock

Posted: 02 Mar 2013 05:30 PM PST

Hi,I'm using SQL Server 2008.i have 2 different statements that lock each other, can you please explain why they are locking each other?maybe i miss nolock in the inner join?[quote] <deadlock victim="process674e508"> <process-list> <process id="process674e508" taskpriority="0" logused="0" waitresource="KEY: 25:72057594045071360 (da0000d37950)" waittime="2024" ownerId="461564310" transactionname="UPDATE" lasttranstarted="2013-03-01T10:27:00.740" XDES="0x81cff970" lockMode="U" schedulerid="32" kpid="12284" status="suspended" spid="133" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-01T10:27:00.727" lastbatchcompleted="2013-03-01T10:27:00.727" clientapp=".Net SqlClient Data Provider" hostname="APP77" hostpid="2140" loginname="APPUser" isolationlevel="read uncommitted (1)" xactid="461564310" currentdb="25" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="APP77.dbo.spTableASelectRows" line="54" stmtstart="4768" stmtend="6220" sqlhandle="0x030019003f43800884f8c40073a100000100000000000000">WITH TableA_CTE AS ( SELECT TOP (@TOP_HANDLED_ROWS) TableAID FROM dbo.TableA WITH (NOLOCK) WHERE DATEDIFF(MINUTE, WaitingRoomCreationTime, @curUtcDate) > @wrExpirationMinutes AND TableAStatusTypeID IN (1,2,3,4) -- CreatePending/Creating/Created/CreatedReportFailed ) UPDATE TOP (@TOP_HANDLED_ROWS) EC WITH (ROWLOCK) SET TableAStatusTypeID = 9, LastUpdateTime = @curUtcDate, FROM TableA_CTE CTE INNER JOIN dbo.TableA EC ON EC.TableAID = CTE.TableAID; </frame> </executionStack> <inputbuf>Proc [Database Id = 25 Object Id = 142623551] </inputbuf> </process> <process id="process4931708" taskpriority="0" logused="0" waitresource="KEY: 25:72057594045071360 (1e00262b2a23)" waittime="2029" ownerId="461564333" transactionname="DELETE" lasttranstarted="2013-03-01T10:27:00.770" XDES="0xce53f970" lockMode="U" schedulerid="5" kpid="7044" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-01T10:27:00.710" lastbatchcompleted="2013-03-01T10:27:00.710" clientapp=".Net SqlClient Data Provider" hostname="APP64" hostpid="2080" loginname="APPUser" isolationlevel="read uncommitted (1)" xactid="461564333" currentdb="25" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="APP77.dbo.spTableASelectRows" line="129" stmtstart="11560" stmtend="12464" sqlhandle="0x030019003f43800884f8c40073a100000100000000000000">WITH TableA_CTE AS ( SELECT TOP (@TOP_HANDLED_ROWS) TableAID FROM dbo.TableA WITH(NOLOCK) WHERE TableAStatusTypeID IN (11,12) ORDER BY LastUpdateTime ASC ) DELETE TOP (@TOP_HANDLED_ROWS) EC WITH (ROWLOCK) FROM TableA_CTE CTE INNER JOIN dbo.TableA EC ON EC.TableAID = CTE.TableAID; --------------------------------------------------------- </frame> </executionStack> <inputbuf>Proc [Database Id = 25 Object Id = 142623551] </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594045071360" dbid="25" objectname="APP77.dbo.TableA" indexname="PK_TableA" id="lockc5768e00" mode="U" associatedObjectId="72057594045071360"> <owner-list> <owner id="process4931708" mode="U"/> </owner-list> <waiter-list> <waiter id="process674e508" mode="U" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594045071360" dbid="25" objectname="APP77.dbo.TableA" indexname="PK_TableA" id="lock818fee00" mode="U" associatedObjectId="72057594045071360"> <owner-list> <owner id="process674e508" mode="U"/> </owner-list> <waiter-list> <waiter id="process4931708" mode="U" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock>[/quote]I see i can't specify NOLOCK in the inner join .. can you advise?

How to insert in batches WITHOUT using @@ROWCOUNT

Posted: 02 Mar 2013 06:04 AM PST

I have the specific insert query below, and it works fine if I have less than 20 records or so, but if I have millions of records to loop through,it is not going to work. I have the batches below in hundreds, but i can be 1000, ,2000, etc.. Any idea how I can write this better WITHOUT using @@rowcount, and better performance would be very much appreciated. DECLARE @min INT; DECLARE @max INT; DECLARE @min2 INT; DECLARE @max2 INT; SET @min = 1; SET @max = 5; WHILE @min <= @max IF @min = 1 THEN SET @min2 = 1, @max2 = 100; ELSEIF @min = 2 THEN SET @min2 = 101, @max2 = 200; ELSEIF @min = 3 THEN SET @min2 = 201, @max2 = 300; ELSEIF @min = 4 THEN SET @min2 = 301, @max2 = 400; ELSEIF @min = 5 THEN SET @min2 = 401, @max2 = 500; END IF; INSERT INTO table2 SELECT colum1, column2 FROM table1 WHERE id BETWEEN @min2 AND @max2; SET @min = @min+1; END WHILE;

ISO 8601 - The correct way to represent a date

Posted: 27 Feb 2013 03:32 PM PST

[url]http://xkcd.com/1179/[/url][img]http://imgs.xkcd.com/comics/iso_8601.png[/img] 

User tables in the system DBs. Are they affected by SPs and CUs

Posted: 26 Feb 2013 05:33 AM PST

Hello all, I have a client with several instances of SQL Server from 2000, 2005, & 2008, at various builds. They unfortunately have several cases where they have created objects in the system DBs. I am only certain of the Master DB being used but it wouldn't surprise me if the other system DBs have user tables as well.Will updates, SP or CU's leave user objects alone in the system DBs?Thanks.Joe

Problems indexing PDF documents with Foxit iFilter on SQL Server 2008

Posted: 21 Feb 2013 06:50 PM PST

I have a SQL Server database and want to index PDF documents in the database. I use the Foxit iFilter. The iFilter is already installed but after indexing the file table the log consists of a huge amount of error messages like[i]"Warning: No appropriate filter was found during full-text index population for table or indexed view ...."[/i]I also think that the iFilter was installed correctly. When I execute[font="Comic Sans MS"] select * from sys.fulltext_document_types order by document_type[/font]there is one entry for PDF as document_type and the correct path to PDFFilt.dllWhat could be the reason, that the filter is not found during the index process however?

Any better way to get this type of data

Posted: 21 Feb 2013 01:26 PM PST

Ok this script works for me but wanted the experts opinion on a better/faster way or more efficient way..Scenario:Have data with products and settings.ID is sequence order.want to concatenate the individual settings into a string for the product.i.e.Prod SettingA A1A B1A C2I want:Prod SettingA A1-B1-C2The script below takes about a minute to run on my laptop...returns about 32K records...Help is much appreciated.. :-D[code="sql"]use tempdbgoif object_id('#Table') is nullcreate table #Table(ID int, Val varchar(50),Prod varchar(50));truncate table #Table--Just Sample Data==============================insert into #Tablevalues(1, 'A1', 'EZ'),(2,'B2','EZ'),(3,'C2','EZ'),(4,'D2','EZ'),(1, 'A1','EX'),(2,'B2','EX'),(3,'C2','EX'),(4,'D2','EX'),(5,'E3','EX'),(1, 'A1','ED'),(2,'B2','ED'),(3,'C2','ED'),(4,'D2','ED'),(5,'E3','ED'),(6,'F3','ED');--This is the actual work scriptwith t(ID, Val, Prod, Pos) as (select a.ID, a.Val, a.Prod, 1 from #Table a where id = 1 union all select b.ID, b.Val, cast(b.Prod + cast(Pos as varchar) as varchar(50)), Pos + 1 from #Table b , t c where b.Prod = left(c.Prod,2)) insert into #Table(ID, Val, Prod)select top 192000 ID, Val, Prod from twhere len(Prod) > 2option (maxrecursion 32000)--End of populating Sample Data--Data in sample tableselect * from #Table;with E1(ID,Prod,Val,MaxID, Pos) as (select ID, Prod, Val, 0, 1 from #Table where ID = 1 union all select t.ID, t.Prod, cast(e1.Val + '-' + t.Val as varchar(50)),max(t.ID)over(partition by t.Prod), Pos + 1 from #Table t inner join E1 on t.ID > e1.ID and t.prod = e1.prod )--Desired outputselect * from e1where MaxID = PosDrop Table #Table[/code]

need help on whitespace

Posted: 01 Mar 2013 09:02 PM PST

I want to remove leading whitespace from a string only if the string has one leading whitespace , if two leading whitespaces then do not ltrim. Can this be done in tsql I'm using 2008

How can i specify the RTL direction for a column in SQL Server?

Posted: 02 Mar 2013 04:28 AM PST

Is there a way in[b] SQL Server [/b], so that i can specify the [b]rtl[/b] direction for a column ?Currently i have a query like this : [quote] SELECT IDSource, IDAviculture, NumberOfReserve, TotalMoney , Name + '(' + Description + ')' AS Descrition, Date FROM dbo.tblReserve WHERE (Sell = 1)[/quote]The data are in Farsi and thus right to left.The problem is unless it is not [b]rtl[/b], it doesn't show the information in correct form.For example the following text needs to be right to left in order for سلام to get inside parentheses [quote]> علی (سلام)[/quote][quote] IDSource IDAviculture NumberOfReserve TotalMoney Name + '(' + Description + ')' AS Descrition Date 12 5 1500 3000 علی (توضیحات) 13 4 700 2500 مینا(تست ) [/quote]I Expected to be like [quote] IDSource IDAviculture NumberOfReserve TotalMoney Name + '(' + Description + ')' AS Descrition Date 12 5 1500 3000 علی (توضیحات) س 13 4 700 2500 مینا (تست ) س [/quote]Please note that the editor here doesn't support `RTL` either , so basically it wont show the text correctly , so i had to put an extra letter (س) so that it shows it correctly .Basically what i wrote above can be translated into English as:[quote] Name + '(' + Description + ')' Ali (Description) Mina (Test)[/quote]in English because of being [b]LTR[/b] it is fine but when it comes to the [b]rtl[/b]content it gets ugly.How can i get this right?

xml query datetime datatype problem while inserting to xml

Posted: 01 Mar 2013 06:57 PM PST

I have a query likeUPDATE aSET oldRemarks1.modify('insert <Value_Py>{sql:column("b.collection_date")}</Value_Py> after (/Root/Row[3]/Item)[1]') from b where a.[Date]='31-May-2004' and a.sl_no=b.regkey_slnoHere collection_date is of datetime datatype.So while it is inserted to xml it will be like 2012-03-05T00:00:00.000 in the xml tag.But i want to insert it as just "2012-03-05" in the xml tag. So how can i use cast or any other method here so that it will insert properly in xml.

Read on "?.bak" failed: 38(failed to retrieve text for this error. Reason: 15105)

Posted: 01 Mar 2013 07:28 PM PST

Trying to restore an SQL 2008 R2 backup, to SQL 2008 R2 - different machine:[code="sql"]RESTORE DATABASE [mydb] FROM DISK = N'd:\test\test.bak' WITH FILE = 1, MOVE N'modeldev' TO N'd:\test\test.mdf', MOVE N'modellog' TO N'd:\test\test.ldf', NOUNLOAD, REPLACE, STATS = 10 [/code][b]Output & Error[/b]10 percent processed.20 percent processed.30 percent processed.40 percent processed.50 percent processed.60 percent processed.70 percent processed.80 percent processed.Msg 3203, Level 16, State 1, Line 2Read on "d:\test\test.bak" failed: 38(failed to retrieve text for this error. Reason: 15105)Msg 3013, Level 16, State 1, Line 2RESTORE DATABASE is terminating abnormally.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog