sql report manager: call

Showing posts with label call. Show all posts

Thursday, March 29, 2012

cant start Trace

I just created a server side Trace with no filters for testing but Im not
able to start it. I call sp_trace_setstatus 1,1 and it says completed
successfully. It then creates the file it writes too but never writes to it
when I generate activity. Any ideas why?
TIA, ChrisR
sql2k sp3aHi Chris
With server side traces, SQL Server writes to the file in 128K chunks, so
you won't see anything written until you have 128K worth of activity.
Look at fn_trace_getinfo( ) to check that your trace really is running.
Then try stopping and closing the trace:
sp_trace_setstatus 1, 0 -- stop the trace
sp_trace_setstatus 1, 2 -- close the trace
You must stop before closing or the close won't do anything. Only after
closing SQL Server write to the file.
Or, you can stop the SQL Server services, and that will also flush the
collected trace events to the file.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>I just created a server side Trace with no filters for testing but Im not
>able to start it. I call sp_trace_setstatus 1,1 and it says completed
>successfully. It then creates the file it writes too but never writes to it
>when I generate activity. Any ideas why?
> TIA, ChrisR
> sql2k sp3a
>|||Kalen I know this will sound odd, but it almost seems as though nothing gets
written ubtil the box is rebooted. It stays at 0kb until I reboot, at which
time there is a larger size.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
> Hi Chris
> With server side traces, SQL Server writes to the file in 128K chunks, so
> you won't see anything written until you have 128K worth of activity.
> Look at fn_trace_getinfo( ) to check that your trace really is running.
> Then try stopping and closing the trace:
> sp_trace_setstatus 1, 0 -- stop the trace
> sp_trace_setstatus 1, 2 -- close the trace
> You must stop before closing or the close won't do anything. Only after
> closing SQL Server write to the file.
> Or, you can stop the SQL Server services, and that will also flush the
> collected trace events to the file.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im not
>>able to start it. I call sp_trace_setstatus 1,1 and it says completed
>>successfully. It then creates the file it writes too but never writes to
>>it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>|||Are you saying you have stopped and closed the trace, and still nothing gets
written?
If so, can you show the output of fn_trace_getinfo and then exact commands
you are running to stop and close the trace?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
> Kalen I know this will sound odd, but it almost seems as though nothing
> gets written ubtil the box is rebooted. It stays at 0kb until I reboot, at
> which time there is a larger size.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks, so
>> you won't see anything written until you have 128K worth of activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only after
>> closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im not
>>able to start it. I call sp_trace_setstatus 1,1 and it says completed
>>successfully. It then creates the file it writes too but never writes to
>>it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>|||--Status before stopping
traceid property value
-- -- -----------
1 1 0
1 2 c:\JobTrace
1 3 5
1 4 NULL
1 5 1
--stopping trace
sp_trace_setstatus 1,0
--status after stopping
traceid property value
-- -- -----------
1 1 0
1 2 c:\JobTrace
1 3 5
1 4 NULL
1 5 0
I dont see a command to close the Trace, Im just closing Query Anyalyzer.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Are you saying you have stopped and closed the trace, and still nothing
> gets written?
> If so, can you show the output of fn_trace_getinfo and then exact
> commands you are running to stop and close the trace?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though nothing
>> gets written ubtil the box is rebooted. It stays at 0kb until I reboot,
>> at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks,
>> so you won't see anything written until you have 128K worth of activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only after
>> closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im
>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>completed successfully. It then creates the file it writes too but never
>>writes to it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>>
>|||Closing Qa will not stop or close the trace and the file size may not update
that often while it is running. If you look at sp_trace_setstatus in BOL
you will see hope to stop and close the trace.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
> --Status before stopping
> traceid property value
> -- -- -----------
> 1 1 0
> 1 2 c:\JobTrace
> 1 3 5
> 1 4 NULL
> 1 5 1
> --stopping trace
> sp_trace_setstatus 1,0
> --status after stopping
> traceid property value
> -- -- -----------
> 1 1 0
> 1 2 c:\JobTrace
> 1 3 5
> 1 4 NULL
> 1 5 0
> I dont see a command to close the Trace, Im just closing Query Anyalyzer.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still nothing
>> gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though nothing
>> gets written ubtil the box is rebooted. It stays at 0kb until I reboot,
>> at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks,
>> so you won't see anything written until you have 128K worth of
>> activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only after
>> closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im
>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>completed successfully. It then creates the file it writes too but
>>never writes to it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>>
>>
>|||As noted earlier I did this:
--stopping trace
sp_trace_setstatus 1,0
Was I supposed to do this:
--stopping trace
sp_trace_setstatus 1,2
I was hoping to avoid that because BOL say it will delete the definition
from the server. I was hoping to be able to reuse it. Will info not be
written until closed/ deleted?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ui3XxrjOFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Closing Qa will not stop or close the trace and the file size may not
> update that often while it is running. If you look at sp_trace_setstatus
> in BOL you will see hope to stop and close the trace.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> --Status before stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 1
>> --stopping trace
>> sp_trace_setstatus 1,0
>> --status after stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 0
>> I dont see a command to close the Trace, Im just closing Query Anyalyzer.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still nothing
>> gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though nothing
>> gets written ubtil the box is rebooted. It stays at 0kb until I reboot,
>> at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks,
>> so you won't see anything written until you have 128K worth of
>> activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is
>> running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only
>> after closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im
>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>completed successfully. It then creates the file it writes too but
>>never writes to it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>>
>>
>>
>|||This is what I said in my first reply to you. Nothing will be written to the
file until the trace is CLOSED.
Please read my first reply again. You must first stop (set status to 0) and
then close (set status to 2).
If you want to save the trace definition, save the SQL commands that you
used to start it, and write them to a file that you can load up again.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uZ8STvjOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> As noted earlier I did this:
> --stopping trace
> sp_trace_setstatus 1,0
> Was I supposed to do this:
> --stopping trace
> sp_trace_setstatus 1,2
> I was hoping to avoid that because BOL say it will delete the definition
> from the server. I was hoping to be able to reuse it. Will info not be
> written until closed/ deleted?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ui3XxrjOFHA.2788@.TK2MSFTNGP09.phx.gbl...
>> Closing Qa will not stop or close the trace and the file size may not
>> update that often while it is running. If you look at sp_trace_setstatus
>> in BOL you will see hope to stop and close the trace.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> --Status before stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 1
>> --stopping trace
>> sp_trace_setstatus 1,0
>> --status after stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 0
>> I dont see a command to close the Trace, Im just closing Query
>> Anyalyzer.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still nothing
>> gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though
>> nothing gets written ubtil the box is rebooted. It stays at 0kb until
>> I reboot, at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K
>> chunks, so you won't see anything written until you have 128K worth
>> of activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is
>> running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only
>> after closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush
>> the collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>>I just created a server side Trace with no filters for testing but Im
>>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>>completed successfully. It then creates the file it writes too but
>>>never writes to it when I generate activity. Any ideas why?
>>>
>>> TIA, ChrisR
>>>
>>> sql2k sp3a
>>>
>>
>>
>>
>>
>>
>|||Woops! Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ursw1NkOFHA.3292@.TK2MSFTNGP12.phx.gbl...
> This is what I said in my first reply to you. Nothing will be written to
> the file until the trace is CLOSED.
> Please read my first reply again. You must first stop (set status to 0)
> and then close (set status to 2).
> If you want to save the trace definition, save the SQL commands that you
> used to start it, and write them to a file that you can load up again.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uZ8STvjOFHA.3512@.TK2MSFTNGP15.phx.gbl...
>> As noted earlier I did this:
>> --stopping trace
>> sp_trace_setstatus 1,0
>> Was I supposed to do this:
>> --stopping trace
>> sp_trace_setstatus 1,2
>> I was hoping to avoid that because BOL say it will delete the definition
>> from the server. I was hoping to be able to reuse it. Will info not be
>> written until closed/ deleted?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:ui3XxrjOFHA.2788@.TK2MSFTNGP09.phx.gbl...
>> Closing Qa will not stop or close the trace and the file size may not
>> update that often while it is running. If you look at
>> sp_trace_setstatus in BOL you will see hope to stop and close the trace.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> --Status before stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 1
>> --stopping trace
>> sp_trace_setstatus 1,0
>> --status after stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 0
>> I dont see a command to close the Trace, Im just closing Query
>> Anyalyzer.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still
>> nothing gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though
>> nothing gets written ubtil the box is rebooted. It stays at 0kb until
>> I reboot, at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>>> Hi Chris
>>>
>>> With server side traces, SQL Server writes to the file in 128K
>>> chunks, so you won't see anything written until you have 128K worth
>>> of activity.
>>>
>>> Look at fn_trace_getinfo( ) to check that your trace really is
>>> running.
>>>
>>> Then try stopping and closing the trace:
>>> sp_trace_setstatus 1, 0 -- stop the trace
>>> sp_trace_setstatus 1, 2 -- close the trace
>>>
>>> You must stop before closing or the close won't do anything. Only
>>> after closing SQL Server write to the file.
>>> Or, you can stop the SQL Server services, and that will also flush
>>> the collected trace events to the file.
>>> --
>>> HTH
>>> --
>>> Kalen Delaney
>>> SQL Server MVP
>>> www.SolidQualityLearning.com
>>>
>>>
>>> "ChrisR" <noemail@.bla.com> wrote in message
>>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>>I just created a server side Trace with no filters for testing but
>>>Im not able to start it. I call sp_trace_setstatus 1,1 and it says
>>>completed successfully. It then creates the file it writes too but
>>>never writes to it when I generate activity. Any ideas why?
>>>
>>> TIA, ChrisR
>>>
>>> sql2k sp3a
>>>
>>>
>>>
>>
>>
>>
>>
>>
>

