Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Function With A Parameter Of Type Object.


I would like to create a function that accepts a value of any type as parameter. I mean, it could receive a value of type int, datetime, char,...
In .net, I would use the type "object". Is there an equivalent for SQL server function ?



View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Unable To Cast Object Of Type 'System.String' To Type 'System.Web.UI.WebControls.Parameter'.
I'm getting this error on a 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 Replies !   View Related
Unable To Cast Object Of Type 'System.Object' To Type 'System.Data.DataSet'.

hi  i dont know how to do type casting.

this.Variables.ObjVariable this objVariables i have create in variable place below like this


Name:Variable datatype int32 values 0

Name: NumberofRowsdatatype int32  values 10000

Name: ObjVariable  datatype Object


My code



public override void CreateNewOutputRows()



Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".


System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();

//System.Data.Odbc.OdbcDataAdapter oLead = new System.Data.Odbc.OdbcDataAdapter();

//SqlDataAdapter oLead = new SqlDataAdapter();

System.Data.DataTable dt = new System.Data.DataTable();

DataSet ds = (DataSet)this.Variables.ObjVariable;  // here i am getting error


ADODB.Record rs = new ADODB.Record();


oLead.Fill(ds, rs, "Variables");

foreach (DataRow row in dt.Rows)




Output0Buffer.Column = (int)row["Column"];

Output0Buffer.Column1 = row["Column1"].ToString();

Output0Buffer.Column2 = row["Column2"].ToString();





This is the error

Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

at ScriptMain.CreateNewOutputRows()

at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)




View Replies !   View Related
Can We Assign DataSet Object To Object Type
Hi Friends,

 I am having a Package scope variable DS of type system.object .In the Script Component which accept this variable as ReadWrite type I am Making the following assignment.

Public Overrides Sub PostExecute()

Variables.DsReport = DsReport // DsReport is a Data Set Object Created and Populated inside the script Component

End Sub

I am getting error while doing this sort of assignment.

Please help me to solve this problem





View Replies !   View Related
How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?

View Replies !   View Related
SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.
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 Replies !   View Related
Unable To Cast COM Object Of Type 'System.__ComObject' To Class Type 'System.Data.SqlClient.SqlConn
Dear all,
I am stuck with a SSIS package and I can€™t work out. Let me know what steps are the correct in order to solve this.
At first I have just a Flat File Source and then Script Component, nothing else.

[Script Component [516]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Script Code (from Script Component):
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
    Inherits UserComponent
    Dim nDTS As IDTSConnectionManager90
    Dim sqlConnecta As SqlConnection
    Dim sqlComm As SqlCommand
    Dim sqlParam As SqlParameter
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim valorColumna As String
        Dim valorColumna10 As Double
        valorColumna = Row.Column9.Substring(1, 1)
        If valorColumna = "N" Then
            valorColumna10 = -1 * CDbl(Row.Column10 / 100)
            valorColumna10 = CDbl(Row.Column10 / 100)
        End If
        Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100)
        Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100)
        Me.Output0Buffer.EJERCICIO = CInt(Row.Column2)
        Me.Output0Buffer.CODPROV = CInt(Row.Column7)
        Me.Output0Buffer.MODALIDAD = CInt(Row.Column8)
        Me.Output0Buffer.NIFPERC = CStr(Row.Column3)
        Me.Output0Buffer.NIFREP = CStr(Row.Column4)
        Me.Output0Buffer.NOMBRE = CStr(Row.Column6)
        Me.Output0Buffer.EJERDEV = CDbl(Row.Column13)
        With sqlComm
            .Parameters("@Ejercicio").Value = CInt(Row.Column2)
            .Parameters("@NIFPerc").Value = CStr(Row.Column3)
            .Parameters("@NIFReP").Value = CStr(Row.Column4)
            .Parameters("@Nombre").Value = CStr(Row.Column6)
            .Parameters("@CodProv").Value = CInt(Row.Column7)
            .Parameters("@Modalidad").Value = CInt(Row.Column8)
            .Parameters("@ImpBase").Value = valorColumna10
            .Parameters("@PorcReten").Value = CDbl(Row.Column11 / 100)
            .Parameters("@ImpReten").Value = CDbl(Row.Column12 / 100)
            .Parameters("@EjerDev").Value = CDbl(Row.Column13)
        End With
    End Sub
    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        Dim nDTS As IDTSConnectionManager90 = Me.Connections.TablaMODELO80
        sqlConnecta = CType(nDTS.AcquireConnection(Nothing), SqlConnection)
    End Sub
    Public Overrides Sub PreExecute()
        sqlComm = New SqlCommand("INSERT INTO hac_modelo180(Ejercicio,NIFPerc,NIFReP,Nombre,CodProv,Modalidad,ImpBase,PorcReten,ImpReten,EjerDev) " & _
    "VALUES(@Ejercicio,@NIFPerc,@NIFReP,@Nombre,@CodProv,@Modalidad,@ImpBase,@PorcReten,@ImpReten,@EjerDev)", sqlConnecta)
        sqlParam = New SqlParameter("@Ejercicio", Data.SqlDbType.SmallInt)
        sqlParam = New SqlParameter("@NIFPerc", Data.SqlDbType.Char)
        sqlParam = New SqlParameter("@NIFReP", Data.SqlDbType.Char)
        sqlParam = New SqlParameter("@Nombre", Data.SqlDbType.VarChar)
        sqlParam = New SqlParameter("@CodProv", Data.SqlDbType.TinyInt)
        sqlParam = New SqlParameter("@Modalidad", Data.SqlDbType.SmallInt)
        sqlParam = New SqlParameter("@ImpBase", Data.SqlDbType.Decimal)
        sqlParam = New SqlParameter("@PorcReten", Data.SqlDbType.Decimal)
        sqlParam = New SqlParameter("@ImpReten", Data.SqlDbType.Decimal)
        sqlParam = New SqlParameter("@EjerDev", Data.SqlDbType.Decimal)
    End Sub
    Public Sub New()
    End Sub
    Public Overrides Sub ReleaseConnections()
    End Sub
    Protected Overrides Sub Finalize()
    End Sub
End Class
Thanks a lot for your help  

View Replies !   View Related
@PARAM1 : Unable To Cast Object Of Type 'System.Data.SqlTypes.SqlInt32 To Type System.IConvertable
I get the following message in the vs2005 querybuilder when i do a preview:


SQL Execution Error.

Executed SQL statement: SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = @PARAM1)


Error Source: SQL Server Compact Edition ADO.NET Data Provider

Error Message: @PARAM1 : Unable to cast object of type 'System.Data.SqlTypes.SqlInt32 to type System.IConvertable'.

The same querypreview works fine without the parameter:


SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = 186)


Can anybody tell me why this is?
And tell me a way to get the tableadapter working?


Anne-Jan Tuinstra

View Replies !   View Related
Unable To Cast COM Object Of Type 'ADODB.CommandClass' To Interface Type 'ADODB._Command'
I have an application which runs successfully on a couple of my customer's machines but fails on a third.  It seems to fail when opening the database:

Unable to cast COM object of type 'ADODB.CommandClass' to interface type 'ADODB._Command'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{B08400BD-F9D1-4D02-B856-71D5DBA123E9}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false; Initial Catalog=lensdb;Data Source = SQL

Before I got this error I was getting another problem (sorry didn't make a copy of that error's text) that made me think that adodb.dll simply wasn't loaded/registered.   I got rid of that error by copying my adodb.dll onto the third machine and running gacutil /i.  There is now an entry in winntassemblies for adodb.

Just in case you think it could be an obvious registry problem: when I started getting the current error I thought that maybe the registry needed updating and I merged the following lines into onto the target machine (from my dev machine):

Windows Registry Editor Version 5.00









but, no change alas.

All three machines are running Windows 2000.

Any advice would be appreciated.

Thanks in advance,


View Replies !   View Related
Unable To Cast Object Of Type 'System.DateTime' To Type 'System.String'.
 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 Replies !   View Related
Unable To Cast Object Of Type 'System.DBNull' To Type 'System.Byte[]'.
I have developed a custom server control for .NET Framework 2.0. The server control has a property named BinaryData of type byte[]. I marked this property to be data bindable. Now, I have varbinary(Max) type of field in my SQL Database and I have used SQLDataSource and bound this varbinary(Max) field with the property BinaryData (byte[]) of my control. It is working fine as long as the data value is not NULL. Now, In my control, I have handled the NULL value so that no Exception is thrown. Still, when I bind this property using the SQLDataSource, I get Error "Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'." I am not sure if I can do anything to stop this erro within my control. If it is not possible from the control, then what is the workaround that I can do in my ASPX page in order to stop this error ?
Thanks a lot in advance.

View Replies !   View Related
Creating Object Type
How to create object type in sql server,
i.e in Oracle we can directly create an object TYPE and we can use it in other applications.
What's the equivalent of this object Type in SQL Server

View Replies !   View Related
Getting Invalid Object Type Name... Help Plsssssss

Im new to SQL Server. I'm trying to execute the following query

create view v_IB_IssueView1 (CreatedMonth, StatusSubmitted, StatusOpen, StatusPending)
as select CreatedMonth, sum(case CorporateStatus when 'submitted' then 1 else 0 end),
sum(case CorporateStatus when 'open' then 1 else 0 end),
sum(case CorporateStatus when 'pending' then 1 else 0 end)
from tbl_IB_Issue_new1
group by CreatedMonth

and I'm getting an error that says:
Server: Msg 208, Level 16, State 1, Procedure v_IB_IssueView1, Line 2
Invalid object name 'tbl_IB_Issue_new1'.

Here tbl_IB_Issue_new1 is a table

Now I dont know where i'm goofing up ..can someone pls help me out with it.... plssssssssssssss

Shruti Majithia
Quinnox Consultancy Services

View Replies !   View Related
What Is The Type Of Object Returned By AquireConnection

I cannot seem to find the documentation on what is returned when you call ConnectionManager. For example in the code below I'm using an FTP Connection Manager. The cn variable is a System.__ComObject and I cannot see how to determine what com type it actually is, nor can I find any API documentation which describes the types each connection manager "manages".

The reason I'm trying to do this is I want to create a script which enumerates the directoy listing of an FTP site, I've got it working using  varaibles but I'm trying to see if it's possible to use the connection manager instead.

Dim cmgr As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("FTP Connection Manager")

Dim cn As Object = cmgr.AcquireConnection(Nothing)


View Replies !   View Related
Using Variable Of Type Object In Expression

I have some SSIS variables of type System.Object (they have to be this
type because they are used to hold the results of a single row result
set in an Execute SQL task which is querying an Oracle database.
Although I know the Oracle table columns are Numeric, this was the only
SSIS type that worked).

My problem is that I want to use these variables in expressions, but
can't - I get the error "The data type of variable "User::varObjectVar"
is not supported in an expression".

The only workaround I can think of  is to use a script to assign
the numeric values (integers, in fact) that these variables hold to
other variables of type Int32.

Is that my only option, or am I missing something?


- Jerzy

View Replies !   View Related
Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies !   View Related
Data Type &> Save Various Object Types
Hello,I am working on a .NET project and I would like to save some LOCALIZED content in an SQL 2005 database.This content might be a string, a number or even a class with its properties values defined so I can retrieve it later.I will not need search or any complex operation just the following:1. Add content to the database2. Remove content from the database by contentId3. Get content from the database by contentIdAnyway, is this possible?What SQL 2005 datatype should I use to accomplish this?Thanks,Miguel

View Replies !   View Related
Unable To Cast Object Of Type 'System.Xml.XmlTextReaderImpl'
I'm getting the following error:  Unable to cast object of type 'System.Xml.XmlTextReaderImpl' to type 'System.Xml.XmlTextReader'. This works in .net 1.1 but having issues in the conversion to 2.0.
Public Function RunSPReturnXMLReader(ByVal strSP As String, ByVal ParamArray params() As SqlParameter) As System.Xml.XmlTextReader
Dim objHelperConnection As SqlConnectionobjHelperConnection = New SqlConnection(sConnect)Dim myXmlReader As System.Xml.XmlReader = objHelperCommand.ExecuteXmlReader()
RunSPReturnXMLReader = myXmlReader - GETTING ERROR HERE
End Function

View Replies !   View Related
Unable To Cast Object Of Type 'System.Char'
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
                                                TableDefinition = "ObjID int,ObjCategoryID int,ObjValue nvarchar(400)",
                                                DataAccess= DataAccessKind.Read)]

        public static IEnumerable Obj_IDs(SqlInt32 Data_1, SqlInt32 Data_2, SqlInt32 Data_3)
                using (SqlConnection conn = new SqlConnection("context connection=true"))
                        string sql;
                            case 1:
                             sql= @"Select Obj_ID, Obj_Categoy_ID, Obj_Value
                                    from (select ObjA_ID as Obj_ID, Obj_Category_ID, Obj_Value from dbo.function_A(12,23))A,
                                         (select ObjA_ID as Obj_ID, Obj_Category_ID, Obj_Value from dbo.function_A(15,27))B
                                    Where A.ObjA_ID= B.ObjA_ID
                                        and Obj_ID in (Select ObjB_ID from tbl_Object where Obj_CategoryID=10)";
                                sql = @"Select Obj_ID, Obj_Categoy_ID, Obj_Value from tbl_Object";

                            SqlCommand cmd = new SqlCommand(sql, conn);
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                            DataTable dt = new DataTable();
                            return dt.Rows;
                    catch (Exception ex)
                        return "";


        public static void Obj_Row(Object item, out int ObjID, out int ObjCategoryID, out string ObjValue)
            DataRow row = (DataRow)item;
            ObjID = Convert.ToInt32(row["Obj_ID"]);
            ObjCategoryID = Convert.ToInt32(row["Obj_Categoy_ID"]);
            ObjValue = row["Obj_Value"].ToString();


compile with no error. Call funtion from sql studio the error come ==>

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.Char' to type 'System.Data.DataRow'.
   at UserDefinedFunctions.Object_Row(Object item, Int32& ObjID, Int32& ObjCategoryID, String& ObjValue)
when I change SQL script to simple not "nested select" it work fine. How to get out of this kind of trouble????!!!!

View Replies !   View Related
Using IPAddress Object In A User Defined Data Type
Im trying to use a .NET IPAddress in my UDT. i create the assembly, but i get this error when i try to create the type in SQL Server:


Type "IPAddress.IPAddressUDTType" is marked for native serialization, but field "address" of type "IPAddress.IPAddressUDTType" is not valid for native serialization.



From what i understand about the IPAddress object in .NET it is serializable. what am i doing wrong


( note, im just doing this for research purposes and not planing to use the UDT)




View Replies !   View Related
Data Type Object In Send Mail Task?
I have an Execute SQL Task that runs a simple SELECT query.  I have the result set = Full Result Set.  The variable is of type Object and the value is System.Oject.  After successful completion of the Execute SQL Task, I am doing a Send Mail task.  For the Message Source, I want to use this Object.  The drop down is only listing variables of type String.  Can you not use a variable of type Object in a Send Mail Task?  If not, what is the easiest workaround?  Thanks!

View Replies !   View Related
Invalid Object Like View, Function Etc.
Hi is there a way to know if object (view, function, etc) are invalid?let say a have a table t1 (field col1, col2)and a view v1 (field t1.col1, t1.col2)if I drop t1.col2, the view v1 is not working anymore. I want to knowthat information.In Oracle (8.1.7), i can query the all_objects, user_object table,where status = 'INVALID'. So i can recompile invalid objects (orcorrect it).In sql Server, the table sysobjects give me some status info, butthey are not documented enough.Do you know if i can user one of those fields : status, userstat,sysstat ?Same question for function , procedure.TKS.

View Replies !   View Related
Invalid Object Name In User Function
The following user function is returning an error "invalid object name admin.udfAlphaOnly." This worked for me in an SQL 2000 database as a user function. However, now I am trying to add this as a Scaler-valued function in SQL 2005. Could someone please help me find what is incorrect.





-- =============================================

-- Author: Richard Bennett

-- Create date: May 28, 2008

-- Description: Strips non-alpha characters from a string

-- =============================================

ALTER FUNCTION Admin.udfAlphaOnly (@String varchar(255))

RETURNS varchar(255)



Declare @nPos Int,@Strip varchar(150)

Set @nPos=1

Set @Strip='0123456789!@#$%^&*()-_=+[]{}|;:"<>,./?'

While @nPos<=Len(@Strip)


Set @String=Replace(@String,Substring(@Strip,@nPos,1),'')

Set @nPos=@nPos+1


Return Replace(@String,'''','')



View Replies !   View Related
System Function To Get Current T-SQL Object Name?
We're building a simple logging facility and would like to systematically determine the name of the current T-SQL object(i.e., the procedure name or function name) to provide this as a logging parameter.  For example, in procedure FooBar, I'd like to be able to call a system function that will return 'FooBar', the name of the current object.  Does such a feature exist?

Thanks - Dana

View Replies !   View Related
Unable To Cast Object Of Type 'System.String' Error Help
Unable to cast object of type 'System.String' to type 'System.Web.UI.DataSourceSelectArguments'.
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.DataSourceSelectArguments'. 
Hi, In my pageload i have data retriving from a page and i want it to load it in the GridView. i have GridView and SqlDataSource but when the page loads i am getting the above error. i am not sure what im doing wrong here. i tried different ways. but no luck Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Thenfp = CType(Context.Handler, Member)
'lblEmail.Text = fp.EMM.ToString
'this labelbox for Email address is under the Name'lblEmail.Text = fp.EmailAddress.ToString
 Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT , [fname], [mname], [lname], [address], [city], [state], [zip], [phone] FROM [t_CustomerAcct] WHERE = @email", conn)cmd.Parameters.AddWithValue("@email", fp.EMM.ToString)
End If
End Sub

View Replies !   View Related
Sql_variant Column Type In Transfer Sql Server Object Task
I have a transfer sql server object task which copies few tables between two different database.

Problem: if the source table that I am trying to copy has got sql_variant column type the the task will fail complaining that task can not copy tables with sql_variant table column!

task properties
drop objects first : false
copy data : true
existing data : replace
copy schema : true
use collation : true
include dependent objects : false

can you shed any light if I am doing anything wrong and if there is way around this problem.

p.s. By using old fashion DTS package in 2000 this issue can be resolved! but who wants to go back to old standard!

View Replies !   View Related
How To Fix Unable To Cast Object Of Type 'Microsoft.SqlServer.Management.Smo.Urn'
In Sql Server 2005, Sql Server Mangerment Studio 9.00.1399.00
 I connected an user instance to the databases under .SQLEXPRESS(SqlServer 9.0.3042 -....)
when I right click on a table
and chose modify, or new table...
I get

TITLE: Microsoft SQL Server Management Studio
Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.Urn' to type 'Microsoft.SqlServer.Management.Smo.Urn'. (SQLEditors)

same thing for views
What can I do to fix the problem? is there a patch?

btw script .... as seem to be  erro free Error free is also View dependence,

View Replies !   View Related
Using A Custom Object As Sqldatasource Parameter
hi i want to select data based on a user id, which is stored in a custom object->
how can i put that in the select parameter?
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:RentTodaySQL %>" DeleteCommand="DELETE FROM [Rentals] WHERE [RentalID] = @original_RentalID"
InsertCommand="INSERT INTO [Rentals] ([Headline], [Description], [MoveInSpecial], [MonthlyCost], [Deposit], [AvailableDate], [FeaturedListing], [UserID], [Address], [Address2], [City], [State], [Zip], [LeaseTermID], [LeaseDetails], [Section8], [Section8Details], [PetsAllowed], [PetsDetails], [PetDeposit], [ApplicationFee], [ApplicationDetails], [SmokingAllowed], [RentalCategoryID], [Bedrooms], [Bathrooms], [SqFootage], [LotSize], [YearBuilt], [DefaultImageID]) VALUES (@Headline, @Description, @MoveInSpecial, @MonthlyCost, @Deposit, @AvailableDate, @FeaturedListing, @UserID, @Address, @Address2, @City, @State, @Zip, @LeaseTermID, @LeaseDetails, @Section8, @Section8Details, @PetsAllowed, @PetsDetails, @PetDeposit, @ApplicationFee, @ApplicationDetails, @SmokingAllowed, @RentalCategoryID, @Bedrooms, @Bathrooms, @SqFootage, @LotSize, @YearBuilt, @DefaultImageID)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Rentals] WHERE ([UserID] = @UserID)"
UpdateCommand="UPDATE [Rentals] SET [Headline] = @Headline, [Description] = @Description, [MoveInSpecial] = @MoveInSpecial, [MonthlyCost] = @MonthlyCost, [Deposit] = @Deposit, [AvailableDate] = @AvailableDate, [FeaturedListing] = @FeaturedListing, [UserID] = @UserID, [Address] = @Address, [Address2] = @Address2, [City] = @City, [State] = @State, [Zip] = @Zip, [LeaseTermID] = @LeaseTermID, [LeaseDetails] = @LeaseDetails, [Section8] = @Section8, [Section8Details] = @Section8Details, [PetsAllowed] = @PetsAllowed, [PetsDetails] = @PetsDetails, [PetDeposit] = @PetDeposit, [ApplicationFee] = @ApplicationFee, [ApplicationDetails] = @ApplicationDetails, [SmokingAllowed] = @SmokingAllowed, [RentalCategoryID] = @RentalCategoryID, [Bedrooms] = @Bedrooms, [Bathrooms] = @Bathrooms, [SqFootage] = @SqFootage, [LotSize] = @LotSize, [YearBuilt] = @YearBuilt, [DefaultImageID] = @DefaultImageID WHERE [RentalID] = @original_RentalID">
<asp:Parameter DefaultValue="<%= SessionHandler.User.ID %>" Name="UserID" Type="Int32" />

View Replies !   View Related
Data From Wcf-webservice / Object Parameter

i have a wcf webservice method with a "GetPropertyDetailsRequest" object as parameter.
GetPropertyDetailsRequest Object:
<GetPropertyDetailsRequest xmlns:i="" xmlns="WZ.Services.Sso.20070423">
  <MessageId xmlns="WZ.Services.200709.1">00000000-0000-0000-0000-000000000000</MessageId>
  <UserGuid i:nil="true" />
      <IDs xmlns4p1="">
        <d4p1:anyType xmlns5p1="" i:type="d5p1:int">9</d4p1:anyType>
      <IDs xmlns4p1="">
        <d4p1:anyType xmlns5p1="" i:type="d5p1:int">90</d4p1:anyType>
      <IDs xmlns4p1="">
        <d4p1:anyType xmlns5p1="" i:type="d5p1:int">900</d4p1:anyType>
  <PropertyCodes xmlns2p1="">
  <requestedApplicationGuid i:nil="true" />
is it possible to include the GetPropertyDetailsRequest object as parameter for the XML Datasource in Reporting Services?  i have try it so


    <Method Namespace="" Name="GetData"> 


<Parameter name="request" type="GetPropertyDetailsRequest">

<!-- ..... -->
<!--GetPropertyDetailsRequest -->
<!-- ..... -->

but i get an error, that the webservice method (GetDetails(GetPropertyDetailsRequest request) ) needs an object as parameter .
can you help me?

View Replies !   View Related
Open SSIS Project Error: Unable To Cast COM Object Of Type
When I open up my existing SSIS project, I always get this error.  Does anyone know what was wrong ?


TITLE: Microsoft Visual Studio

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).


View Replies !   View Related
Syntax Error In Accessing An Object (table Value Function)
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
 "INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _

View Replies !   View Related
Passing SqlDataSource Object An Array As A Parameter
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
        DataTable dt = new DataTable();        DataTable dt2 = new DataTable();        DataView dv = new DataView();                        
       foreach(ListItem liOptions in ListBox1.Items)       {             if(liOptions.Selected)             {                                      SqlDataSource1.SelectParameters.Add("Parameter1", liOptions);                   dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);                   dt2 = dv.Table;                   dt.Merge(dt2);                   dt2.Dispose();                   SqlDataSource1.SelectParameters.Clear();             }       }
        if (dt.Rows.Count > 0)        {           Graph(dt);                             //Pass original datatable (dt) to Graph();           dt.Columns.RemoveAt(2);      //Reformat datatable (dt) and remove last column before binding to Gridview1
           GridView1.DataSource = dt;           GridView1.DataBind();        } else {
    errorMessage.Text = "No data was returned!"; }

View Replies !   View Related
Need An LPAD Type Function
Hello all, dumb question here I'm sure.

I have social security numbers stored in an INT data type column. Leading zeros keep getting dropped.

I am executing a stored procedure to export the data into other tables and fixed length text files, and when I do, the leading zeros drop.


SSNO = 11111

I want this 000011111

But I get the 11111

I've used this command.

select (REPLICATE(0, (9 - len(11111))) + '11111')

Which works fine.

But! When I substituted @v_ssno for the literal string, It truncates the leading zeros!

select (REPLICATE(0, (9 - len(@v_ssno))) + @v_ssno)

So I have tried CAST to make it a VARCHAR, hopeing that it would not clip the zeros. But it still does every time I put it in a variable holder.

Oracle has a command called LPAD. Basically you tell it the total length of the field, and the character to "pad" on the left with.

I can't for the life of me find the equivalent in MS SQL 2000.

Any information is massively appreciated!


View Replies !   View Related
How To Get The Parameter Type In
I am currently using the following embedded code to get the selected values from a Parameter and display in a textbox.  I however have a number of reports and I don't want to include this in all of them. I have created a dll with some other functions but when i try to include the following function it throws an error on the Parameter type. What am I doing wrong, I am not all that familiar with dot net?


Public Function ShowParameterValues(ByVal parameter as Parameter)  as String
 Dim s as String

   If parameter.count <= 5 then
     If parameter.IsMultiValue and parameter.count > 1 then
       s = " "
       For i as integer = 0 to parameter.Count-1
  if i = parameter.count - 1
   s = s + CStr(parameter.Label(i))
   s = s + CStr(parameter.Label(i))  + ", "
  end if
       s = " " + CStr(parameter.Label(0))
    End If
 s = " Only a maximum of 5 selection values can be displayed. "
End if
   Return s
End Function

View Replies !   View Related
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
FROM Split(@tmp,DEFAULT)

View Replies !   View Related
Contains Function On Image Data Type
Hi all,

I am working on application maintance. I got a Contains(myField, myString) that used to look into an image data type field (text) for the string, but right now it's not responding they way it's supposed to do.

Recently really big files have been introduced in the DB, could this related to the issue?

Anyone can help regarding this?

Many thanks,


View Replies !   View Related
Data Type Date ,Add Function

I have an accounts table in which i have a date, credit,debit,balance
columns.I gave the datatype for date column as datetime.But after entering the values,Date is displayed in a different format.How to display the date in our specified format.

And also,in the balance column i want to store the balance.amd when ever there is a credit or debit,i want to add or subtract from the balance correspondingly.How do i manipulate this .

Thanks In Advance

View Replies !   View Related
CS0266: Cannot Implicitly Convert Type 'object' To 'bool'. An Explicit Conversion Exists (are You Missing A Cast?)
Using OnSelected on the SqlDataSource----here is my currect code:private void On_Load(Object sender, SqlDataSourceCommandEventArgs e){if (e.Command.Parameters["@DueDate"].Value = "NULL"){e.Command.Parameters["@DueDate"].Value = "0000-00-00 00:00:00.000";}}
I am trying to change the value from  NULL(in a datetime field) to "0000-00-00 00:00:00.000" for use on the page.Any ideas, suggestions, or thoughts would be greatly appreciated!
,Gary"we truly fail, only when we quit."

View Replies !   View Related
Replace-type Function For Text Datatype
I have a table that has a Text datatype column that has gotten somegarbagecharacters in it somehow, probably from key entry. I need to removethe garbage, multiple occurances of char(15). The replace functiondoes not work on Text datatype. Any suggestions?

View Replies !   View Related
Creating Sql-clr Function With Void Return Type

hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script







NAME [AMEX].[UserDefinedFunctions].[Function1];


but i am getting error.
there is something wrong in my sql syntax..can anybody help me?

when creating funtion having signature

public static void Function1()
 i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
can anybody help me?
thanks in advance
Chetan S. Raut

View Replies !   View Related
Table Data Type As Parameter
How can I use Table Data Type as parameter for Stored Procedures and Functions.

View Replies !   View Related
SqlDataSource Using A Type Parameter Of Table
I am not sure (and I've been known to overlook things) if it is possible to use a Table parameter type as parameter to a stored procedure?  Is it possible? I would prefer not to create a SQL batch statement calling an stored proc w/ one parameter over and over. Instead i'd rather just create the stored proc to take a table parameter and pass the table into the stored proc.  I know I could use a CSV string of IDs instead of a table...  or I could create a big SQL batch and use that.  But I'd rather not. Could anyone provide some guidance? Thanks!Joe

View Replies !   View Related
Bit Type Parameter For Stored Procedure
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI

The syntax I am trying to use is:

MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@pdi").value = -1

When I do my ExecuteNonQuery I get error 8114

What am I doing wrong?

View Replies !   View Related
Use Of Variant Type Parameter In ExecuteNonQuery()

In the following code snippet, the stored procedure executes up to the

iRet = vciSqlCommand.ExecuteNonQuery();

command and fails with error:

System.Data.SqlClient.SqlException: Incorrect syntax near 'value'.

That would point to the

" = @data_Param where



So what is the proper syntax?


Code Snippet

SqlParameter data_Param = new SqlParameter("@data_Param", SqlDbType.Variant);

SqlCommand vciSqlCommand = new SqlCommand();

switch ((vcidatatype)FieldDef.ivcidatatype)

{ // read in the binary data!!!!!!

case vcidatatype.eENGUNITS: data_Param.Value = Encoding.ASCII.GetString(r.ReadBytes(FieldDef.iLen)); break;

case vcidatatype.eLANADR: data_Param.Value = r.ReadInt16(); break;

case vcidatatype.eBYTESIZE: data_Param.Value = r.ReadByte(); break;

case vcidatatype.eFLOATSIZE: data_Param.Value = r.ReadSingle(); break;

case vcidatatype.eINT32SIZE: data_Param.Value = r.ReadUInt32(); break;

case vcidatatype.eFILLERBYTES: r.ReadBytes(FieldDef.iLen); break; // eat up empty bytes

default: FieldDef.ivcidatatype = (Int32)vcidatatype.eFILLERBYTES; break;


if (FieldDef.ivcidatatype != (Int32)vcidatatype.eFILLERBYTES)

{ // all but filler

vciSqlCommand.CommandText =

"update " + acPointType + "set " + FieldDef.sFldName +

" = @data_Param where VEC = " + acVECName +

" and name = " + acPointName;

vciSqlCommand.Connection = conn;


iRet = vciSqlCommand.ExecuteNonQuery();

























View Replies !   View Related
Stored Procedure Parameter Without Type
Hi All !

How i can create a stored procedure like ISNULL ?

I need to a procedure that accepts Int type parameters and Float type ect.


Thanks. DBT.

View Replies !   View Related
Pop-Up Calendar For DateTime Parameter Type

We have noticed that the calendar months in the Pop-Up Calendar option, based upon month word length, will shift in size and this will cause the left and right arrows to shift. For example, 'May' is much shorter than 'December'.
My user is complaining because she cannot quickly move from one month to another without having to reposition the mouse over the navigation arrow.
Is the Calendar feature working as designed?
Thank you,

View Replies !   View Related
Parameter Control Chart Type?
Can I use a parameter to control the type of charts that is displayed?  For example, I want the user to choose (from a drop-down) whether they see a Line or Bar graph.


 I'm using SQL Server 2005 SP2 and VS2005.

View Replies !   View Related
Using A Complex Type As Parameter To A Webservice
I really need some information that shows how I could construct a query that take a complex type as parameter.

Regards, Christian

View Replies !   View Related
Multi Select Type-in Parameter
Hi All

Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.

My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..

I'm using SSRS with SSAS cube all in BI all 2005

Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.

Thanks in advance

Gerhard Davids

View Replies !   View Related
Passing Object Variable As Input Parameter To An Execute SQL Task Query
I've encountered a new problem with an SSIS Pkg  where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of  What  type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )


2) Now I want to use this User::LoadID as input  parameter of   What  type for the next  task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).


Please give me solutions for the above two..



View Replies !   View Related
Function Parameter
I am writing a function which will take two parameters. One the fieldto be returned from a table and second parameter is the ID of therecord to be returned.Problem is it's not returning the value of the field specified in theparameter but instead returns the parameter itself. Is there afunction that will get the parameter to be evaluted first?ALTER FUNCTION [dbo].[getScholarYearData](-- Add the parameters for the function here@FieldName varchar(50), @ScholarID int)RETURNS varchar(255)ASBEGIN-- Declare the return variable hereDECLARE @ResultVar varchar(255)-- Add the T-SQL statements to compute the return value hereSELECT @ResultVar=EXECUTE(@FieldName)FROM dbo.qmaxScholarYearID INNER JOINdbo.tblScholarYears ONdbo.qmaxScholarYearID.ScholarID = dbo.tblScholarYears.ScholarID ANDdbo.qmaxScholarYearID.MaxOfScholarYearID =dbo.tblScholarYears.ScholarYearID-- Return the result of the functionRETURN @ResultVarEND

View Replies !   View Related
Parameter In Function

this has started from me wanting to write a report that allowed the user to choose from a drop down list of how many months they want the report to cover. IE i want the report for the last 3 months, or 5 months or ...

To do this i created a report with a value list parameter (values are negative integers to give the necessary start date for the reported rows) and had that parameter in the where clause, along the lines of ...
select *blah*
from *blah*
where ( *myfield* > dateadd (mm, @MonthParam, GetDate())

i get a sql error (ie an error from the database not reporting services) saying that the variable hasnt been declared.

I have worked around the problem in this instance but is there a 'feature' in RS that means report parameters cannot take part in functions?


View Replies !   View Related

Copyright © 2005-08, All rights reserved