Database

Mycroft Holmes12-Jul-10 14:45

12-Jul-10 14:45

Dev servers, due to their close relationship with, well developers, tend to be better behaved than production servers and they quickly learn the consequences of disruptive behavior (the 3 fingered salute is not unknown to a dev server). Surely you have been in the position where a user cannot get something working until you go stand beside them, then the dammed thing behaves itself. The same applies to servers.

Also SQL likes to throw in the odd bizarre behavior occasionally, one of the favourites is to take 10-100 times as long processing a query from the application than it does in SSMS.

To date I have not found a solution to this odd behaviour, however I have found it beneficial to NOT discuss these aberrations with my manager! Good Luck...

Never underestimate the power of human stupidity
RAH

SomeGuyThatIsMe13-Jul-10 2:59

SomeGuyThatIsMe

13-Jul-10 2:59

I did, finally, notice 2 differences in the configuration of the databases. The compatibility mode and Page_Verify settings were different. Dev was set to 80 (sql server 2k) and torn page, prod was set to 100(server 2k8) and checksum. I was under the impression that new fancy features wouldnt work on a database unless the compat mode was set to the correct version. I checked the queries(update and select) in SSMS and compared execution plans and they looked identical except for the time taken.

I forgot to mention that we use Bulk Insert to insert the million rows, dont know if that would make a difference or not.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Corporal Agarn13-Jul-10 5:12

Corporal Agarn

13-Jul-10 5:12

SQL 2008 does some strange things on bulk insert. I have had to rewrite several processes that worked just fine on 2K that do not work on 2008. Import wizard in SSMS does not import things that Enterprise Manager did fine.

bcp and int>ASCII code conversion [modified]

SomeGuyThatIsMe13-Jul-10 5:17

SomeGuyThatIsMe

13-Jul-10 5:17

I have noticed a few cases where the import wizard had turned into a complete pile, i really miss Enterprise Manager, Object Explorer annoyes the crap outa me. I cant figure out why an index change that shouldnt be supported on the database made everything faster, and screwed up everything but reading on the database where it should have been supported.(actually the last bit is what i expected to happen, so i understand that just fine).

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

devvvy11-Jul-10 20:00

11-Jul-10 20:00

hello

I'm trying to bcp in matrix of integers into SQL using SQL Server bcp.exe

<br />
1,2,3<br />
3,4,5<br />
6,7,8<br />

And bcp command as follows:

<br />
bcp.exe DEV.dbo.SomeIntData in C:\BcpTest\bcp\output\output_int_pc.txt -S SomeServerName -f int.fmt -T  <br />

In my formatting file:

<br />
9.0<br />
3<br />
1       SQLBIGINT    0       25       ","    1     Col1         ""<br><br />
2       SQLBIGINT    0       25       ","    2     Col2         ""<br><br />
3       SQLBIGINT    0       25       "\n"    3     Col3        ""<br><br />

My simple test table:

<br />
CREATE TABLE [SomeIntData](<br />
 [Col1] [int] NULL,<br />
 [Col2] [int] NULL,<br />
 [Col3] [int] NULL<br />
) <br />

Problem is, integer value of "1" when inserted (by bcp.exe) into database became "49" (ASCII value/code of "1") - Any suggestion?! Thanks!

http://www.asciitable.com/[^]

http://msdn.microsoft.com/en-us/library/ms191479.aspx[^]

http://msdn.microsoft.com/en-us/library/ms189110.aspx[^]

dev

modified on Monday, July 12, 2010 2:37 AM

Mycroft Holmes11-Jul-10 23:18

11-Jul-10 23:18

Just an observation

We found that the BCP.exe in SQL 2008 was a lot more unstable than earlier versions. Apparently MS tightened up some of the formatting rules and it basically broke BCP. If you can make it behave then it is still the fastest load method.

We do not do any tansformation during the load, shove everything into varchar fields in a staging table. We then use stored procs to do the transforms. This gives us a number of advantages:

No errors in the load
Easier to manage the errors in the data when it is right there in front of you.
Dramatically faster - I mean hugely faster than either SSIS or Biztalk.

Never underestimate the power of human stupidity
RAH