Thursday, March 22, 2012

cant seem to connect to a store procedure on SQL server

Hi,

I am trying to get a page to call on a store procedure in SQL server and can seem to get it right. Any advice would be appreiated

<%@. Page Language="vb" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.OleDb" %>
<script runat="server"
 Dim Conn as new OleDbConnection("Provider=SQLOLEDB;Server=*;Database=WEST1;User ID=*;Password=*;Trusted_Connection=False")
 Sub Submit(Sender as Object, e as EventArgs)
 Dim objCmd as OleDbCommand = new OleDbCommand ("vendorPending 10031,85140109", Conn)
 Dim objReader as OleDbDataReader
 objCmd.CommandType = CommandType.StoredProcedure
 Try
 objCmd.Connection.Open()
 response.write ("opening data")
 objReader = objCmd.ExecuteReader
 catch ex as OleDbException
 Response.write ("Error retrieving data.")
 end try
 DataGrid1.DataSource = objReader
 DataGrid1.DataBind()
 objCmd.Connection.Close()
 End Sub
</script>
<html>
<head>
 <title></title>
</head>
<body>
 <form method="post" runat="server">
 <tbody>
 <tr>
 <td>
 <br />
 </td>
 </tr>
 <tr>
 <td>
Login :
 </td>
 <td>
 <asp:textbox id="user" runat="server"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
