Hi all,
My Problem is Hoe the Stored Procedure returns data to the Calling Env.If it is fieds values then we can use the OUT Parameters.In Case of My SP returns more number of rows .Then how we capture the data and returs to the Calling Env.i know that we can use the Cursors in Oracle but how is it in SQLSERVER???????pls
pls Help me throw some samll exps.Any help Really appriciate..ThaksRams
My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName '@total' is not contained by this SqlParameterCollection.
Here is my stored Proc and my VB program is right below
I- Stored Proc:
CREATE PROCEDURE dbo.totalsub @account bigint,@total bigint outputASselect total=sum(SubPhnNmbr) from tblsub where SubAccNmbr=@accountreturnGO
II- And my pogram in VB is:
Dim totsub As Int64 Dim cm As New SqlCommand Dim cn As New MyConnection cn.open 'my connection is defined by me don't worry about it cm.CommandType = CommandType.StoredProcedure cm.CommandText = "totalsub" cm.Connection = cn Dim pm As SqlParameter pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Account", System.Data.SqlDbType.BigInt)) pm.Value = 100000165 pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4)) pm.Direction = ParameterDirection.Output totsub = cm.Parameters("total").Value cm.ExecuteScalar() totsub = cm.Parameters("total").Value
I also tried using @total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output
No Luck, someone pls can help
I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.
I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).
I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.
I am calling the SP as follows:
Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val) Dim result As String Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString Using con As New System.Data.SqlClient.SqlConnection(cs) con.Open() Dim cmd As New System.Data.SqlClient.SqlCommand() cmd.Connection = con cmd.CommandType = Data.CommandType.StoredProcedure cmd.CommandText = "CreateLogEntry" cmd.Parameters.Add("@ChID", Data.SqlDbType.Int) cmd.Parameters("@ChID").Value = ID cmd.Parameters.Add("@Value", Data.SqlDbType.Int) cmd.Parameters("@Value").Value = val result = cmd.ExecuteNonQuery().ToString End Using End Sub
I have tried amending the ExecuteNonQuery line to ExecuteReader()
Any help appreciated
Regards
Clive
Hi !
I have trying to do an insert with a subroutine that calls a stored procedure, but it doesn’ t run. The page loads properly but nothing is inserted in the table of the database, no errors appears after submit the form.
<asp:Button id="SubmitButton" OnClick="Send_data" Text="Submit" runat="server"/>
Here is the code:
Sub Send_data(Sender As Object, E As EventArgs)
Dim CmdInsert As New SqlCommand("new_user1", strConnection)
CmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = CmdInsert
CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "User_id"))
CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters.Add(New SqlParameter("@e_mail", SqlDbType.varchar, 50, "e_mail"))
CmdInsert.Parameters("@e_mail").Value = mail.Text()
End Sub
What it lacks?
hi, im new to this site so i don't know if i'm posting in the correct forum. anyway, this is my code:---Dim dbMac As DBLibrary = Nothing
dbMac = New DBLibrary(General.GetMACConnectionString)dbMac.OpenConnection("SPR_STAFFMAIN_GETEMPLOYEERECORDS")dbMac.CreateParameter("@USERENTITYID", GetUserEntityID(), Data.SqlDbType.Int)drpEmpNumbers.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataBind()---DBLibrary is a class that opens a connection to the SQL server. i'm getting an empty grid even though the stored procedure returns a row when i test it in the analyzer. is there to debug or test this code? thanks!
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
SQLserver on Small Business Server for Win 2003.Client application updates records in DB. i.e. the content of a field isupdated from "First" to "Second"Client application closed and opend again. DB returns value "Second".Next morning _SOME_ - not all - of the updates seems missing. DB returnsthe value "First"The client software has run for years i several thousand installations,with MSDE/SQLserver on w2k and 2003 servers.This is the first time we have seen this spooky behavior. And it is thefirst time we are targeting SBS.Could there be some unflushed cache between client and SQLserver?Poul Petersen
View 1 Replies View RelatedI have a table which has a row that contains a subreport. I'd like to set the visibility of the row to hidden if the subreport returns no data. I've played with a few techniques but can't seem to get it right. Any ideas?
View 1 Replies View RelatedI have a stored procedure
Code Block
CREATE PROCEDURE WEA_SelectEmployeeListByCourseOrProject
@ProjectID int,
@CourseID int,
@blnIsSearch int,
@strUserName nvarchar(20)
AS
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @strRole nvarchar(15),
@ContactID int
SELECT @strRole = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName
Select DISTINCT Contact.ContactID ID, UPPER(Surname + 'gd ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR
, UPPER(ISNULL(ContactReference,'')) ContactReference
FROM Contact
LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID
WHERE RUEmployee=1
AND
( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID))
AND
(@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID)
AND
(
(@strRole = 'MIS_TUTOR' AND
(AssignedEmployee.ContactID = @ContactID
OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1)
OR AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID))
)
OR
(@strRole <> 'MIS_TUTOR')
)
SET CONCAT_NULL_YIELDS_NULL ON
GO
now if i run this stored procedure in Query Analyzer like so...
exec wea_SelectEmployeeListByCourseOrProject 0,0,1,'K_T'
i get 48 records returned.
but if i lift the SQL out of the stored procedure and run it in Query Analyzer like so....
Code Block
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @ProjectID int,
@CourseID int,
@blnIsSearch int,
@strUserName nvarchar(20)
SET @ProjectID = 0
SET @CourseID = 0
SET @blnIsSearch = 1
SET @strUserName = 'K_T'
DECLARE @Role nvarchar(15),
@ContactID int
SELECT @Role = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName
PRINT @ContactID
Select DISTINCT Contact.ContactID ID, UPPER(Surname + ' ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR
, UPPER(ISNULL(ContactReference,'')) ContactReference
FROM Contact
LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID
WHERE RUEmployee=1
AND
( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID)) -- the above line was modified to make sure only employees explicitly assigned to a project are brought back. unless it's a search
AND
(@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID)
AND
(
(@Role = 'MIS_TUTOR'
AND ( (AssignedEmployee.ContactID = @ContactID OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1)))
OR
AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID)
)
OR
(@Role <> 'MIS_TUTOR')
)
SET CONCAT_NULL_YIELDS_NULL ON
i only get 5 records returned???
so why do i get a difference when its the same SQL??
Username 'K_T' is of role 'MIS_TUTOR' therefore @Role = 'MIS_TUTOR'
any help on unravelling this mystery is appreciated!
Cheers,
Craig
I'm working with PivotTable on Excel 2000 which is connected to an OLAP server (from SQL Server 7 installation). The pivot is intended to analyze Sales during April 2001. Yesterday I found out that OLAP/Excel returned/displayed inconsistent data. The 'April Total' value is NOT equal to the 'Quarter 2 Total' (I already inspected the underlying database and sure that there is absolutely NO data for months after April 2001). The value for 'April Total' is the correct one. I'm not sure whether the problem resides on the OLAP Server or Excel (pivot) itself. For ones who like to help me I would be glad to supply you with the screenshots (just email me).
Please help.
Regards
Dave
I am migrating from 2000 to 2005 and in the process of rewriting DTS to SSIS. So far so good.
I have an import of a flat file that I need to reproduce that works fine in DTS but throws SSIS.
The file contains details of products, and has a Long Description field that may contain lots of different chars - CR & LF amongst them. It's source and destination columns are Text type.
The file is tab delimited, text delimited with double quotes, with row delimiters of CRLF and column names on row 1.
The error SSIS throws is "The column delimiter for column "LongDescription" was not found"
I imagine (perhaps wrongly) that this is because of the extra CRLFs but it worked fine in DTS. It also previews fine, and lets me define the column properties OK in SSIS package designer.
Any help greatly appreciated. I am trying to avoid the obvious thing of replacing those chars in that field at this point as currently the export is shared between a live production server, and my test 2005 rig.
Thanks v much.
Chris.
Hi,
The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"
I get the result as:
Name Address
---- -------
No rows affected.
(1 row(s) returned)
But not the actual row is displayed whereas I would expect to see something like:
Name Address
---- -------
John Doe
No rows affected.
(1 row(s) returned)
I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.
Why no results in first case?
Thanks,
Bahadir
Hello,
I created a Market Basket Data Mining Model with Association Rules, which I want to query and show in a Report. Everything works fine, when I preview the result in the Reporting Services Data tab I see some sort of table which I can expand and then see the related products.
Unfortunately this result seems to be an ADOMD Datareader which I cannot place in a table, matrix or textfield.
If anyone knows how I can make this Informationen available in my Report please let me know.
Thanks in advance
I have a Gridview bound to a SQLDataSource that uses a Stored Procedure expecting 1 or 2 parameters. The parameters are bound to textbox controls. The SP looks up a person by name, the textboxes are Last Name, First Name. It will handle last name only (ie. pass @ln ='Jones' and @fn=null and you get all the people with last name=Jones. I tested the SP in Management Studio and it works as expected. When I enter a last name in the Last Name textbox and no First Name I get no results. If I enter a Last Name AND First Name I get results. I don't understand.
Here's the HTML View of the page. The only code is to bind the Gridview when the Search button is pressed.
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server" TabIndex=1></asp:TextBox> <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3 /> <hr /> </div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="EmpID" CellPadding="4" EnableSortingAndPagingCallbacks="True" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="EmpID" HeaderText="Emp ID" ReadOnly="True" SortExpression="EmpID" /> <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName" /> <asp:BoundField DataField="Nickname" HeaderText="Nickname" ReadOnly="True" SortExpression="Nickname" /> <asp:BoundField DataField="BGS2" HeaderText="BGS2" SortExpression="BGS2" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpbaseConnectionString %>" SelectCommand="GetByName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="fn" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> <asp:ControlParameter ControlID="TextBox1" Name="ln" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> </SelectParameters> </asp:SqlDataSource> </form> </body></html>
I'm pretty new to .NET and am looking for advice on how to speed up a simple stored procedure that returns 35,000 plus rows. It's a super simple query that returns a client list. It's just that there is soooooo many rows, it's super slow when the page loads.
View 4 Replies View RelatedHi, whenever the underlying query being called by EXEC in the followinghas an empty result set I get the following error -- Invalid Data for'Numeric' when EXEC returns empty row. However if I call the querywithout using REPLACE (which I'm forced to do, because openquery doesnot allow variables), I get just an empty result set. Whenever theunderlying query returns a non-empty result set, the code works withouterror (regardless of wether there are nulls in the numeric column).set @switch ='5707550'set @start_date = '01-JAN-2006'set @end_date = '27-JAN-2006'set @month = 1set @year = 2006set @sql_str='SELECT * FROM(select MSC_KEY,to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)), 5)as "PER_CPU_UTIL"FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view vWHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +@switch + ')and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @start_date + '00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') andto_date(''' + @end_date + ' 23:59:00'', ''DD-MON-YYYYHH24:MI:SS'')group by MSC_KEY, trunc(tstamp))WHERE rownum < 10000'SET @sql_str = N'select * from OPENQUERY(VISION, ''' +REPLACE(@sql_str, '''', '''''') + ''')'EXEC (@sql_str);Is there anyway to prevent this error?Thanks,Crazy
View 1 Replies View RelatedI am trying to get data from an Oracle view using an OLE DB data source and a "SQL Command". When I "preview" the data it looks fine, but when I execute the package I get the following error:
Error: 0xC0202009 at Data Flow Task, CEDAR View [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CEDAR View" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset
rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset
Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset
B) Build a “pager� of some sort using the values from the first resultset
I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?
View 1 Replies View Relatedhi,
i am new to SQL. i am trying to retrieve a set of data(i.e. set of rows which satisfies my condition given in the select statement). i want to output this recordset to my calling program i.e C#.net. how could i do this. i was adviced to use temp tables. but i am not sure how to do it. can any one say that using the temp table is the correct way. if so can u give me some sample code.
my proble is
Table name: ProductDetails Table
It has following columns
ProductID Name UnitPrice Category Stock
when i give a category value from front end it should bring me the rows which comes under the given category. there will be many rows under this category. how could i send this set of rows back to my calling program.
regards,
Johny
I have a vbscript to read all files from a directory and, if the fileis valid, I would like my DTS to process it. I tried using thevbscript as an ActiveX workflow script in the DTS, but it does notexecute the data pump until it has completed looping through all thefiles, so only the last file read is sucked into the database(utilizing a global variable as the filename). Is there a way toexecute the data pump task from within the activex script? I can'tseem to find any documentation about executing a DTS task.Basically, the workflow I want is:1)Read files from directory (the number and names may change eachtime). (done with vbs)2)For each file, send it through the transformation into the database.3)When the information is in the database, append a date to the fileand move it to the archive folder. (done with vbs)If I am going about this the wrong way and you see something that isnot obvious to me, please let me know.Thanks in advance!
View 3 Replies View Relatedjava code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies View RelatedHi, does anyone know how to delete data from the SQL database by
calling the stored procedure in the Visual Basic.NET? Because I did the
Delete hyperlink bounded inside a datagrid. I have already displayed
the appointment date, time in the datagrid so I do not have to input
any values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS
(SELECT MemNRIC, AppDate, AppTime
FROM DasAppointment
WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime))
RETURN -400
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10))
RETURN -401
ELSE
DELETE FROM DasAppointment
WHERE MemNRIC = @MemNRIC
END
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
Can someone pls help? Thanks!
I need to call a stored procedure in order to list products by either a text box based search or by clicking on a category.I am very new to this and would really appreciate any knowledge anyone could share.
View 1 Replies View RelatedHi,
I am looking to create a job on our SQL Server 7.0 dB to:
1. Manipulate and move data from table 'a' to table 'b' (via stored proc)
2. Export table 'b' to a comma delimeted, double quote text identifier file (via dts export wizard stored as a DTS )
3. Cleanup table 'b' and end the job.
I am creating a DTS package to execute step 1, then 2 and lastly 3.
How do I execute another DTS package (#2) from within this DTS package?
Also, is there a way I can make the file name for #2 a dynamically generated name (i.e. include date and time in the file name)?
Thank You
Jamie Reis
I have a couple of questions that I hope someone might be able to provide some direction on.
1. Why can't you call a proc when the proc uses temp tables? I wrote a simple proc (select * from table) and the data subscription works perfect. I then take that same query and put it into a temp table and the data set can't be validated by the subscription, Why?
2. Why can't you pass a simple execute statement in a data subscription? (execute dbo.RptTakeTheLeadFLSDinnerCompetition @FiscalQuarter ='2007-Q3', @StoreRegionID = '9231', @StoreType = 'Full-Line Store') works perfect in SQL but once you try and validate in the data subscription is fails.
3. Another question, why if I dumb down the proc and the number of parameters does it work? Are there limitations on the number of parameters you can pass into a proc from a data driven subscription?
Basically, I want to declare a few variables dynamically and pass them into a execute statement that calls the proc. Which in turn runs the report.
Any ideas on where I seem to be going wrong?
Hello
I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.
Any help is highly appreciated!
Kind Regards
Gayatri
Hi All,
It's been a while since I post here. Anyway, I'm trying to call a SSIS package as my data source for SQL reporting service but I keep getting the error "Cannot create a connection to data source 'DsSSIS'.
My DsSSIS consists of the following in the connection string.
="/file c:FarmBillTesting.dtsx /Set Package.Variables[User::FilterValue].Properties[Value];" & Parameters!FilterValue.Value.ToString()
I have tried everything that I know to make this work but I have been able to do so. Any help from the group would be great.
Thanks
Ham
This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).
Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is
"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"
which typically evaluates as
usp_ValveStatusForDay '18 Oct 07','Report_Name'
This previews correctly and the resulting columns are available for mapping to a destination. So far so good.
By the way, is this the best way to call a stored procedure with parameters?
I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.
Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.
I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.
At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.
It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?
ALTER procedure [dbo].[usp_ValveStatusForDay]
(
@dateTime DateTime,
@reportName VarChar(100)
)
AS
BEGIN
DECLARE @day VarChar(10)
DECLARE @month VarChar(10)
DECLARE @year VarChar(10)
DECLARE @start VarChar(25)
DECLARE @end VarChar(25)
SET @day = Convert(Varchar(10),DatePart(day, @dateTime))
SET @month = Convert(VarChar(10), DatePart(month, @dateTime))
SET @year = Convert(VarChar(10), DatePart(year, @dateTime))
IF @month = '1' SET @month = 'Jan'
IF @month = '2' SET @month = 'Feb'
IF @month = '3' SET @month = 'Mar'
IF @month = '4' SET @month = 'Apr'
IF @month = '5' SET @month = 'May'
IF @month = '6' SET @month = 'Jun'
IF @month = '7' SET @month = 'Jul'
IF @month = '8' SET @month = 'Aug'
IF @month = '9' SET @month = 'Sep'
IF @month = '10' SET @month = 'Oct'
IF @month = '11' SET @month = 'Nov'
IF @month = '12' SET @month = 'Dec'
SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'
SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'
--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)
exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'
END
Hi
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
ALTER Procedure [dbo].[usp_CheckUser]
@name nvarchar(50),
@ssn nvarchar(50),
@accountnumber nvarchar(50)
AS
BEGIN
if(@name!=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber
else if(@name!=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber AND name=@name
else if(@name!=null AND @ssn=null AND @accountnumber=null)
select * from Userinfo where name=@name
else if(@name!=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where name=@name AND ssn=@ssn
else if(@name=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent.
thanx for your help in advance.
ramya
I have a SSRS 2012 report which references a custom c# assembly.  This report exists in multiple environments (alpha, beta and  production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies View RelatedHi,
I have a report which contains a subreport placed inside a list. when the subreport returns no data. it appears as a blank space in the main report. So i want to suppress the list when the subreport returns no data. Can somebody help me with this?
thanks
shri