Showing posts with label via. Show all posts
Showing posts with label via. Show all posts

Thursday, March 29, 2012

ETL & BINARY_CHECKSUM?

We are currently populating our WH/DM using only tools availale in SQL2K
(i.e no Syncsort etc). Some of our modules (controlled via DTS) take,
understandably, a fair amount of time to processand I was ondering if there
was a trick was missing to speed things up. Example: -
In a data acquisition step for a Junk dimension (containing various state
flags) I extract CustomerURN and15 flags into a work table I then take
select the DISTINCT 15 flags into a table that will eventually be the
dimension for publication. I now need a cross-reference table so that other
processes can link on CustomerURN and pickup the dimension key. The obvious
SQL method is simply to join the tables on the 15 columns and O/P the xref
table - which takes a fair amount of time, but it does work.
Finally to the question, is there a quicker way of doing this? I have looked
at using BINARY_CHECKSUM but believe that this will not guarantee a unique
value that can be used for the match. Anybody got any ideas how to improve
on the method a using?
Thanks,
Paul
Hi Paul,
1. Checksum etc...A common problem.
We have released free utilities for win2000+ and one of them is a
utility to generate deltas given two versions of a file. You give it
yesterdays file and todays file (or just current version and previous
version) and it spits out Insert/Update/Delete records which must have
been applied to previous version to get current version. It is fully
null aware if you have nulls in your source data. We also publish the
source code for our free software and it is on the web site too. ( See
http://www.instantbi.com/Default.aspx?tabid=30 and the section on
Instant Data Warehouse utilities. Anyone is welcome to use our free
software...the utilities are things we have developed over many years
for our own projects so we think they are still useful...)
Checksums (CRCs) are frequently used but I choose not to use them
because there is still a chance to lose a change. And my feeling is
that even though it is 1 in 4 Billion I'd rather be sure.....;-)
2. Processing speeds.
Well depends on your tools...and how you design your jobs....we have
fee software in this area.....the biggest speed up you can get is
using files rather than using the database...
Files vs database is about 10 to 1. When we need to we use files as
much as possible for ETL. Life is much better now that machines are
faster but files are still the way to go if you need the
speed.......also for the really expensive processing we typically
load data into memory and use binary searches...in our fee software we
even do things like load data into memory mapped IO and have many
processes share the memory in the memory mapped IO...this gives us
great speed......
In our free software we do things like reformat the data so it can be
loaded via the database loaders...we also do things like run through
the input file and delete any rows that exist in the target table so
that the loader can be certain no constraint clashes will happen when
the loader runs....
These are all things that you have to write into DTS...I'm not sure
what is included in the next version of DTS...not keen to load 2005
betas onto my laptop.. ;-)
Hope this helps...
Peter Nolan
www.peternolan.com

ETL & BINARY_CHECKSUM?

We are currently populating our WH/DM using only tools availale in SQL2K
(i.e no Syncsort etc). Some of our modules (controlled via DTS) take,
understandably, a fair amount of time to processand I was ondering if there
was a trick was missing to speed things up. Example: -
In a data acquisition step for a Junk dimension (containing various state
flags) I extract CustomerURN and15 flags into a work table I then take
select the DISTINCT 15 flags into a table that will eventually be the
dimension for publication. I now need a cross-reference table so that other
processes can link on CustomerURN and pickup the dimension key. The obvious
SQL method is simply to join the tables on the 15 columns and O/P the xref
table - which takes a fair amount of time, but it does work.
Finally to the question, is there a quicker way of doing this? I have looked
at using BINARY_CHECKSUM but believe that this will not guarantee a unique
value that can be used for the match. Anybody got any ideas how to improve
on the method a using?
Thanks,
PaulHi Paul,
1. Checksum etc...A common problem.
We have released free utilities for win2000+ and one of them is a
utility to generate deltas given two versions of a file. You give it
yesterdays file and todays file (or just current version and previous
version) and it spits out Insert/Update/Delete records which must have
been applied to previous version to get current version. It is fully
null aware if you have nulls in your source data. We also publish the
source code for our free software and it is on the web site too. ( See
http://www.instantbi.com/Default.aspx?tabid=30 and the section on
Instant Data Warehouse utilities. Anyone is welcome to use our free
software...the utilities are things we have developed over many years
for our own projects so we think they are still useful...)
Checksums (CRCs) are frequently used but I choose not to use them
because there is still a chance to lose a change. And my feeling is
that even though it is 1 in 4 Billion I'd rather be sure.....;-)
2. Processing speeds.
Well depends on your tools...and how you design your jobs....we have
fee software in this area.....the biggest speed up you can get is
using files rather than using the database...
Files vs database is about 10 to 1. When we need to we use files as
much as possible for ETL. Life is much better now that machines are
faster but files are still the way to go if you need the
speed.......also for the really expensive processing we typically
load data into memory and use binary searches...in our fee software we
even do things like load data into memory mapped IO and have many
processes share the memory in the memory mapped IO...this gives us
great speed......
In our free software we do things like reformat the data so it can be
loaded via the database loaders...we also do things like run through
the input file and delete any rows that exist in the target table so
that the loader can be certain no constraint clashes will happen when
the loader runs....
These are all things that you have to write into DTS...I'm not sure
what is included in the next version of DTS...not keen to load 2005
betas onto my laptop.. ;-)
Hope this helps...
Peter Nolan
www.peternolan.com