Password :
 </td>
 <td>
 <asp:textbox id="Pass" runat="server" textmode="Password" MaxLength="10"></asp:textbox>
 <br />
 </td>
 </tr>
 <tr>
 <td>
 </td>
 <td>
 <asp:button id="Button1" onclick="submit" runat="server" text="submit"></asp:button>
 </td>
 </tr>
 <br />
 <tr>
 <td>
 </td>
 </tr>
 </tbody>
 <br />
 <asp:DataGrid id="DataGrid1" runat="server" HorizontalAlign="Left" Font-Names="Arial" ItemStyle-BackColor="#FFFFFF" AlternatingItemStyle-BackColor="#CCCCCC" Font-Size="10pt" Font-Name="Arial" width="755px" cellpadding="2" gridlins="vertical" BorderColor="Black" AutoGenerateColumns="False">
 <HeaderStyle font-size="Small" font-names="Arial" font-bold="True"></HeaderStyle>
 <EditItemStyle horizontalalign="Left"></EditItemStyle>
 <AlternatingItemStyle backcolor="#CCCCCC"></AlternatingItemStyle>
 <ItemStyle horizontalalign="Right" backcolor="White"></ItemStyle>
 <Columns>
 <asp:BoundColumn DataField="invnum" HeaderText="<b>Invoice Number</b>"></asp:BoundColumn>
 <asp:BoundColumn DataField="invdate" HeaderText="Invoice Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
 <asp:BoundColumn DataField="duedate" HeaderText="Due Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
 <asp:BoundColumn DataField="invamt" HeaderText="Invoice Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
 <asp:BoundColumn DataField="payamt" HeaderText="Pay Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
 </Columns>
 </asp:DataGrid>
 </form>