devvvy12-Jul-10 0:29

12-Jul-10 0:29

nooeee.... I have no time to play around or experiment - and how do you implement "Stored procs to do transforms"?

Thanks

dev

Mycroft Holmes12-Jul-10 14:32

12-Jul-10 14:32

You know what the data looks like and you know what/where it needs to be. So the staging tables have the original data, simply write an insert statement with the transformations and lookups (joins) to move it to your final format.

Never underestimate the power of human stupidity
RAH

devvvy12-Jul-10 15:44

12-Jul-10 15:44

hey, I got it working - problem with bcp format file

if you follow MSDN advice and mark a field SQLINT (corresponds to database table col type), you'd end up having this int to ASCII conversion problem.

Just mark the damn field as SQLCHAR, and let db do conversion and all good.

dev

Mycroft Holmes12-Jul-10 17:29

12-Jul-10 17:29

devvvy wrote:
Just mark the damn field as SQLCHAR

Basically what I suggested in the first place, get the data in as char and deal with the crap after you have the data.

Never underestimate the power of human stupidity
RAH

devvvy12-Jul-10 18:02

Is it possible not to specify format file?

12-Jul-10 18:02

Thanks and sorry

dev

devvvy13-Jul-10 0:29

Is it possible to skip -f switch (i.e. not supply format file) and let bcp to do this job herself (i.e. infer field type auto like old days?) [modified]

13-Jul-10 0:29

Can we skip format file (-f switch) at all?

I got it working by indicating column tye SQLVAR instead of SQLINT and let SQL do conversion herself - however it's still ridiculous having (mandatory) to specify format file...?

Thanks

dev

devvvy13-Jul-10 0:37

sum of a column upto the current row for each row

13-Jul-10 0:37

Is it possible to skip -f switch (i.e. not supply format file) and let bcp to do this job herself (i.e. infer field type auto like old days?)

C:\somedir\bcp.exe DEV.dbo.SomeMixedData in C:\somedir\output\output_abc_mixed.txt -n -S SomeSErver -T

Starting copy...

0 rows copied. << Just can't get it working without format file.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

When open with Notepad my sample file resembles:

20100701,1.110000e+000,2.220000e+000
20100702,3.330000e+000,4.440000e+000
20100703,5.550000e+000,6.660000e+000

And my simple table:
CREATE TABLE [dbo].[SomeData](
[Col1] [varchar](255) NULL,
[Col2] [varchar](255) NULL,
[Col3] [varchar](255) NULL
)

dev

modified on Tuesday, July 13, 2010 7:02 AM

indian1439-Jul-10 8:31

indian143

9-Jul-10 8:31

Hi All,

How to get sum of a column upto the current row for each row into a different column in SQL Server.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: sum of a column upto the current row for each row

Eddy Vluggen9-Jul-10 8:59

Eddy Vluggen

9-Jul-10 8:59

Hi,

There's multiple ways of calculating a running total. This article[^] shows three of those solutions.

I are Troll Suspicious | :suss:

Re: sum of a column upto the current row for each row

CitrusTech9-Jul-10 9:03

CitrusTech

9-Jul-10 9:03

Not sure how this differs from your earlier question http://www.codeproject.com/Messages/3527850/Complex-Query.aspx[^]

You're looking for running totals.

Data Quality Tools and Data Profiling software

Re: sum of a column upto the current row for each row

Bernhard Hiller11-Jul-10 22:10

Bernhard Hiller

11-Jul-10 22:10

Not a full solution, but a step towards it:
Let us assume a table "Table1" with following columns and values:

ID val total
 1  12 <NULL>
 2  23 <NULL>
 3   2 <NULL>
 4  14 <NULL>

Now you can update the "total" column for each row with a query like the one below for ID=3:

UPDATE    table1
SET       total = tmp.result
FROM      (SELECT   MAX(ID) AS maxid, SUM(val) AS result
                    FROM          Table1
                    WHERE      (ID <= 3)) TMP
WHERE     table1.id = tmp.maxid

Next, replace "3" by a parameter and run the query for every ID value...

Re: sum of a column upto the current row for each row

Mycroft Holmes11-Jul-10 23:21