User Vars Of Type String Initialized To Nulls Not Passing Correctly To Sp

Feb 24, 2008

i defined 3 pkg scope user variables of type string in the ssis variables window, typed null as their value, and tried passing them (thru exec sql task) to an sp who expects 3 varchar(23) params.

The sp is blowing up because their values arent null.

The sql task command reads exec sp_name ?,?,?. In the sql task editor's param mapping window, I have each param listed with direction "input", data type varchar and the individual sp params in the parameter name column. I think the plumbing is set up correctly because I'm fine when I send the System Variable StartTime as a 4th param to the sp with data type DATE on the ssis end and datetime on the sp end.

Does anyone know if ssis string and engine varchar are incompatible or perhaps if null in the variables window doesnt initialize variables?

View 1 Replies


ADVERTISEMENT

Passing Vars To An In Statment

May 9, 2008

i am going mad. when i hardcode my usernames into my in statment i get the results i expect...

Code------------------------------
Select u.UserID from dbo.[User] u where u.UserName in ('iresponsejamie.seaton','iresponsephil.smith)


Result---------------------------
3509
3506




but when i use a variable with more than one user in it and pass that into my in clause i get nothing returned when i should have the same result set.

Code------------------------------
DECLARE @CollectorList VarChar(100)

Set @CollectorList= '''iresponsejamie.seaton'',''iresponsephil.smith'''

Select u.UserID from dbo.[User] u where u.UserName in (@CollectorList)

Result---------------------------
nothing


as i said if i use - Set @CollectorList= 'iresponsejamie.seaton' - it works fine but when i include another user - Set @CollectorList= '''iresponsejamie.seaton'',''iresponsephil.smith''' - i get nothing back.

any ideas guys

View 7 Replies View Related

Best Way To Set User Vars Based On Query

Mar 1, 2008

what's the best way to set user vars in ssis from a query. I have some components that set a from and to date in a table. I'd like to select those values right into some user vars in the pkg.

View 1 Replies View Related

The Connection String Property Has Not Been Initialized

Jan 2, 2007

Dear all,
I'm using ASP.net 2003, I need to display some of the messages that retrieve from a table in my sql server 2k. When I run the program, it works fine, but all of the messages that are supposed to get from the db table were not show. Below is my connection string in web config :
<add key="dbconn" value="data source=localhost;initial catalog=MyWeb;Min Pool Size=3;Max Pool Size=20;Pooling=true;password=mypassword;persist security info=True;user id=sa;workstation id=local" />
Any help will be greatly appreciated

View 3 Replies View Related

The Connection String Property Has Not Been Initialized

Jan 2, 2007

Dear all,
I'm using ASP.net 2003, I need to display some of the messages that retrieve from a table in my sql server 2k. When I run the program, it works fine, but all of the messages that are supposed to get from the db table were not show. Below is my connection string in web config :
<add key="dbconn" value="data source=localhost;initial catalog=MyWeb;Min Pool Size=3;Max Pool Size=20;Pooling=true;password=mypassword;persist security info=True;user id=sa;workstation id=local" />
Any help will be greatly appreciated

View 5 Replies View Related

Connection String Property Has Not Be Initialized ???

Jan 18, 2008

Hello,
I try to execute the following SP in my class below. But get a runtime error saying
"the connection string property has not been initialized" line 11
I have set permissions on the SP to public/execute
What is wrong here. My connection string also is below
thanks
Ehi

 1 public class signup_data_entry
2 {
3 public signup_data_entry()
4 {
5 SqlConnection con = new SqlConnection("cellulant_ConnectionString");
6
7
8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
9 command.CommandType = CommandType.StoredProcedure;
10
11 con.Open();

 
 
<connectionStrings>  <add name="cellulant_ConnectionString" connectionString="Data Source=1NEWDAYSQLEXPRESS;Initial Catalog=cellulant;Integrated Security=True"   providerName="System.Data.SqlClient" /> </connectionStrings>

View 14 Replies View Related

The Connection String Property Has Not Been Initialized!

May 15, 2008

Hi,  
I wrote the code below to store images on SQL Server 2005; however, I keep getting this error: The connection String property has not been initialized! My Connection String is stored in the web.config and am using ASP.NET 3.5.
 1 using System;
2 using System.Configuration;
3 using System.Data;
4 using System.Web;
5 using System.Web.UI;
6 using System.Web.UI.HtmlControls;
7 using System.Web.UI.WebControls;
8 using System.IO;
9 using System.Data.SqlClient;
10 using System.Web.SessionState;
11
12 public partial class CarImage : System.Web.UI.Page
13 {
14 protected void Page_Load(object sender, EventArgs e)
15 {
16 }
17
18 protected void UploadButton_Click(object sender, EventArgs e)
19 {
20 if (Page.IsValid)
21 {
22 Stream imgStream = UploadImage.PostedFile.InputStream;
23 int imgLen = UploadImage.PostedFile.ContentLength;
24
25 byte[] imgBinaryData = new byte[imgLen];
26 int n = imgStream.Read(imgBinaryData, 0, imgLen);
27
28 String StrCarID = Request.QueryString["CarID"];
29 int CarID = System.Convert.ToInt32(StrCarID);
30
31 int RowsAffected = SaveToDB(imgBinaryData, CarID);
32 if (RowsAffected > 0)
33 {
34 Response.Write("&lt;BR>The Image was saved");
35 }
36 else
37 {
38 Response.Write("&lt;BR>An error occurred uploading the image");
39 }
40 }
41 }
42
43 private int SaveToDB(byte[] imgBin, int carID)
44 {
45 //Store Conn String in Web.Config
46 SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["LocalSqlServer"]);
47 SqlCommand command = new SqlCommand("INSERT INTO tblImage (CarID, Image) Values (@carID, @imgBin)", connection);
48
49 SqlParameter param0 = new SqlParameter("@imgBin", SqlDbType.Image);
50 param0.Value = imgBin;
51 command.Parameters.Add(param0);
52
53 SqlParameter param1 = new SqlParameter("@carID", SqlDbType.Int, 4);
54 param1.Value = carID;
55 command.Parameters.Add(param1);
56
57 connection.Open();
58 int numRowsAffected = command.ExecuteNonQuery();
59 connection.Close();
60
61 return numRowsAffected;
62 }
63 }
  Any help will be very much appreciated.E
 

View 11 Replies View Related

Parameter Not Passing Correctly From One Report To Another

Feb 29, 2008

I am trying to create a drill-down report by passing the Customer ID number to the drill report. The 'parent' report is a tabular report, as is the 'child.' As long as I used the numeric Customer ID, the child report returned no rows from the passed parameter. However, when I use the Customer Name instead, the child report functions correctly.

Here is the parameter as passed using the Customer ID:
[Customer Hierarchy].[Customer ID].&[70011231]

Can anyone shed some light on why this wouldn't work?

Thanks,

nanc

View 2 Replies View Related

Nulls In Concatenated String

Sep 23, 2005

How do I prevent the following null 'Answer'?This SQL will return a null string for 'Answer' whenever the count is null either for 'subquery-1' or for 'subquery-2', even though the other is not null. I need a string in either case. It would be better to have 'Answer' be "f1=, f2=25" than to have nothing. It doesn't seem right that both COUNT's have to be non-null to get anything other than null for the concatenated 'Answer'. There ought to be a way for COUNT to return 0 in some cases where it now returns null. I'd expect/prefer an 'Answer' of "f1=0, f2=25" or maybe even "f1=<null>, f2=25".I expect I'd have the same problem with nulls even if I wasn't using subqueries.SELECT 'f1='+CAST(COUNT(subquery-1) AS VARCHAR)+', f2='+CAST(COUNT(subquery-2) AS VARCHAR) AS AnswerFROM table1WHERE condition=5GROUP BY fieldX

View 1 Replies View Related

Replace Nulls With Blank Spaces In Float Data Type

May 8, 2008

Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks

View 7 Replies View Related

Conversion From Type 'DBNull' To Type 'String' Is Not Valid

Mar 7, 2008

Hello Friends
           How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like 'Conversion from type 'DBNull' to type 'String' is not valid'. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance.
 Regards,

View 1 Replies View Related

Implement Time Interval Type In Form Of User Defined Type

Dec 7, 2011

Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:

1/4/2006:1/10/2006

which would mean the time period from 1/4 to 1/10.

View 3 Replies View Related

Problems With The Query, ResultSet Property Not Set Correctly, Parameters Not Set Correctly, Or Connection Not Established Cor

Oct 22, 2007



I have the following query in an ExecuteSQL Task:

Insert Into Table2
Select * From Table1 Where Column1Val = '4'


As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.

Why is the above query failing with

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

View 4 Replies View Related

Cannot Debug Stored Procedures Because The SQL Server Database Is Not Setup Correctly Or User Does Not Have Permission To Execute Master.sp_sdidebug.

Aug 7, 2007

Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting 
Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.

View 3 Replies View Related

SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.

Oct 19, 2006

I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx    Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!

View 3 Replies View Related

Unable To Cast Object Of Type 'System.String' To Type 'System.Web.UI.WebControls.Parameter'.

Jun 19, 2008

I'm getting this error on a vb.net page the needs to execute two separate stored procedures.  The first one, is the main insert, and returns the identity value for the ClientID.  The second stored procedure inserts data, but needs to insert the ClientID returned in the first stored procedure.  What am I doing wrong with including the identity value "ClientID" in the second stored procedure? 
Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'.Source Error:




Line 14: If li.Selected Then
Line 15: InsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.Value
Line 16: InsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")
Line 17:
Line 18:
Source File: C:InetpubwwwrootIntranetExternalAppsNewEmploymentClientNewClient.aspx.vb    Line: 16
Here is my code behind... What am I doing wrong with grabbing the ClientID from the first stored procedure insert? 
 
Protected Sub InsertNewClient_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)ClientID.Text = e.Command.Parameters("@ClientID").Value.ToString()ViewState("ClientID") = e.Command.Parameters("@ClientID").Value.ToString()End SubProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.ClickInsertNewClient.Insert()For Each li As ListItem In CompanyTypeID.Items
If li.Selected ThenInsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.ValueInsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")InsertClientCompanyType.Insert()End IfNextEnd Sub

View 2 Replies View Related

Passing Xml String

Apr 27, 2004

Hi,
i m tring to pass my xml string to my sql server but i receive empty value even tho my xml have it's value.
here's my code,

insert user(userName)
select xml_user
from OPENXML(@xmlHandle,'/NewDataSet',1)
with (xml_user nvarchar(20) 'table/user_name')


my XML code
<NewDataSet>
<table>
<user_name>dorris</user_name>
</table>
</NewDataSet>

Please advice on this matter.Thanks

View 1 Replies View Related

Data Type Of Parameter Passing To A Stored Procedure

Feb 25, 2004

Hi,
I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.

The exact error message that occurs is:

ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.

The error occurs in the following code line:
.parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput)

I need to know what to write instead of advarwchar?
Thanks in advance

View 1 Replies View Related

Unable To Cast Object Of Type 'System.DateTime' To Type 'System.String'.

Dec 31, 2007

 Hi,      I got this field (dateSubmitted) having a data type of DateTime but I receive this error "Unable to cast object of type 'System.DateTime' to type 'System.String'."       All value for dateSubmitted field are 12/27/2007 12:00:00 AM. cheers,imperialx 

View 3 Replies View Related

Passing A String Variable To Sql

May 11, 2008

hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great !  tnx

View 2 Replies View Related

Passing A String For IN Clause

Sep 26, 2004

Hello,

How do I pass a string parameter to a Stored Procedure with an 'in' clause?

ex.
Select * FROM Persons
WHERE PersonID IN (1,2,3,4,5,20,56,80)

How do I define my Store Procedure so I can pass the values between () as a string (nvarchar) parameter to the SqlCommand?

Thanks,
W

View 1 Replies View Related

Integration Services :: Passing Complex Type As A Variable In Web Service Task

Oct 5, 2011

When you pass a complex type (the one represented by class) to a web service the BIDS UI allows you to enter values for every field of that type as constants. But what if you want to pass a variable? Once again the UI allows you to specify a variable for that complex type parameter. But how to make this variable in SSIS?I understand it should have the type of Object. But how to specify what the runtime type of this object is? And how to assign all fields to that object?

