none
StoredProc Query added to TableAdapter ends up with a ;1 at the end.. Any ideas why? (Studio 2008, sp1). RRS feed

  • Question

  • Ok, so, here's the deal.  I have 2005 & 2008 loaded on my computer.  When I go into 2005 and drop a table onto my canvas, everything is great.  Then, I go add a stored proc query to the TableAdapter, again, everything works like it should.

    Now here comes 2008.  I do the same steps, same database.  I drop the table onto the canvas, so far so good.  Now, when I go add a stored proc to the TableAdapter, it adds a ;1 to the end of the command text...   It should be INSERT_RECORD, but it comes back as INSERT_RECORD;1.

    This is the same stored proc between 2005, 2008.  Now, since the CommandText does have ;1 in it, the TableAdapter can't find the underlying stored procedure to actually call.

    Any ideas why?
    Friday, July 10, 2009 4:06 AM

Answers

  • Hi Nick,

    We can repro in VS2008 with OLEDB data provider and we have fixed that issue in the next release of Visual Studio.
    Using SQL Server Provider shoud not have a problem and we can not repro it.
    Could you make sure that you are using SQL Server Provider as indicated in this picture?
    Thanks!

    John


    • Marked as answer by Nick Jacobs Monday, July 20, 2009 7:49 PM
    Sunday, July 19, 2009 5:46 PM
  • Nick,

    Next version is VS2010 at this time as I know.
    Please check if you still have the problem with SQL Server provider. That is the most important thing to check, and we can discuss further from there.
    Thanks!

    John


    Sunday, July 19, 2009 6:20 PM

All replies

  • Hey Nick,

    Is there any chance you can get us more information on this? We don't know what type of stored procedure this is so we have no way of reproing the issue. How many arguments does it have?

    Thanks,
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 10, 2009 4:20 PM
  • Well, I can post part of the query, but, the first query I added had about 15 parameters and didn't return any values at all.  It was essentially doing an insert into a table.

    The 2nd query I added took 1parameter, and looking for a max(identity) kind of situation and returned that value.

    So, goofing around on something that didn't make sense, but I "Added" a function instead of stored proc.  The ;1 changed to a ;0 for the functions...

    Is there a chance the ;1 is really an indicator that it's a stored proc versus other types?

    Nick

    Saturday, July 11, 2009 3:01 AM
  • Hey Nick,

    This seems weird. I am unsure how to procede. I think you need to do each piece separately to understand where its going wrong.

    For example the code generation will add in stuff for this stored proc. Perhaps you can copy the code that is generated and runit separately from the typed DataSet adapter. This may help you to see if the results coming back in the DataReader are wrong. Perhaps the stored procedure is incorrect itself? When you execute it via Sql Server Management studio does it work?

    Sorry I can't help more. I have an email address on my profile, if you want you create a small repro and send it to me.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 14, 2009 12:49 AM
  • I'll see what I can come up with... And yes, the procs work just fine in SSMS.  Actually, one is so simple it's almost redundant.  Really just a select MAX(IDENTIY) kind of query.

    Details to follow shortly  (Ok, tonight. :) )
    Tuesday, July 14, 2009 5:11 PM
  • Likely what is happening is some part of the Ado.net stack is not handling the ;1 correctly

    http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx

    The ;1 is used as an additional identifier on a stored proc. Perhaps this can help you investigate more.

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 14, 2009 6:33 PM
  • Hi Nick,

    I was asked by Chris to get involved with your question, which seems very odd and I have never seen this before.

    I wonder your store procedure do use the ;number syntax in http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx
    (It does not like you have but just like to confirm)

    Also could you confirm that what database you are using? Is it a SQL Server?

    Thanks!
    John
    Tuesday, July 14, 2009 8:34 PM
  • Hi Nick,

    Could you answer some questions to help narrow down the repro?

    0. What version of VS you have? Could you check the help about to get the version number.  

    0.1 Is that the only VS you installed in the machine?

    0.2 Is there any chance you can try in another machine with the same VS and same DB?  

    1. What is the database server you used? e.g. SQL Server, SQL CE, Assess, DB2, Oracle.

    2. What is the DataSource Provider you are using?
        You can check this in AddConnection dialog here, clock the change and see what is the 
        DataSource and DataSourceProvider. See sample picture http://cid-7ff4cd2b1e6a7d55.skydrive.live.com/self.aspx/Public/AddConnectionDiag.JPG and http://cid-7ff4cd2b1e6a7d55.skydrive.live.com/self.aspx/Public/AddConnectionDiag.JPG.

    3. Does the issue occur to all sprocs and functions? 

    John
    Wednesday, July 15, 2009 3:37 AM
  • I'm sitting here in an MS Class right at the moment so I'm not in front of my PC....

    Couple of things:
    SQL 2005 is the server.
    Studio 2008/SP1.  (2005 doesn't do it).

    I do have Studio 2005/2008 installed on the machine.  I have tried the connection string with both OleDB and ADO.Net.

    It doens't matter what sproc I choose, but it does change from ;1 to ;0 when I switch from sprocs to functions...

    Wednesday, July 15, 2009 5:19 PM
  • Thanks for the info. Will get back to you.
    Friday, July 17, 2009 5:46 AM
  • Hey Nick,

    John has been working dilgently on this issue by the way and he has a repro of the problem that is occurring. They are currently evaluating the bug. John or I will let you know if we determine a workaround that can be used.

    thanks
    Chris Robinson
    Program manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 17, 2009 5:48 AM
  • so you can reproduce it?  Cool, well kinda cool... At least it's not just me.

    Thanks so much,

    Nick

    Let me know what I can do to help...


    Saturday, July 18, 2009 1:29 AM
  • Hi Nick,

    We can repro in VS2008 with OLEDB data provider and we have fixed that issue in the next release of Visual Studio.
    Using SQL Server Provider shoud not have a problem and we can not repro it.
    Could you make sure that you are using SQL Server Provider as indicated in this picture?
    Thanks!

    John


    • Marked as answer by Nick Jacobs Monday, July 20, 2009 7:49 PM
    Sunday, July 19, 2009 5:46 PM
  • Thanks John, I'll check the provider and see how it's set and get back to you... Would the "Next Version" of studio be a future service pack?  Or are we talking VS2010?
    Sunday, July 19, 2009 6:13 PM
  • Nick,

    Next version is VS2010 at this time as I know.
    Please check if you still have the problem with SQL Server provider. That is the most important thing to check, and we can discuss further from there.
    Thanks!

    John


    Sunday, July 19, 2009 6:20 PM
  • Just a follow up, I did change the provider from OleDB to SQL as the picture was showing and I'm not getting ;1 any more.  I guess this wont wont with the OleDb Provider then?  At least not until 2010?

    Monday, July 20, 2009 7:49 PM