I am trying to find invalid dates in a column after loading from a text file
but the queries I have doesn't give me any result.
Because of the error, I have loaded column as a varchar rather than datetime
so that I can find the invalid dates. My query is:
Select col from dbo.mytable
where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
month
-----
Select col from dbo.mytable
where substr(col, 4, 2) NOT in
(01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) -- For Day
-----
Select col from dbo.mytable
where substr(col, 7, 2) NOT in (19,20) -- For year
-----
Typical dates in the text file are
01/12/1958
09/05/2007
04/23/1978
12/28/2003
01/01/1900
If I try to change the column data type from varchar to datetime I get an
error like 'can not convert to datetime, date is out of range'.
Thanks for any help.Well, I can think of a procedural approach.
Load the data as you have done and create a table variable with a datetime
column.
For each row, attempt an insert, or update to the table variable (put a
dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
somewhere else. Or on success, move the row to the final table, or choose
your own logic.
All that said, I was pretty sure DTS did this kind of thing for you.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>I am trying to find invalid dates in a column after loading from a text
>file
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than
> datetime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> For
> month
> -----
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> -- For Day
> -----
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> -----
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.|||The message from DTS is not what the data is. It is something like this (I
don't have the exact error):
Can not insert data Source column (Column number 70) data type string -
destination column (column_name) data type
DATE..................something like that which doesn't tell you what
the exact invalid date is. If the data is small, you can scroll it up and
down and find it but when it is 2.4 million rows, it is hard to find it
manually or when the text file is ~800 MB.........
"Jay" wrote:
> Well, I can think of a procedural approach.
> Load the data as you have done and create a table variable with a datetime
> column.
> For each row, attempt an insert, or update to the table variable (put a
> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
> somewhere else. Or on success, move the row to the final table, or choose
> your own logic.
> All that said, I was pretty sure DTS did this kind of thing for you.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
> >I am trying to find invalid dates in a column after loading from a text
> >file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than
> > datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> > For
> > month
> > -----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> > -- For Day
> > -----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > -----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
>
>|||I've only used DTS when I had full control of the complete data stream, but
I though I remembered something about exceptions.
Still, if that isn't working for you and no one can help you with exception
filters in DTS, write the import filter yourself.
You could also try:
select *
from table
where datestr not LIKE '[0-1][0-9]/[[0-3][0-9]/[1-2][09][0-9][0-9]'
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:91B0F853-FD70-415D-A31E-17CE8EDC4E0E@.microsoft.com...
> The message from DTS is not what the data is. It is something like this (I
> don't have the exact error):
> Can not insert data Source column (Column number 70) data type string -
> destination column (column_name) data type
> DATE..................something like that which doesn't tell you what
> the exact invalid date is. If the data is small, you can scroll it up and
> down and find it but when it is 2.4 million rows, it is hard to find it
> manually or when the text file is ~800 MB.........
>
> "Jay" wrote:
>> Well, I can think of a procedural approach.
>> Load the data as you have done and create a table variable with a
>> datetime
>> column.
>> For each row, attempt an insert, or update to the table variable (put a
>> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that
>> key
>> somewhere else. Or on success, move the row to the final table, or choose
>> your own logic.
>> All that said, I was pretty sure DTS did this kind of thing for you.
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>> >I am trying to find invalid dates in a column after loading from a text
>> >file
>> > but the queries I have doesn't give me any result.
>> >
>> > Because of the error, I have loaded column as a varchar rather than
>> > datetime
>> > so that I can find the invalid dates. My query is:
>> >
>> > Select col from dbo.mytable
>> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
>> > For
>> > month
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 4, 2) NOT in
>> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
>> > -- For Day
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 7, 2) NOT in (19,20) -- For year
>> > -----
>> >
>> > Typical dates in the text file are
>> >
>> > 01/12/1958
>> > 09/05/2007
>> > 04/23/1978
>> > 12/28/2003
>> > 01/01/1900
>> >
>> > If I try to change the column data type from varchar to datetime I get
>> > an
>> > error like 'can not convert to datetime, date is out of range'.
>> >
>> > Thanks for any help.
>>|||On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> I am trying to find invalid dates in a column after loading from a text f=ile
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than datet=ime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- F=or
> month
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,=25=AD,26,27,28,29,30,31) -- For Day
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> ----=--=AD--
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.
Hi, there is an isdate function. Find the invalid dates and handle
them separately. HTH.|||That did it............Thanks..........
"SB" wrote:
> On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> > I am trying to find invalid dates in a column after loading from a text file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
> > month
> > ----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25-,26,27,28,29,30,31) -- For Day
> > ----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > ----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
> Hi, there is an isdate function. Find the invalid dates and handle
> them separately. HTH.
>
Showing posts with label loading. Show all posts
Showing posts with label loading. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
query engine error
"query engine error" in ...4trerf-fr454-5453-ffftre4.rpt"
Usually what is the problem when i get this message error,when is loading a report into a reportviewer?
thanksThis may be due to the improper link between the tables used. Check for the link between the tables properly.
Ashok|||I am have 7 reports in my app and none of my reports work on the deployment machine. Could there be anything else wrong with it? It works fine on my dev machine.
Sue
Usually what is the problem when i get this message error,when is loading a report into a reportviewer?
thanksThis may be due to the improper link between the tables used. Check for the link between the tables properly.
Ashok|||I am have 7 reports in my app and none of my reports work on the deployment machine. Could there be anything else wrong with it? It works fine on my dev machine.
Sue
query designer very slow loading tables
when i use the query designer and add tables it takes 15-30 mins to load a table....so if i need 6 tables in my query thats like 6 times 30 minute load times...i'm using oracle as my data source
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
any ideas?
moldypenguins wrote:
when i use the query designer and add tables it takes 15-30 mins to load a table
You're saying it takes 15-30 minutes to drag and drop a table control onto the layout of query designer?
What is it exactly that is taking that long?
|||>>when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
Alternatively... what version of what type of connection are you using when you don't use OLEDB? I use Oracle stuff, maybe I can help or somebody else will spot what's wrong. (I'll be off-line shortly for about a week)
>L<
|||it takes 15-30 minutes to load the table...ie i click the add table button, click a table and click add, then i wait
|||Can you post a screenshot of where you are doing this?
If you can't then describe further. I'm not slow, it's just there are several places you could be designing a query.
Are you using SQL 2000? 2005? Is this in BIS? SQL Server Management Studio?
|||sorry i'm using sql 2005 in bis, here is a screen shot
|||How much RAM is in the PC that you are adding the table to query designer from? What is the processor speed? How about on the SQL server?
What is the network configuration like between the PC that you are using query designer and the SQL server? Is it the same machine?
Have you noticed slow load times in any other SQL application (such as Management Studio)?
|||its not the pc because i have tried it on other computers in the office, server is open vms running orcle 9i
|||Just because you have tried it on other computers and it does the same thing doesn't mean it's not the PC.
If they all have 256 mb of RAM in them then you could experience the same problem in all of them.
In my experience, slow load time = not enough hardware OR poor network setup OR antivirus interference.
|||i have 512mb
and the other 2 have 2gb or more
and like i said it works quickly when i chose oledb but i need to keep named parameters|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
here is someone else with the same problem!
|||Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
So I am going to ask again what I asked before <s>:
>>
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
I'm serious, let's give this a go.
>L<
|||the query doesn't matter because i don't even make it that far....its just when i'm loading the tables into the designer, it seems like it is taking long to link the databases or something, but when i use oledb it works super fast.....|||Hi there,
Look: I'm not talking about "optimizing" your query, and I don't think your query is the problem.
But the query *does* matter. Because if you could change the query so you could use oledb on the query, then you would be using oledb to load the tables in the designer and your problem would go away <g>.
As I said before:
Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
... and this is something we probably can't fix. So I'm going for something that we *can*. OK?
>L<
|||BTW -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2096059&SiteID=1... please look at this thread, you may be using an unsupported connection method for the query designer. So if you want to speed up the QD loads, let's work at arranging your query so you can use the OLE DB driver, really I think it may be possible to get around the "named parameters" reason that you can't use the OLE DB driver in this case.
So describe what you're doing and we'll take a shot...
>L<
query designer very slow loading tables
when i use the query designer and add tables it takes 15-30 mins to load a table....so if i need 6 tables in my query thats like 6 times 30 minute load times...i'm using oracle as my data source
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
any ideas?
when i use the query designer and add tables it takes 15-30 mins to load a table
You're saying it takes 15-30 minutes to drag and drop a table control onto the layout of query designer?
What is it exactly that is taking that long?
|||>>when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
Alternatively... what version of what type of connection are you using when you don't use OLEDB? I use Oracle stuff, maybe I can help or somebody else will spot what's wrong. (I'll be off-line shortly for about a week)
>L<
|||it takes 15-30 minutes to load the table...ie i click the add table button, click a table and click add, then i wait
|||Can you post a screenshot of where you are doing this?
If you can't then describe further. I'm not slow, it's just there are several places you could be designing a query.
Are you using SQL 2000? 2005? Is this in BIS? SQL Server Management Studio?
|||sorry i'm using sql 2005 in bis, here is a screen shot
|||How much RAM is in the PC that you are adding the table to query designer from? What is the processor speed? How about on the SQL server?
What is the network configuration like between the PC that you are using query designer and the SQL server? Is it the same machine?
Have you noticed slow load times in any other SQL application (such as Management Studio)?
|||its not the pc because i have tried it on other computers in the office, server is open vms running orcle 9i
|||Just because you have tried it on other computers and it does the same thing doesn't mean it's not the PC.
If they all have 256 mb of RAM in them then you could experience the same problem in all of them.
In my experience, slow load time = not enough hardware OR poor network setup OR antivirus interference.
|||i have 512mb
and the other 2 have 2gb or more
and like i said it works quickly when i chose oledb but i need to keep named parameters|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
here is someone else with the same problem!
|||Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
So I am going to ask again what I asked before <s>:
>>
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
I'm serious, let's give this a go.
>L<
|||the query doesn't matter because i don't even make it that far....its just when i'm loading the tables into the designer, it seems like it is taking long to link the databases or something, but when i use oledb it works super fast.....|||Hi there,
Look: I'm not talking about "optimizing" your query, and I don't think your query is the problem.
But the query *does* matter. Because if you could change the query so you could use oledb on the query, then you would be using oledb to load the tables in the designer and your problem would go away <g>.
As I said before:
Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
... and this is something we probably can't fix. So I'm going for something that we *can*. OK?
>L<
|||BTW -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2096059&SiteID=1... please look at this thread, you may be using an unsupported connection method for the query designer. So if you want to speed up the QD loads, let's work at arranging your query so you can use the OLE DB driver, really I think it may be possible to get around the "named parameters" reason that you can't use the OLE DB driver in this case.
So describe what you're doing and we'll take a shot...
>L<
query designer very slow loading tables
when i use the query designer and add tables it takes 15-30 mins to load a table....so if i need 6 tables in my query thats like 6 times 30 minute load times...i'm using oracle as my data source
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
any ideas?
when i use the query designer and add tables it takes 15-30 mins to load a table
You're saying it takes 15-30 minutes to drag and drop a table control onto the layout of query designer?
What is it exactly that is taking that long?
|||>>when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
Alternatively... what version of what type of connection are you using when you don't use OLEDB? I use Oracle stuff, maybe I can help or somebody else will spot what's wrong. (I'll be off-line shortly for about a week)
>L<
|||it takes 15-30 minutes to load the table...ie i click the add table button, click a table and click add, then i wait
|||Can you post a screenshot of where you are doing this?
If you can't then describe further. I'm not slow, it's just there are several places you could be designing a query.
Are you using SQL 2000? 2005? Is this in BIS? SQL Server Management Studio?
|||sorry i'm using sql 2005 in bis, here is a screen shot
|||How much RAM is in the PC that you are adding the table to query designer from? What is the processor speed? How about on the SQL server?
What is the network configuration like between the PC that you are using query designer and the SQL server? Is it the same machine?
Have you noticed slow load times in any other SQL application (such as Management Studio)?
|||its not the pc because i have tried it on other computers in the office, server is open vms running orcle 9i
|||Just because you have tried it on other computers and it does the same thing doesn't mean it's not the PC.
If they all have 256 mb of RAM in them then you could experience the same problem in all of them.
In my experience, slow load time = not enough hardware OR poor network setup OR antivirus interference.
|||i have 512mb
and the other 2 have 2gb or more
and like i said it works quickly when i chose oledb but i need to keep named parameters|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
here is someone else with the same problem!
|||Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
So I am going to ask again what I asked before <s>:
>>
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
I'm serious, let's give this a go.
>L<
|||the query doesn't matter because i don't even make it that far....its just when i'm loading the tables into the designer, it seems like it is taking long to link the databases or something, but when i use oledb it works super fast.....|||Hi there,
Look: I'm not talking about "optimizing" your query, and I don't think your query is the problem.
But the query *does* matter. Because if you could change the query so you could use oledb on the query, then you would be using oledb to load the tables in the designer and your problem would go away <g>.
As I said before:
Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
... and this is something we probably can't fix. So I'm going for something that we *can*. OK?
>L<
|||BTW -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2096059&SiteID=1... please look at this thread, you may be using an unsupported connection method for the query designer. So if you want to speed up the QD loads, let's work at arranging your query so you can use the OLE DB driver, really I think it may be possible to get around the "named parameters" reason that you can't use the OLE DB driver in this case.
So describe what you're doing and we'll take a shot...
>L<
query designer very slow loading tables
when i use the query designer and add tables it takes 15-30 mins to load a table....so if i need 6 tables in my query thats like 6 times 30 minute load times...i'm using oracle as my data source
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
any ideas?
when i use the query designer and add tables it takes 15-30 mins to load a table
You're saying it takes 15-30 minutes to drag and drop a table control onto the layout of query designer?
What is it exactly that is taking that long?
|||>>when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
Alternatively... what version of what type of connection are you using when you don't use OLEDB? I use Oracle stuff, maybe I can help or somebody else will spot what's wrong. (I'll be off-line shortly for about a week)
>L<
|||it takes 15-30 minutes to load the table...ie i click the add table button, click a table and click add, then i wait
|||Can you post a screenshot of where you are doing this?
If you can't then describe further. I'm not slow, it's just there are several places you could be designing a query.
Are you using SQL 2000? 2005? Is this in BIS? SQL Server Management Studio?
|||sorry i'm using sql 2005 in bis, here is a screen shot
|||How much RAM is in the PC that you are adding the table to query designer from? What is the processor speed? How about on the SQL server?
What is the network configuration like between the PC that you are using query designer and the SQL server? Is it the same machine?
Have you noticed slow load times in any other SQL application (such as Management Studio)?
|||its not the pc because i have tried it on other computers in the office, server is open vms running orcle 9i
|||Just because you have tried it on other computers and it does the same thing doesn't mean it's not the PC.
If they all have 256 mb of RAM in them then you could experience the same problem in all of them.
In my experience, slow load time = not enough hardware OR poor network setup OR antivirus interference.
|||i have 512mb
and the other 2 have 2gb or more
and like i said it works quickly when i chose oledb but i need to keep named parameters|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
here is someone else with the same problem!
|||Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
So I am going to ask again what I asked before <s>:
>>
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
I'm serious, let's give this a go.
>L<
|||the query doesn't matter because i don't even make it that far....its just when i'm loading the tables into the designer, it seems like it is taking long to link the databases or something, but when i use oledb it works super fast.....|||Hi there,
Look: I'm not talking about "optimizing" your query, and I don't think your query is the problem.
But the query *does* matter. Because if you could change the query so you could use oledb on the query, then you would be using oledb to load the tables in the designer and your problem would go away <g>.
As I said before:
Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
... and this is something we probably can't fix. So I'm going for something that we *can*. OK?
>L<
|||BTW -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2096059&SiteID=1... please look at this thread, you may be using an unsupported connection method for the query designer. So if you want to speed up the QD loads, let's work at arranging your query so you can use the OLE DB driver, really I think it may be possible to get around the "named parameters" reason that you can't use the OLE DB driver in this case.
So describe what you're doing and we'll take a shot...
>L<
query designer very slow loading tables
when i use the query designer and add tables it takes 15-30 mins to load a table....so if i need 6 tables in my query thats like 6 times 30 minute load times...i'm using oracle as my data source
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
any ideas?
when i use the query designer and add tables it takes 15-30 mins to load a table
You're saying it takes 15-30 minutes to drag and drop a table control onto the layout of query designer?
What is it exactly that is taking that long?
|||>>when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
Alternatively... what version of what type of connection are you using when you don't use OLEDB? I use Oracle stuff, maybe I can help or somebody else will spot what's wrong. (I'll be off-line shortly for about a week)
>L<
|||it takes 15-30 minutes to load the table...ie i click the add table button, click a table and click add, then i wait
|||Can you post a screenshot of where you are doing this?
If you can't then describe further. I'm not slow, it's just there are several places you could be designing a query.
Are you using SQL 2000? 2005? Is this in BIS? SQL Server Management Studio?
|||sorry i'm using sql 2005 in bis, here is a screen shot
|||How much RAM is in the PC that you are adding the table to query designer from? What is the processor speed? How about on the SQL server?
What is the network configuration like between the PC that you are using query designer and the SQL server? Is it the same machine?
Have you noticed slow load times in any other SQL application (such as Management Studio)?
|||its not the pc because i have tried it on other computers in the office, server is open vms running orcle 9i
|||Just because you have tried it on other computers and it does the same thing doesn't mean it's not the PC.
If they all have 256 mb of RAM in them then you could experience the same problem in all of them.
In my experience, slow load time = not enough hardware OR poor network setup OR antivirus interference.
|||i have 512mb
and the other 2 have 2gb or more
and like i said it works quickly when i chose oledb but i need to keep named parameters|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
here is someone else with the same problem!
|||Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
So I am going to ask again what I asked before <s>:
>>
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
I'm serious, let's give this a go.
>L<
|||the query doesn't matter because i don't even make it that far....its just when i'm loading the tables into the designer, it seems like it is taking long to link the databases or something, but when i use oledb it works super fast.....|||Hi there,
Look: I'm not talking about "optimizing" your query, and I don't think your query is the problem.
But the query *does* matter. Because if you could change the query so you could use oledb on the query, then you would be using oledb to load the tables in the designer and your problem would go away <g>.
As I said before:
Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
... and this is something we probably can't fix. So I'm going for something that we *can*. OK?
>L<
|||BTW -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2096059&SiteID=1... please look at this thread, you may be using an unsupported connection method for the query designer. So if you want to speed up the QD loads, let's work at arranging your query so you can use the OLE DB driver, really I think it may be possible to get around the "named parameters" reason that you can't use the OLE DB driver in this case.
So describe what you're doing and we'll take a shot...
>L<
query designer very slow loading tables
when i use the query designer and add tables it takes 15-30 mins to load a table....so if i need 6 tables in my query thats like 6 times 30 minute load times...i'm using oracle as my data source
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
any ideas?
when i use the query designer and add tables it takes 15-30 mins to load a table
You're saying it takes 15-30 minutes to drag and drop a table control onto the layout of query designer?
What is it exactly that is taking that long?
|||>>when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
Alternatively... what version of what type of connection are you using when you don't use OLEDB? I use Oracle stuff, maybe I can help or somebody else will spot what's wrong. (I'll be off-line shortly for about a week)
>L<
|||it takes 15-30 minutes to load the table...ie i click the add table button, click a table and click add, then i wait
|||Can you post a screenshot of where you are doing this?
If you can't then describe further. I'm not slow, it's just there are several places you could be designing a query.
Are you using SQL 2000? 2005? Is this in BIS? SQL Server Management Studio?
|||sorry i'm using sql 2005 in bis, here is a screen shot
|||How much RAM is in the PC that you are adding the table to query designer from? What is the processor speed? How about on the SQL server?
What is the network configuration like between the PC that you are using query designer and the SQL server? Is it the same machine?
Have you noticed slow load times in any other SQL application (such as Management Studio)?
|||its not the pc because i have tried it on other computers in the office, server is open vms running orcle 9i
|||Just because you have tried it on other computers and it does the same thing doesn't mean it's not the PC.
If they all have 256 mb of RAM in them then you could experience the same problem in all of them.
In my experience, slow load time = not enough hardware OR poor network setup OR antivirus interference.
|||i have 512mb
and the other 2 have 2gb or more
and like i said it works quickly when i chose oledb but i need to keep named parameters|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324176&SiteID=1
here is someone else with the same problem!
|||Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
So I am going to ask again what I asked before <s>:
>>
when i switch the source to oledb it adds tables within seconds the only thing is i need to use named parameters
<<
Yes -- use oledb and find a way around the named parameters thing <g>. Tell us more about your query, maybe we can figure out something there.
I'm serious, let's give this a go.
>L<
|||the query doesn't matter because i don't even make it that far....its just when i'm loading the tables into the designer, it seems like it is taking long to link the databases or something, but when i use oledb it works super fast.....|||Hi there,
Look: I'm not talking about "optimizing" your query, and I don't think your query is the problem.
But the query *does* matter. Because if you could change the query so you could use oledb on the query, then you would be using oledb to load the tables in the designer and your problem would go away <g>.
As I said before:
Given that the other post also uses the same Oracle connector (right/) I wonder if it is trying to re-use the connection for the same connection and the Oracle connector doesn't like that, the first use has to time out before it's possible. Sounds like a bug in the connector or the way it's being used -- no idea --
... and this is something we probably can't fix. So I'm going for something that we *can*. OK?
>L<
|||BTW -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2096059&SiteID=1... please look at this thread, you may be using an unsupported connection method for the query designer. So if you want to speed up the QD loads, let's work at arranging your query so you can use the OLE DB driver, really I think it may be possible to get around the "named parameters" reason that you can't use the OLE DB driver in this case.
So describe what you're doing and we'll take a shot...
>L<
Subscribe to:
Posts (Atom)