View 6 Replies View Related

Syntax For Passing A String To SelectCommand

Sep 15, 2007

 Hey,I've inherited a project from my office and am stuck.I'm trying to take input from multiple souces (DropDownLists, TextBoxes, etc) and depending on which ones are used, update a SELECT string with additional AND statements.   <script language=VB runat=server>
Dim resultsql As String
Public Sub Button_Click(ByVal client As String, ByVal state As String)
Dim dv As String
resultsql = resultsql & "SELECT ClientName, Address1, City, State FROM tblClient"
dv &= ""
If (StrComp(client, dv) <> 0) Then
resultsql &= "AND ClientName = " & client
End If
If (StrComp(state, dv) <> 0) Then
resultsql &= "AND State = " & state
End If
resultsql &= " ORDER BY ClientName ASC"
End Sub
</script>Now when I got to display the results of this new string in a GridView I am recieving errors trying to pass my variable "resultsql" into SelectCommand. <asp:GridView ID="Results" runat="server" AutoGenerateColumns="False" DataKeyNames="ClientNumber"
DataSourceID="SqlDataSource3">
<Columns>
<asp:BoundField DataField="ClientName" HeaderText="ClientName" SortExpression="ClientName" />
<asp:BoundField DataField="Address1" HeaderText="Address1" SortExpression="Address1" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConnectionString %>"
SelectCommand=resultsql> </asp:SqlDataSource> I've scoured the web without any success. Any suggestions are appreciated.

