I want to run an update command where the user types in a CSV value and the query runs. If I simulate 1 number it works, but if I put in 2 variable it returns nothing (but doesn't fail).
declare @SITE_ID int
declare @txtSchedule varchar (500)
set @SITE_ID=1
set @txtSchedule='5,6'
select * from Schedules WHERE SITE_ID=@SITE_ID and WEEK IN(@txtSchedule .
How can I get the UserID to pass to the sql statement? The date is being passed but nothing else
Thank you.
My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime
dtToday = DateTime.Today
'Create connectionDim con As New SqlConnection(_connectionString)
'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd
.Connection = con
.CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here
.CommandType = CommandType.Text
'Add Parameters.Parameters.AddWithValue("@UserID", UserID)
.Parameters.AddWithValue("@Today", Today)
End With
'Return DataReader
con.Open()Return cmd.ExecuteReader()
End Function
Hi,
I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' Â Â -> true
2. 'DOB is valid;ST is invalid;' Â Â -> true
3. 'DOB is valid; ST is invalid;'   -> true
4. 'DOB is valid;invalid ST;'   -> true
5. 'DOB is valid; invalid ST;'   -> true
6. 'DOB is valid; invalid STate;'Â Â Â -> false
Means the exact ST should be search. It is  not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ...
What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus can you pass a parameter value to the CTE that I just listed above? If so, show t-sql to accomplish this goal?
I’m working on building a file list from a directory I have and wish to loop through the filenames. I’ve worked out I can get the list using
EXEC
xp_dirtree @path, 10, 1
I’m just not sure out I pass this to my loop 1 element at a time.
E.g. Let’s say there are 10 files in my folder, I wish to loop through each name using my @Filename parameter
DECLARE @Path
varchar(50), @FileName
varchar(20)
SET @Path
= 'C:myFiles'
EXEC
xp_dirtree @path, 10, 1
[Code] ....
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) Â NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)Â
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUESÂ
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
[code]....
Help! I'm trying to figure out how to pass a string variable to my sqldatasource's "selectcommand" attribute. I'm trying to construct my SQL dynamically to adjust some things. My code is something like this,
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="report.aspx.vb" Inherits="_Default" EnableEventValidation="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><%@ Import Namespace="System.IO" %><html xmlns="http://www.w3.org/1999/xhtml"><head id="Head1" runat="server"></head><body style="font-family: Arial,Verdana; font-size: 8"> <% Dim selectCommandVar as String = "select column from mytable" %>
<form runat="server"> <asp:SqlDataSource ID="SqlChild" runat="server" ConnectionString="<%$ ConnectionStrings:mynnectionString %>" ProviderName="<%$ ConnectionStrings:gmConnectionString.ProviderName %>" selectcommand="selectCommandVar"> </asp:SqlDataSource></form></body></html>
What's the best way to get the contents of my selectCommandVar variable into the SelectCommand attribute of my sqldatasource? The above syntax doesn't work and I've tried several permutations that do not work either. The examples of sqldatasource I keep finding have the SQL statement hardcoded in the <asp:sqldatasource> section, which won't work for me. HELP!
Hello,
We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.
My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?
Will I have to create Dynamic SQL to do this?...or... can I do something like this...
@ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString
Thanks for your help.
The following query works perfectly (returning all words on a list called"Dolch" that do not contain a form of "doing"):SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "doing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)However, what I really want to do requires me to piece two strings together,resulting in a word like "doing". Any time I try to concatinate strings toget this parameter, I get an error.For example:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "do' + 'ing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)I have also tried using & (as in "do' & 'ing") and various forms of singleand double quotes. Does anyone know a combination that will work?FYI, in case this query looks goofy because of the unused "CombinedLexicons"table, it is because the end result should be a working form of thefollowing...Figuring out the string concatination is just a step toward this goal:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')WHERE (dbo.CombinedLexicons.vchWord IS NULL)Thanks!
View 2 Replies View RelatedHi,
In c# - how to pass uid,pwd,dbname and servername as input parameters from vs 2003 windows application (am calling the rdl file from reporting service 2005 web service) to sql server 2005 rdl files.
Thanks,
Shanthi
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:
Declare @FromDate as date
Declare @ToDate as date
Set @FromDate=Convert(date,'09-01-2015',110)
Set @ToDate= Convert(Date,'10-13-2015',110)
Select @FromDate
[code]...
How can I keep the date as a date and still pass it to stored procedure.
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong
here is the code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20)
set @TABLEDESC = 'ClientComment'
set @DBO = '.dbo.'
set @BDFR = 'Commander' + @DBO + @TABLEDESC
set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC
set @EQID = '80_300_113'
insert into @BDTO
select from @BDFR where Eqid = @EQID
Below is the query for my procedureÂ
ALTER PROC [dbo].[sp_GetInvitationStatusTest]
(
@invited_by NVARCHAR (50)
)
AS
BEGIN
-- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by
select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
I have a requirement, we need to pass comma separated list using powershell script.
How can we achieve the above scenario?
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria;
I get: Failed to convert parameter value from a String to a Guid.
Ugh! It's a string, dummy!!! I have even tried:
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString();
and
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B");
but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?
Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.
Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?
Here's a basic idea of what I'd like to do:
DECLARE @myVar AS NVARCHAR(50)
SELECT *
FROM @myVar
or
DECLARE @myVar AS NVARCHAR(50)
SELECT @myVar
FROM MyTable
I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...
Hi,
Using SSIS 2005 how is it possible to loop through a folder on the network, look at each file, pass the data inside each file as a string into a stored procedure.
Thanks
I have this.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; string strQuery = "SELECT * FROM enews WHERE ((name_nws LIKE %'" + strSearch + "'%)"; SqlDataAdapter newsResultAdapter = new SqlDataAdapter(strQuery, strConn); //create a new dataset DataSet dsNewsSearch = new DataSet(); //populate the dataset with the "board_mtg_date" table from the database newsResultAdapter.Fill(dsNewsSearch, "enews"); //go through each rows in the dataset foreach (DataRow dr in dsNewsSearch.Tables[0].Rows) { int newsID = Convert.ToInt16(dr["id_nws"]); } I'm trying to pass a value to the Select statement with the variable "strSearch". It kept giving me error on this section.
The error is:
[SqlException (0x80131904): Line 1: Incorrect syntax near 'Challenge'.]"Challenge" is the text I passed it in.
Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...
View 5 Replies View RelatedI have a function in VBA (Access) that passes a sql string into a passthrough query that is server side executed
I changed the inner joins and left joins to where statements but this particular sql string crashes with the titled error folowed by the multi-part identifier "task_backup.activityid" could not be bound (#4104) and then it repeats that one more time in the same message
Which i think the second "sub message" is for the null criteria
INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line)
SELECT [TASK Excel Data].[Activity ID],[TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], [TASK Excel Data].Area, [TASK Excel Data].Line
FROM [TASK Excel Data]
where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] and TASK_BackUP.[Activity ID] Is Null
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
 UPDATE TABLE1
  SET TABLE1.FIELD2 = 1
 ELSE
  UPDATE TABLE2
  SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
