I have this: string a = "a+a"; SqlCommand q = new SqlCommand("SELECT * FROM table WHERE a = @a", conn); q.Parameters.AddWithValue("@a", a); But the parameterization totally erases the + from a, leaving me with a a instead of the desired a+a. I need that + in place; I just want it escaped, not removed. Is there a way I can tell C# to...
I have a SQL query and it fails at times. That means that query runs fine most of the times. Once the query fails if I execute is again with some delay, it is successful in the same execution. Please note there are no changes in the tables being used in the query in between the success and failure scenarios. They are exactly same. As m...
I have an xml column and I want to persist a node count in it in an adjacent column. For example, in an xml given below, I have 3 nodes a and so I want to output it. The problem that I have, however, is that I can't manage to create a generic schema-bound function that would take @xml and @nodeName so as to return the count of a speci...
Possible Duplicate: Testing for inequality in T-SQL Hi, does there any different to use the '!=' vs '<>' vs 'Not'? which one will have the worst performance or it exist just because of the backward compatibility syntax? ...
Hello. Rightly or wrongly I’m dealing with a generalised SuperType table which I have the sub type data for but not the SuperType data. However to insert the SubType data I obviously need the Identity ID from the SuperType table first. I was wondering if anyone else has come across this and how they got around it. Unfortunately there...
hi there I have a SQL Server sp using a cursor thus: DECLARE TestCursor CURSOR FOR SELECT tblHSOutcomes.strOutcomeName, tblHSData.fkHSTest FROM tblHSData INNER JOIN tblHSOutcomes ON tblHSData.fkOutcome = tblHSOutcomes.uidOutcome INNER JOIN tblHSTests ON tblHSData.fkHSTest = tblHSTests....
Hi, i'm developing a simple windows application (C#, WinForms, ADO. NET). Application should have a following functionality: create a login and corresponding user in my database (database1); add roles to new user (role names: rol1, rol2). New login can (1) and (2) and so on. How should I do it ? What I want: script for creating an...
-- Given a CSV string like this: declare @roles varchar(800) select @roles = 'Pub,RegUser,ServiceAdmin' -- Question: How to get roles into a table view like this: select 'Pub' union select 'RegUser' union select 'ServiceAdmin' After posting this, I started playing with some dynamic SQL. This seems to work, but seems like there...
We've been using the sys.database_files view and the FILEPROPERTY function to get the size of the database. Unfortunately, as I have just found in the documentation, the values returned by sys.database_files are not always reliable: When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defe...
Consider following piece of code: declare @var bit = 0 select * from tableA as A where 1= (case when @var = 0 then 1 when exists(select null from tableB as B where A.id=B.id) then 1 else 0 end) Since variable @var is set to 0, then the result of evaluating searched case operator is 1. In the documentation of case i...
Can't i write something in TSQL declare @set1 int declare @set2 int set @set1=1 set @set2=2 select @set1 < @set2 by getting true as result? I Know i can use case when @set<@set then 'true' but can't i do by the way i have written above? ...
I've been asked to change a pivot query that currently displays a week ending date, category and hours by date for one week in a row. I've been asked to display a comment field at the end of the row and I can't figure out how to alter the query to do this. The table is structured like this Category Date Comments Hours test...
Hi, I'm having a problem with this trigger: ALTER TRIGGER [dbo].[trg_B_U_Login] ON [dbo].[Login] FOR UPDATE AS BEGIN IF @@ROWCOUNT = 0 RETURN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Campos nvarchar(500); DECLARE @Old...
DECLARE @SQL Varchar(Max) DECLARE @DESCR Varchar(Max) -- Customer enters description into @Descr SET @SQL = 'Update TableName SET FieldName=''' + @DESCR + ''' WHERE ID=123' The problem is when the customer enters an apostrophe into the @Descr variable. Q: In Microsoft SQL Server 2005, how do I replace all apostrophies with double apo...
Hi My Stored Procedure is using Bulk Insert Task. As per requirement of Bulk Insert, the text file should be on same server where database is. Now file is residing on another machine. But database server can access file using shared network drive. Now question is How my stored procedure can read or copy the file from network drive an...
Let's say I have the Products table. On my UI, I allow the user to search by name, description, code The user can only search on criteria. Should I create an index for each criteria : name, description, code or create ONE single index for all 3? What will make you choose one versus the other? ...
I am trying to run an update statement as follows... UPDATE tblDeductionSystem SET [ORDER] = [0RDER] + 6 WHERE [ORDER] >= 7 AND ScoringCriteriaTypeID = @CheerDeductionScoreSheetID Sql Server if giving me invalid column name ORDER. I thought if I delimited the reserved word with the square brackets this would work. ...
My background color in VS is set to black, the problem is that the numeric sql types also (by dafault I guess) are set to black, so I can not see them. Under what name are associated numeric types in "Fonts and Colors" options menu? ...
BACKUP DATABASE <myDataBaseName> TO DISK = 'C:\PathtoBackup\FileName.bak' this query is worked for a database which is created in the gui of SQLServer express edition I have attached my database which is physically at D drive(D:\testing.mdf) to SQLServer using GUI in SQlServer Mgmt Studio.After attaching, SSMS displays the database na...
Please help me to generate the following query. Say I have customer table and order table. Customer Table CustID CustName 1 AA 2 BB 3 CC 4 DD Order Table OrderID OrderDate CustID 100 01-JAN-2000 1 101 05-FEB-2000 1 102 10-MAR-2000 1 103 01-NOV-20...