I need to pass the where clause of my statement as an input parameter for a stored procedure. As this is built dynamically within the program (Borland C++), it could sometimes exceed the maximum 8000 bytes of a varchar. I thought of sending in two input paramenters and concatenating them into a memo in the sql, but cannot seem to be able to execute the memo variable (i.e. exec(memo) doesn't work). Is there any other options for passing in such a huge parameter.
Also, I do not think there is any other option than passing in the where clause: passing in seperate parameters and building the clause in sql would require over 300 parameters to be passed, and as the possible number of combination is indefinate, I cannot have a different Stored Procedure for each potential case.
hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.
basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.
From what I've read the replicate() fn will covert to varchar of max 8000 bytes.
I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.
I'm using this store proc to get a pivot table in SQL:
USE [m0851System] GO /****** Object: StoredProcedure [dbo].[sp_CrossTabIntoTable] Script Date: 01/25/2008 09:59:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable] @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100) -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007 ,@tbl_result varchar(100), @fld_sufx varchar(10) -- FIN JULIEN BONNIER AS
--DECLARE @sql varchar(8000), @delim varchar(1) DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007 -- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case) IF LEFT(@tbl_result,1)='#' BEGIN IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '') EXEC ('DROP TABLE ' + @tbl_result + '') END ELSE BEGIN IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '') EXEC ('DROP TABLE ' + @tbl_result + '') END -- FIN JULIEN BONNIER
EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '''' END FROM tempdb.information_schema.columns WHERE table_name='##pivot' AND column_name='pvt'
-- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007 --SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' + SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot -- FIN JULIEN BONNIER -- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007 ORDER BY pvt -- FIN JULIEN BONNIER
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1) PRINT(LEN(@sql)) SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ') -- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007 SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ') -- FIN JULIEN BONNIER
--EXEC (@select) PRINT('YOYO'+@select) RETURN SET ANSI_WARNINGS ON
But now my @sql and @select that are varchar(8000) get bigger than 8000 for one of my reports... So my query fails ever times.
I had a VARCHAR(MAX) parameter declared in my stored procedure and trying to concatenat single column from a table which has~500 rows into a string and keep in this variable, if i am not mistaken, i read that the VARCHAR(MAX) actually can hold up to 2GB of data, so it make me confuse why the variable which i declared as MAX size, can only hold up 8000 characters, any idea?
I am trying to change a text field into a varchar 8000. I get his error message when trying to convert.
Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or to select only those that are over the 8000 character mark?
I was trying to build a string based on one field from one table in Stored Procedure. I know if I declare A VARCHAR variable for the string, it won’t allow to exceed 8000. If does, it will truncate the reminding parts of the fields. What might be the simple solution for that? Even though I know I might need to use several substrings but how? Thanks!
J827
Declare @sql varchar 8000
Select @sql =@sql + convert(varchar(50),fieldName) from tableA where conditions …
Hi, everyone, I want to know is there a way for me to set varchar to store more than 8000 characters? (I did checked from sql server books online and i know that the maximum storage for varchar, but i just want to know is there any exceptional way for me to store more than that).
When we invoque the sqlcommand we get this sqlexception: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length. If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure work correctly in SQL Server 2000 with the same .Net code. Any help would be appreciated. Thanks, Sam
in the statement, where @sql is defined as DECLARE @sql varchar(Max). the problem is that this statement produces results that are in excess of 8000 characters and the results are truncated. Is there anyway to avoid this? I know that it's not possible to user ntext/text as a local variable, and if i try to return the result as an ouput paramater, only the first result is returned.
my code is based off of this article http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables Thanks for any suggestions.
I need to handle this conversion in SSIS and not on oracle.
The following expression is executed on a datatype of dt_str with a length of 8000.
SUBSTRING((DT_STR,8000,1252)Column_name,1,8000)
Records longer then 4000 bytes take an error path
The next expression with 4000 bytes works but there is truncation.
SUBSTRING((DT_STR,4000,1252)Column_name,1,4000)
Basically I need to know how to cast a text or ntext into a varchar or nvarchar using ssis but I need to capture the first 8000 byes without truncation.
is this possible?
Using SSIS Reading From oracle I can convert to a text or ntext field but I am having a hard time going directly to a varchar.
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
We have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] ( @original_FormNum nvarchar(20), @NewRetVal INT OUTPUT ) AS SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */ SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum ) SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/ -----This means the delete can't happen........... BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0 BEGIN SET @NewRetVal = 1 END
IF @AssignedFound > 0 AND @AvailFound > 0 BEGIN SET @NewRetVal = 2 END
IF @AssignedFound = 0 AND @AvailFound > 0 BEGIN SET @NewRetVal = 3 END END
ELSE BEGIN DELETE FROM dbo.Form WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0 ---Successful deletion END GO -------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
Hi, I need to do the following task, which is described by pseudo-code SELECT * FROM Customers SORT BY @SortExpression How can I do something like it (sorting according to input parameter) Thanks for any idea
Hi everyone, I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.
Hi Help with syntax, I get the error in the line: myDA.Fill(ds, "t1") Function GetProductsOnDepartmentPromotionPaging(ByVal departmentId As String) Dim myConnection As New _ SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myDA As New SqlClient.SqlDataAdapter _ ("MM_SP_GetProductsOnDepartmentPromotion", myConnection)
' Add an input parameter and supply a value for it myDA.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4) myDA.SelectCommand.Parameters("@DepartmentID").Value = departmentId Dim ds As New DataSet Dim pageds As New PagedDataSource myDA.Fill(ds, "t1") pageds.DataSource = ds.Tables("t1").DefaultView pageds.AllowPaging = True pageds.PageSize = 4 Dim curpage As Integer If Not IsNothing(Request.QueryString("Page")) Then curpage = Convert.ToInt32(Request.QueryString("Page")) Else curpage = 1 End If pageds.CurrentPageIndex = curpage - 1 lblCurrpage.Text = "Page: " + curpage.ToString() If Not pageds.IsFirstPage Then lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + _ "?Page=" + CStr(curpage - 1) End If If Not pageds.IsLastPage Then lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + _ "?Page=" + CStr(curpage + 1) End If list.DataSource = pageds list.DataBind() End Function
I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? ThanksRyan
hi all i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently... i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)... first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type... please any help will be appreciated thanks in advance
i created a cookie as follows HttpCookie myCookie = new HttpCookie("Portal"); myCookie["EMail_ID"] = Email_ID;myCookie["Role"] = Role_ID.Value.ToString();myCookie.Expires = DateTime.Now.AddMinutes(1); Response.Cookies.Add(myCookie);
Now i have to take the"EMail_ID" as input parameter in sqldatasourse.
Hi, I am using a ListBox where a user can choose multiple lines. The index of the selected items are then used in a stored procedure.
I wan´t to use the ID´s in this statement: SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)
But how can I do this? If I pass them as a string, then I can´t use them as above. Can I separate the string '1,2,4,9' so I can use them in the statement above? Or can I send the values as a array to the stored procedure?
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great: Code: Dim cmd As New SqlDataSource cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure cmd.InsertParameters.Add("@firstName", txtFirstName.Text) cmd.InsertParameters.Add("@lastName", txtLastName.Text) cmd.InsertParameters.Add("@address1", txtAddress1.Text) cmd.InsertParameters.Add("@address2", txtaddress2.Text) cmd.InsertParameters.Add("@city", txtCity.Text) cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value) cmd.InsertParameters.Add("@zipCode", txtZipCode.Text) cmd.InsertParameters.Add("@telephone", txtTelephone.Text) cmd.InsertParameters.Add("@email", txtEmail.Text) cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value) cmd.InsertParameters.Add("@birthday", txtBirthday.Text) cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@question", txtquestion.Text) cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" cmd.InsertCommand = "sp_insertCustomer"
ERROR: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. 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.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Source Error: Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
I am designing a report which need to take parameter input by user when the report is open. Can anyone please tell me how to do this? Using MS SQL 2005 report. Thanks.
Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----#################################################### USE [tbldata] GO /****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Transfer](@ID int)
I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero
I have created one SSIS package which extract data from database and put that into verious text files like Emp.txt,Add.txt like that. Also I set one globle veriable (CityID) that help in extracting data as citywise. When I ran it through command prompt by passing globle veriable to it like C:setup pcaSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Data>DTExec /FILE Package.dtsx /SET Package.Variables[CityID].Value;100
it gives me data whose CityID is 100 and format it into text files. but when i want data for another CityID (101) it overwrites my all previous text files where i kept that files.
Now my requirment is that i want to set another globle veriable that take PATH as input parameter and place these files in that path location.(How i set this path in command promt and also in SSIS)
Hi I have this procedure it is creating the proc but when I execute it gives error Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@ID". Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@nextCode".
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30)
) AS BEGIN
SET NOCOUNT ON; Declare @ID int; DECLARE @SQL1 VARCHAR(2000) SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code' EXEC(@SQL1); Declare @nextCode varchar; DECLARE @SQL2 VARCHAR(2000) SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)' EXEC(@SQL2); Declare @NextAction varchar(30); Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode; Select @NextAction;
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.
Here is an example of the XML being passed: <object> <property @propID="14" @propType="4" @propValue="Blah blah text" /> <property @propID="217" @propType="2" @propValue="Some other text" /> </object>
I have a table like this in my database: CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT) and I want a stored procedure that will accept XML and update this table. Here is what I am trying: CREATE PROCEDURE sp_UpdateObject @inValues XML AS BEGIN --create a temporary table DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)
--And then insert the values from the @inValues XML argument into the temporary table --I am sure the SELECT statement is VERY wrong
INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE) SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT) FROM @inValues.nodes('/object/property')
--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID AND @props.PROPTYPE != 6
END
I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.
I apologize for the long post. Thanks in advance for any help you can provide.
i want to make a function that will take a table as input parameter. this table will be the output of a stored procedure. while i were writing the function i have an error and when i read about it i found that i can not send a table as input parameter to a function till i create a new TYPE of this table with its columns and data types as UDT but i found that sql server 2005 does not support the type 'table'... my question now is it possible technically to make this function? is it possible to write something like that :
SELECT dbo.MyFunction(exec dbo.MystoredProc)
and in my function i am using CLR-Integration as this : create function MyFunction ( @TempTable table ( ContractID int, ContractNumber nvarchar(20), Name_En nvarchar(80), Name_Ar nvarchar(80), ContractAmount money, CurrencyID nchar(3), DateStart smalldatetime, DateEnd smalldatetime, Currency_En nvarchar(30), Currency_Ar nvarchar(30) ) ) returns table( ContractNumber nvarchar(20), Name_En nvarchar(80), Name_Ar nvarchar(80), ContractAmount decimal, Currency_En nvarchar(30), Currency_Ar nvarchar(30), [Year] int, [Month] int, DomesticAmount decimal ) as external name [AssemblyName].[PathOfTheFunctionInTheAssembly].[FunctionNameInAssembly]
please help me in this code as i need it urgently... thanks in advance, best regards, Moustafa
I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:
Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "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.
The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.
The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.
Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.
Is there a way to force the parameter input area to always show even when default values are specified? I want to always display the parameter area even when I set initial parameters.
