3

I have some VB code:

If cnn.State = ConnectionState.Closed Then
   cnn.Open()
End If

Dim cmd As New SqlCommand("[WENCO].[dbo].[MyProc]", cnn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Data_Date", useDate)
cmd.Parameters.AddWithValue("@ALTERNATE_LOC", alternateLocation)
cmd.ExecuteReader()

The procedure itself grabs some DB information, creates a file and then writes to it. The code is merely a run procedure GUI for those who do not have permissions in SSMS. This issue I am facing is that whenever the data that must write to the file is above 104 KB it does not write any information at all.

I myself can run the stored procedure from SSMS with zero issue.

Any thoughts on what could possibly be causing this issue would be much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HelloWorld
  • 283
  • 4
  • 12
  • 24
  • The `If cnn.State = ConnectionState.Closed` stuff is a code smell to me... a sign that something is likely wrong. – Joel Coehoorn Nov 14 '13 at 16:55
  • This is only a supsicion, and so I'll just leave this as a comment, but I suspect it's not the size of the data that matters, but the amount of time it takes to write it. There is a timeout that's reached at some level before the query has a chance to fill a file/io buffer for larger data. – Joel Coehoorn Nov 14 '13 at 16:58
  • 1
    To elaborate on the first comment: .Net and Sql Server work together with a feature called "Connection Pooling", such that things actually work best when you create a brand new connection object for each individual call to your database. Really. You should never need to first check if the connection is open/closed. You already **know** the connection is closed, because you _just created it_ a line or two prior. Anything else implies you are sharing/reusing a single connection object, which is rarely a good idea. Of course there are exceptions, but a shared connection shouldn't be your default. – Joel Coehoorn Nov 14 '13 at 17:02
  • Thank you for elaborating :D – HelloWorld Nov 14 '13 at 17:08
  • Wrap the cmd in a Using CNN // code End Using – Mad Dog Tannen Nov 14 '13 at 17:30
  • 1
    Also, should you use a executereader for a stored procedure? – Mad Dog Tannen Nov 14 '13 at 17:32
  • @KayNelson sure, if it returns results – Joel Coehoorn Nov 14 '13 at 17:41
  • @JoelCoehoorn, indeed you are right! Sometimes i say things to fast i guess... – Mad Dog Tannen Nov 14 '13 at 18:10

2 Answers2

2

Hey guys thanks for the answers. I did some more digging and found out applying:

    Dim command As New SqlCommand(queryString, connection)
    Dim reader As SqlDataReader = command.ExecuteReader()
    While reader.Read()
        Console.WriteLine("{0}", reader(0))
    End While

Was the answer. I don't know why it was timing out in such a way but this forced it to look at all of the data.

doing the read also helps with this:

If a transaction is deadlocked, an exception may not be thrown until Read is called.

as found on the MSDN website http://msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx

HelloWorld
  • 283
  • 4
  • 12
  • 24
  • If you are not doing anything with result of the stored proc, then you should use @KayNelson's answer. – StingyJack Nov 14 '13 at 17:51
  • You are correct that worked as well, so I will pick as the primary answer since I do not specifically need the results read. But now for people who get stuck they have two solutions :D – HelloWorld Nov 14 '13 at 18:34
1

Try using ExecuteNonQuery()

Found this link, its in C# but should be the same: How to execute a stored procedure within C# program

Community
  • 1
  • 1
Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55