Hi, how to use LIKE in CASE statement, i have got this example script but does not work. I use MS SQL 2008 DECLARE @DataBaseName sysname, @Message char(255) SET @DataBaseName = 'DBa'; SET @Message = CASE @DataBaseName WHEN LIKE 'DBa' THEN 'Valid name' ELSE 'INVALID name' END Print @Message; ...
Hi all I am trying to eak out a bit extra performance from some sql queries which have a where clause on a field which is a non-unique non-clustered index, it is also a foreign key in table A. That foreign key is the primary key on table B and is the clustered index. What I'm wondering is, will I get a performance gain if I add a join ...
Hi, i want to loop over a period of time in tsql, and print the utc datetimes and our local variant. We live in UTC +1, so i could easily add 1 hour, but in the summertime we live in UTC +2. In C# i can create a datetime and use a method to ask for the UTC variant and vice versa. Till now i have this: declare @counter int declare @l...
I have a process that starts a transaction, inserts a record into Table1, and then calls a long running web service (up to 30 seconds). If the web service call fails then the insert is rolled back (which is what we want). Here is an example of the insert (it is actually multiple inserts into multiple tables but I am simplifying for thi...
How can I determine from code, if SQL Server Agent is running - using SMO? I would think there was a property on the JobServer class, but I can't find anything. Powershell is not an option. ...
I have to convert a set of C# classes (class library) to SQL tables to be used by SQL Server, so that the data can be stored in a database and manipulated through the database. The problem is that the number of these classes is big (more than 1000 classes), and it would take a long time to setup manually such a database schema (tables,...
Hi All Does anyone know a way of viewing and editing an existing SQL CE 4.0 databse? I've tried using SQL Server 2008 but it looks as if it only supports version 3.5. I read on Scott Guthrie's blog that you can use WebMatrix but I can't figure out how. The blog article is located here: http://weblogs.asp.net/scottgu/archive/2010/07/1...
I'm currently working on an SSIS package to load mainframe logs from multiple server/file sources into a database. As it stands at the moment I'm using a foreach loop container to loop through a recordset containing filenames and load the files using a Data Flow task from a Flat File Source and File connection to an OLE DB Destination t...
Hi I have a table in SQL Server with the following columns: id int (primary key) text nvarchar(max) (full text indexed) type int and I have queries like this: where Contains([text], @text) or where Contains([text], @text) AND [type] = 3 However the second query is slow. I think I should integrate full text index with [type] fie...
I'm using Visual Studio's designer to create a typed data set, and I've changed one of the fields in the stored procedure to be a TEXT type. I now can't refresh as the TEXT type is not comparable. How do I get around this? ...
I'm trying to create a CLR function in SQLServer that calls a web service. When I create the first assembly, the AssemblyVersion in AssemblyInfo.cs is 1.0.*. I'll run sgen to create the accompanying XMLSerializers assembly, and I don't see any problems there. >"C:\Program Files\Microsoft SDKs\Windows\v7.0\Bin\sgen.exe" /force /assembl...
I have asp.net application and log4net which configured for write logging messages in to msssql. But when i start my application messages doesnt writing in to db. Only when i change my source code file and restart application all message write in to database with right time stamp. Why? Sorry for my bad english. ...
I alway wondered how could a very big site like facebook to be faster than any other sites ,though the very big large amount of data which stored everyday .. what they are using to store information and if I use sql server to store e.g news feed is that ok or what (the news feed will be stored in a separate table which called News) . ...
I have this XML in a SQL Server table: <root> <meetings> <meeting> <id>111</id> <participants> <participant><name>Smith</name></participant> <participant><name>Jones</name></participant> <participant><name>Brown</name></participant> </participants> </meeting> <meeting> <id>22...
SELECT u.Id FROM Users u WHERE FREETEXT((FirstName,Lastname,MiddleName),'') UNION SELECT c.AId FROM Certification c WHERE FREETEXT(*,'') UNION SELECT ad.AId FROM ApplicantDetails ad WHERE FREETEXT(*,'') UNION SELECT eb.AId FROM EducationalBackground eb WHERE FREETEXT(*,'') UNION SELECT ed.AId FROM EmploymentDetails ed WHERE F...
this stored procedure does not return salary with decimal format 00.00 ALTER PROCEDURE taxable_varsalary @emp_code bigint, @co_id bigint AS declare @t_varsalary decimal(8,2) set @t_varsalary = (select sum(tran_value) from emp_ded_ben_trans where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0) RETURN @t_var...
In .net (c# or vb) expressions, how would you implement SQL's handy IN() functionality? i.e. value in (1, 2, 4, 7) rather than: value = 1 or value = 2 or value = 4 or value = 7 There are obviously many ways, I'm looking for the most clean and simple! ...
Changes to the data are not updated immediately. It is required to close the query results window and then re-open it again to see the changes. Is it possible to refresh the data without reopening? ...
We have C# entity classes which map to some of our SQL Server database tables. We need to be able to serialize these classes in order to send them back to the client, from the server, via WCF. For each SQL Server data type, we have a corresponding C# CLR data type in the associated entity. We have some SQL Server data types, such as Im...
I'm creating a stored procedure that will create 3 triggers (insert, update, delete) given a table name. Here is an example to illustrate the issue I'm experiencing : CREATE PROCEDURE [dbo].[sp_test] AS BEGIN BEGIN TRAN -- Create trigger 1 DECLARE @sql NVARCHAR(MAX) = 'CREATE TRIGGER test1 ON TableXML AFTER INSERT AS B...