</body>
</html>

The store proceudure takes 2 int parameters. I have coded it inline in order to test it. I also will implement validation once I get this working

Thanksuse sqlclient instead oledb. there is built in support for sql server.
heres some sample code :


Dim myCommand As SqlCommand
Dim myParam As SqlParameter
dim objcon as new sqlconnection("...") ' check www.connectionstrings.com for the right connection string
myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "usp_Test"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int))
myCommand.Parameters("@.userid").Value = Trim(userid)
'add other params
dim objreader as
If objCon.State = 0 Then objCon.Open()
objreader = mycommand.ExecuteReader() ' objdatareader is your sqldatareader.
myCommand.dispose()

hth

Sunday, March 11, 2012

Can't run dts package job

I setup a job which call a dts package (I try to call it from file system and from SSIS Package Store with the same result) and it doesn't run.

If you take a look from the SQL Server Agent you could see this >>
2012-06-17 09:01:02 - ! [LOG] Step 1 of job 'prueba' (0x85B4D5E843DB3145A6A1A6A0A43D04F3) cannot be run because the DTS subsystem failed to load. The job has been suspended
Any ideas?This was happening to people that installed to a drive other than C:. Did you install to a different drive? It's a bug in setup. If so, you can fix it by changing the dbo.syssubsystems table in MSDB for the dts subsystem.

Look to see where it's pointing. It should be something like this:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL

If the drive is different then the one you installed on, correct it. It should work then.

Please let me know if this worked.
Thanks,|||I saw what you said. I installed SQL in D drive in a directory called SQL_Server. The path you told me was right but the path in the agent_exe field was wrong.

Thanks for your help.|||Excellent! Glad to help out.|||

This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

|||

Hi,

I have a similar situation. We have program files on C:\ and Data files on D:\. I ran the following sql to update the syssubsystems table, but I still get the " 2007-01-22 12:13:37 - ! [LOG] Step 1 of job 'MaintenancePlan 1' (0xC433119AFB756E4E844D94759A65B68A) cannot be run because the SSIS subsystem failed to load. The job has been suspended" message.

INSERT INTO [msdb].[dbo].[syssubsystems]

([subsystem_id]

,[subsystem]

,[description_id]

,[subsystem_dll]

,[agent_exe]

,[start_entry_point]

,[event_entry_point]

,[stop_entry_point]

,[max_worker_threads])

VALUES

(11

,'SSIS'

,14538

,'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL'

,'C:\Program Files\Microsoft SQL Server\90\DTS\Connections\Binn\DTExec.exe'

,'DtsStart'

,'DtsEvent'

,'DtsStop'

,200);

Do I need to restart the Intergration services service?

Paul

|||

janislkovach wrote:
This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

Did you log in as a SQL Server admin?|||

Yes I can login to the box with Domain admin, then onto the Database engine with the SA id and password. I run the sqlagent job as a 'SQL Server Intergration Services package' and it fails with the 'failed to load susbsystem message'. If I run it as an 'Operating system(CmdExec)' i.e dtexec / SQL "[package name]" etc, then it works.

I can obviously get round the problem this way, but it's frustrating it doesn't work as it should. I think it started to happen after SP1 was installed.

Is it something to do with what account Integration Services runs under? As all the other SQL server service run as domain admin, my intergration Services service runs as NT authority\network service. Should I run this as domain admin also?

One further thing, when comparing other boxes with my problem box, is that I have DTEXEC.exe & DTAttach(and associated Dll's) in C:\Program Files\Microsoft SQL Server\90\DTS\Connections\Binn on the problem box whereas they are in C:\Program Files\Microsoft SQL Server\90\DTS\Binn in all other ones. Is this my problem? Can I simply move them to C:\Program Files\Microsoft SQL Server\90\DTS\Binn?

Can't run dts package job

I setup a job which call a dts package (I try to call it from file system and from SSIS Package Store with the same result) and it doesn't run.

Look to see where it's pointing. It should be something like this:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL

If the drive is different then the one you installed on, correct it. It should work then.

Thanks for your help.|||Excellent! Glad to help out.|||

This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

|||

Hi,

INSERT INTO [msdb].[dbo].[syssubsystems]

([subsystem_id]

,[subsystem]

,[description_id]

,[subsystem_dll]

,[agent_exe]

,[start_entry_point]

,[event_entry_point]

,[stop_entry_point]

,[max_worker_threads])

VALUES

(11

,'SSIS'

,14538

,'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL'

,'C:\Program Files\Microsoft SQL Server\90\DTS\Connections\Binn\DTExec.exe'

,'DtsStart'

,'DtsEvent'

,'DtsStop'

,200);

Do I need to restart the Intergration services service?

Paul

|||

janislkovach wrote:
This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

Did you log in as a SQL Server admin?|||

I can obviously get round the problem this way, but it's frustrating it doesn't work as it should. I think it started to happen after SP1 was installed.

Can't run dts package job

I setup a job which call a dts package (I try to call it from file system and from SSIS Package Store with the same result) and it doesn't run.

Look to see where it's pointing. It should be something like this:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL

If the drive is different then the one you installed on, correct it. It should work then.

Thanks for your help.|||Excellent! Glad to help out.|||

This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

|||

Hi,

INSERT INTO [msdb].[dbo].[syssubsystems]

([subsystem_id]

,[subsystem]

,[description_id]

,[subsystem_dll]

,[agent_exe]

,[start_entry_point]

,[event_entry_point]

,[stop_entry_point]

,[max_worker_threads])

VALUES

(11

,'SSIS'

,14538

,'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL'

,'C:\Program Files\Microsoft SQL Server\90\DTS\Connections\Binn\DTExec.exe'

,'DtsStart'

,'DtsEvent'

,'DtsStop'

,200);

Do I need to restart the Intergration services service?

Paul

|||

janislkovach wrote:
This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

Did you log in as a SQL Server admin?|||

I can obviously get round the problem this way, but it's frustrating it doesn't work as it should. I think it started to happen after SP1 was installed.

Sunday, February 12, 2012

Can't get unicode strings using JDBC driver

Hi;

If I call the following to get a Statement, ResultSet.getString() works fine with unicode strings:
Statement stmt = conn.createStatement();

But If I use the following:
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Then all chars > 127 are returned as an ? from ResultSet.getString().

I think this is a bug.

? - thanks - dave

ps - I believe it's the latest Sql Server 200 drivers - they are all dated 4/21/04|||David --

It looks like this was resolved in the microsoft.public.sqlserver.jdbcdriver forum. Let us know if you still have issues.

-shelby

Can't get unicode strings using JDBC driver

Hi;

If I call the following to get a Statement, ResultSet.getString() works fine with unicode strings:
Statement stmt = conn.createStatement();

But If I use the following:
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Then all chars > 127 are returned as an ? from ResultSet.getString().

I think this is a bug.

? - thanks - dave

ps - I believe it's the latest Sql Server 200 drivers - they are all dated 4/21/04|||David --

It looks like this was resolved in the microsoft.public.sqlserver.jdbcdriver forum. Let us know if you still have issues.

-shelby

Can't get unicode strings

Hi;
If I call the following to get a Statement, ResultSet.getString()
works fine with unicode strings:
Statement stmt = conn.createStatement();
But If I use the following:
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
Then all chars > 127 are returned as an ?
I think this is a (BIG BIG BIG) bug.
? - thanks - dave
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
What driver are you using? 2000, 2005 beta 1 or 2005 beta 2?
Thanks,
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"David Thielen" <david@.windward.net> wrote in message
news:3rqsk1tti6dmn9dr5ifevdsls612iqu7hs@.4ax.com...
> Hi;
> If I call the following to get a Statement, ResultSet.getString()
> works fine with unicode strings:
> Statement stmt = conn.createStatement();
> But If I use the following:
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> Then all chars > 127 are returned as an ?
> I think this is a (BIG BIG BIG) bug.
> ? - thanks - dave
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
|||I think the latest SqlServer 2000 ones. They are dated 4-21-04
thanks - dave
On Fri, 14 Oct 2005 15:13:15 -0700, "Angel Saenz-Badillos[MS]"
<angelsa@.online.microsoft.com> wrote:

>What driver are you using? 2000, 2005 beta 1 or 2005 beta 2?
>Thanks,
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Dave:
That behavior doesn't reproduce for me -- can you post some code?
-shelby
"David Thielen" <david@.windward.net> wrote in message
news:teh0l19vvmg9gc2fvkes9a24rh9jpr9otv@.4ax.com...
>I think the latest SqlServer 2000 ones. They are dated 4-21-04
> thanks - dave
> On Fri, 14 Oct 2005 15:13:15 -0700, "Angel Saenz-Badillos[MS]"
> <angelsa@.online.microsoft.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
|||On Mon, 17 Oct 2005 18:03:36 -0700, "Shelby Goerlitz [MSFT]"
<shelbyg@.online.microsoft.com> wrote:

>Dave:
>That behavior doesn't reproduce for me -- can you post some code?
Please take a look at http://www.windward.net/jdbc_unicode.zip Two key
notes:
1) use conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE) - if you just do conn.createStatement() it
works.
2) If you create a database with, for example Chinese as the default
charset, then Chinese can be retrieved - but not Russian. So the
"default" charset seems to be ok - but not other charsets. If your
default charset is English then most anything > 127 is a problem.
thanks - dave