View 7 Replies View Related

Passing XML String To Stored Procedure

Nov 28, 2005

hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer  by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in

View 1 Replies View Related

Passing String To Rdlc's TextBox

Jan 23, 2008



Hello
I am pretty new to reporting services. Now i have something like this. on the .cs file, i have a fullName lets say
String fullName = "John Smith",
Now I want to pass this string to the report's textBox and show this value on this textbox.

How can i do this?
1.How should i write in .cs file?
2. How should i set the expression for the textbox?

Thank you very much. It is very urgent! Please help!

View 6 Replies View Related

Solution To Passing Parameters With Sql Text String.

Dec 26, 2003

I thought I would post this solution. I searched a long time and didnt see anything about how to solve my problem. After avoiding this and simply building strings I decided to dig in my heels and try and figure this out. Well, maybe I am just slow. :) Anyhow, here is some code that should help a lot of folks with this question...


Function GetProductCategories(ByVal departmentID) As DataSet

'set the connection string (comes from a property in this case)
Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringWeb"))

'set the sql text string notice the @DepartmentID is my parameter. protected from sql injections
Dim strSQL As String = "SELECT * FROM ProductCategory WHERE DepartmentID = @DepartmentID"

'set new command object to strsql and the connection. required
Dim command As New SqlCommand(strSQL, connection)

'set parameters to pass to through with the strsql. required for parameters. you can take this a set further. See commented fields below.
command.Parameters.Add(New SqlParameter("@DepartmentID", departmentID))

'additional set up for parameters if you like...
'command.Parameters.Add("@departmentID", SqlDbType.Int, 4)
'command.Parameters("@departmentID").Value = departmentID



'set SQLDataAdapter to your previously created command object
'this enables your adapter access to your strSQL, connection and parameters
Dim da As New SqlDataAdapter(command)

'set proper name of table for data set based upon departmentID
If departmentID = 1 Then
Dim ds As New DataSet()
da.Fill(ds, "dtCarriers")
Return ds
End If