I apologize in advance for posting yet another connection failure issue. I went through quite a few posts and could not find the actual answer so here is the issue. I have a main SSIS package to call five other packages. This seems to work fine in my BIDS workstation; however, when I copied it, including the bat file that was created by dtexecui utility, to the production environment (which runs on 64 bit - probably has nothing to do with the 64 bit platform), the main package executes fine but the child packages failed with this error:
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "An error has occurred while establishing a connection
to the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote conne
ctions.".
It seems that the child packages are still using the old connection strings from my workstation. My question is how can I pass the connection string from the batch file to all the child packages that the parent (main) package is using.
Thanks,
Andrew
I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.
Here's the table structure, let's call it #temp
[Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]
Bike, 200
Accessory , 100
I have a table in the DB created as [ProductSales]
Here's what works for me:
insert into [ProductSales](Product, Sales)
Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp
But in reality the product level is not always on level 03, I want to define a parameter @Product and pass it to the select list.
Here's what I tried to do:
Declare @Product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")
Declare @SQL varchar(MAX)
SET @SQL='insert into [ProductSales](Product, Sales)
Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'
From #temp'
Exec @SQL
But it threw me error, what is the right way to do it, is it because the double quote or single quote thing?
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
Hi.. My Finance user create a microsoft Excel OLAP file that connect to our SQL server. And I have created the same ODBC setting for every PCs.
But for a few PCs, I encountered the following error "Client Safety Options do not allow pass through statement to be issued to the data source" When trying to connect the OLAP file to SQL server after entering the same SQL account.
Any idea what is the isssue ??
I'm writing some generalized stored procedures for use form managed code. The following stored proc works great:
CREATE PROCEDURE fnd_Send (
@Message VARCHAR(5000),
@Contract SYSNAME,
@MessageType SYSNAME,
@FromService SYSNAME,
@ToService VARCHAR(100),
@Encrypted Bit
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE @FromService
TO SERVICE @ToService
ON CONTRACT @Contract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE @MessageType(@Message);
Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages.
The following will not compile:
CREATE PROCEDURE fnd_Receive (
@Queue SYSNAME,
@Message VARCHAR(5000) out,
@MessageType SYSNAME out
)
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM @Queue;
END
GO
It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly:
Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15
Incorrect syntax near '@Queue'.
It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to.
Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement.
Thank you in advance -
Jeff Odell
Catapult Systems
I would like to wrap the following code in a function and reuse it. Â I use this code in many triggers.
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'U' Â -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' Â -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' Â -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete". Â Â
END)
Is it possible to write a function and pass the INSERTED and DELETED logical tables to it?
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max)
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;