Online Data Transform: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

OpenQuery using parameters

I need a little help with using parameter values in the where clause
with the OpenQuery statement.
What would the syntax for the following be if 10 was a variable? And
does the syntax change depending on the datasource? In my case I am
querying an Oracle database. But I can't get it work on an Oracle or
Access database.
SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
WHERE deptno = 10'
Many thanks to the answer for this.
johnny
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenetJohnny
Did you create a linked server to the Oracle database?
BOL has some examples how to create a linked server to Oracle adatabase.
SELECT * FROM OracleServer.Database.dbo.Table WHERE blblbl
Does the aboe example work for you?
For Access database you can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Johnny" <myob@.beatles.com> wrote in message
news:PBoof.631530$_o.29628@.attbi_s71...
>I need a little help with using parameter values in the where clause
> with the OpenQuery statement.
> What would the syntax for the following be if 10 was a variable? And
> does the syntax change depending on the datasource? In my case I am
> querying an Oracle database. But I can't get it work on an Oracle or
> Access database.
> SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
> WHERE deptno = 10'
> Many thanks to the answer for this.
> johnny
> -- Posted with NewsLeecher v3.0 Beta 6
> -- http://www.newsleecher.com/?usenet

OpenQuery to a LinkedServer

I have SQL Server 2000 with Pervasive SQL 2000i attached
as a linked server. When I run the following...
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT "START_ORDER_NO"
FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0
--The START_ORDER_NO field contains a 48
Changing the SET clause to ...
SET START_ORDER_NO = 1~9
--The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10
--The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
--The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
--The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value I write...
The LinkedServer is Pervasive SQL 2000i using 'OLE DB
Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code
page issue, but I haven't had any luck yet.
Any help would be greatly appreciated.
Hi Bob,
Thanks for your post.
From your descriptions, I understood that you would like to set the number
to be one and it appears to be something else. Correct me if I was wrong.
This issue seems strange, here are some steps I think you could make a try
to see whether it will make any further progress
First of all, try to use xp_enum_loedb_providers listed in the document
below to setup your linked server.
INF: xp_enum_oledb_providers Enumerates the OLE DB Providers
http://support.microsoft.com/?id=216575
Secondly, could you use INTEGER or UINTEGER in Pervasive.SQL 2000 instead
of NUMERIC(8,0)?
Pervasive.SQL 2000 Supported Data Types
http://www.pervasive.com/library/doc...BtrDType2.html
Unfortuantely, I do not have Pervasive SQL 2000 installed and cannot
reporduce it. If I may, I would like to suggest you seeing whether
Pervasive Software had meet this before.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||You understand correctly.
I am using 'OLEDB Provider for ODBC' or MSDASQL
The field types have been defined by the original vendor, Pervasive SQL
2000 is part of a proprietary ERP system. I don't have the luxury of
changing field definitions.
Thanks for the information though.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Bob,
Thanks for your prompt updates letting me know the status of the issue.
First of all, you may use (numeric(8,0), 0) to test whether it will make
any progress.
Secondly, what odbc driver you are using? I think you may try to check this
with that Pervasive ODBC Driver vendor about issue.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

OPENQUERY Question. How to update using 2 tables

All,

Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.

Some of the relevant information is as follows:

ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.

UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE

FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID

WHERE
UPPER(SiteAddress_New.PROCESS_CODE) = 'U'

Best Regards,

addi"addi" <addi_s@.hotmail.com> wrote in message
news:6f426fb3.0406090845.6afdbf32@.posting.google.c om...
> All,
> Can someone help me with the following SQL and help me write it in an
> OPENQUERY format. I am running the following code from a SQL Server 7
> box, trying to update a table in an Oracle Linked Server. The code
> runs fine, except it takes almost an hour to complete. I know if I run
> via OPENQUERY,I can get the same done in much less time.
> Some of the relevant information is as follows:
> ORACLE_HBCPRD04 is a linked Oracle Server.
> SITEADDRESS is a table in Oracle
> #SiteAddress_New is a table in SQL Server.
> UPDATE ORACLE_HBCPRD04...SITEADDRESS
> SET
> CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR1 END,
> CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR2 END ,
> CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR3 END,
> CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
> ELSE SiteAddress_New.CUST_ADDR4 END ,
> CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CTY_NM END,
> ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
> SiteAddress_New.ST_ABBR END,
> POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
> SiteAddress_New.POST_CD END,
> CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTY_NM END,
> CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
> SiteAddress_New.CNTRY_NM END,
> ADDR_STAT = NULL ,
> LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE
> FROM
> ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
> #SiteAddress_New SiteAddress_New ON
> SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID
> WHERE
> UPPER(SiteAddress_New.PROCESS_CODE) = 'U'
> Best Regards,
> addi

I don't really understand your question - OPENQUERY() executes entirely on
the linked server, so it would not be possible to join to a local MSSQL
table.

It's hard to say what the issue is without knowing more about how many rows
are involved, but you might want to look at the REMOTE join hint, to see if
a remote join is faster. This may help if the MSSQL table is much smaller
than the Oracle one.

Openquery Help Requested

sql server = server A
sybase = server B (set up as a linked server in A)
I have a stored procedure in A that contains the following statement:
SELECT * FROM OPENQUERY(B, 'exec storedproc')
which runs the procedure in B just fine. It truncates a table in B and
then populates it with data from various queries. The issue is later in
the stored procedure issued from A I perform an insert where I grab the
data inserted into the table in B and insert that data into a table in
A. It seems as though everytime a number of records are inserted into
the table in A the statement above: SELECT * FROM OPENQUERY(B, 'exec
storedproc') is run again and again. Does anybody know why this is
occurring and how I can stop this from occurring?
Thanks,
E> the stored procedure issued from A I perform an insert where I grab the
> data inserted into the table in B and insert that data into a table in
> A. It seems as though everytime a number of records are inserted into
> the table in A the statement above: SELECT * FROM OPENQUERY(B, 'exec
> storedproc') is run again and again. Does anybody know why this is
> occurring and how I can stop this from occurring?
You mean that later within 'storedproc' you are perfoming insert into a
table that is resided on server 'A' and you have seen in SQL Server Profiler
that it runs SELECT * FROM OPENQUERY(B, 'exec> storedproc') many times. Am
I right?
"w" <ebrooks@.sbcglobal.net> wrote in message
news:1136953074.780909.282800@.g14g2000cwa.googlegroups.com...
> sql server = server A
> sybase = server B (set up as a linked server in A)
> I have a stored procedure in A that contains the following statement:
> SELECT * FROM OPENQUERY(B, 'exec storedproc')
> which runs the procedure in B just fine. It truncates a table in B and
> then populates it with data from various queries. The issue is later in
> the stored procedure issued from A I perform an insert where I grab the
> data inserted into the table in B and insert that data into a table in
> A. It seems as though everytime a number of records are inserted into
> the table in A the statement above: SELECT * FROM OPENQUERY(B, 'exec
> storedproc') is run again and again. Does anybody know why this is
> occurring and how I can stop this from occurring?
> Thanks,
> E
>|||Well the reason I know that it keeps reissuing that statement is that
while the table in A is being populated after a number of records have
been inserted the table in B is truncated and repopulated again and
this cycle goes on until the table in A has been completely populated.
E
Uri Dimant wrote:
> You mean that later within 'storedproc' you are perfoming insert into a
> table that is resided on server 'A' and you have seen in SQL Server Profil
er
> that it runs SELECT * FROM OPENQUERY(B, 'exec> storedproc') many times. A
m
> I right?
>
> "w" <ebrooks@.sbcglobal.net> wrote in message
> news:1136953074.780909.282800@.g14g2000cwa.googlegroups.com...|||Well the reason I know that it keeps reissuing that statement is that
while the table in A is being populated after a number of records have
been inserted the table in B is truncated and repopulated again and
this cycle goes on until the table in A has been completely populated.
E
Uri Dimant wrote:
> You mean that later within 'storedproc' you are perfoming insert into a
> table that is resided on server 'A' and you have seen in SQL Server Profil
er
> that it runs SELECT * FROM OPENQUERY(B, 'exec> storedproc') many times. A
m
> I right?
>
> "w" <ebrooks@.sbcglobal.net> wrote in message
> news:1136953074.780909.282800@.g14g2000cwa.googlegroups.com...

OpenQuery Error?

Hi All,

I have an Openquery insert within a trigger. When i go to check the syntax it errors with the following message...

Error 403. Invalid Error for Data Type. Operator equals add, type equals varchar.

Below is my Openquery statement

SET @.TSQL2 = 'INSERT INTO ' +
'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
'VALUES(''' + @.EMPLOYEE_NO + ''', ''E'', ''' + @.ADDRESS1 + ''', ''' + @.ADDRESS2 + ''', ''' + @.SUBURB + ''', ''' + @.COUNTRY + ''', ''' + @.POSTCODE + ''', ' +
'''' + @.AREACODE + ''', ''' + @.WORK1 + ''', ''' + @.ECD_SURNAME + ''', ''' + @.ECD_FIRSTNAME + ''', ''' + @.ECD_RELATIONSHIP + ''', '''', ''' + @.STATE + ''', ''' + @.MOBILE + ''', ' +
''' IT :21105101017 '', ''' + @.KEYE + ''')'

All variables are varchar except for @.KEYE which is Varbinary and this is the one that is causing the error on, because if I take it out the syntax is correct.

Any ideas why this occurs and how do I add a varbinary variable to the statement??

Regards
Anthonyyou are trying to concat string with varbin - this will not work. you have to convert to varchar before...|||Originally posted by msieben
you are trying to concat string with varbin - this will not work. you have to convert to varchar before...

How do I do this?? Can you show me an example.|||Originally posted by aljubicic
:
Below is my Openquery statement

SET @.TSQL2 = 'INSERT INTO ' +
'OPENQUERY([TRILOGY-TSG],''Select DET_NUMBERA, ADR_TYPEA, ADR_LINE_1A, ADR_LINE_2A, ADR_LINE_3A, ADR_LINE_4A, ADR_PST_CODEA, ADR_AREA_CODA, ADR_PHONEA, ADR_CNT_SURA, ' +
'ADR_CNT_NAMEA, ADR_CNT_RELA, FILLER_01A, ADR_STATEA, ADR_MOBILEA, FILLER_02A, SRGTE_KEY_1 FROM CHRISCS.EMADR'') ' +
'VALUES(''' + @.EMPLOYEE_NO + ''', ''E'', ''' + @.ADDRESS1 + ''', ''' + @.ADDRESS2 + ''', ''' + @.SUBURB + ''', ''' + @.COUNTRY + ''', ''' + @.POSTCODE + ''', ' +
'''' + @.AREACODE + ''', ''' + @.WORK1 + ''', ''' + @.ECD_SURNAME + ''', ''' + @.ECD_FIRSTNAME + ''', ''' + @.ECD_RELATIONSHIP + ''', '''', ''' + @.STATE + ''', ''' + @.MOBILE + ''', ' +
''' IT :21105101017 '', ''' + @.KEYE + ''')'
:

the code you posted will build the insert statement togeter and put it into @.TSQL2 as a varchar. so everything you put toghether needs to be varchar or to be converted to (either by the sql-server or by using "convert(". i don't know what you really want to do - but look alt sp_executesql in BOL. you can store your statement into varchar and use parameters, which will be replaced at runtime. so you don't have to push your values list into the varchar at all.

OPENQUERY and string

Does anyone know how to include a string in the statement of an open query?

I want to execute the following query:

select * from TEST where A like 'A'

But if use this it in an openquery like it follows

SELECT *

FROM OPENQUERY (MD_AS400, 'select * from TEST where A like 'A'')

The 'A' is not recognize like a string.

This is due to the single quote around 'A'

try this

''A'''

rule is if u need a quoted string put TWO quotes.

Gurpreet S. Gill

|||

You should escape quote by putting another quote.

So your query would be

' select * from TEST where A like ''A'' '

|||

Lot of lanugaues accepted the escape sequence char starts with \.

But in SQL Server (i remember in VB & MDX also) the same character will be repeated.

Code Snippet

SELECT *

FROM OPENQUERY (MD_AS400, 'select * from TEST where A like ''A''')

sql

Wednesday, March 28, 2012

Openning an access database using ADO.NET

What is the proper way to open an access database using ADO.NET (that is, using c#) ?
I have the following code, but somehow it does not compile saying that it doesn't find the mdb file called PhoneBook :

OleDbConnection Myconnection = null;
Myconnection = new OleDbConnection(@."Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PhoneBook");
Myconnection.Open();

What am I doing wrong here ?
All help appreciated !:)Stab in the dark:
change C:\PhoneBook to include the file extension i.e. C:\PhoneBook.mdb|||http://www.connectionstrings.comsql

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
Thanks
I have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:

> I have been getting the following winmgmt error in my application event log
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
ThanksI have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:
> I have been getting the following winmgmt error in my application event log
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within 10
> where TargetInstance isa 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>sql

Opening up SQL 2005 Management studio generates Winmgmt error

I have been getting the following winmgmt error in my application event log
when I connect to a clustered SQL 2005 server using SQL Management Studio.
Has anyone ever encountered this and does anyone know how to stop this?
Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.
ThanksI have the same error. It appears mmc cannot connect to wmi class.
"Steven" wrote:

> I have been getting the following winmgmt error in my application event lo
g
> when I connect to a clustered SQL 2005 server using SQL Management Studio.
> Has anyone ever encountered this and does anyone know how to stop this?
> Event filter with query "select * from __InstanceModificationEvent within
10
> where TargetInstance ISA 'Win32_Service'" could not be (re)activated in
> namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of err
or
> 0x80041010. Events may not be delivered through this filter until the
> problem is corrected.
> Thanks
>
>
>
>
>

Opening up SQL 2005 Management studio generates Winmgmt erro

Event filter with query "select * from __InstanceModificationEvent within 10
where TargetInstance isa 'Win32_Service'" could not be (re)activated in
namespace "//./root/Microsoft/SqlServer/ComputerManagement" because of error
0x80041010. Events may not be delivered through this filter until the
problem is corrected.

ThanksI have the same problem any help would be appreciated :D
|||

We get the same "select *" error message and found that it was probably resulting from a unisys sentinel script; practically the exact verbage could be found in several such .vbs scripts. Certainly some event is triggered and the sentinel software attempts to run one of the scripts and fails. The failure may be due to the sentinel software trying to perform a sql2000 task on a sql2005 server. We're still hunting for an exact cause though.

I hope that this points you in the right direction for your particular case.

Opening up SQL 2005 Management studio generates Winmgmt erro

ThanksI have the same problem any help would be appreciated :D|||

I hope that this points you in the right direction for your particular case.

Friday, March 23, 2012

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

I have a stored procedure which has the following line:
select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
This stored procedure was called from a VB application. It's odd that
ONLY when ADO connection user need SA database role
AND \\MachineName\shared\account.xls was physical located in the same
machine of DATABASE
the stored procedure would work.
Otherwise it returned error like:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give an
y
information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the error. 0 7399
-2147217900 Microsoft OLE DB Provider for SQL Server 42000
Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
Any suggestion?Because the security reson, I don't want ADO connection user has the SA role
,
and Excel file can located in any shared folder in the LAN.
"david" wrote:

> I have a stored procedure which has the following line:
> select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
> This stored procedure was called from a VB application. It's odd that
> ONLY when ADO connection user need SA database role
> AND \\MachineName\shared\account.xls was physical located in the same
> machine of DATABASE
> the stored procedure would work.
> Otherwise it returned error like:
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
any
> information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provide
r
> for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the error. 0 7399
> -2147217900 Microsoft OLE DB Provider for SQL Server 42000
> Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
> Any suggestion?
>sql

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

I have a stored procedure which has the following line:
select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
This stored procedure was called from a VB application. It's odd that
ONLY when ADO connection user need SA database role
AND \\MachineName\shared\account.xls was physical located in the same
machine of DATABASE
the stored procedure would work.
Otherwise it returned error like:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give any
information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the error. 0 7399
-2147217900 Microsoft OLE DB Provider for SQL Server 42000
Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
Any suggestion?Because the security reson, I don't want ADO connection user has the SA role,
and Excel file can located in any shared folder in the LAN.
"david" wrote:
> I have a stored procedure which has the following line:
> select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
> This stored procedure was called from a VB application. It's odd that
> ONLY when ADO connection user need SA database role
> AND \\MachineName\shared\account.xls was physical located in the same
> machine of DATABASE
> the stored procedure would work.
> Otherwise it returned error like:
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give any
> information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
> for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the error. 0 7399
> -2147217900 Microsoft OLE DB Provider for SQL Server 42000
> Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
> Any suggestion?
>

Opendatasource problem

hi to every one...

To delete records in tableA which is not found in tableB i am using the following :
DELETE tableA from tableA aa left join tableB bb on aa.fld = bb.fld where bb.fld is null
** This will work fine **
but if i Use similer but using OPENDATASOURCE , like this:

delete OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\data2004.mdb";
User ID="Admin";Password="";Jet OLEDB:Database Password="lea"')...tableA
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\data2004.mdb";
User ID="Admin";Password="";Jet OLEDB:Database Password="lea"')...tableA aa left join tableB bb on aa.lno=ba.lno where b.lno is null

the result is deleteing whole of TableA....

any help is appriciated
Thanks.Does this syntax work?:

delete
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\data2004.mdb";
User ID="Admin";Password="";Jet OLEDBatabase Password="lea"')...tableA aa left join tableB bb on aa.lno=ba.lno where b.lno is null|||this will not work , it will give the following error:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'aa'.

Opendatasource

I was able to use the following select qry with an OpenDataSource, but I
recieve and error when I change it to an Update qry.
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
but when I change it to and Update it errors out. See code below
update h
set h.LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
I get the following error; Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'h'.
Any suggestions.
Thank you in advance for your helpdouble post by mistake?
"bhorwitz" <bhorwitz@.discussions.microsoft.com> wrote in message
news:33E09107-36E6-4131-A5F8-7D1053521CD3@.microsoft.com...
> I was able to use the following select qry with an OpenDataSource, but I
> recieve and error when I change it to an Update qry.
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> but when I change it to and Update it errors out. See code below
> update h
> set h.LOCATION = b.Location
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> I get the following error; Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'h'.
> Any suggestions.
> Thank you in advance for your help|||try this..
untested..
update h
set LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1

Friday, March 9, 2012

OO Database Design performance question

I am hoping someone has had a similar table design scenario and has a slick solution that i am not thinking of.

Basically I have the following tables (kind of OO based design in this case).

Create Table Entity (EntityID int, EntityTypeID)

Create Table Company (EntityID, CompanyName)

Create Table Contact (EntityID, FirstName, LastName)

Create Table Group (EntityID, GroupName)

Basically an Entity can be a Company, a Contact or a Group based on the EntityTypeID

The trick becomes when I get an EntityID is how to get the name of the Entity in an efficient manner.

You can make a view that does a Union to all three tables like such...

select CompanyName as Name from Company

union all

Select FirstName + LastName as Name from Contact

union all

Select GroupName as Name from Group

Perfomance wise this stinks. I have also tried a left join to all the tables and that performs better, but still not great when you are joining a bunch of records (such as Orders back to get the name of the Entity on the order).

I was thinking of an indexed view but it doesn't let you do a left join or a union. A trigger also comes to mind but I have other triggers on those tables and I really don't want to create any more. I could also add all the different name columns to the entity table but then I end up with a bunch of null values.

Any creative ideas on how to create a function, view, indexed view, etc that will make this efficient?

Thanks!

OO designs often fail to translate well to a high-performance transactional database. Subtypes are especially troublesome in this regard.

The problem here is one of abstraction. The more you abstract something, the more it becomes difficult to query in a practical way. Some package software takes this to the extreme with the concept of the "thing" table. works great on the whiteboard but kills performance as tables get large.

A function is probably going to be your best option. Write a T-SQL function that chooses the table for the lookup based on the type column. You can either pass in the type as a parameter or look it up in the function.

something like:

if @.type = 1 then
select @.retval = companyname from Company

if @.type = 2
select @.retval = firstname+lastname from Contact

if @.type = 3
select @.retval = groupname from group

|||

The key here is that if you are going to use subtypes, (and I am a big fan) you must keep everything that is common between them in the same table, otherwise you are just asking for trouble. The problem here is that you have to be careful with subtypes not to overdo them, and in ths case I think you may have done that. In SQL, if you are going to do a subtype, the supertype really needs be compellingly similar and have a similar purpose.

For example, a vehicle type might be proper for an auto shop, because all vehicles are tracked the same, then each subtype has some differences. Or you might have a patient supertype (records, names bills, etc) and then subtypes for male patients and female ones.

In your case, the subtypes have nothing to do with one another so there is no point and all you are doing is forcing this OO design into a database. You could fix your performance problems by adding a Name column to the Entity table and update it using a trigger on each of the supertypes, but unless there really is some compelling similarities that merit a supertype, you should rethink your design.

|||Take a look at the AdventureWorks sample database. IMHO, it has a very good normalized representaion of Contacts/People/Customers, etc.|||I agree that a function will work however when it comes to searching based on name the function is going to scan which obviously wouldn't be good.|||

i see your point however I argue that it does need an OO design. For example a Customer that orders can be a contact or a company. This really is only an issue when dealing with searching. A contact for name has a Salutation, FirstName, LastName, MiddleName, Suffix while a company might have CompanyName and DBAName. Both are customers however their "name" structure is entirely different. Would you then put all of those fields on the Entity table and leave some null depending on the EntityTypeID? I can see going with a Name1 and Name2 column on the Entity table and those fields have different meanings based on the EntityTypeID. Thoughts?

|||

It is not really an OO design, it is a common database thing. In your case, if you do mean a customer, which can be a person or a company, then yes, this is a valid thing to do. However, I am not so sure I wouldn't make the user choose the subtype before doing a search, since it is pretty unlikely that they wouldn't know if they were looking for one or the other.

I might also do the search in each table seperate from one another, perhaps in different connections from your UI. Either way I would certainly not create generic columns for names, as that would make the design harder to use, and I wouldn't put the names in the customer table with nulls where values don't quite work because then the searching issues are exactly the same (perhaps worse) than with two tables.

Otherwise, like I said before, I would add a name column to the customer table that was managed by a trigger on the child tables. So I would populate the customer.name column with person.firstName + ' ' + person.lastName (or something like it) for persons, and company.name for the companies, if you really need to search both in a single statement.

|||

Stark77 wrote:
I agree that a function will work however when it comes to searching based on name the function is going to scan which obviously wouldn't be good.

I don't think SQL Server has function-based indexes like Oracle, however you might be able to create an indexed view that includes the function as a column.

if you schemabind your function and your view (the view will have to be pretty simple) then this may allow you to create an indexed view, which is similar to a materialized view in Oracle, and that may help your performance.

worth a try if you haven't thought of this option.

Another option would be to use a trigger to denormalize the correct name into the parent table.

Wednesday, March 7, 2012

Only text pointers are allowed in work tables

..Bill.
Threads active in .programming. Please do not post the same question
multiple times to multiple newsgroups.
"Bill" <no@.no.com> wrote in message
news:%23Tzghb9wFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Using SS 2000. I have a UNION ALL query that includes a TEXT column.
> When I add an ORDER BY clause I get the following error.
> "Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table."
> So far, Google has failed to find a work around. Any suggestions?
> Paradox can do this query. I cannot believe that SQL Server can't.

> --
> .Bill.

Only text pointers are allowed in work tables

.Bill.Threads active in .programming. Please do not post the same question
multiple times to multiple newsgroups.
"Bill" <no@.no.com> wrote in message
news:%23Tzghb9wFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Using SS 2000. I have a UNION ALL query that includes a TEXT column.
> When I add an ORDER BY clause I get the following error.
> "Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table."
> So far, Google has failed to find a work around. Any suggestions?
> Paradox can do this query. I cannot believe that SQL Server can't.

> --
> .Bill.

Only text pointers are allowed in work tables

Using SS 2000. I have a UNION ALL query that includes a TEXT column.
When I add an ORDER BY clause I get the following error.
"Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table."
So far, Google has failed to find a work around. Any suggestions?
Paradox can do this query. I cannot believe that SQL Server can't.:)
--
.Bill.Threads active in .programming. Please do not post the same question
multiple times to multiple newsgroups.
"Bill" <no@.no.com> wrote in message
news:%23Tzghb9wFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Using SS 2000. I have a UNION ALL query that includes a TEXT column.
> When I add an ORDER BY clause I get the following error.
> "Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table."
> So far, Google has failed to find a work around. Any suggestions?
> Paradox can do this query. I cannot believe that SQL Server can't.:)
> --
> .Bill.

Friday, March 30, 2012

OpenQuery using parameters

OpenQuery to a LinkedServer

OPENQUERY Question. How to update using 2 tables

Openquery Help Requested

OpenQuery Error?

OPENQUERY and string

Wednesday, March 28, 2012

Openning an access database using ADO.NET

Opening up SQL 2005 Management studio generates Winmgmt error

Opening up SQL 2005 Management studio generates Winmgmt error

Opening up SQL 2005 Management studio generates Winmgmt error

Opening up SQL 2005 Management studio generates Winmgmt erro

Opening up SQL 2005 Management studio generates Winmgmt erro

Friday, March 23, 2012

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

Opendatasource problem

Opendatasource

Friday, March 9, 2012

OO Database Design performance question

Wednesday, March 7, 2012

Only text pointers are allowed in work tables

Only text pointers are allowed in work tables

Only text pointers are allowed in work tables

Online Data Transform

Blog Archive

About Me