'set proper name of table for data set based upon departmentID
If departmentID = 2 Then
Dim ds As New DataSet()
da.Fill(ds, "dtProducts")
Return ds
End If

End Function

View 4 Replies View Related

Problems Passing String Value To Stored Procedure

Aug 2, 2005

Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work.  I'm baffled since I know how to pass an integer to a stored procedure.  In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db.  If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure.  Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below.  Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes  strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception  ProcessModuleLoadException(Me, exc)End TryEnd Sub----------------------------------------
*******************Controller.vb file*******************
Public Function SearchBlog(ByVal strSearch As String) As ArrayList            Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo))        End Function----------------------------------------
*******************        DataProvider.vb file*******************
Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader----------------------------------------
*******************SqlDataProvider.vb file*******************
Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader            Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function----------------------------------------
*******************Stored Procedure*******************
CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS
SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------

View 3 Replies View Related

Passing Variable To String Compare In Function

Dec 8, 2007

I have created a function with:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity

RETURN @Output


END

how can I pass the variable @item correctly for the string comparison

WHERE item = @item AND week = @week

to work correctly please?

WHERE item = '@item' AND week = @week

won't work and

WHERE item = @item AND week = @week

won't work.

View 2 Replies View Related

Passing An IN (a, B, C) List To A Sproc As A String -- Best Method?

Jul 20, 2005

I want to do something like this in a stored proc:------Create Procedure dbo.GetPatients@PatientIdList varchar(200) -- comma separated list of PatientIDsAsSelect *From PatientsWhere PatientId In (@PatientIdList)------I know the above won't work, but of course what I want is if@PatientIdList = '1,2,3' then I want Patient records with PatientIds1, 2, and 3 returned.It looks like the only way to do this is to build the SQL statement asa string within the stored procedure ... which pretty much defeats theusefulness of using precompiled sprocs as I understand it (better offbuilding a dynamic query against a View in that case).Thoughts?Joel Thornton ~ <groups@joelpt.eml.cc>

View 1 Replies View Related

Identifier! [problem In Passing A Lengthy String To An SP]

Sep 5, 2006



exec sp_SearchProductAdvanced "(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice between 3 and 10) ",null

after executing i got this error:

The identifier that starts with '(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice ' is too long. Maximum length is 128.

how can i solve this problem. so that i can use more values .

my lenght exceeds 128. is there any way to get rid of this.

in my SP i have declared the parameter variable as

@criteria nvarchar(1000)



View 9 Replies View Related

Error Passing Concatenated String To Proc

Nov 8, 2007

Hi,

I have a stored proc which accepts a varchar(255) as a parameter and when I call the proc using a concatenised string I get an error i.e.

-- Proc
CREATE PROCEDURE #proc_param_test
@p_param1 varchar(40) = NULL
, @p_param2 varchar(40) = NULL
AS
BEGIN
SELECT @p_param1, @p_param2
END

EXEC #proc_param_test 'test', 'test 2'
returns
---------------------------------------- ----------------------------------------
test test 2

but EXEC #proc_param_test 'test', 'test 2' + ' - the rest'

gives a Incorrect syntax near '+'. error

The solution must be a real doddle but it's a 'mare to find anywhere.

Cheers,
John

View 4 Replies View Related

Passing A Multivalued String To Stored Procedure

Oct 14, 2007

I have a multivalued parameters populated from a dataset with country codes: US,CA,HK etc... I tried to pass the parameter to the stored procedured but that did not work. I did some research and found out that I won't be able to do that.


Writing Queries that Map to Multivalued Report Parameters

http://msdn2.microsoft.com/en-us/library/ms155917.aspx


You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN statement to specify the parameter.
If I am not able to pass the parameter that way, what is the best way to accomplish my goal?
Thanks,
Elie

View 5 Replies View Related

Column Allows Nulls I Want To Change No Nulls Allowed

May 16, 2006

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This sux

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved