Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

T-SQL  While Loop

Posted on 2008-10-07
4
631 Views
Last Modified: 2012-06-27
i am trying to pull in information from a MySQL db into my MSSQL db.
i have created a linked server called LINKEDSERVERDB
There are over 900k rows in the table in the MySQL db and when i try to use a simple openquery select statement i get an error. I can pull back up to 600k rows.
I was thinking maybe if i use a while loop i would be able to insert 250k rows at a time into a temp table in my MSSQL db.
I have tried to write a while loop but i am now getting an error stating :

Cannot process the object "0,250000". The OLE DB provider "MSDASQL" for linked server
 "LINKEDSERVERDB" indicates that either the object has
no columns or the current user does not have permissions on that object

I don't know how i wouldn't have permissions with my linked serer, i set it up the same way as i do all my linked servers?
i have attached the code i have tried to use.
i would really appriciate if anyone could try and advise me please.

Kind Regards,
putoch

--Create table in MSSQL 
 
create table Imagine_prequal_temp(
id int not null, 
prefix varchar(5) not null,
number varchar(12)not null ,
mprefix varchar(5)null,
mnumber varchar(12)null,
fname varchar(40)null,
sname varchar(40)null,
prequalid int null,
testdate varchar(10)null,
result varchar(100)null,
result_code int null,
maxbw varchar(10)null,
description varchar (255)null,
checked datetime not null, 
step varchar(20)null,
company int null , 
IP varchar (15) null , 
contact int null ,
processed int null)
 
create unique Clustered index prequalindx on Imagine_Prequal_temp(id)
create index prequalindx1 on Imagine_Prequal_temp (prefix,number)
 
 
--WHILE LOOP 
TRUNCATE TABLE [BossDataView].[dbo].Imagine_prequal_temp;
 
GO
 
BEGIN
 
 
DECLARE @record_count INT, @while_counter INT, @limit_start INT, @limit_amount INT
 
SET @limit_start=0
SET @limit_amount=250000
SET @while_counter=-1
 
SELECT @record_count=count(*) FROM [BossDataView].[dbo].[IMAGINE_PREQUAL_TEMP]
 
WHILE @while_counter<@record_count
            BEGIN
                        DECLARE @final_query VARCHAR(255)
                        SET @final_query = CAST(@limit_start AS VARCHAR) + ',' + CAST(@limit_amount AS VARCHAR)
                        INSERT INTO [BossDataView].[dbo].[IMAGINE_PREQUAL_TEMP] 
                        EXEC('SELECT * FROM OPENQUERY(LINKEDSERVERDB,''' + @final_query + ''') AS subq');
                        SET @while_counter=@record_count
                        SELECT @record_count=count(*) FROM [BossDataView].[dbo].[IMAGEIN_PREQUAL_TEMP]
                        SET @limit_start= @limit_start + @limit_amount
            END
END

Open in new window

0
Comment
Question by:Putoch
4 Comments
 

Author Comment

by:Putoch
ID: 22661544
By using the simple openquery select from mysql db i was able to pull the data without getting the error above.
It was to do with my Advanced flag options on my ODBc connection
I went into http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html
and checked what sort of information i was looking to bring over.

i choose: Large tables with too many rows which indicated to use(2049 )
So i choose :
2048 FLAG_COMPRESSED_PROTO Use Compressed Protocol
and
1 FLAG_FIELD_LENGTH Don't Optimize Column Width

This has allowed me to simply pull the data over using teh open query but i would still like some advise on the while query please/
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 250 total points
ID: 22661618
The while condition doesn't test itself after each process so you need to include some login within the loop to determine whether it should break or continue

WHILE @while_counter<@record_count
  begin
        --do your inserts and counter updates

        --check if you should do it some more
        if @while_counter<@record_count
              continue
        else
            break
  end
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question