Problem with EXEC and SELECT in same query

[Renee Jylkka]

Hello all,

I am using Golang to create a web app that connects to the same MS SQL database as a desktop app.  There is a database procedure, GetNextID, which is used to generate the next ID of various sorts.  When I run the following code in the Microsoft SQL Server Management Studio, it properly increments the counter for the CalendarID, and returns the next ID:

DECLARE @NewCalendarID int; EXEC @NewCalendarID = GetNextID @StationID=44, @CounterID=13; SELECT @NewCalendarID;

However, when I run the above code as a Go query, it increments the counter but does not return any rows.  Here is my code:

        /* find the next CalendarID */

        queryString = "DECLARE @NewCalendarID int; EXEC @NewCalendarID = GetNextID @StationID=44, @CounterID=13; SELECT @NewCalendarID AS NewCalendarID;"

        /* Execute the query */

        _, err = fldb.Exec("USE " + sqlDB)

        if err != nil {

            log.Println("Error in USE statement (finding the next CalendarID): " + err.Error())

            return err

        }

        rows, err := fldb.Query(queryString)

        if err != nil {

            log.Println("Error executing query (finding the next CalendarID): " + queryString + " : " + err.Error())

            return err

        }

        if rows.Next() {

            err = rows.Scan(&newCalID)

            if err != nil {

                log.Println("Error scanning data (finding the next CalendarID): " + queryString + " : " + err.Error())

                return err

            }

            calID = strconv.Itoa(int(newCalID))

        } else {

            log.Println("No rows returned (finding the next CalendarID): " + queryString)

        }

I expect I am doing something wrong, but I can't figure out what.  Any ideas?

Thanks in advance,

Renee

[Daniel Theophanes]

What if you prefixed your query with "SET NOCOUNT ON", so run:

SET NOCOUNT ON; DECLARE @NewCalendarID int; EXEC @NewCalendarID = GetNextID @StationID=44, @CounterID=13; SELECT @NewCalendarID;

Does that work?

[Renee Jylkka]

It worked!  Why does this make a difference?

Thanks so much!

-- You received this message because you are subscribed to the Google Groups "golang-sql" group. To unsubscribe from this group and stop receiving emails from it, send an email to golang-sql+...@googlegroups.com. To post to this group, send email to golan...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/golang-sql/d48f475d-2805-4a4d-93ff-599158c7ef83%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.

[Daniel Theophanes]

In SQL Server, each database can have different defaults. In addition, certain drivers and connections (ADO.NET) will initialize established connections after they are made with certain default, such as max TEXT size and NOCOUNT. The current go driver doesn't do such automatic initialization. It may be good to add that in, feel free to file a driver issue. So what you were seeing was different database settings in effect, with the Go driver using the database defaults while SSMS uses it's own defaults per connection.

So why does "set nocount on;" work? because without it SQL SERVER actually returns the count as a record I believe, rather then returning just your select statement. If you read the next result set, you would probably get your intended rows.

-- You received this message because you are subscribed to the Google Groups "golang-sql" group. To unsubscribe from this group and stop receiving emails from it, send an email to golang-sql+unsubscribe@googlegroups.com. To post to this group, send email to golan...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/golang-sql/d48f475d-2805-4a4d-93ff-599158c7ef83%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.