>-shelby
>"David Thielen" <david@.windward.net> wrote in message
>news:teh0l19vvmg9gc2fvkes9a24rh9jpr9otv@.4ax.com.. .
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Hi;
Any luck finding the bug using the example I created?
thanks - dave
On Tue, 18 Oct 2005 07:58:16 -0600, David Thielen <david@.windward.net>
wrote:

>On Mon, 17 Oct 2005 18:03:36 -0700, "Shelby Goerlitz [MSFT]"
><shelbyg@.online.microsoft.com> wrote:
>
>Please take a look at http://www.windward.net/jdbc_unicode.zip Two key
>notes:
>1) use conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
>ResultSet.CONCUR_UPDATABLE) - if you just do conn.createStatement() it
>works.
>2) If you create a database with, for example Chinese as the default
>charset, then Chinese can be retrieved - but not Russian. So the
>"default" charset seems to be ok - but not other charsets. If your
>default charset is English then most anything > 127 is a problem.
>thanks - dave
>
>
>david@.at-at-at@.windward.dot.dot.net
>Windward Reports -- http://www.WindwardReports.com
>Page 2 Stage -- http://www.Page2Stage.com
>Enemy Nations -- http://www.EnemyNations.com
>me -- http://dave.thielen.com
>Barbie Science Fair -- http://www.BarbieScienceFair.info
>(yes I have lots of links)
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Dave:
Sorry for the delay -- the issue has been escalated on this side and the dev
team is looking at it.
-shelby
"David Thielen" <david@.windward.net> wrote in message
news:cq5jl1d9gk6csfr197rc2lbaqfsk4lqgbs@.4ax.com...
> Hi;
> Any luck finding the bug using the example I created?
> thanks - dave
>
> On Tue, 18 Oct 2005 07:58:16 -0600, David Thielen <david@.windward.net>
> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)

Friday, February 10, 2012

Cant get my Reporting Services to work within DotNetNuke

