.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

unable to pass dynamic dates to stored procedure with pivot

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :ASP.Net

hi All,

                  I am unable to date as dynamic parameter to stored procdure with pivot.i am getting


Msg 8114, Level 16, State 1, Procedure Sample, Line 3

Error converting data type nvarchar to datetime.

Msg 473, Level 16, State 1, Procedure Sample, Line 3

The incorrect value "@date1" is supplied in the PIVOT operator.

below is my stored procdure



procedure Sample(@date1 datetime,@date2 datetime)



View Complete Post

More Related Resource Links

Dynamic Pivot in Stored Procedure


i have a dynamic  pivot table which has a parameter passed in stored procdure

i have temp table to store columns in it to be dynamic and also a parameter

but this script make me go mad

colud you find me the error

Inncorrect syntax near '+@columns+'

Item_Group2 table structure is


this is script

	@Group1_ID int
create table #tASD ( ID int,pivot_column varchar(100))
DECLARE @columns VARCHAR(8000),@sql varchar(max)
Select @sql='select *  from Item_Group2 where Group1_ID=('+@Group1_ID+')'
insert into #tASD
SELECT @columns = COALESCE(@columns + ',[' + cast(pivot_column as varchar) + ']',
'[' + cast(pivot_column as varchar)+ ']')


max(ID) FOR [Name]IN (' + @columns + ')

AS p
drop table  #tASD

thanks so much

Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?

Entity Framework 4.0 Dynamic Data - stored procedure


I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.

passing dates in stored procedure?

I am trying to pass dates in  a stored procedure and keep getting an error - here is the code and error.  eventually the sproc will be used in SSRS.     ALTER PROC [dbo].[USP_SPROC_X] @DATE DATETIME AS SELECT ITEM , PRICE , SELL_DSCR , PRC_EFF_DT , PRC_END_DT , PROPOSED_BY ,CONFIRMED_BY FROM SALES WHERE @DATE BETWEEN PRC_EFF_DT AND PRC_END_DT   Msg 4104, Level 16, State 1, Procedure [PROCNAME], Line 141 The multi-part identifier "PRC_EFF_DT" could not be bound.

can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

how to pass dataset from one stored procedure to another stored procedure

I'm wondering how a dataset returned by a stored procedure can be passed to another stored procedure.     mark it as answer if it answered your question :)


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Can I pass a value for a output variable in a stored procedure in ADO.NET?

I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?

Unable to access VM network drive in SQL during backup generated by stored procedure

I get the following error in a log file created in the osql command: Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150Cannot open backup device 'x:\Servername_master_sqlbu_200703101930.bkf'.Device error or device off-line. See the SQL Server error log for moredetails.Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150BACKUP DATABASE is terminating abnormally. The device is established in a CMD file right before the osql command is started that starts my stored procedure.  The CMD in this file is: for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i :loopif exist x:\ net use x: /delnet use x: \\192.168.%SUBNET%.1\backup if not ERRORLEVEL 1 ( goto continue) else ( echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%\LogFiles\%Computername%.log" sleep 60 goto loop) :continue echo IP ADDRESS OBTAINED Echo delete old log file if it exists if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log Echo Rename log file to old.log rename %3\%computername%_kaman_full_sqldb_backup.log %computername%_kaman_full_sqldb_backup_old.log echo backup SQL Databases on server will start now osql -E -n -d %1 -i %2\kaman_full_sqldb_backup.sql -h-1 -o %3\%Co

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Pass list to stored procedure



I'm looking to pass a list into a stored procedure, stored as List<myClass>.  I've passed a table in before but I'm not sure how to pass a list - anyone help?

Please help URGENT - how to pass XML from aspx page to Stored procedure


Hi all,

I would like to take your help for a small task of mine. I have dataset whose contents have been converted as xml, the contents of which needs to be sent to a stored procedure. How do i go about creating methods in the data layer and the stored procedure.

What should be parameter type in the data layer's method and what should be the parameter type in the stored proc. I dont want to use a varchar at the stored proc level because it is limited to a length of only 8000 characters. Please suggest a solution

I would be glad if someone could post some sample code.

Pass Multi-Value parameters to Stored Procedure problem



I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:


ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
cte2 as
SELECT [ID],[First Name],[Last Name] FROM Sta

error in pivot stored procedure




                    Please check my pivot stored procedure which i am getting error.I think there is a mistake in quotations.I am unable to figure it out.

I have a column name in the table but still getting errors

Invalid column name 'Totalhou.rsInvalid column name 'EmpNo'.


PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME

How do I call an SSRS Report from a stored procedure, and pass parameters?


Hi all,

I have a Report, that I want to run ad-hoc from a Stored Procedure. 

I want to render this report to PDF format, and save it to a drive.

The difference between this post and most threads I've seen is I don't want to run a stored proc within the report and send parameters to the stored proc.

I DO want to call a report FROM the stored proc, and send parameters TO the report.

How can I do this?

Many thanks,


Running SSIS package from Stored Procedure using dtexec and Pass boolean value



We are using SSIS 2005 and sql server 2005. My package has a boolean type package level variable. Eg: IsClientNull boolean type.

I call this package from stored procedure. In addition to passing values for other parameters, how can I pass boolean value.

My code is like this:

SET @CMD='dtexec ' +
'/FILE ' + @pShareName + @pPackageName + ' ' +
'/MAXCONCURRENT " -1 " ' +
'/SET "\Package.Variables[User::StartDate].Properties[Value]";' +
CONVERT(char(10), @pStartDate, 120) + ' ' +
'/SET "\Package.Variables[User::EndDate].Properties[Value]";' +
CONVERT(char(10), @pEndDate, 120)
IF (@pClient IS NULL)

 SET @CMD = @CMD + ' ' +
 '/SET "\Package.Variables[User::IsClientNull].Properties[Value]";' +

Datetime filter value pass to BCS stored procedure parameter


I have data coming from one of the stored procedures that has startdate and enddate as parameters.I have created a business data webpart tht accepts this ECT.When I give the start date and end date in this format----> 2010-02-02 00:00:00Z,I can able to get the data from the database.

               As this is not a feasible approach,I have used a two datetime filters that has calendar control attached to it,so that user can use this to pick the start date and end date.Now,my issue here is when I choose the startdate and enddate,ECT is not accepting the format of the date that these filters are passing.I have changed the filter descriptor LOBDateTimeMode to "Local" in the external content type,but still I am receiving the same error.Does any one has any solution for this??

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend