I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!! i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'
The proc below does two queries that are functionally identical. The only difference is that one LIKE 'foo%', and the other uses LIKE @searchText, where @searchText = 'foo%'.
But the first does an index seek, and the second does an index scan -- and it makes a big difference in performance. (Timing stats are below.)
How can I make the second query seek instead of scanning?
I get the following error when I do a CONVERT(UNIQUEIDENTIFIER, 'guid'), where 'guid' is a properly formatted GUID created in VS2005, inside my INSERT statement:
The data was truncated while converting from one data type to another. [ Name of function(if known) = ]
The column I am inserting into has a datatype of uniqueIdentifier.
This is probably a simple wuestion but i would appreciate some help I am trying to get and insert the userId into a table called "Orders" I got the user id as Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString The button on click command is worldshop.ShoppingCart.CreateOrder(CustomerId,......................... The Function Has the parameter for Customer Id as follows Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_CustomerId.ParameterName = "@CustomerId" dbParam_CustomerId.Value = CustomerId dbParam_CustomerId.DbType = System.Data.DbType.String command.Parameters.Add(dbParam_CustomerId) When i run this I am getting the error message "Conversion failed when converting from a character string to uniqueidentifier." Can anyone tell me where I am going wrong many thanks Martin
Hi, i have a problem, i keep getting this Error. I want to insert an uniqueidentifier using a textbox, i use the following code to insert. SqlDataSource1.InsertParameters["RWID"] = new Parameter("RWID", TypeCode.String, RWID); SqlDataSource1.Insert(); The databasetype is an uniqueidentifier of that column. Anyone who can help me with this problem?
I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.
Conversion failed when converting from a character string to uniqueidentifier. My Code is shown below: comSQL.Parameters.AddWithValue("@PROPERTYID", Format(Request.QueryString("ID").ToString, "{0:########-####-####-####-############}")) This has worked before but isnt' anymore. Any ideas?
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
Hello, Im Getting an error (below) at the login page. The only control on that page is a Login control, adn the error below comes when I click login. Does anyone know what Im doing wrong? This is my login page:<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" Title="myapp- Login" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentHeader" Runat="Server"> LOGIN</asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentBody" Runat="Server"> <table border="1" cellpadding="0" cellspacing="0" width="305"> <tr> <td class="bodyText" colspan="2" style="height: 20px; text-align: center;"> <p> <!-- TemplateBeginEditable name="mainText" --> <asp:Login ID="Login1" runat="server" TitleText="Existing Users Log In"> </asp:Login> </p> </td> </tr> <tr> <td style="text-align: right"> <asp:LinkButton ID="lbNewmembers" runat="server" OnClick="lbNewmembers_Click">New Member?</asp:LinkButton></td> <td> <asp:LinkButton ID="lbForgotPassword" runat="server" OnClick="lbForgotPassword_Click">Forgot Password?</asp:LinkButton></td> </tr> </table></asp:Content>Error: Server Error in '/myApp' Application.
Conversion failed when converting from a character string to uniqueidentifier. 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: Conversion failed when converting from a character string to uniqueidentifier.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
Hi guys, I have a large text that can have some ENTER character inside of it. The problem is that the text must be inserted into a txt file in a single row. So what I need is to find those ENTER character and replace them with a space so as to have the whole string in one row. How can I build that formula in derivide column? Thanks for your help
I have a RPC which gives me multiple - single record rows like
Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column
Now I need to split the above record into 6 Fields and populate in my local DB I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's
In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.
SELECT DISTINCT FC.CASE_ID, UPPER(FC.CASE_NUMBER) AS CASE_NUMBER, UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE, FC.CASE_STATUS_DESCR, FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,
[Code] ....
In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.
Result set should be in the below format:
TABLE NAME Column Name VALUE CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000) FROM Table tbl JOIN Person p ON p.PersonID = tbl.PersonID JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2 WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I would like to test following DMX, but it seems like we cannot use @param in DMX. If i indeed need what other tricks can avoid this constraint?
Declare @HCVS_MemberId nvarchar(15);
INSERT INTO test (HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse) OPENQUERY(Healthcare, 'SELECT TimeIndex, Quantity FROM v_VitalSignForecast WHERE HCVS_MemberId=@HCVS_MemberId AND HCVS_MeasureDate>=@From AND HCVS_MeasureDate<=@To')
is there a way in t-sql to pass a db name as a parameter, so that select * from [@passedDB].[dbo].[tableName] would work? Without dynamically building and executing the sql statement?
The following stored procedure sets a value for the @@RowCount global variable. How do I make use of it in the Data Access Layer? When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime @selectDate datetime ,@selectCountry Int AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_cmc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname SELECT @@RowCount As numberRows
And a multivalued parameter that lets the user select which TypeIDs specifically he wants to see:
ParamID ParamValue 1 Q1 2 Q2 3 Q3 4 Q4
And in my Report, I have data showing up something like this:
CountofAllSales: 6 SumOfAllSales: 491.18 CountofCustomSales: (count of sales with type specified in parameter) SumOfCustomSales: (sum of sales with type specified in parameter)
The count and sum of custom sales should show -ONLY- the numbers from the TypeIDs selected in the multi-value parameter. But the CountAll and SumAll show everything, regardless. This is where I run into problems. I can't seem to find an "in" clause in the SSRS expressions. If the TypeID parameter was single value, I could write something like this
Expression for CountOfCustomSales: =SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value, 1, 0))
However, since its multi-valued, that won't work. You'd have to write something like: =SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(0), 1, 0)) + SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(1), 1, 0)) + .... SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(length), 1, 0))
And obviously this doesn't work when you don't know exactly how many elements are going to be selected.
What would be ideal would be something like an "in" clause, but I can't find any such functionality or think how to write my own function: =SUM(iif(Fields!TypeID.Value in Parameters!TypeID.Values, 1, 0))
Short of modifying the StoredProc itself (and for me, that means red tape. :( :( ) can anyone think of a way to count/sum only the values specified in an MVP??
Hi AllMy query is as follows:SELECT STRINGTEXT, TOKENIDFROM WEBSTRINGSWHERE TOKENID IN (6,20,234,19,32,4,800,177)All I want is my resultset to come back in the order that I have defined inthe IN clause, but unfortunately SQL is trying to be too helpful and sortsthe numbers in the IN clause so that the resultset comes back with a TOKENIDorder of 4,6,19,20,32,177,234,800.I don't want this bloody order I want 6,20,234,19,32,4,800,177!!Sorry for my rant, but its got my hot under the collar.Is there anyway round this?ThanksYobbo
Is there a limit to how many items you can have in a multi value param list. I have 20 items in an activity type param and when I chose "Select All" and run the report, it doesn't return. I opened profiler and picked up the following statement sent to SQL:
exec sp_executesql N'SELECT de.employeenumber as Employee_Id,de.employeelastname + '', '' + de.employeefirstname + '', '' + case when de.employeemiddlename=''N/A'' then '''' else de.employeemiddlename end as Employee_Name,da.activitytype as Activity_Type,da.activitycode as Activity_Code,da.activityname as Activity_Name,dd.fulldate as Completion_Date,das.currentattemptstatus as Current_Attempt,das.successstatus as Success_Status,das.completionstatus as Completion_Status,das.registrationstatus as Registration_Status,fa.score as Score,fa.dimgradeid as GradeId
FROM dimemployee de inner join factattempt fa on (de.dimemployeeid = fa.dimemployeeid) inner join dimattemptstatus das on (fa.dimattemptstatusid = das.dimattemptstatusid) inner join dimactivity da on (fa.dimactivityid = da.dimactivityid) inner join dimdate dd on (fa.attemptenddateid = dd.dimdateid)
WHERE
de.employeenumber = (@EmployeeId) and da.activitytype in (N''CBT'',N''Course'',N''Dart'',N''Discuss'',N''Document'',N''Evaluator'',N''JPM'',N''Lesson Plan'',N''Module'',N''Observation'',N''Procedure'',N''PSG'',N''Qual'',N''Read'',N''Reference'',N''Session'',N''Sign-off'',N''Simulator'',N''Task'',N''Trainer'')
and das.isvalidattempt = ''Yes'' and ((@LastAttempt=1 and das.currentattemptstatus = ''Yes'') or (@LastAttempt=0 and das.currentattemptstatus in (''Yes'',''No''))) and das.lmsmartcompletionstatus in (@CompletionStatus) and (dd.fulldate >= @StartDt or @StartDt is NULL) and (dd.fulldate <= @EndDt or @EndDt is NULL)
ORDER BY de.employeefirstname + '' '' + de.employeelastname,da.activitytype,da.activityname,dd.fulldate',N'@EmployeeId int,@LastAttempt nvarchar(1),@CompletionStatus nvarchar(10),@StartDt nvarchar(4000),@EndDt nvarchar(4000)',@EmployeeId=108001,@LastAttempt=N'1',@CompletionStatus=N'Successful',@StartDt=NULL,@EndDt=NULL
If I take any one of the items in "da.activitytype in" out (for example....N''Trainer'')...the query is fine. But if I run it as is, it never returns. I have also reduced the number of items in the MVP to 19 and "Select All" and it runs fine...it just bombs when I have 20 and Select All. Any ideas?
Hello.... I am trying for several weeks to figure out how can I create a stored procedure with parameters that returns all rows from a column (even then Nulls) when the parameter value is not set (or is '%', or anything tha might be. In a few words, in case the user hasn't input any data).
I 've created a WHERE clause that goes like this: WHERE fieldName LIKE @param + N'%' OR IS NULL Well this query returns all rows in case the user hasn't input data but if the user inputs data it returns the correct rows but it is also returns null fields.
Is it possible to take a report input parameter and display it at the top of the report? StartDate/EndDate are two input params for my report that would be nice to display at the top.
I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code: SqlConnection conn = new SqlConnection(KPFData.getConnectionString()); SqlCommand cmd = new SqlCommand("KPFSearchName", conn); cmd.CommandType = CommandType.StoredProcedure;
No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??Thanks for your help!!eddie
I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.
How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.
Any tips highly appreciated! If I'm not being clear I'll gladly post more details.
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle @style varchar(30), @artistname varchar(30) output as select @artistname = artistname from artists where style = @style go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure exec showpopstyle 'Pop', @artistname = @returnname output print @returnname
I also have the same error, I am trying to do two things in my Stored Proc.
1) - Insert a parent record. 2) - Insert 1 or many records in the child table using the parent ID
The child records are being passed as a XML param.
I am also getting the 'Subqueries are not allowed in this context. Only scalar expressions are allowed.' error when I try to create the procedure.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:pryder -- Create date: -- Description: -- ============================================= CREATE PROCEDURE TestProc -- Add the parameters for the stored procedure here @Name String , @bings XML AS BEGIN transaction -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @NewID as int declare @err as int -- Insert statements for procedure here INSERT INTO PARENT (Name) VALUES (@Name) SELECT @err = @@error if @err <> 0 begin rollback transaction return @err end
SELECT SCOPE_IDENTITY = @NewID
INSERT INTO Child (ParentID, name)
Values
(( SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)') FROM @bings.nodes('bings/group') as ParamValues(ID) ))
SELECT @err = @@error if @err <> 0 begin rollback transaction return @err end
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result. Here is my code
create procedure sp_Select_ProfileNames @NameSearch varchar(50) as select ProfileFirstName +' '+ ProfileLastName as ProfileName from Profiles where ProfileLastName like '%@NameSearch%';
When i change this line: where ProfileLastName like '%@NameSearch%'; to where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
I have a rs report with a parameter(named Site) that is defined as multi-value. What I am trying to do is call the report from an html form with the parameter populated with a comma seperated list of values. The html code is a minimally modified version of the code found in ch. 11 of the hitchhikers guide(code included below after the solid line). If I don't select any items, then execution is fine.
