Is there a way to get build a WHERE clause on the fly in a sql statement? This code is within a Stored Procedure. I have x amount of parameters and each parameter's default value is NULL SELECT * FROM MyTable m WHERE IF(NOT(@Param1 IS NULL)) m.Col1 = @Param1 END IF AND IF(NOT(@Param2 IS NULL)) m.Col2 = @Param2 END...
Hi All, I am contemplating using some user defined function calls within some of my queries instead of using a bunch of inline case statements. The inline statements will probably perform better, but the functions make it so much easier to view and possibly maintain. I just wanted to get an idea of what the typical best practice is fo...
My requirement is that user performing alter CANNOT be sysadmin (it can have all other rights but not sysadmin). I am running a query from local server which should modify a remote one EXEC ('ALTER DATABASE REMOTEDB MODIFY FILEGROUP ftfg_REMOTEDB NAME=ftfg_REMOTEDB') at [REMOTESERVER] This query works once I add sysadmin right to the...
I have ado.net sync services working on a mobile device. It does a snapshot sync over WCF (i used the .sync wizard stuff) and I have no problems. My problem is when I generate scripts from the database so that I can create it else where that DB does not work with sync. I get a TargetInvocationException when it does its GetSchema call. Th...
i have a sql server that has a test pp i have written, this thing is gaining traction and i am worried about loosing data (and the time it took users to enter the data) if the SQL server gets corrupted or someone does something dumb. i am thinking of what options i have that would do a nightly backup as a maintenance plan, and what i ca...
I have data Table1 ID Name ----------- 1 n1 2 n2 3 n4 Table2 FID YearS Val ---------------------- 1 2008 Up 1 2009 Down 1 2010 Up 2 2000 Up 2 2001 Down 2 2002 Up 2 2003 Up 3 2009 Down 3 2010 Up I want to return data in following format:...
I have a SQLCE table with a date field. Any given date could have one or more records. My client would like the input form to default to the next date (starting from and including the current date) that doesn't yet have a record. I'm having trouble wrapping my head around a query to accomplish this. Googling I've found a couple snippets,...
Hi, I'm writing once again about my encoding issue... Now with some code samples. In a nutshell: when saving to database input data, some language specyfic characters like polish 'ń' won't save - insted 'n' is saved. On the other hand, string: Adams æbler, with æ is saving. Here is code begind code that does save stuff and displays d...
I have 2 parameter lists. 1 is Owner and 1 is unit which is dependent on owner. These are multiselect parameters. By default select all is chosen for both so right when you enter the report select all is selected for both Owner and Units. Now when I uncheck select all and check 3 owners it again selects checks all units in the unit lis...
I have to get the month from a column which stores string in format - Column ---------- `Feb2007' 'Sep2008' So if the value was 'Feb2007' then I need to get back 2 or else if the value was 'Sep2009' then I should get back 9. Is there inbuilt function in SQL Server 2008 to achieve something like this? ...
Hello Everybody, I have been trying to find out total count of rows in each tables which are de-activated. In my DB I have 20 tables with column IsActive. I have tried below cursor,however it is getting error saying Invalid object name @Namee. Create table #t ( NoOfRows bigint, ) Declare @Namee Varchar(500) Declare @GetName Cursor S...
Hi, I have a simple table: CREATE TABLE [dbo].[Users]([Surname] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL) ON [PRIMARY] with two rows: Paweł Pawel Issuing following select statement: SELECT *, CAST(Surname AS VARBINARY(30)) AS Expr1, CAST(N'Paweł' AS VARBINARY(30)) AS Expr1 FROM Users WHERE Surname = N'Paweł' gives foll...
What is the SQL to delete a SQL User from a database? ...
I'm using a stored procedure to update\insert data into a table using MERGE. One of the items being inserted is a VarBinary file. Now if I wish to add a new file it's just a case of using ReadAllBytes as below, to remove the file I pass DBNull. Now what if other fields are being updated but the file is not being changed? I want the fiel...
I am creating a procedure something like below. it works fine when there is no "TOP @Count", or it works fine when i put a concrete vaule "TOP 100" . So why i cannot pass the value there??? how can i walk around it??? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE MyProcedure @Count int = 100 AS BEGIN SELE...
At the moment, if I save <element></element> to a SQL Server 2008 database in a field of type xml, it converts it to <element/>. How can I preserve the xml empty text as is when saving? In case this is a gotcha, I am utilising Linq to Sql as my ORM to communicate to the database in order to save it. ...
Hi there, I have a C# program that queries the database(SQL Server) for some value. Currently the application queries the database every minutes to make sure that the table is up to date. What I would like to be able to do is that the querie is only done when the database has been changed/updated.How do i notify my program when some t...
I have MS SQL Server database and insert some values to one of the tables. Let's say that the table contains columns ID, int Status and text Text. If possible, I would like to create a trigger which prevents from writing specific incorrect status (say 1) to the table. Instead, 0 should be written. However, other columns should be prese...
I'm wondering if there is any desktop OLAP solution that can use SQL Express (and therefore does not require Analysis Services) I've been tasked with finding a way to allow our customers to do 'Ad-Hoc' reports, but the vast majority of them are on Sql Express, In previous jobs, customers have had Analysis Services and typically Cognos o...
I have a statement with a single UPDATE command. If I manually terminate it will all the results be rolled back? ...