Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

String concatenation with OPENROWSET

JomacJomac Member Posts: 231
Hi all, I need to find a way to execute a dynamic query in 'OPENROWSET', in other words, I need to make this work...

[code]
CREATE PROCEDURE [spInvLink]
@parILCN varchar(12)

AS

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\shadtrmnlIlinkDATAinteg.mdb';; '',
'SELECT * FROM _transactions WHERE ILCN=' + @parILCN)
GO
[/code]

Any help would be great.
Regards,
______
[size=2][b][blue]J[/blue]omac.[/b][/size]

Comments

  • DaedaliusDaedalius Member Posts: 30
    I haven't tried this but following the example in BOL it looks like that select statement should like:

    [code]
    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    '\shadtrmnlIlinkDATAinteg.mdb';; '',
    _transactions)
    WHERE ILCN = @parILCN
    [/code]

    : Hi all, I need to find a way to execute a dynamic query in 'OPENROWSET', in other words, I need to make this work...
    :
    : [code]
    : CREATE PROCEDURE [spInvLink]
    : @parILCN varchar(12)
    :
    : AS
    :
    : SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    : '\shadtrmnlIlinkDATAinteg.mdb';; '',
    : 'SELECT * FROM _transactions WHERE ILCN=' + @parILCN)
    : GO
    : [/code]
    :
    : Any help would be great.
    : Regards,
    : ______
    : [size=2][b][blue]J[/blue]omac.[/b][/size]
    :

  • JomacJomac Member Posts: 231
    Thanks for trying, but give me some credit, that was one of the first things I tried. However it would take almost 2 minutes to return because OPENROWSET is returning the whole table (1Gb of it) and then Row-Scanning that. Too Slow...

    : I haven't tried this but following the example in BOL it looks like that select statement should like:
    :
    : [code]
    : SELECT *
    : FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    : '\shadtrmnlIlinkDATAinteg.mdb';; '',
    : _transactions)
    : WHERE ILCN = @parILCN
    : [/code]
    :
    : : Hi all, I need to find a way to execute a dynamic query in 'OPENROWSET', in other words, I need to make this work...
    : :
    : : [code]
    : : CREATE PROCEDURE [spInvLink]
    : : @parILCN varchar(12)
    : :
    : : AS
    : :
    : : SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    : : '\shadtrmnlIlinkDATAinteg.mdb';; '',
    : : 'SELECT * FROM _transactions WHERE ILCN=' + @parILCN)
    : : GO
    : : [/code]
    : :
    : : Any help would be great.
    : : Regards,
    : : ______
    : : [size=2][b][blue]J[/blue]omac.[/b][/size]
    : :
    :
    :

  • DaedaliusDaedalius Member Posts: 30
    Sorry. I just thought you were having difficulty with the syntax. In the BOL it states that the query parameter is a string constant. How about setting up a linked server?


    : Thanks for trying, but give me some credit, that was one of the first things I tried. However it would take almost 2 minutes to return because OPENROWSET is returning the whole table (1Gb of it) and then Row-Scanning that. Too Slow...
    :
    : : I haven't tried this but following the example in BOL it looks like that select statement should like:
    : :
    : : [code]
    : : SELECT *
    : : FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    : : '\shadtrmnlIlinkDATAinteg.mdb';; '',
    : : _transactions)
    : : WHERE ILCN = @parILCN
    : : [/code]
    : :
    : : : Hi all, I need to find a way to execute a dynamic query in 'OPENROWSET', in other words, I need to make this work...
    : : :
    : : : [code]
    : : : CREATE PROCEDURE [spInvLink]
    : : : @parILCN varchar(12)
    : : :
    : : : AS
    : : :
    : : : SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    : : : '\shadtrmnlIlinkDATAinteg.mdb';; '',
    : : : 'SELECT * FROM _transactions WHERE ILCN=' + @parILCN)
    : : : GO
    : : : [/code]
    : : :
    : : : Any help would be great.
    : : : Regards,
    : : : ______
    : : : [size=2][b][blue]J[/blue]omac.[/b][/size]
    : : :
    : :
    : :
    :
    :

Sign In or Register to comment.