Calling Variable Inside T-SQL Statement
Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)
I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XXXXXXXXXXXXX
-- Create date: 10/4/2007
-- Description: This proc is designed to pull all CSA
-- part sales from XXXXXX business system and upload them
-- into the local XXXXXXXX Database for commission reporting
-- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int
As
INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )
SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo
WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')
In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.
This is the error message I get when I try to create the proc:
Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17
Incorrect syntax near '+'.
~~~ Thanks All~~~
View Complete Forum Thread with Replies
Related Forum Messages:
Calling SP Inside The SP
hai guys how should we have to cal the store procedure inside the same store procedure. for Example Create procedure A as Begin Select * from mytable execute A end. is this the correct one
View Replies !
Calling A SP Inside A Cursor Loop..
I have SP, which has a cursor iterations. Need to call another SP forevery loop iteration of the cursor. The pseudo code is as follows..Create proc1 asBeginVariable declrations...declare EffectiveDate_Cursor cursor forselect field1,fld2 from tab1,tab2 where tab1.effectivedate<Getdate()---/////Assuming the above query would result in 3 recordsOpen EffectiveDate_CursorFetch next From EffectiveDate_Cursor Into @FLD1,@FLD2begin/*Calling my second stored proc with fld1 as a In parameterand Op1 and OP2 Out parameters*/Exec sp_minCheck @fld1, @OP1 output,@OP2 outputDo something based on Op1 and Op2.endWhile @@Fetch_Status = 0Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2/* Assume If loop count is 3.and If the Fetch stmt is below the begin Stmt, the loop iterations are4 else the loop iterations are 2*/begin/*Calling my second stored proc with fld1 as a In parameter and Op1and OP2 Out parameters*/Exec sp_minCheck @fld1, @OP1 output,@OP2 outputDo something based on Op1 and Op2.endThe problem I had been facing is that, the when a stored proc is calledwithin the loop, the proc is getting into infinite loops.Any Help would be appreciated.Satish
View Replies !
Calling Stored Procedures Inside Cursors
I have created a cursor using the following type of syntax: DECLARE MyCursor CURSOR FOR SELECT ID FROM tblEmployees OPEN MyCursor BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.
View Replies !
Newbe Question: Calling Function Inside Select
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View Replies !
Calling Subscription Screen Inside Report Viewer
Hello I use SQL Server 2005 Reporting Service to develop all my Reports. i design the report and deploy to the report server. i have created a Web application where i use the above report to be shown inside a ReportViewer page. when any user view's the report i also want to give them the subscription screen inside the report viewer. BIG QUESTION is how do i call the subscription screen of a specific report inside the report viewer? any help is appreciated /Chandresh Soni
View Replies !
Variable Inside A Variable From Sql TAsk
I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning: "select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +"" As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing. [Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Where am I wrong? TIA
View Replies !
Calling Managed CLR Procedure From Inside User Defined Function -- How To ?
I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value. I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement. I did read that you could use an external stored procedure and/or managed CLR procedures inside a function. I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine. However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement. Any advice on how to do this? Thanks, Bruce
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View Replies !
Set Variable To Sp Result, Inside Sp
I need to get the result of one stored procedure, and set it as the value of a variable in another. Would this work? (Currently not in a location I can test this!) Code: create proc spAddEntry @pkid int, @calcTotal numeric(10,2) = exec spCalcTotal '@pkid' as ...
View Replies !
Variable Inside A Nested Loop
I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Update Variable Inside DataFlow
Dear friends, Someone told me that I couldnt update the global variavel inside a dataflow... but i need to have some solution to do that... :-( I crate my identity column manually based on a global variable... and it works perfect, but in one dataflow I have to insert 2 times in the some table... so... in the second time my identity will be GlobalVariavel + increment of the first time rows inserted... how can I save this increment in the dataflow? How can I count the rows inserted in the first time do use it in the second time i save Data to the some table?? Thanks
View Replies !
Updating The Variable Inside The Dataflow
Is it possible to update the value of a user defined variable within the DataFLow in SSIS. I am aware you can update a variable using a script task in the Control Flow, but how about the DataFlow? Thanks for any help in advance.
View Replies !
Executing A Variable Inside A Stored Procedure
Hello :) I need to do something like this: CREATE PROCEDURE SelectCostumers @name varchar(100) Declare @SQL = "SELECT Id, Name FROM Costumers" AS IF (@name IS NULL) @SQL ELSE @SQL += "WHERE Name LIKE @name" See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get. Thank you
View Replies !
Change Local Variable Inside Query
/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
View Replies !
Assign Value To A Variable Inside Data Flow
hi, I have an aggregate transformation in a dataflow task. It has only 1 output value. I'm trying to assign this value to a user variable, but I can't figure out how to do that. i can hack something silly together - like write the value to the db, and then get it out, but I there has to be an easier way.. Thanks a lot.!
View Replies !
Using Variable Value As Calling Parameter
I want to know why this code does not work in a query. The table 'iDay_INTRAD' exists and is detected if I use a statement exec sp_tables iDay_INTRAD. This statement returns one row. The code below returns no rows. Apparently the query is looking for table '@p1' not the value of this variable. Is it possible to do what I want to do in SQL Server? DECLARE @p1 varchar (10) DECLARE @p2 varchar(10) SET @p1 = 'iDay_' SET @p2 = 'INTRAD' SET @p1 = @p1 + @p2 exec sp_tables @p1 Thanks
View Replies !
Using A While Inside A Select Statement
Hi All, Can we use the while loop inside a select statement? Meaning, something like this: Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right. Hope the question is clear. Thanks a lot. Mannu.
View Replies !
Select Statement Inside UDf
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
View Replies !
Automatic Rollback Inside Using Statement?
If I start a transaction using the following approach ... using (SqlTransaction trans = destConn.BeginTransaction()) { ...do some transfers using SqlBulkCopy }...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?
View Replies !
If Statement Inside A Stored Procedure!!!!
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far: CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1 begin SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO any suggestion will be very helpful thanks
View Replies !
'Case' Statement Inside 'Where' Clause
Hi I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following? DECLARE @searchCriteria Int SET @searchCriteria = 2 SELECT column1, column2 FROM TABLE WHERE CASE @searchCriteria WHEN 1 THEN (column3 = 1000100) WHEN 2 THEN (column3 = 1000101) END CASE ...cheers
View Replies !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Select Case Inside Sql Statement ?
Code: function findingcinemaid(nameofthecinema) findcinemaid = "select cinemasid from cinemas" &_ " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'" set cinemaidfound = objconndb.execute (findcinemaid) end function select case foreachcinema case 0 cinemaname = "ONE UTAMA" findingcinemaid(cinemaname) case 1 cinemaname = "MINES" findingcinemaid(cinemaname) case 2 cinemaname = "SEREMBAN 2" findingcinemaid(cinemaname) case 3 cinemaname = "KINTA CITY" findingcinemaid(cinemaname) case 4 cinemaname = "BUKIT RAJA" findingcinemaid(cinemaname) case 5 cinemaname = "TEBRAU CITY" findingcinemaid(cinemaname) case 6 cinemaname = "SUNWAY PYRAMID" findingcinemaid(cinemaname) case 7 cinemaname = "SURIA KLCC" findingcinemaid(cinemaname) end select any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it
View Replies !
Variable Type Errors When Calling Stored Procedure
I currently have a stored procedure that is defined as follows: CREATE PROCEDURE UpdateSyncLog @TableName char(100), @LastSyncDateTime datetime, @ErrorState int OUTPUT I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered: exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results). I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32 The parameter mappings are as follows: Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1 Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1 The error I am getting when running this execute sql task is as follows: Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well. What gives?
View Replies !
Calling A VB Function From An SQL Statement
I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.
View Replies !
Help: Table Name Displaying Twice In SQL Statement Inside SQL Server Enterprise Manager
For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.For example,SELECT * FROM State StateEven when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly. Does anyone know why the table name would display twice in a table inside of a particular database?
View Replies !
Need Info About Dynamic Reporting (Read Problem Statement, Inside)
Hi All! I have a specific requirement. I have to generate a report on the fly. The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more. From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly. So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time. I hope i have explained very clearly. I would be grateful for your contributions. Thanks
View Replies !
Calling Three Different Variable Coming From Three Different Sql Queries Which Defined In Same Stored Procedure From DataAdapter
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view. Simply I want to below statement in stored procedures and call from program. Can any one help me plz. con = DataBaseConnection.GetConnection(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da.Fill(ds,"Dep"); SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con); da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da1.Fill(ds,"Exp"); SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con); da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da2.Fill(ds,"Inc"); string deposits = Convert.ToString(ds.Tables["Dep"].Rows[0].ItemArray[0]); string expenses = Convert.ToString(ds.Tables["Exp"].Rows[0].ItemArray[0]); string income = Convert.ToString(ds.Tables["Inc"].Rows[0].ItemArray[0]); GridView1.DataSource = ds; GridView1.DataBind(); GridView1.Rows[0].Cells[0].Text = "Total"; GridView1.Rows[0].Cells[1].Text = deposits GridView1.Rows[0].Cells[2].Text = expenses; GridView1.Rows[0].Cells[3].Text = income; //The above program was success. // This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u. SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString(); cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString(); cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString(); ASPX Code for Grid View <h2>Account Summary</h2><br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" > <Columns> <asp:TemplateField > <ItemTemplate> <asp:Label ID="Label1" runat="server" Text="Total"></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField ><%--HeaderText="Deposits"--%> <ItemTemplate> </ItemTemplate> <HeaderTemplate> <a href="Deposits.aspx" >Deposits</a> </HeaderTemplate> <ItemStyle HorizontalAlign="Center" /> </asp:TemplateField> <asp:TemplateField > <HeaderTemplate> <a href="Expenses.aspx">Expenses</a> </HeaderTemplate> <ItemStyle HorizontalAlign="Center" /> <ItemTemplate> </ItemTemplate> </asp:TemplateField> <asp:TemplateField > <HeaderTemplate> <a href="Income.aspx">Income</a> </HeaderTemplate> <ItemStyle HorizontalAlign="Center" /> <ItemTemplate> <%-- <%# Eval("Course") %>--%> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> Plz solve this.
View Replies !
Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement DECLARE @VALUE_KEEP NVARCHAR(120), @COLUMN_NAME NVARCHAR(120) SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME') SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3) PRINT @VALUE_KEEP PRINT @COLUMN_NAME RESULTS ------------------------------------------------------------------------------------------- FirstName <-----------@VALUE_KEEP FirstName <-----------@COLUMN_NAME SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName SELECT FirstName from Contacts returns: Brent How do I make this select statement work using the @COLUMN_NAME variable? Any help greatly appreciated!
View Replies !
Calling Stored Procedures In A Select Statement
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
View Replies !
Use Of A SSIS Variable Of Type “Object� Inside Script Component And Task Component
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type. On exiting from the script the variable of type object should contain something like in the following lines: AAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDD €¦€¦€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦€¦. On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion. Is there anyone who have experienced something like this? Could anyone provide any example of that? Thanks in advance!
View Replies !
Using C# Variable With SQL Statement
Greetings everyone, I am trying to use a c# string with an SQL statement in a data adapter (.NET 03) The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row: WHERE (login = '" & test & "') WHERE (login = '" + test + "') any ideas? PS: If I change to something like WHERE (login = 'abcdef') I get a result meaning there's something wrong with the way I am putting the variable in the sql query. Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window THANKS!
View Replies !
|
TRACKER
