I have recently seen the OPENQUERY function in a stored procedure which
was used to INSERT values into a table on a remote server. However, I
normally use the sp_executesql stored proc.
Can any one shed some light on which method is better and in what
cases?
The following code is from Microsoft website, not my own.
http://msdn.microsoft.com/library/d...br />
5xix.asp
<MS CODE>
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
</MS CODE>
VS.
<MYCODE>
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
-- OR
OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
</MYCODE>
Thanks for your time folks.
Johnny DWhy would you even consider sp_executesql in your case? There is no need to
use dynamic sql to access a linked server.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148649898.280530.174390@.j73g2000cwa.googlegroups.com...
>I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
> The following code is from Microsoft website, not my own.
> http://msdn.microsoft.com/library/d... />
z_5xix.asp
> <MS CODE>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
>
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
> </MS CODE>
> VS.
> <MYCODE>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
> -- OR
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
> </MYCODE>
>
> Thanks for your time folks.
>
> Johnny D
>|||Sorry Andrew, this was an oversight in me writing a simple query to
illustrate my question...
The reason for this is I would use a cursor to loop through my
different servernames
DECLARE @.rc INT
DECLARE @.v_sql NVARCHAR(4000)
DECLARE @.vc_servername VARCHAR(100)
DECLARE c_myservers CURSOR
FOR SELECT
servername
FROM listservers
WHERE active=1
FOR READ ONLY
OPEN c_myservers
FETCH NEXT FROM c_myservers INTO @.vc_servername
WHILE (@.@.FETCHSTATUS = 0 )
BEGIN
SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
'].dbname.joe.titles'
EXEC @.rc = sp_executesql @.v_sql
-- if @.rc <> 0
-- etc...
FETCH NEXT FROM c_myservers INTO @.vc_servername
END
CLOSE c_myservers
DEALLOCATE c_myservers
sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'|||Well one key point of using OPENQUERY is that it passes the statement to the
other server where it is executed as is. That way the remote server can
choose the proper plan for that statement without regard to what the rest of
the statement is that was issued locally. Kind of hard to explain but it is
truly a pass-through query where as a linked server query may be influenced
by the rest of the statement. For instance a join to a local table.
Andrew J. Kelly SQL MVP
"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1148652836.663893.44660@.g10g2000cwb.googlegroups.com...
> Sorry Andrew, this was an oversight in me writing a simple query to
> illustrate my question...
> The reason for this is I would use a cursor to loop through my
> different servernames
> DECLARE @.rc INT
> DECLARE @.v_sql NVARCHAR(4000)
> DECLARE @.vc_servername VARCHAR(100)
> DECLARE c_myservers CURSOR
> FOR SELECT
> servername
> FROM listservers
> WHERE active=1
> FOR READ ONLY
> OPEN c_myservers
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> WHILE (@.@.FETCHSTATUS = 0 )
> BEGIN
> SET @.v_sql = N'SELECT name, id FROM ['+@.vc_servername +
> '].dbname.joe.titles'
> EXEC @.rc = sp_executesql @.v_sql
> -- if @.rc <> 0
> -- etc...
> FETCH NEXT FROM c_myservers INTO @.vc_servername
> END
> CLOSE c_myservers
> DEALLOCATE c_myservers
>
> sp_executesql N'SELECT name, id FROM OracleSvr.dbname.joe.titles'
>|||Johnny D (john.dacosta@.gmail.com) writes:
> I have recently seen the OPENQUERY function in a stored procedure which
> was used to INSERT values into a table on a remote server. However, I
> normally use the sp_executesql stored proc.
> Can any one shed some light on which method is better and in what
> cases?
I think they are as comparable as apples and oranges.
Apparently you loop over servers. That is nothing OPENQUERY can help
you with - the server name must be a constant.
Rather your choice is between:
SELECT @.sql = 'SELECT ... FROM ' + @.server + 'catalog.schema.tbl'
and
SELECT @.sql = 'SELECT ... FROM OPENQUERY(' + @.server + ', ' +
'''SELECT ... FROM catalog.schema.tbl'')'
That is, accessing the table in four-partnotation, or running a
passthrough query.
><MYCODE>
> OracleSvr..sp_executesql N'SELECT name, id FROM dbname.joe.titles'
></MYCODE>
Ehum, I don't think you will find sp_executesql on Oracle...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Friday, March 30, 2012
Openquery q
Hi,
INSERT INTO my table (c1, c2, .cn)
SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"')
How can be retrieved only values From my table2( in the
linked server) that are NOT IN mytable ( or values that
are > max(values of my table )?
(The linked server is an Access, only openquery
statements are working)
Thanks for any suggestions.
DobbyThis is completely untested, but it may give you enough information to =get started...
SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn FROM "mytable2"') A
LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =3D B.PrimaryKeyColumn
WHERE B.PrimaryKeyColumn IS NULL
You could also try to write it using NOT EXISTS as well as NOT IN
-- Keith, SQL Server MVP
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message =news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
> Hi,
> > > INSERT INTO my table (c1, c2, .cn)
> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn > FROM "mytable2"')
> > How can be retrieved only values From my table2( in the > linked server) that are NOT IN mytable ( or values that > are > max(values of my table )?
> > (The linked server is an Access, only openquery > statements are working)
> > Thanks for any suggestions.
> > Dobby
> >|||Hi Keith,
It works fine!
Thank you very much!
Dobby
>--Original Message--
>This is completely untested, but it may give you enough
information to get started...
>SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"') A
>LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =B.PrimaryKeyColumn
>WHERE B.PrimaryKeyColumn IS NULL
>You could also try to write it using NOT EXISTS as well
as NOT IN
>--
>Keith, SQL Server MVP
>"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
>> Hi,
>>
>> INSERT INTO my table (c1, c2, .cn)
>> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
>> FROM "mytable2"')
>> How can be retrieved only values From my table2( in the
>> linked server) that are NOT IN mytable ( or values
that
>> are > max(values of my table )?
>> (The linked server is an Access, only openquery
>> statements are working)
>> Thanks for any suggestions.
>> Dobby
>>
>.
>
INSERT INTO my table (c1, c2, .cn)
SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"')
How can be retrieved only values From my table2( in the
linked server) that are NOT IN mytable ( or values that
are > max(values of my table )?
(The linked server is an Access, only openquery
statements are working)
Thanks for any suggestions.
DobbyThis is completely untested, but it may give you enough information to =get started...
SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn FROM "mytable2"') A
LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =3D B.PrimaryKeyColumn
WHERE B.PrimaryKeyColumn IS NULL
You could also try to write it using NOT EXISTS as well as NOT IN
-- Keith, SQL Server MVP
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message =news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
> Hi,
> > > INSERT INTO my table (c1, c2, .cn)
> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn > FROM "mytable2"')
> > How can be retrieved only values From my table2( in the > linked server) that are NOT IN mytable ( or values that > are > max(values of my table )?
> > (The linked server is an Access, only openquery > statements are working)
> > Thanks for any suggestions.
> > Dobby
> >|||Hi Keith,
It works fine!
Thank you very much!
Dobby
>--Original Message--
>This is completely untested, but it may give you enough
information to get started...
>SELECT A.* FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
FROM "mytable2"') A
>LEFT OUTER JOIN mytable B ON A.PrimaryKeyColumn =B.PrimaryKeyColumn
>WHERE B.PrimaryKeyColumn IS NULL
>You could also try to write it using NOT EXISTS as well
as NOT IN
>--
>Keith, SQL Server MVP
>"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:0c3201c352cd$6a95c780$a501280a@.phx.gbl...
>> Hi,
>>
>> INSERT INTO my table (c1, c2, .cn)
>> SELECT * FROM OPENQUERY (SS, 'SELECT c1, c2, .cn
>> FROM "mytable2"')
>> How can be retrieved only values From my table2( in the
>> linked server) that are NOT IN mytable ( or values
that
>> are > max(values of my table )?
>> (The linked server is an Access, only openquery
>> statements are working)
>> Thanks for any suggestions.
>> Dobby
>>
>.
>
Openquery from SQL Server to Oracle error
I want to insert records into an Oracle 8.03 database from MS SQL 2000. I have created a link and have used OPENQUERY to successfully query my Oracle tables. See example, DEV is the LINK name. I need to insert and update records from MS SQL to Oracle and also I need update MS SQL from
Oracle. Can you please give me a working example of insert and update?
example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
It makes sense that this update would work, but it got WORSE after running this:
update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
Connection Broken
From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.
The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.
Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
There are 3 articles on technet that may help or are just a wild goose chase.
PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:
FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:
FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:
UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1
Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):
UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.
I will try the syntax you suggested early next week (the server crashed and needs new drives).
What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.
In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.
SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.
Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.
FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106
Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.
Oracle. Can you please give me a working example of insert and update?
example that works:
select *
from OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
It makes sense that this update would work, but it got WORSE after running this:
update
OPENQUERY(DEV, 'SELECT *
FROM USER.ORDERS_ALL')
set last_updated_by = 3
where orders_id = 1
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.
Connection Broken
From now on, I cannot get the link to work AT ALL! It is so strange. I have rebooted the machine with SQL Server and the Link on it. I added a new link. (Both show up as valid links.) I have gone into ODBC and tested the connection successfully. The Oracle server I am linked to is up and running and I can select from the table.
The only help I can find on Microsoft that is similar says I need SQL Server 2000 service pack 2, which I already have installed.
Now after getting this error, in SQL Server when I try to display the tables for the linked server in the Enterprise Mgr or run the simple select query (the first above) I get no response at all. The query goes off and 30 minutes later I have to break out of the SQL Query Analyzer or Enterprise Mgr because NOTHING happened except the logs show me as having timed out. I left the machine for hours to see if there were queries that needed to complete or something?? No luck -- it does not give results from the query.
:rolleyes:if you want to do that in the easy way, create view out of table of oracle database in sql server and then insert the view, but take care when you insert into the view you should take in the consideration all fields even if its null values
:)|||I don't think you can UPDATE using the OPENQUERY, however have you tried using sp_addlinkedserver and then doing the UPDATE
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'MyOracle',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'MyServer'
GO
UPDATE MyOracle...ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1|||Yes, I used sp_addlinkedserver to create the link. Openquery is supposed to work for insert and update. The update statement you suggested doesn't work and that is why Openquery is needed.|||Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
There are 3 articles on technet that may help or are just a wild goose chase.
PRB: Installing DA SDK Causes SQL Distributed Queries to Fail (Q196292) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196292)
After installing the Microsoft Data Access 2.0 SDK, the following errors may occur when trying to perform a SQL Server 7.0 distributed query:
FIX: Cannot Use Dynamic SQL Statements Within OPENQUERY (Q291376) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q291376)
An Access Violation (AV) may occur if you use the OPENQUERY function to execute a stored procedure that has these properties:
FIX: MDX Queries from Query Analyzer to a Linked Analysis Server Result in Fatal Exception (Q316295) (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316295)
When you execute a Multidimensional Expressions (MDX) query against a SQL Server Linked Analysis Server configured with the|||Originally posted by achorozy
[B]Have you tried rewriting the query by bring the WHERE clause into the OPENQUERY
UPDATE
FROM OPENQUERY(DEV, 'SELECT * FROM USER.ORDERS_ALL WHERE orders_id = 1')
SET last_updated_by = 3
Good idea, but no luck. Thank you!
:D|||Here is a solution using T-sql four part name convention - the previous solution listed like this was missing the Oracle Schema name:
UPDATE DEV..USER.ORDERS_ALL
SET last_updated_by = 3
WHERE orders_id = 1
Alternately, this should also work with OPENQUERY (note that Microsoft recommends the 'where 1 = 2' clause to prevent rows from being returned, which would add overhead to the query, and most likely cause the query to fail):
UPDATE OPENQUERY(DEV, 'Select * from USER.ORDERS_ALL where 1 = 2)
SET last_updated_by = 3
WHERE orders_id = 1
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.|||Originally posted by zokrc
I have used both syntaxes successfully, but not until my dba set up our Ole DB provider to handle Heterogeneous updates/inserts (required a registry change). Hope this helps.
I will try the syntax you suggested early next week (the server crashed and needs new drives).
What do you mean by the above? Is that on the MS SQL Server box? How do you do it and which Ole DB provider?|||I should preface my response by saying that this only concerns you if you are trying to perform changes on the Oracle side as a part of a Sql Server distributed transaction (e.g. a transaction which can be rolled back). If you just want to make updates to the Oracle side, the syntax I have provided should stand on its own.
In my instance, I needed to include my updates/deletes/inserts to Oracle as a part of a Sql Server stored procedure which contained a Distributed Transaction. That way, if anything went wrong either side of the procedure (Oracle or SS), I would be able to roll back transactions in both databases.
SQL Server generally uses the MSDAORA Ole DB provider located on the SQL Server box to talk to Oracle. DTC (Distributed Transaction Coordinator) is the Sql Server component which actually manages the transaction and implements the appropriate Ole DB provider for executing heterogeneous queries.
Each ole db provider has certain properties which can be set that describe what functionality the provider will and won't support. To get distributed transactions to work with the MSDAORA, the ITransactionJoin(see books online for more info on this) property should be set accordingly. I believe this property can be set for the linked server through Enterprise Manager.
FINALLY - what I made reference to in my previous post was a problem we ran into where our MDAC registry settings were not set properly (a lot of things have to be in sync for Distributed Transactions to work). Here is the link on Microsoft's support site on how to do this (very complete!): http://search.support.microsoft.com/search/viewDoc.aspx?docID=KC.Q280106&dialogID=16829074&iterationID=1&sessionID=anonymous|15672521&url=kb;en-us;Q280106
Again, though, if your transactions aren't a part of a distributed transaction, you probably won't have to worry about this part. Let me know if you have any other questions.
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.
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.
Friday, March 23, 2012
OPEN XML
I have to insert one table by using the concept OPENXML
But i want to insert a table lot of fields i require, in the open XML few fields fields are there , so i want to selet some fields from other table
can u guide me for this scenario to INSERT some fields from one table and some in OPENXML as a single insert statement
Awaiting for Reply PLease
Thanx
Consider using the openXml to create a temporary table first. Then join the two tables as the base for the insert.
Friday, March 9, 2012
Open a TEXTE file with SQL server (stored procedure)
Hi,
I would like to open a *.log file (TEXTE format) from un stored
procedure SQL SERVER. After that, i just want to read each lines and
insert each line in a sql server table.
With that, i'll could schedule this stored procedure in the jobs of
sql server
Thant a lot for your help.
Alex.Have a look at BULK INSERT in BOL
--
HTH
Ryan Waight, MCDBA, MCSE
"alexfayard" <alexfayard@.hotmail.com> wrote in message
news:e3ed656a.0309170523.3b897e91@.posting.google.com...
> Hi,
> I would like to open a *.log file (TEXTE format) from un stored
> procedure SQL SERVER. After that, i just want to read each lines and
> insert each line in a sql server table.
> With that, i'll could schedule this stored procedure in the jobs of
> sql server
> Thant a lot for your help.
> Alex.
I would like to open a *.log file (TEXTE format) from un stored
procedure SQL SERVER. After that, i just want to read each lines and
insert each line in a sql server table.
With that, i'll could schedule this stored procedure in the jobs of
sql server
Thant a lot for your help.
Alex.Have a look at BULK INSERT in BOL
--
HTH
Ryan Waight, MCDBA, MCSE
"alexfayard" <alexfayard@.hotmail.com> wrote in message
news:e3ed656a.0309170523.3b897e91@.posting.google.com...
> Hi,
> I would like to open a *.log file (TEXTE format) from un stored
> procedure SQL SERVER. After that, i just want to read each lines and
> insert each line in a sql server table.
> With that, i'll could schedule this stored procedure in the jobs of
> sql server
> Thant a lot for your help.
> Alex.
Open / Read / Split / Insert / Repeat
Right now I have a web page that when you click on the only button on the page it opens a text file, reads a line from the text file, splits the comma separated line and stores it in to an array 'strData' and then moves on to the next line and repeats this process.
What I want to do before it goes to the next line is insert that strData in to a SQL DB.
This is my InsertCommand for my SqlDataSource1:
InsertCommand="INSERT INTO [Numbers] ([WTN], [DSL_Qualified], [DSL_Qualified_Date], [Equip_1MM], [Line_Trouble], [Sync_Rate], [Res_Bus], [Host_Remote]) VALUES (@.WTN, @.DSL_Qualified, @.DSL_Qualified_Date, @.Equip_1MM, @.Line_Trouble, @.Sync_Rate, @.Res_Bus, @.Host_Remote)"
Plus the <InsertParameters>:
<InsertParameters>
<asp:Parameter Name="WTN" Type="Int32" />
<asp:Parameter Name="DSL_Qualified" Type="String" />
<asp:Parameter Name="DSL_Qualified_Date" Type="DateTime" />
<asp:Parameter Name="Equip_1MM" Type="String" />
<asp:Parameter Name="Line_Trouble" Type="String" />
<asp:Parameter Name="Sync_Rate" Type="String" />
<asp:Parameter Name="Res_Bus" Type="String" />
<asp:Parameter Name="Host_Remote" Type="String" />
</InsertParameters
So I'm not sure exactly what to do after this:
while (file.Peek() > -1)
{
string strCSVData = file.ReadLine();
string [] strData = strCSVData.Split(',');
// somehow get strData in to the SqlDataSource1.Insert method??
}
Any help would be appreciated.
Thanks.
DataSource controls are meant to be databound to other controls. You can programmatically control them, but they can be a bit touchy if you do.
This is what you want:
(Written in vb.net, sorry)
Dim conn as new sqlconnection("{Your connect string here}") ' or pull from web.config
dim cmd as new sqlcommand("INSERT INTO [Numbers] ([WTN], [DSL_Qualified], [DSL_Qualified_Date], [Equip_1MM], [Line_Trouble], [Sync_Rate], [Res_Bus], [Host_Remote]) VALUES (@.WTN, @.DSL_Qualified, @.DSL_Qualified_Date, @.Equip_1MM, @.Line_Trouble, @.Sync_Rate, @.Res_Bus, @.Host_Remote)",conn)
cmd.parameters.add("@.WTN",int)
cmd.parameters.add("@.DSL_Qualified",varchar)
cmd.parameters.add("@.DSL_Qualified_Date",datetime)
{add rest of parameters here}
conn.open;
while (file.Peek() > -1)
{
string strCSVData = file.ReadLine();
string [] strData = strCSVData.Split(',');
// somehow get strData in to the SqlDataSource1.Insert method??
cmd.parameters("@.WTN").value=strData[0];
cmd.parameters("@.DSL_Qualified").value=strData[1];
{Set rest of parameters here}
cmd.executenonquery;
}
conn.close;
Your other choices if you really want to continue with the sqldatasource control, is to set the parameters using the default values, then call the .insert method of the datasource control. If that doesn't work, then you can make strData class scoped variable (Private member for C#?), call the .insert method, and catch the datasource_Inserting event, and set the sqlcommand parameters there via e.command.parameters.
Saturday, February 25, 2012
only Japanese got error from WebSphere to SQL 2000
Dear all:
Please help.
I have the java code to insert multi-language data from WebSphere in
Unix to MS SQL 2000 by using MS JDBC driver Service Pack 1 Version
2.2.0029.
All other language are ok but not Japanese.
I am sure that I input the correct Japanese in the statement before I
send the insert statement because I put the string into log file to
double check.
Does any one have a clue?
Thanks in advance
dennis_chen_canada@.hotmail.com
Dear all:
It is too bad.
I download and installed the MS SQL JDBC driver SP2.
The result is the same.
The traditional Chinese, simple Chinese, German, Franch are OK.
Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
more info:
- I log the sql statement before calling JDBC and it is correct Japanese letter
- I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
then run the cold fusion page, the result is OK in table
more question:
- How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
It is kind of urgent.
Any help is welcome
|||dennis_chen_canada@.hotmail.com wrote:
> Dear all:
> It is too bad.
> I download and installed the MS SQL JDBC driver SP2.
> The result is the same.
> The traditional Chinese, simple Chinese, German, Franch are OK.
> Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
> more info:
> - I log the sql statement before calling JDBC and it is correct Japanese letter
> - I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
> then run the cold fusion page, the result is OK in table
> more question:
> - How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
> It is kind of urgent.
> Any help is welcome
>
Hi. You would only get support from MS by paying for support. They rarely
answer posts here unless they can identify the poster as a paying support
customer... I suggest you try a commercial driver. Try the DataDirect driver,
because that is likely to indicate how quickly MS will provide a fixed
driver. DataDiarect made MS's free driver and will likely make the next version.
Their commercial driver is their most advanced product, so if that works, it will
show that they know the problem already. If you do buy a commercial driver you
will likely get faster support.
Joe
|||From: Dennis Chen <dennis_chen_canada@.hotmail.com>
To: joeNOSPAM@.bea.com
Subject: Re: only Japanese got error from WebSphere to SQL 2000
Dear Joe:
Thanks for your email.
I will try to get it later on.
Currrently, we are facing the urgent issue that comes from customers.
Do you have any other suggestion.
rgds,
Dennis Chen
Techinical Manager, E-Commerce Div.
www.ulead.com
|||dennis_chen_canada wrote:
> From: Dennis Chen <dennis_chen_canada@.hotmail.com>
> To: joeNOSPAM@.bea.com
> Subject: Re: only Japanese got error from WebSphere to SQL 2000
> Dear Joe:
> Thanks for your email.
> I will try to get it later on.
> Currrently, we are facing the urgent issue that comes from customers.
> Do you have any other suggestion.
> rgds,
> Dennis Chen
> Techinical Manager, E-Commerce Div.
> www.ulead.com
Call Microsoft technical support and pay them to take your case,
but don't expect rapid response because typical MS support only
has a vague idea of what Java/JDBC is, so they would eventually
pass the case on to the folks in MS who talk to the external company
that makes the driver...
I would create a standalone jdbc program that demonstrates the problem
if possible. I would suggest also trying to get support from IBM
Websphere folks. They should have some MS/JDBC experts. If you had
been using BEA's Weblogic, I would have been able to do more, and
would have solved your problem...
Joe Weinstein at BEA
>
Please help.
I have the java code to insert multi-language data from WebSphere in
Unix to MS SQL 2000 by using MS JDBC driver Service Pack 1 Version
2.2.0029.
All other language are ok but not Japanese.
I am sure that I input the correct Japanese in the statement before I
send the insert statement because I put the string into log file to
double check.
Does any one have a clue?
Thanks in advance
dennis_chen_canada@.hotmail.com
Dear all:
It is too bad.
I download and installed the MS SQL JDBC driver SP2.
The result is the same.
The traditional Chinese, simple Chinese, German, Franch are OK.
Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
more info:
- I log the sql statement before calling JDBC and it is correct Japanese letter
- I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
then run the cold fusion page, the result is OK in table
more question:
- How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
It is kind of urgent.
Any help is welcome
|||dennis_chen_canada@.hotmail.com wrote:
> Dear all:
> It is too bad.
> I download and installed the MS SQL JDBC driver SP2.
> The result is the same.
> The traditional Chinese, simple Chinese, German, Franch are OK.
> Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
> more info:
> - I log the sql statement before calling JDBC and it is correct Japanese letter
> - I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
> then run the cold fusion page, the result is OK in table
> more question:
> - How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
> It is kind of urgent.
> Any help is welcome
>
Hi. You would only get support from MS by paying for support. They rarely
answer posts here unless they can identify the poster as a paying support
customer... I suggest you try a commercial driver. Try the DataDirect driver,
because that is likely to indicate how quickly MS will provide a fixed
driver. DataDiarect made MS's free driver and will likely make the next version.
Their commercial driver is their most advanced product, so if that works, it will
show that they know the problem already. If you do buy a commercial driver you
will likely get faster support.
Joe
|||From: Dennis Chen <dennis_chen_canada@.hotmail.com>
To: joeNOSPAM@.bea.com
Subject: Re: only Japanese got error from WebSphere to SQL 2000
Dear Joe:
Thanks for your email.
I will try to get it later on.
Currrently, we are facing the urgent issue that comes from customers.
Do you have any other suggestion.
rgds,
Dennis Chen
Techinical Manager, E-Commerce Div.
www.ulead.com
|||dennis_chen_canada wrote:
> From: Dennis Chen <dennis_chen_canada@.hotmail.com>
> To: joeNOSPAM@.bea.com
> Subject: Re: only Japanese got error from WebSphere to SQL 2000
> Dear Joe:
> Thanks for your email.
> I will try to get it later on.
> Currrently, we are facing the urgent issue that comes from customers.
> Do you have any other suggestion.
> rgds,
> Dennis Chen
> Techinical Manager, E-Commerce Div.
> www.ulead.com
Call Microsoft technical support and pay them to take your case,
but don't expect rapid response because typical MS support only
has a vague idea of what Java/JDBC is, so they would eventually
pass the case on to the folks in MS who talk to the external company
that makes the driver...
I would create a standalone jdbc program that demonstrates the problem
if possible. I would suggest also trying to get support from IBM
Websphere folks. They should have some MS/JDBC experts. If you had
been using BEA's Weblogic, I would have been able to do more, and
would have solved your problem...
Joe Weinstein at BEA
>
only 1 row from a join
I need to select one column of the first row of a left=20
join operation to use it to insert one row in a table, so=20
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @.fecha datetime, @.alias varchar(20),@.usuario=20
varchar(20),@.grupo varchar(20)=20
set @.fecha =3D(select fechanac from inserted)
set @.alias=3D(select alias from inserted)
set @.usuario=3D(select usuario from inserted)
set @.grupo=3D (select grupo from inserted)
=20
IF (@.fecha IS NOT NULL) /* Tiene fecha de nacimiento */
begin
/***crear una cita de cumplea=F1os ***/
DECLARE @.hora CHAR(5)
SET @.hora=3D(SELECT horasCumples.hora FROM horasCumples=20
LEFT JOIN Vcitas ON horasCumples.hora=3DVcitas.hora)
INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,
aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta
)
VALUES=20
(@.usuario,@.fecha,@.hora,'Cumplea=F1os','P
',@.alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
/*** prueba: update contactos set fechanac=20
=3D '10/10/1980' where alias=3D@.alias and usuario=3D@.usuario and=20
grupo=3D@.grupo ***/
end=20
GO=20
this returns me a message saying that the subquery=20
returned more than 1 result and it is not allowed (in the=20
set @.hora=3D(select... line)
What may I do?
Thnx in advance(reply cross-posted to microsoft.public.sqlserver.programming)
Which row is the 'first'? The row with the earliest datetime value? Rows
in a relational database table have no order so you need to specify your
rules that identify the 'first' row.
Only horasCumples.hora is referenced in the column so the LEFT JOIN to the
Vcitas table serves no purpose. You will get all rows from horasCumples
regardless of the Vcitas table contents. This may not be exactly what you
want but you can use the following to select the horasCumple.hora value with
the earliest date:
SELECT @.hora= MIN(hora) FROM horasCumples
There are some fundamental problems with your trigger that need to be
addressed. Importantly, more than one row can be inserted with a single
INSERT statement. The example below illustrates a set-based technique that
eliminates the need to select values from the inserted table into variables.
INSERT INTO Vcitas
(
usuario,
fecha,
hora,
motivo,
tipo,
citaCon,
aviso,
descripcion,
categoria,
zona,
horaAbsoluta,
fechaAbsoluta
)
SELECT
i.usuario,
i.fechanac,
@.hora,
'Cumpleaos',
'P',
i.alias,
'Si',
NULL,
NULL,
NULL,
NULL,
NULL
FROM inserted i
Hope this helps.
Dan Guzman
SQL Server MVP
"pedro j." <anonymous@.discussions.microsoft.com> wrote in message
news:01d101c3d893$c5d9e710$a101280a@.phx.gbl...
I need to select one column of the first row of a left
join operation to use it to insert one row in a table, so
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @.fecha datetime, @.alias varchar(20),@.usuario
varchar(20),@.grupo varchar(20)
set @.fecha =(select fechanac from inserted)
set @.alias=(select alias from inserted)
set @.usuario=(select usuario from inserted)
set @.grupo= (select grupo from inserted)
IF (@.fecha IS NOT NULL) /* Tiene fecha de nacimiento */
begin
/***crear una cita de cumpleaos ***/
DECLARE @.hora CHAR(5)
SET @.hora=(SELECT horasCumples.hora FROM horasCumples
LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)
INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,
aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta
)
VALUES
(@.usuario,@.fecha,@.hora,'Cumpleaos','P',
@.alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
/*** prueba: update contactos set fechanac
= '10/10/1980' where alias=@.alias and usuario=@.usuario and
grupo=@.grupo ***/
end
GO
this returns me a message saying that the subquery
returned more than 1 result and it is not allowed (in the
set @.hora=(select... line)
What may I do?
Thnx in advance
join operation to use it to insert one row in a table, so=20
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @.fecha datetime, @.alias varchar(20),@.usuario=20
varchar(20),@.grupo varchar(20)=20
set @.fecha =3D(select fechanac from inserted)
set @.alias=3D(select alias from inserted)
set @.usuario=3D(select usuario from inserted)
set @.grupo=3D (select grupo from inserted)
=20
IF (@.fecha IS NOT NULL) /* Tiene fecha de nacimiento */
begin
/***crear una cita de cumplea=F1os ***/
DECLARE @.hora CHAR(5)
SET @.hora=3D(SELECT horasCumples.hora FROM horasCumples=20
LEFT JOIN Vcitas ON horasCumples.hora=3DVcitas.hora)
INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,
aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta
)
VALUES=20
(@.usuario,@.fecha,@.hora,'Cumplea=F1os','P
',@.alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
/*** prueba: update contactos set fechanac=20
=3D '10/10/1980' where alias=3D@.alias and usuario=3D@.usuario and=20
grupo=3D@.grupo ***/
end=20
GO=20
this returns me a message saying that the subquery=20
returned more than 1 result and it is not allowed (in the=20
set @.hora=3D(select... line)
What may I do?
Thnx in advance(reply cross-posted to microsoft.public.sqlserver.programming)
quote:
> I need to select one column of the first row of a left
> join operation to use it to insert one row in a table, so
> a put (in a trigger):
Which row is the 'first'? The row with the earliest datetime value? Rows
in a relational database table have no order so you need to specify your
rules that identify the 'first' row.
quote:
> SET @.hora=(SELECT horasCumples.hora FROM horasCumples
> LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)
Only horasCumples.hora is referenced in the column so the LEFT JOIN to the
Vcitas table serves no purpose. You will get all rows from horasCumples
regardless of the Vcitas table contents. This may not be exactly what you
want but you can use the following to select the horasCumple.hora value with
the earliest date:
SELECT @.hora= MIN(hora) FROM horasCumples
There are some fundamental problems with your trigger that need to be
addressed. Importantly, more than one row can be inserted with a single
INSERT statement. The example below illustrates a set-based technique that
eliminates the need to select values from the inserted table into variables.
INSERT INTO Vcitas
(
usuario,
fecha,
hora,
motivo,
tipo,
citaCon,
aviso,
descripcion,
categoria,
zona,
horaAbsoluta,
fechaAbsoluta
)
SELECT
i.usuario,
i.fechanac,
@.hora,
'Cumpleaos',
'P',
i.alias,
'Si',
NULL,
NULL,
NULL,
NULL,
NULL
FROM inserted i
Hope this helps.
Dan Guzman
SQL Server MVP
"pedro j." <anonymous@.discussions.microsoft.com> wrote in message
news:01d101c3d893$c5d9e710$a101280a@.phx.gbl...
I need to select one column of the first row of a left
join operation to use it to insert one row in a table, so
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @.fecha datetime, @.alias varchar(20),@.usuario
varchar(20),@.grupo varchar(20)
set @.fecha =(select fechanac from inserted)
set @.alias=(select alias from inserted)
set @.usuario=(select usuario from inserted)
set @.grupo= (select grupo from inserted)
IF (@.fecha IS NOT NULL) /* Tiene fecha de nacimiento */
begin
/***crear una cita de cumpleaos ***/
DECLARE @.hora CHAR(5)
SET @.hora=(SELECT horasCumples.hora FROM horasCumples
LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)
INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,
aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta
)
VALUES
(@.usuario,@.fecha,@.hora,'Cumpleaos','P',
@.alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
/*** prueba: update contactos set fechanac
= '10/10/1980' where alias=@.alias and usuario=@.usuario and
grupo=@.grupo ***/
end
GO
this returns me a message saying that the subquery
returned more than 1 result and it is not allowed (in the
set @.hora=(select... line)
What may I do?
Thnx in advance
Subscribe to:
Posts (Atom)