Monday, March 26, 2012

Establishing a remote connection..! (Again)

Hi,

I am trying to connect via the Management Studio Express. I share a LAN with the server. In Netowrk connections I can see the server and I can even access the "Shared" folders. However,when I try to connect I get an error that says the error may be caused by the server not allowing remote connections. I have already done the following:

With the Surface Area Configuration facility I have set the server to allow remote connections I have selected TCP/IP as the preferred mode of connection. I have selected Windows authentification as mode of authorisation

What else can I do?

Please help.

Derekprinsloo

Verify that there is no local firewall blocking.

These resources should help you.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Sunday, February 26, 2012

Error=cantconvertdata

Hi everyone!

When I try to process a cube via DTS or in a Terminal Server window I get the following error message:

DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221386 (80040076)
Error string: RECORD=1, _ERROR=CANTCONVERTDATA, COLUMN=30..

The cube runs on:
Win 2000 Advanced SP1 - MS SQL 2000 SP 1

We have found that it works when we keep a user with Admin privileges logged on locally on the server.
Well, it is the solution of the problem but it seems to be a bug anyway.

Has anyone got the same problem or maybe solved the problem another way?

Thanks for your replies

Jacek
:confused:Hello, Can you tell me please how I can solve this problem?? Because I have the same problem. THanks.
Yolanda

Hi everyone!

When I try to process a cube via DTS or in a Terminal Server window I get the following error message:

DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221386 (80040076)
Error string: RECORD=1, _ERROR=CANTCONVERTDATA, COLUMN=30..

The cube runs on:
Win 2000 Advanced SP1 - MS SQL 2000 SP 1

We have found that it works when we keep a user with Admin privileges logged on locally on the server.
Well, it is the solution of the problem but it seems to be a bug anyway.

Has anyone got the same problem or maybe solved the problem another way?

Thanks for your replies

Jacek
:confused:

Wednesday, February 15, 2012

Error: SQL Server Requires Encryption On

Hello All,

I have a commercial application built for Pocket PC's that connects to SQL Server 2005 via TCP/IP over wireless networks. In installations for our sales people we installed SQL 2005 Express on their notebooks for demonstrations. In one of these installs we are getting the following error message: "An error occurred - SQL Server requires Encryption On". We do not use encrypted connections and I have verified encryption is turned off in the Options tab in SQL 2005 login screens.

Here is the connection string we use in the app.config file on the Pocket PC's:
<add key="connStr" value="Data Source=192.168.0.19,1433;Initial Catalog=SQL0018;User ID = User01;Password=PW01"/>

Other apps on the notebook are connecting to this same SQL Server without any issues. Thank you in advance for any help here,

Jack

From the description and the error message that you copied, the failure seems to be coming from the client (Pocket PC software).

It seems like you are indeed trying to connect to a SQL Server instance that requires encryption, it may be possible that the settings on that particular instance were changed but the service was not restarted.

I would also recommend asking on the SQL Server data access forum in case they have a better answer: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for the reply Raul, but the problem turned out to be a boolean in the registry on the machine running SQL Express. Here is the post in SQL Server Data Access forum that solved it.

The registry key that controls this behaviour is below (assuming express is the first SQLServer you installed on the machine). If that key is set to 1, then set it to 0 and it should fix your problem.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\ForceEncryption

Thanks to John for that post.

Jack

Error: SQL Server Requires Encryption On

Hello All,

I have a commercial application built for Pocket PC's that connects to SQL Server 2005 via TCP/IP over wireless networks. In installations for our sales people we installed SQL 2005 Express on their notebooks for demonstrations. In one of these installs we are getting the following error message: "An error occurred - SQL Server requires Encryption On". We do not use encrypted connections and I have verified encryption is turned off in the Options tab in SQL 2005 login screens.

Here is the connection string we use in the app.config file on the Pocket PC's:
<add key="connStr" value="Data Source=192.168.0.19,1433;Initial Catalog=SQL0018;User ID = User01;Password=PW01"/>

Other apps on the notebook are connecting to this same SQL Server without any issues. Thank you in advance for any help here,

Jack

Move to Sql Server Express forum. Thanks!