Variable Paramters To Functions
Jul 20, 2005
From previous postings I have read it seems that I cannot create a
function that accepts a variable list of parameters. Seems that SQL
Server does not support this.
A few questions:
Is this true?
What is a workaround?
Then how does the function COALESCE do it?
Cut and pasted from online books:
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be
specified. All expressions must be of the same type or must be
implicitly convertible to the same type.
View 3 Replies
ADVERTISEMENT
Nov 4, 2007
I am in a SQL Class and I am starting to learn about SQL Parameters in conjunction with using them from a C# program. I have a couple of questions.
1. When I include an Output parameter in the list or paramters that I send, SQL always is returning a message indicating that the parameter was missing, when I know in fact that I am passing the paramter (@RtnMsg)
2. When I remove any reference to "@RtnMsg", which it can not find, and execute the stored procedure it always returns a -1 and never inserts the record.
Here is my code for the stored procedure....
ALTER procedure [dbo].[Insert_Biblio]
@ISBN as char(11),
@CALL_NO as char(20),
@DEWEY_NO as char(20),
@AUTHOR as char(5),
@TITLE as char(100),
@AUTHOR_BORN as int,
@AUTHOR_DIED as int,
@PUBLISHER as char(30),
@YEAR_PUBLISHED as int,
@BOOK_INFO as char(49),
@BOOK_HEIGHT as int,
@BIBLIO_INFO as char(100),
@COST as money,
@TOPIC_1 as char(30),
@TOPIC_2 as char(30),
@RtnMsg as varchar(50) output
as
declare @Book_ID as char(5);
begin
Set @RtnMsg = 'Insert Successful'
return 0
select @Book_ID = max(book_ID) + 1
from collection
insert into biblio
(BOOK_ID,ISBN, CALL_NO, DEWEY_NO, AUTHOR, TITLE,
AUTHOR_BORN, AUTHOR_DIED, PUBLISHER, YEAR_PUBLISHED,
BOOK_INFO, BOOK_HEIGHT, BIBLIO_INFO, COST,
TOPIC_1, TOPIC_2)
values (@BOOK_ID,@ISBN, @CALL_NO, @DEWEY_NO,
@AUTHOR, @TITLE, @AUTHOR_BORN,
@AUTHOR_DIED, @PUBLISHER, @YEAR_PUBLISHED,
@BOOK_INFO, @BOOK_HEIGHT, @BIBLIO_INFO,
@COST, @TOPIC_1, @TOPIC_2)
return 0
end
Here is the C# program code (this version is the one where SQL tells me @RtnMsg is missint.
SqlParameter[] parms = new SqlParameter[16];
parms[0] = new SqlParameter("@ISBN", SqlDbType.Char, 11);
parms[0].Value = (txtISBN.Text.Length == 0 ? (object)DBNull.Value : txtISBN.Text);
parms[1] = new SqlParameter("@CALL_NO", SqlDbType.Char, 20);
parms[1].Value = (txtCallNbr.Text.Length == 0 ? (object)DBNull.Value : txtCallNbr.Text);
parms[2] = new SqlParameter("@DEWEY_NO", SqlDbType.Char, 20);
parms[2].Value = (txtDewerNbr.Text.Length == 0 ? (object)DBNull.Value : txtDewerNbr.Text);
parms[3] = new SqlParameter("@AUTHOR", SqlDbType.Char, 5);
parms[3].Value = (txtAuthor.Text.Length == 0 ? (object)DBNull.Value : txtAuthor.Text);
parms[4] = new SqlParameter("@TITLE", SqlDbType.Char, 100);
parms[4].Value = (txtTitle.Text.Length == 0 ? (object)DBNull.Value : txtTitle.Text);
parms[5] = new SqlParameter("@AUTHOR_BORN", SqlDbType.Int);
parms[5].Value = (txtAuthorDOB.Text.Length == 0 ? (object) DBNull.Value : Convert.ToInt32(txtAuthorDOB.Text));
parms = new SqlParameter("@AUTHOR_DIED", SqlDbType.Int);
parms.Value = (txtAuthorDOD.Text.Length == 0 ? (object) DBNull.Value : Convert.ToInt32(txtAuthorDOD.Text) );
parms[7] = new SqlParameter("@PUBLISHER", SqlDbType.Char, 30);
parms[7].Value = (txtPublisher.Text.Length == 0 ? (object)DBNull.Value : txtPublisher.Text);
parms = new SqlParameter("@YEAR_PUBLISHED", SqlDbType.Int);
parms.Value = (txtYearPublished.Text.Length == 0 ? (object) DBNull.Value : Convert.ToInt32(txtYearPublished.Text) );
parms[9] = new SqlParameter("@BOOK_INFO", SqlDbType.Char, 49);
parms[9].Value = (txtBookInfo.Text.Length == 0 ? (object)DBNull.Value : txtBookInfo.Text);
parms[10] = new SqlParameter("@BOOK_HEIGHT", SqlDbType.Int);
parms[10].Value = (txtBookHeight.Text.Length == 0 ? (object)DBNull.Value : Convert.ToInt32(txtBookHeight.Text));
parms[11] = new SqlParameter("@BIBLIO_INFO", SqlDbType.Char, 100);
parms[11].Value = (txtBiblioInfo.Text.Length == 0 ? (object)DBNull.Value : txtBiblioInfo.Text);
parms[12] = new SqlParameter("@COST", SqlDbType.Money);
parms[12].Value = (txtCost.Text.Length == 0 ? (object)DBNull.Value : Convert.ToDouble(txtCost.Text));
parms[13] = new SqlParameter("@TOPIC_1", SqlDbType.Char, 30);
parms[13].Value = (txtTopic1.Text.Length == 0 ? (object)DBNull.Value : txtTopic1.Text);
parms[14] = new SqlParameter("@TOPIC_2", SqlDbType.Char, 30);
parms[14].Value = (txtTopic2.Text.Length == 0 ? (object)DBNull.Value : txtTopic2.Text);
parms[15] = new SqlParameter("@RtnMsg", SqlDbType.VarChar, 50);
parms[15].Direction = ParameterDirection.Output;
foreach(SqlParameter parm in parms)
{
if (parm.ParameterName != "@ReturnValue")
{ parm.Direction = ParameterDirection.Input; }
}
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["CIS277ConnectionString"].ConnectionString;
SqlConnection cn = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand("dbo.Insert_Biblio", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parms);
cn.Open();
int rtnCode = cmd.ExecuteNonQuery();
View 1 Replies
View Related
Jan 12, 2000
Does anyone have a way of finding all parameters with default values, for any stored procedure in a database?
aTdHvAaNnKcSe!
View 1 Replies
View Related
Nov 15, 2007
What is the proper syntax for the SET transact-sql for mult-value parameters? Is this even possible?
Code Block
declare @Name char(55);
set @Name = 'name1';
select *
from table1
where name in (@Name)
How do I write the SET line so that it will behave like this:
where name in ('name1','name2','name3')
I'm sure this is an easy one, I'm currently looking it up, but thought someone might be able to answer this for me quick.
Thanks much!
View 5 Replies
View Related
Feb 28, 2008
i have a query in SQL Server 2005 that uses a parameter like so:ALTER Procedure [dbo].[p_FullTextEquipmentSearch]
@search as nvarchar(50),
as
SELECT * FROM EquipmentView
where contains(*, @search);
what i want to so is add more parameters to the query so that the user can choose either 1 or many for the search can anyone help me on this
View 2 Replies
View Related
Nov 27, 2007
Hi there,
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
Code Block
RS2000
="with " &
"member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " &
"set [Rolling Quarters] as '" + Parameters!DateQuarter.value +".lag(4):" + Parameters!DateQuarter.value + "'" &
"SELECT " &
"{[Measures].[Ex Price]} ON COLUMNS , " &
"filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows " &
"FROM Sales " &
"WHERE (" & Parameters!CustomersLevel.Value & ")"
RS2005
with
member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)'
set [Rolling Quarters] as '@DateQuarter' +'.lag(4):' + '@DateQuarter'
SELECT
{[Measures].[Ex Price]} ON COLUMNS ,
filter({CrossJoin([Items by Class].[Item Class Description].members, [Rolling Quarters])}, [Measures].[Ex Price] > 0) on Rows
FROM Sales
WHERE STRTOSET(@CustomersLevel)
Thanks in advance,
Vivek
View 4 Replies
View Related
Nov 14, 2006
Hello,
I just installed the new SQL 2005 sp2 CTP on my stand alone report server. Now whenever a parmeter is not multi-value I get
One or more data sources is missing credentials
when clicking off the parameter. If I set the parameter to multi-value it works fine but confuses the users.
Does anyone know how to debug this or if a fix is available ? Yes it was working before I installed the service pack.
View 3 Replies
View Related
Apr 28, 2008
We are using MS Reporting Services to generate Xmls for corporate billing statements. Our DBAs found that MS ReportingServices are querying Oracle Catalogue before executing stored procedures in RDLs to get Oracle parameter information. Is there a way to eliminate the trip to database before executing the stored procedures?
Hongmei
View 1 Replies
View Related
Sep 20, 2007
Using gridview to display the data and sql server 2000 I have
a column in the database say departtime of datetime datatype that
cntains the date and time resp(09/19/2007 9:00 PM). I am separating the
date and time parts to display in two different textboxes say
txt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by using
the convert in sqldatasource. Now i need to update the column in the
database and i am using Updatecommand with parameters in aspx lke
updatecommand = "Update table set departtime = @departtime" . How can
i update my column as datetime by getting the data from 2 texboxes as
now i have 2 textboxes displaying data for single column means if user
edit the data in txt1 as(10/19/2007) then on click of update i need to
populate the column daparttime as (10/19/2007 9:00 PM).Please let me know if you have any questions.
View 1 Replies
View Related
Mar 29, 2006
I have one table with categoriestblCategoriescat_id | cat_name-----------------1 | cat 12 | cat 23 | cat 34 | cat 45 | cat 56 | cat 6and one table with projects which relates to tblCategoriestblProjectsproj_id | proj_name | cat_id----------------------------1 | proj 1 | 22 | proj 2 | 23 | proj 3 | 34 | proj 4 | 2How would you create stored procedure for searching some string infiled proj_name but within multiple categoriesfor exampleCREATE PROCEDURE [spSearch](@SEARCH_STRING nvarchar(200),@CAT_ID int)ASBEGINSELECT proj_idFROM tblProjectsWHERE (proj_name LIKE '%' + @SEARCH_STRING + '%') AND (cat_id =@CAT_ID)ENDBut that one works only with one categorie and i need to search for oneor more categories at once, does anyone have a solution? Is theresomething like ellipsis (...) in C++ for MSSQL?
View 7 Replies
View Related
Nov 5, 2007
Hi
Im writing a program that stores data to SQL server 2005.
Program writen on VC++ 2003.
Code is very simple and it works ok if server runs and parameters are ok.
try
{
HRESULT hr = m_Connection.CreateInstance(__uuidof(ADODB::Connection));
String ConnectionStr;
ConnectionStr.Format(_T("Provider=sqloledb;Data Source=%s;Initial Catalog=MyDB;User Id=%s;Password=%s;"),m_Server.c_str(),m_Username.c_str(),m_Password.c_str());
_bstr_t bstrConnection(ConnectionStr.c_str());
m_Connection->CursorLocation = ADODB::adUseClient;
hr = m_Connection->Open(bstrConnection, L"", L"", ADODB::adConnectUnspecified);
}
catch(_com_error &e)
{
_bstr_t Description = e.Description();
}
But!
if i give wrong paramterer for Data Source , it may happen if user configures it and misprints it, or if server goes offline for some reason, program just hangs on m_Connection->Open and never gives exception as it should.
Any ideas what wrong?
Thanks.
View 4 Replies
View Related
Nov 16, 2007
In SQL 2000, working with Maintenance plan wizard, what would be best settings and values should i choose in "Update Data Optimization information" window,
Thanks,
View 6 Replies
View Related
Mar 5, 2008
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing. Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
Thanks for any help!
View 1 Replies
View Related
May 26, 2006
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
View 9 Replies
View Related
Oct 25, 2006
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
View 1 Replies
View Related
Mar 6, 2008
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
View 4 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" 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 Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" 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: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Oct 15, 2007
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
View 1 Replies
View Related
Feb 15, 2006
I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()
View 2 Replies
View Related
Nov 4, 2015
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
View 6 Replies
View Related
Oct 9, 2001
Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)
Thanks
View 1 Replies
View Related
Mar 7, 2005
When trying to upsize an access database to sql server using the upsize wizard, I get the following error:
"Object variable or With block variable not set."
Any assistance is greatly appreciated.
View 3 Replies
View Related
Oct 18, 2007
Hello!
I'm using SQL Server 2000.
I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)
SELECT @operation='DIS'
CREATE table #myTable(value VARCHAR(20))
INSERT into #myTable values('@operation')
SELECT top 1 @parameterValue = value from #myTable
-- Now @parameterValue is assigned the string '@operation'
-- Here I need some way to retrieve the value of the @operation variable I declared before (in fact
-- another process writes into the table I retrieved the value from), in this case 'DIS'
DROP TABLE #myTable
I've tried several ways, but didn't succeed yet!
Please tell me there's a way to solve my problem!!!
Thank you very much in advance!
View 7 Replies
View Related
May 27, 2015
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
View 8 Replies
View Related
Aug 17, 2004
While I was processing the cubes, error "Object Variable Or With Block Variable Not Set" prompt out,
what does it mean ?
Please help !!!
View 1 Replies
View Related
May 15, 2007
Is there any way to use a 2 dimensional array of strings as the Variable Enumerator for the "Foreach From Variable Enumerator". I am trying to copy a collection of files from folder A to folder B. In a script, I would populate, let us say, an array of (2,10), for 10 files, with one column representing the source file and other column representing the target column task. Then I would like to set this string array variable as the "Variable Enumerator" for the "Foreach From Variable Enumerator" and use file system tasks in the foreach loop to perform the tasks. The problem is that the "Foreach From Variable Enumerator" does not let me choose an index, but passes only one index 0, so, I will only be able to pass just one column. How do I let the foreach enumerator let me choose an index. The other foreach enumerators, foreach item and ADO give me the option to select index. I would like the same functionality in the foreach variable.
Note: I cannot use the "For Each File" enumerator, since the files are to be selected by a script only.
Thanks for the help.
View 3 Replies
View Related
Oct 4, 2006
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 2 Replies
View Related
Sep 28, 2006
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 12 Replies
View Related
Jan 3, 2007
Hi,
I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:
DECLARE @my_variable int
, <several_others>
SET @my_variable = ?
<do_some_stuff>
Of course, I also set up the parameter mapping.
However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.
I have several reasons for wanting to do this:
- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.
- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.
- Passing the variable once also makes it easier to design the script outside IS using Management Studio.
- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.
- I prefer to use the OLE DB connection manager because it's a de facto standard here.
Could anyone help me out with the following questions:
- Is the above possible?
- If so, how?
- If not, why not?
- If not, what would be the best way around this problem?
Thanx in advance,
Pipo
View 6 Replies
View Related
May 21, 2007
How can I inside a DFT set a System variable, for example "TaskName" to an own created User Variable?
The reason is that I need to use this variable later in the Control Flow.
Regards
Riccardo
View 10 Replies
View Related
Feb 15, 2008
Hi All!
I have a parent package that contains two children... The second child depends on the succes of the first child.
THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...
Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...
I tried to execute coth children as Out Of Proc as well as In Proc...The same
Would anybody have an idea how to resolve this problem?
Thanks in advance for any help!
Vladimir
View 5 Replies
View Related
