Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Dynamic SQL Filter Gives Error Message

    Hi again everyone,

    I can't figure out this error and I have spent a day looking on google, etc. Is there a limit to the number of filters you can have in dynamic sql? I know that is not possible. I have one filter on an "affiliate" and when I run this query with this one filter it returns data. I want to add a second filter on active records only which is the @Accountstatus variable below and it keeps throwing the error below. I have output the query through variable @sql and then I copied and pasted it (with the second variable added) and successfully ran it to the server I am attempting to pass through to.

    Any suggestions? Thanks in advance bec I know you will have the answer.

    ddave

    -----------------------------------------------


    declare @SCUserID as nvarchar(30)
    declare @SCPW as nvarchar(30)
    declare @AffiliateID as nvarchar(10)
    declare @AccountStatus as nvarchar(20)
    declare @sql as nvarchar(2000)


    set @SCUserID = 'myuserid'
    set @SCPW = 'mypassword'
    set @AffiliateID = '760'
    set @AccountStatus = '''Active'''

    set @sql = 'SELECT * FROM OPENROWSET(' + '''MSDASQL''' + ',' + '''DRIVER={SQL Server};SERVER=SERVERNAME;UID=' + @SCUserID + ';PWD=' + @SCPW + ''',' + '''select b.Brokerage_Id__c as "SC_BrokerageID"
    , upper(b.Name) as "SC_BrokerageName"
    , Broker_ID__c as "SC_BrokerID"
    , upper(c.FirstName) as "SC_BrokerFirstName"
    , upper(c.LastName) as "SC_BrokerLastName"
    , cast(a.AccountNumber as int) as "SC_AccountNumber"
    , upper(a.Name) as "SC_GroupName"
    , a.Account_Status__c as "SC_AccountStatus"
    from dbo.Brokerage__c b
    join dbo.Contact c
    on b.Brokerage_Id__c = c.Brokerage_Id__c
    join dbo.account a
    on a.Broker_Location__c = c.Id
    where b.Brokerage_Id__c = ' + @AffiliateID + '
    and a.Account_Status__c = ' + @AccountStatus + '
    order by upper(b.Name)
    , upper(c.LastName)
    , upper(c.FirstName)
    , upper(a.Name)''' + ') a'

    print @sql


    EXEC sp_executesql @sql


    ----------------------------------------------------------------
    --My @sql Output(When I copy and paste this into the query window and run it against the database I am passing through to it works!):

    SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=servername;UID=Myuserid;PWD=mypassw ord,'select b.Brokerage_Id__c as "SC_BrokerageID"
    , upper(b.Name) as "SC_BrokerageName"
    , Broker_ID__c as "SC_BrokerID"
    , upper(c.FirstName) as "SC_BrokerFirstName"
    , upper(c.LastName) as "SC_BrokerLastName"
    , cast(a.AccountNumber as int) as "SC_AccountNumber"
    , upper(a.Name) as "SC_GroupName"
    , a.Account_Status__c as "SC_AccountStatus"
    from dbo.Brokerage__c b
    join dbo.Contact c
    on b.Brokerage_Id__c = c.Brokerage_Id__c
    join dbo.account a
    on a.Broker_Location__c = c.Id
    where b.Brokerage_Id__c = 760
    and a.Account_Status__c = 'Active'
    order by upper(b.Name)
    , upper(c.LastName)
    , upper(c.FirstName)
    , upper(a.Name)') a

    --Error Msg:

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

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    try:

    set @SCUserID = 'myuserid'
    set @SCPW = 'mypassword'
    set @AffiliateID = '760'
    set @AccountStatus = 'Active'

    set @sql = 'SELECT * FROM OPENROWSET(' + '''MSDASQL''' + ',' + '''DRIVER={SQL Server};SERVER=SERVERNAME;UID=' + @SCUserID + ';PWD=' + @SCPW + ''',' + '''select b.Brokerage_Id__c as "SC_BrokerageID"
    , upper(b.Name) as "SC_BrokerageName"
    , Broker_ID__c as "SC_BrokerID"
    , upper(c.FirstName) as "SC_BrokerFirstName"
    , upper(c.LastName) as "SC_BrokerLastName"
    , cast(a.AccountNumber as int) as "SC_AccountNumber"
    , upper(a.Name) as "SC_GroupName"
    , a.Account_Status__c as "SC_AccountStatus"
    from dbo.Brokerage__c b
    join dbo.Contact c
    on b.Brokerage_Id__c = c.Brokerage_Id__c
    join dbo.account a
    on a.Broker_Location__c = c.Id
    where b.Brokerage_Id__c = ' + @AffiliateID + '
    and a.Account_Status__c = ''' + @AccountStatus + '''
    order by upper(b.Name)
    , upper(c.LastName)
    , upper(c.FirstName)
    , upper(a.Name)''' + ') a'

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    That works! Sigh.........just shoot me now please!!!

    Thanks.

    dolfandave
    "Under Promise and Over Deliver" -- James J. Kramer

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by dolfandave
    That works! Sigh.........just shoot me now please!!!

    Thanks.

    dolfandave
    "Under Promise and Over Deliver" -- James J. Kramer

    It's what I hate about dynamic sql and openrowset/query, gotta play with the quotes, and eventually you get it right.

    Another round of layoffs here, so I'm in lala land myself.

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    Gotta study dynamic sql more.

    Sorry to hear that. Do you work w/ tech recruiters? I used to be one once (unfortunately).

    ddave

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by PMASchmed
    It's what I hate about dynamic sql and openrowset/query, gotta play with the quotes, and eventually you get it right.

    Another round of layoffs here, so I'm in lala land myself.

    I just hope those layoffs dont come your way, its rough out there right now

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by desireemm
    I just hope those layoffs dont come your way, its rough out there right now
    It's getting ugly here. I'm the only DBA, so I guess I'm somewhat safe, I do notice that I am not as busy as I was, so I am a bit scared. I'm not doing as many sql/ETL development projects as I was.

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    OK,

    I now successfully retrieve data from the above script. How do I put the data into a variable or temp table that I can further manipulate? I have read that it must be a temp table but I can't figure out the syntax again.

    ddave

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Loading it into a temp table is as simple as adding "into #YourTable" in the script. But the problem is that the temp table will only last as long as the scope of the dynamic sql statement that generates it.
    So...pftt! Your result set is gone.
    To solve this, you will need to define and create your temporary table first using a regular CREATE #YourTable statement, and then change your dynamic SQL statement to an insert statement.
    ...and that means, by the way, dumping the "SELECT *" from your statement and substituting actual column names instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Posts
    193
    OK,

    I will fiddle with this. Thanks again. I can appreciate the "pfft" part with 3 kids in Los Angeles County that happens to my paycheck every payday.

    ddave

  11. #11
    Join Date
    Feb 2004
    Posts
    193
    Now I get this error:

    Server: Msg 8501, Level 16, State 3, Line 1
    MSDTC on server 'MYSERVERNAME' is unavailable.

    -------------------------------------

    I created a table with the fields desired called "SCDataTest20090114" and this is how I wrote the insert statement. I tried running the entire script from top to bottom including this below:

    insert into dbo.SCDataTest20090114
    EXECUTE sp_executesql @sql

  12. #12
    Join Date
    Feb 2004
    Posts
    193
    Nevermind, I got it to work by creating a table in the script and modifying the statement in the OPENROWSET to:

    set @sql = 'insert into dbo.SCDataTest20090114 SELECT * ..........

    Thanks again everyone. You guys are the best.

    ddave

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bingo. The insert clause is included in the dynamic SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Posts
    193
    That's surprising that it would work this way. Obviously it did but at first glance to me it looks like it is saying "OK, do this insert on the server side since it is being passed to another server" as opposed to "Bring back the data from the query and THEN insert it on my side." You know what I mean?

    ddave

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You actually have two commands in your dynamic SQL
    The "SELECT * FROM OPENROWSET" is executed locally, and that is where you put your INSERT INTO statement. The script that is submitted to the OPENROWSET function is executed remotely.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •