hey folks...
i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how....
any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management
:-)
I have a crystal report that calls a stored procedure from SQL Server 2000. The only parameter I have is @A the SP is: -------------- CREATE PROCEDURE Final @A INT AS Declare @SQL VARCHAR(2000) SET @SQL = 'SELECT * FROM Schools where Areano = @A' EXEC(@SQL) GO
-------------
From the standard report creation wizard, i choose the data (SP). and select the SP, then i got window to enter a parameter values for @A, and i check ( Set to Null value), then press OK, the following error occured
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
create procedure usp_test (@AccountID INT)asbegin DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Account_ID FROM Accounts OPEN @getAccountID FETCH NEXT FROM @getAccountID INTO @AccountID WHILE @@FETCH_STATUS = 0 BEGIN PRINT @AccountID FETCH NEXT FROM @getAccountID INTO @AccountID END CLOSE @getAccountID DEALLOCATE @getAccountIDend i get nearly ten rows in the print statement how can i assign the output to a out variable where i get all ten rows.
What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?
I've been looking into using the ability to select the table of an OLEDB destination task based on a package variable but would like to know if this could be utilised in the following example.
I have a CSV file (potentially there are many) with a number of rows. The rows do not necessarily contain the same number of elements. Each row has an EventID and this is used to determine the database table that the row is to be inserted in.
Initially i thought about using a conditional split to send each row down the path relating to its event ID. There would be multiple destinations depending on the table. With the possibility of 60 to 70 different event ID's this is very quickly going to get out of hand in the designer. I'd like to know if it would be possible to set up a case statement (or similar) in a script component and specify the table name as a variable depending on the event ID. I'd then use this table variable to set the destination task's table property.
Does this sound like a possibility or do i have to go down the route of multiple paths?
I have to download a file via FTP from one of our vendors, the filename is MMDDYYYY.zip, this is a weekly file and is posted between Tuesday and Thursday, so basically I never know exactly what the file is going to be called, to fix that, I added a FTP Task that will download a file if the date = today (Today = Thursday, when the job kicks off), if this fails, try a file where the name = today-1 (Wednesday), if this fails, try today-2 (Tuesday), all of that inside of a Sequence Container and with MaximumErrorCount set to 3. This is fine and it works fine, good.
Now my other issue, the .txt file inside the zip file (1080.txt), after being decompressed, needs to be renamed to MMDDYYYY.txt, where MMDDYYY = to the file I downloaded, but I never know what the file is going to named (MMDDYYYY, MMDD-1YYYY, MMDD-2YYYY).
Is it possible to output the filename of the file that I successfully download to a variable, so I can re-use name?
Can someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff
hi can anyone please help me i have a stored procedure ,can i put the value of this into a variable......???? I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ... example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) ) and ans of this are NL UK IN GN JK then how can i put this into a variable like
Declare @PortfolioName as varchar(250) set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) ) ....... and my ans is @PortfolioName = 'NL','UK','IN','GN','JK'
I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.
Here is what i am trying:
The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.
Code SnippetEXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.
What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows
I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @LoginCount variable to some value, this initially set value is successfully written to the file header.
Really stuck trying different ways to get a string variable (expression based) to parse and run correctly in an ExecuteSQL task. Would someone be able to advise why one of the following methods works and the other does not? I'd really prefer to have the second currently not working method be used if possible.
This method generates a "Syntax error, permission violation, or other nonspecific error. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
Any ways to get the second failing expression to work? Is this a problem due to the OUTPUT parameter? THANKS!
I'm calling a procedure on a remote Server (local SQL2005, remote SQL2005) and I need the return value.
Local: declare @value int execute ('exec mbtest1.dbo.psybcis ?', @value OUTPUT) at [REMOTESQLSERVER] select @value
Remote: create procedure [dbo].[psybcis] (@value int OUTPUT) as begin select @value = '13' end
I do not get a value in the OUTPUT variable - just NULL. Documentation says: Execute a pass-through command against a linked server { EXEC | EXECUTE } ( { @string_variable | [ N ] 'command_string [ ? ] ' } [ + ...n ] [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ] ) [ AS { LOGIN | USER } = ' name ' ] [ AT linked_server_name ] [;] There is an OUTPUT parameter, but how does it work? Is my syntax wrong?
It works when using following syntax, declare @value int exec [REMOTESQLSERVER].mbtest1.dbo.psybcis @value OUTPUT select @value but I need to use this procedure call in a distributed transaction to a Sybase ASE server and this syntax is not allowed for cross-system-calls. So first I want to get it work from SQL2005 to SQL2005.
I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.
Here is the entirety of the SQL in the SQLStatement property: EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT; I have also tried it like this: EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;
Here are my Parameter Mappings: Variable Name Direction Data Type Parameter Name User::ImportJobId Input LONG 0 User::FileType Input LONG 1 User::FileName Input LONG 2 User::FilePath Input LONG 3 User::ImportId Output LONG 4
When this task is run, I get the following error:
0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work. Any thoughts on what I might be doing wrong? Thanks.
I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime) declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it! SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName); SELECT @CoDeptRowID = SCOPE_IDENTITY()" <insertparameters> <asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/> <asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/> <asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/> <asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" /> </insertparameters> </asp:SqlDataSource>
Hello, I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J. THE CODE I USE TO CALL THE SPROC Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty) THE STORED PROCEDURECREATE Proc [prs_VerifyUser @regid uniqueidentifier,@regemail nvarchar(250) OUTPUT ASBEGIN IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid) BEGIN SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid Return 1 END Return 2 END
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!
SELECT count(*) FROM hsi.itemdata a JOIN hsi.keyitem105 b on a.itemnum = b.itemnum JOIN hsi.keyitem106 c on a.itemnum = c.itemnum JOIN hsi.keyitem108 d on a.itemnum = d.itemnum WHERE a.itemtypegroupnum = 102 AND a.itemtypenum = 108 AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + " AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + " AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")
Note that in the sp_executesql statement, the @ret variable from the dynamic SQL gets assigned to the local variable @retVal.
Here's the problem; when the procedure [log].[Log.SetSeverityLevel] that gets dynamically executed does a RAISERROR, the code in [dbo].[ProcedureTest] falls through to the catch block in [dbo].[ProcedureTest] as it should, but the value of @retVal is null in the catch block, when the return code in [log].[Log.SetSeverityLevel] is set to -1.
If you comment out BEGIN TRY / END TRY / BEGIN CATCH / END CATCH in [dbo].[ProcedureTest], the value of @retVal is correctly seen as -1. There's something about the TRY CATCH structure that's blowing away @retVal when an error is raised and it falls through to the CATCH block.
Anybody ever seen this before? Is there a workaround?
I'm trying to use "findstr.exe" to extract some lines of interest from a data file, which I will later load to a table. I'd like to issue this form of a command:
findstr.exe "^SEARCHSTRING" "srcfile" > "dstfile"
I build the arguments using expressions, and both the search string and source file get correctly set. However, the ">" seems to be ignored--I can see the lines spitting out to the temporary window when I run under VS.
QUESTION: how do you redirect the output of a command run under an Execute Process Task?
I am downloading a webpage as a text file in order to read a specific string to assign it as a variable/parameter in order to create an output file name. I would like to know how would I be able to look for a specific string and output as another variable for the rest of the package.
2015 Conforming Loan Limits ------------------------------------------------------------------------ o _Loan Limits for Calendar Year 2015--All Counties _[XLS] </DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL_FLAT.xlsx>_ , _[PDF] </DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL.pdf>_ ​ o _List of 46 Counties with Increases in Loan Limits for 2015
[Code] ...
To explain it a more better way, I have a sample webpage text here. I should be searching for "FullCountyLoanLimitList" appended by the current year (like FullCountyLoanLimitList2015) and copy the entire file name in the text file and assign it to another variable so that I can download that specific file using WebClient connection.
I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string. Â For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.
Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.
