October 6, 2010 at 11:07 am
jcrawf02 (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.I think that's because nobody ever considered that this was a possibility. Why are you being forced to do this?
Ich, Ich, Ich
Cleansing eyes - loading data into Access - ich
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 6, 2010 at 11:08 am
jcrawf02 (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.I think that's because nobody ever considered that this was a possibility. Why are you being forced to do this?
My favorite reason of all "Business Requirements". A Business end user wants to see the data in an access database on his desktop. My first try for one table hit the 2 gig limit. I went back and pared down the data sizes and managed to get it to only 1.2 gig. Takes an hour and a half to move 9 million rows into the table.
And I've just realized, I'm doing this with Access 2007, but they might have an older version. Fun! Ah well, at least the Jet 4 drivers come installed. I had to go find the Access 2007 driver and install it on the server for this to work.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 6, 2010 at 11:10 am
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.
Really? I haven't had any problems with it. What's your issue?
EDIT: Sorry. Thought you were going the opposite direction. From Access to SQL. So, why not just give him a front end .mdb with linked tables?
October 6, 2010 at 11:15 am
Brandie Tarvin (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.Really? I haven't had any problems with it. What's your issue?
Speed, making sure data types match, remembering to drop down to 32 bit, finding the right drivers, trying to get the total data size under 2 gig. And, of course, this is all in addition to trying to figure out what the error messages really mean. I got an ODBC connectivity error that actually meant I'd hit the database size limit. I got another that meant I had a data-type mismatch and another that meant I hadn't switched to 32 bit from 64. All pretty much the same error meaning all those things.
The first 4.5 million rows are pretty quick, the last 4.5 million seem to take 70 minutes.
On the bright side, I learned how to do a bunch of new things today!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 6, 2010 at 11:16 am
Stefan Krzywicki (10/6/2010)
jcrawf02 (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.I think that's because nobody ever considered that this was a possibility. Why are you being forced to do this?
My favorite reason of all "Business Requirements". A Business end user wants to see the data in an access database on his desktop. My first try for one table hit the 2 gig limit. I went back and pared down the data sizes and managed to get it to only 1.2 gig. Takes an hour and a half to move 9 million rows into the table.
And I've just realized, I'm doing this with Access 2007, but they might have an older version. Fun! Ah well, at least the Jet 4 drivers come installed. I had to go find the Access 2007 driver and install it on the server for this to work.
Any idea what the Business user is actually going to do with the data? Given any thought to porting that data to another table, database, instance or whatnot and then setting it up as some pass through queries in an ADP? That way hopefully the data conversion end is pretty quick and you don't have to worry about size limits?
-Luke
October 6, 2010 at 11:18 am
Brandie Tarvin (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.Really? I haven't had any problems with it. What's your issue?
EDIT: Sorry. Thought you were going the opposite direction. From Access to SQL. So, why not just give him a front end .mdb with linked tables?
Heh, no. That's the good way. : -)
Permissions and data security mostly. After I have this working, I'm going to suggest we get him SQL Server Express installed on an isolated server that he can access and load it there. Then we can either give him the front-end mdb or a linked excel workbook or even the SQL Server Express version of SSMS.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 6, 2010 at 11:21 am
Luke L (10/6/2010)
Stefan Krzywicki (10/6/2010)
jcrawf02 (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.I think that's because nobody ever considered that this was a possibility. Why are you being forced to do this?
My favorite reason of all "Business Requirements". A Business end user wants to see the data in an access database on his desktop. My first try for one table hit the 2 gig limit. I went back and pared down the data sizes and managed to get it to only 1.2 gig. Takes an hour and a half to move 9 million rows into the table.
And I've just realized, I'm doing this with Access 2007, but they might have an older version. Fun! Ah well, at least the Jet 4 drivers come installed. I had to go find the Access 2007 driver and install it on the server for this to work.
Any idea what the Business user is actually going to do with the data? Given any thought to porting that data to another table, database, instance or whatnot and then setting it up as some pass through queries in an ADP? That way hopefully the data conversion end is pretty quick and you don't have to worry about size limits?
-Luke
No idea, but I'm sure he's up to no good. ; -)
That is the kind of thing I intend to suggest, but we're hurting right now in our networking/server group. Only one guy atm after some departures.
The only place we could put the database/table right now is Production and I don't want to take a chance there. If I can get Express installed on a file server or some other low-priority machine, I'll be much happier.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 6, 2010 at 11:25 am
GilaMonster (10/6/2010)
WayneS (10/6/2010)
GilaMonster (10/6/2010)
... I have 3 table variable myths targeted.Did I cover them in my article[/url]? If not, I'd love to know what you have, so that I can enhance it (and add it to the presentation that I'm making on this article!).
Two yes, one no, though I'm planning to debunk one of the ones you did in a very different way.
Okay, I've got a few questions:
1. Can I help?
2. Can you share the myth that I didn't cover?
3. Which myth are you debunking differently?
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 6, 2010 at 11:29 am
Stefan Krzywicki (10/6/2010)
Brandie Tarvin (10/6/2010)
Stefan Krzywicki (10/6/2010)
Wow, loading data into Access from SQL Server through SSIS is a Royal PITA.Really? I haven't had any problems with it. What's your issue?
EDIT: Sorry. Thought you were going the opposite direction. From Access to SQL. So, why not just give him a front end .mdb with linked tables?
Heh, no. That's the good way. : -)
Permissions and data security mostly. After I have this working, I'm going to suggest we get him SQL Server Express installed on an isolated server that he can access and load it there. Then we can either give him the front-end mdb or a linked excel workbook or even the SQL Server Express version of SSMS.
Okay, but you can still do this. I do it all the time.
Guy has a domain login, right? On SQL Server, grant db_datareader to only the necessary columns on the necessary tables. Then create Access front end .mdb that links to those tables. I promise, he can't see more than what you let him see. And since he doesn't have SQL Server client tools, he can't get access to the data via SSMS (big assumption I'm making here).
EDIT: Doh, forgot to mention the use of a File System ODBC for the Access db to connect to SQL Server.
October 6, 2010 at 11:33 am
WayneS (10/6/2010)
Okay, I've got a few questions:1. Can I help?
Thanks, but I have it under control (will hack out the post this weekend)
2. Can you share the myth that I didn't cover?
3. Which myth are you debunking differently?
Wait and see... :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 11:42 am
Maybe I've gone round the bend. Possibly senility has finally kicked in good and hard. Maybe it's just me, but does this sound a little bit nuts to anyone else?
They're planning to:
A) toss normalization
B) Use GUIDs but no other unique constraints
C) Toss RI because it's "too much work"
D) Use triggers for RI instead
Unless I'm completely missing the boat because of encroaching senility, insanity or just plain old stupidity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2010 at 11:44 am
Grant Fritchey (10/6/2010)
Maybe I've gone round the bend. Possibly senility has finally kicked in good and hard. Maybe it's just me, but does this sound a little bit nuts to anyone else?They're planning to:
A) toss normalization
B) Use GUIDs but no other unique constraints
C) Toss RI because it's "too much work"
D) Use triggers for RI instead
Unless I'm completely missing the boat because of encroaching senility, insanity or just plain old stupidity.
Grant, your link is not working.
I think you meant: http://www.sqlservercentral.com/Forums/Topic998790-373-1.aspx#bm999605
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 6, 2010 at 11:45 am
Your link, it is broken.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 11:45 am
edit: spam of the broken link!
Yes, I agree though..I would try to avoid using triggers whenever possible anyway, especially if there's a better way to do it (in this case, FKs). It adds so much overhead and is another point of failure. Especially since he's building it from the ground up..do it right.
October 6, 2010 at 11:45 am
GilaMonster (10/6/2010)
WayneS (10/6/2010)
Okay, I've got a few questions:1. Can I help?
Thanks, but I have it under control (will hack out the post this weekend)
2. Can you share the myth that I didn't cover?
3. Which myth are you debunking differently?
Wait and see... :hehe:
[Heavy Sigh]
okay, wait mode on. This will be on your blog?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 19,981 through 19,995 (of 66,751 total)
You must be logged in to reply to this topic. Login to reply