Hi,
I get the following error when replicating...
ODBCBCP/Driver version mismatch
(Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
what could be the cause... and what can I do?
Thanks,
Alex
your versions of odbcbcp.dll or related components on the
publisher/distributor/subscriber are not consistent. Try to apply the latest
MDAC and SP/Hot fix to get them all consistent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alexandre" <nospam@.nospam.net> wrote in message
news:OSYSfruAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I get the following error when replicating...
> ODBCBCP/Driver version mismatch
> (Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
> ----
--
> --
>
> what could be the cause... and what can I do?
> Thanks,
> Alex
>
|||I knew it was something like that... just couldn't remember..
Thanks a lot! Have a good day!
Alex.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23fxpNvuAFHA.2392@.TK2MSFTNGP14.phx.gbl...
> your versions of odbcbcp.dll or related components on the
> publisher/distributor/subscriber are not consistent. Try to apply the
latest
> MDAC and SP/Hot fix to get them all consistent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Alexandre" <nospam@.nospam.net> wrote in message
> news:OSYSfruAFHA.2180@.TK2MSFTNGP12.phx.gbl...
> ----
> --
>
Showing posts with label odbc. Show all posts
Showing posts with label odbc. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Error when changing the length on DataReader Source
Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.
Under the Component properties tab, the SQL Command looks something like this.
Select ID, ADDRESS, REVISED from ADDRESS
The data type for the source columns are Integer, Varchar(30) and DATE VMS.
Now when I look at the Input and Output properties window,
The External columns has the following data types.
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
The Output columns has the following data types
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
When I tried to change the length of the ADDRESS on the output column, I get the following error.
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.
Any thoughts.
Thanks,
SK
Did you try using a Data Conversion transformation?
Rafael Salas
|||Hi,
I did try to use the Data Conversion transformation. But when I execute the package, I get the truncation error at the Data Source Reader itself.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "ADDRESS" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039
Thanks,
SK
Hi,
Does anybody have inputs on this issue?
SK
|||It sounds like the provider you are using is reporting the length of the string incorrectly as 0. I've seen that before with a provider, but I don't recall what one... Is there a different provider you can try?
Otherwise, you might try changing the select statement to cast the value explicitly to a string of length 30 and see if the DataReaderSrc correctly creates the column then.
|||Unfortunately I can't use any other providers other than ADO.net as I have to use Oracle RDB ODBC to connect to RDB database.
Strangely, now the column is showing as text instead of Unicode string and I was able to use the data conversion transformation to convert back to Unicode String. Not sure what changed on the server. The only thing that I think of is we updated SQL Server with some updated from MS website.
Thanks,
SK
We had the exact same problem trying to pull data from Oracle Rdb. I went round & round but eventually found the solution.
Basically we had to install SQL*Net for Rdb (or OCI Services for Rdb) on our VMS server to make our Rdb database look like an Oracle database. Then we had to install Oracle client software on our client machine. I've used both Oracle 9.2 client and ODAC 10.2. You then set up your client connection using the Oracle Net Manager.
After you've got client & server configured, set up an SSIS connection manager using .Net Providers for OleDb -> Oracle Provider for OLE DB.
It's kind of a hassle to set up but it seems to work just fine.
Regards, Pat
Error when changing the length on DataReader Source
Hi,
I am trying to import data from Oracle RDB into SQL Server 2005 using SSIS. Created a ODBC data source to connect to Oracle and used DataReader Source component and ADO.net to connect to the ODBC data source.
Under the Component properties tab, the SQL Command looks something like this.
Select ID, ADDRESS, REVISED from ADDRESS
The data type for the source columns are Integer, Varchar(30) and DATE VMS.
Now when I look at the Input and Output properties window,
The External columns has the following data types.
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
The Output columns has the following data types
ID - four-byte signed integer [DT_I4]
ADDRESS - Unicode string [DT_WSTR], length = 0
REVISED - database timestamp [DT_DBTIMESTAMP]
When I tried to change the length of the ADDRESS on the output column, I get the following error.
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Error at Data Flow Task [DataReader Source [1]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
Is this the default length for the Unicode string type. I was not able to load the ADDRESS column as it gets truncated before I load it into destination. Even if I use Derived or Data Conversion transformation, the ADDRESS is getting truncated before it reaches this transformation.
Any thoughts.
Thanks,
SK
Did you try using a Data Conversion transformation?
Rafael Salas
|||Hi,
I did try to use the Data Conversion transformation. But when I execute the package, I get the truncation error at the Data Source Reader itself.
Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "ADDRESS" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039
Thanks,
SK
Hi,
Does anybody have inputs on this issue?
SK
|||It sounds like the provider you are using is reporting the length of the string incorrectly as 0. I've seen that before with a provider, but I don't recall what one... Is there a different provider you can try?
Otherwise, you might try changing the select statement to cast the value explicitly to a string of length 30 and see if the DataReaderSrc correctly creates the column then.
|||Unfortunately I can't use any other providers other than ADO.net as I have to use Oracle RDB ODBC to connect to RDB database.
Strangely, now the column is showing as text instead of Unicode string and I was able to use the data conversion transformation to convert back to Unicode String. Not sure what changed on the server. The only thing that I think of is we updated SQL Server with some updated from MS website.
Thanks,
SK
We had the exact same problem trying to pull data from Oracle Rdb. I went round & round but eventually found the solution.
Basically we had to install SQL*Net for Rdb (or OCI Services for Rdb) on our VMS server to make our Rdb database look like an Oracle database. Then we had to install Oracle client software on our client machine. I've used both Oracle 9.2 client and ODAC 10.2. You then set up your client connection using the Oracle Net Manager.
After you've got client & server configured, set up an SSIS connection manager using .Net Providers for OleDb -> Oracle Provider for OLE DB.
It's kind of a hassle to set up but it seems to work just fine.
Regards, Pat
Friday, February 17, 2012
error sqlcode -902
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[XTG Systems][InterBase6 ODBC Driver], I/O error for file, Error while trying to read from file, Reached the end of the file.
how do i fix this? :(Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[XTG Systems][InterBase6 ODBC Driver], I/O error for file, Error while trying to read from file, Reached the end of the file.
how do i fix this? :( Switch to a real RDBMS.|||Switch to a real RDBMS.There are no real RDBMS packages available for purchase (at least that meet the definition used by Codd and Date). Most of us make do with the tools that are commercially available like DB2, Microsoft SQL Server, Oracle, Sybase, etc. While Interbase doesn't often make that list, its biggest problem is its vendor support, not the underlying product itself.
-PatP
[XTG Systems][InterBase6 ODBC Driver], I/O error for file, Error while trying to read from file, Reached the end of the file.
how do i fix this? :(Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[XTG Systems][InterBase6 ODBC Driver], I/O error for file, Error while trying to read from file, Reached the end of the file.
how do i fix this? :( Switch to a real RDBMS.|||Switch to a real RDBMS.There are no real RDBMS packages available for purchase (at least that meet the definition used by Codd and Date). Most of us make do with the tools that are commercially available like DB2, Microsoft SQL Server, Oracle, Sybase, etc. While Interbase doesn't often make that list, its biggest problem is its vendor support, not the underlying product itself.
-PatP
Error Sorting
I need the results of a view sorted by date. When the order by date is added
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
Thanks
Niles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com
|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:
> Niles wrote:
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
David Gugick
Imceda Software
www.imceda.com
|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is added
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maximum
> of 8094"
> Is there a way around this?
> Thanks
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
Thanks
Niles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com
|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:
> Niles wrote:
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
David Gugick
Imceda Software
www.imceda.com
|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is added
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maximum
> of 8094"
> Is there a way around this?
> Thanks
Error Sorting
I need the results of a view sorted by date. When the order by date is adde
d
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
ThanksNiles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:
> Niles wrote:
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
David Gugick
Imceda Software
www.imceda.com|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is add
ed
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maxim
um
> of 8094"
> Is there a way around this?
> Thanks
d
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
ThanksNiles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:
> Niles wrote:
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
David Gugick
Imceda Software
www.imceda.com|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is add
ed
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maxim
um
> of 8094"
> Is there a way around this?
> Thanks
Error Sorting
I need the results of a view sorted by date. When the order by date is added
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
ThanksNiles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:
> Niles wrote:
> > I need the results of a view sorted by date. When the order by date
> > is added to the view I get the following error from SQL/ODBC SQL
> > Driver: "cannot sort a row of size 8813, which is greater than the
> > allowable maximum of 8094"
> > Is there a way around this?
> >
> > Thanks
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
--
David Gugick
Imceda Software
www.imceda.com|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is added
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maximum
> of 8094"
> Is there a way around this?
> Thanks
to the view I get the following error from SQL/ODBC SQL Driver:
"cannot sort a row of size 8813, which is greater than the allowable maximum
of 8094"
Is there a way around this?
ThanksNiles wrote:
> I need the results of a view sorted by date. When the order by date
> is added to the view I get the following error from SQL/ODBC SQL
> Driver: "cannot sort a row of size 8813, which is greater than the
> allowable maximum of 8094"
> Is there a way around this?
> Thanks
You can cut down the result set row size. Why is the row so large?
David Gugick
Imceda Software
www.imceda.com|||I reduced the number of columns returned and it works fine now. Why is the
number of columns a factor here?
Thanks
"David Gugick" wrote:
> Niles wrote:
> > I need the results of a view sorted by date. When the order by date
> > is added to the view I get the following error from SQL/ODBC SQL
> > Driver: "cannot sort a row of size 8813, which is greater than the
> > allowable maximum of 8094"
> > Is there a way around this?
> >
> > Thanks
> You can cut down the result set row size. Why is the row so large?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Niles wrote:
> I reduced the number of columns returned and it works fine now. Why
> is the number of columns a factor here?
>
8060 is the maximum row size for SQL Server. It's not so much the number
of columns, but the total byte size of all columns in a row. While SQL
Server will allow you to create a table with varchar type columns that
could potentially add up to more than 8060 bytes, you do get a warning
when the table is created. If at any point you change data in a row or
attempt to insert more than 8060 bytes in a row, you get an error.
Since you didn't post any SQL, I can only assume that your table is as
described above, or your columns in the query from multiple tables are
large enough that you're reaching the SQL Server limit.
If you are dealing with a large table or a large table is responsible
for most of the row size, I would encourage you to consider redesigning
the table. Large row sizes mean very low page density. This leads to
excessive page reading on some queries and a general slowdown on the
server when the table is accessed. If you can, you could put some of
that large variable data in a text/ntext column or possibly in another
table.
If you want other suggestions, post your DDL and SQL.
--
David Gugick
Imceda Software
www.imceda.com|||In addition to the other posts:
You could try the ROBUST PLAN optimizer hint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D1591C68-5A0E-41A0-A453-C8D7957E5F47@.microsoft.com...
>I need the results of a view sorted by date. When the order by date is added
> to the view I get the following error from SQL/ODBC SQL Driver:
> "cannot sort a row of size 8813, which is greater than the allowable maximum
> of 8094"
> Is there a way around this?
> Thanks
Error selecting text from a SQL database
Hello,
I have an application that used an access database and I am currently
migrating to SQL Server. The software is designed to use Odbc object
found in the .Net framework version 1.1. I have a simple select
statement that is driving me nuts. It works fine with the Access
databases but dies under SQL Server.
The statement is:
SELECT
settingValue
FROM
tblSettings
WHERE ( owner=@.owner and setting=@.setting )
settingValue is an ntext field and owner and setting are varchar fields.
When I try to execute the query I get an error:
ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
ntext, and image data types cannot be compared or sorted, except when
using IS NULL, or LIKE operator.
There are no indexes on any of the fields so I cannot figure out what
the problem is.
Mageos
Try this to see if you still get an error:
SELECT
owner,
setting,
settingValue
FROM
tblSettings
WHERE owner = @.owner
and setting=@.setting
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mageos" <matt.dot.osborne@.cox.dot.com> wrote in message
news:vbGyd.2281$wZ2.581@.newssvr13.news.prodigy.com ...
> Hello,
> I have an application that used an access database and I am currently
> migrating to SQL Server. The software is designed to use Odbc object
> found in the .Net framework version 1.1. I have a simple select
> statement that is driving me nuts. It works fine with the Access
> databases but dies under SQL Server.
> The statement is:
> SELECT
> settingValue
> FROM
> tblSettings
> WHERE ( owner=@.owner and setting=@.setting )
> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
> There are no indexes on any of the fields so I cannot figure out what
> the problem is.
> Mageos
|||> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
The error tells you exactly where the problem is. You are trying to do a
compare on an ntext field. Double check your create table statement. I think
you will find that either owner or setting is not of the datatype you
expected. What method of creating the tables in SQL did you use. I find that
often when I let SQL help me out converting data from an outside source, it
comes up with insane assumptions about what the datatype needs to be.
I tried reproducing the error with the script below and it works fine for
me.
create table test(
settingvalue ntext,
owner varchar(5),
setting varchar(5)
)
insert test (settingvalue, owner, setting)
values ('jgdfkjgiugsv8ohyohfef mne hevbe vmnerv', '1', '2')
declare @.owner varchar(5)
declare @.setting varchar(5)
set @.owner = '1'
set @.setting ='2'
select * from test where owner = @.owner and setting = @.setting
I have an application that used an access database and I am currently
migrating to SQL Server. The software is designed to use Odbc object
found in the .Net framework version 1.1. I have a simple select
statement that is driving me nuts. It works fine with the Access
databases but dies under SQL Server.
The statement is:
SELECT
settingValue
FROM
tblSettings
WHERE ( owner=@.owner and setting=@.setting )
settingValue is an ntext field and owner and setting are varchar fields.
When I try to execute the query I get an error:
ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
ntext, and image data types cannot be compared or sorted, except when
using IS NULL, or LIKE operator.
There are no indexes on any of the fields so I cannot figure out what
the problem is.
Mageos
Try this to see if you still get an error:
SELECT
owner,
setting,
settingValue
FROM
tblSettings
WHERE owner = @.owner
and setting=@.setting
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mageos" <matt.dot.osborne@.cox.dot.com> wrote in message
news:vbGyd.2281$wZ2.581@.newssvr13.news.prodigy.com ...
> Hello,
> I have an application that used an access database and I am currently
> migrating to SQL Server. The software is designed to use Odbc object
> found in the .Net framework version 1.1. I have a simple select
> statement that is driving me nuts. It works fine with the Access
> databases but dies under SQL Server.
> The statement is:
> SELECT
> settingValue
> FROM
> tblSettings
> WHERE ( owner=@.owner and setting=@.setting )
> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
> There are no indexes on any of the fields so I cannot figure out what
> the problem is.
> Mageos
|||> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
The error tells you exactly where the problem is. You are trying to do a
compare on an ntext field. Double check your create table statement. I think
you will find that either owner or setting is not of the datatype you
expected. What method of creating the tables in SQL did you use. I find that
often when I let SQL help me out converting data from an outside source, it
comes up with insane assumptions about what the datatype needs to be.
I tried reproducing the error with the script below and it works fine for
me.
create table test(
settingvalue ntext,
owner varchar(5),
setting varchar(5)
)
insert test (settingvalue, owner, setting)
values ('jgdfkjgiugsv8ohyohfef mne hevbe vmnerv', '1', '2')
declare @.owner varchar(5)
declare @.setting varchar(5)
set @.owner = '1'
set @.setting ='2'
select * from test where owner = @.owner and setting = @.setting
Subscribe to:
Posts (Atom)