I am trying to add a call to a Microsoft Report from my module. I have got the following code:

PrivateSub btnPrint_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnPrint.Click

Dim formatAsString = "PDF"

'This is the name/location where the report will be generated on the web server

'the best part is that you can name the actual file anything you want in this code

'give write permissions to this folder:

Dim fileNameAsString = "C:\Temp\Tester.PDF"

'name of report on the report server that you want to use

'its basically / project name / report name

Dim reportPathAsString = "/NDPReports/Report1"

Dim historyIDAsString =Nothing

Dim deviceInfoAsString =Nothing

Dim showHideAsString =Nothing

'results is the file stream that we will be using

Dim results()AsByte

Dim encodingAsString

Dim mimeTypeAsString

'hbs2 is the name of my web ref to the reportserver,

Dim warnings()As hbs2.Warning =Nothing

Dim reportHistoryParameters()As hbs2.ParameterValue =Nothing

Dim streamIDs()AsString =Nothing

'The actual reporting service

Dim rsAsNew hbs2.ReportingService

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'report parameters, passed in by querystring

Dim Parameters(1)As hbs2.ParameterValue

Parameters(0) =New hbs2.ParameterValue

Parameters(0).Name = "Initials"

Parameters(0).Value = Request.QueryString("Initials")

Parameters(1) =New hbs2.ParameterValue

Parameters(1).Name = "WorkDate"

Parameters(1).Value = Request.QueryString("WorkDate")

'this is where the report is generated

results = rs.Render(reportPath, format, historyID, deviceInfo, Parameters, _

Nothing, showHide, encoding, mimeType, _

reportHistoryParameters, warnings, streamIDs)

'Write the file to the webserver, using what ever name you want!

Dim streamAs FileStream = File.OpenWrite(fileName)

stream.Write(results, 0, results.Length)

stream.Close()

DownloadFile(fileName,True)

Response.ClearContent()

Response.ClearHeaders()

Response.ContentType = "application/pdf"

EndSub

'--This is the Download file function that forces the download----

PrivateSub DownloadFile(ByVal fnameAsString,ByVal forceDownloadAsBoolean)

Dim pathAs Path

Dim fullpath = path.GetFullPath(fname)

Dim name = path.GetFileName(fullpath)

Dim ext = path.GetExtension(fullpath)

Dim typeAsString = ""

IfNot IsDBNull(ext)Then

ext = LCase(ext)

EndIf

SelectCase ext

Case ".pdf", ".html"

type = "Application/pdf"

Case ".txt"

type = "text/plain"

Case ".doc", ".rtf"

type = "Application/msword"

Case ".csv", ".xls"

type = "Application/x-msexcel"

CaseElse

type = "text/plain"

EndSelect

If (forceDownload)Then

Response.AppendHeader("content-disposition", "attachment; filename=" + name)

EndIf

If type <> ""Then

Response.ContentType = type

EndIf

Response.WriteFile(fullpath)

Response.End()

EndSub

I have added the web reference to the reportserver, but when I load up my dotnetnuke portal I am getting an assembly error? Any help?
Thanks,
Greg Duke

Dear Sir:

I use DotNetNuke as the "menu driver" for all Reporting Services reports -

I have noticed that you are doing a lot of work for the User and maybe you have found a solution - but

If Not -

Within DotNetNuke create a page and within the page indicate that a URL is used to call the application - (call the reporting services report and cust and paste the URL into the DotNetNuke URL for the Page definition) once the user calls the Reporting Services Report - then let the user export to whatever format they want (PDF, Excel, etc.)

Best Regards

Thursday, March 29, 2012

cant start Trace

cant start Trace

cant start Trace

Thursday, March 22, 2012

cant seem to connect to a store procedure on SQL server

Sunday, March 11, 2012

Can't run dts package job

Can't run dts package job

Can't run dts package job

Sunday, February 12, 2012

Can't get unicode strings using JDBC driver

Can't get unicode strings using JDBC driver

Can't get unicode strings

Friday, February 10, 2012

Cant get my Reporting Services to work within DotNetNuke

sql report manager

Blog Archive

About Me