sql-server

invoking trigger on column update

Hi all, I have a condition where in a trigger should be invoked whenever a particular column gets updated in a table but not when any other column gets updated. Is it possible? ...

When i select the job properties it is always showing New job window

I have the admin rights on the server and when i select job properties its always showing new job window and the user who is having same access able to view the job properties. Please help me out in this ...

Current transaction ID in an audit trigger

I was looking at storing some form of transaction id from an audit trigger. The solution appeared to be to use sys.dm_tran_current_transaction as in this post http://stackoverflow.com/questions/650761/sql-server-triggers-grouping-by-transactions/651112#651112. However, I cannot use this because the user account running sql statements wi...

what is wrong in my code to call a stored procedure

I Have created a Stored procedure dbo.test as follows use sample go create procedure dbo.test as DECLARE @command as varchar(1000), @i int SET @i = 0 WHILE @i < 5 BEGIN Print 'I VALUE ' +CONVERT(varchar(20),@i) SET @i = @i + 1 END Now i am created a c# console application to call the stored ...

SQL Server Indexed View syntax for joined DB

I'm trying to set up an index on a VIEW of mine. First error was 1939, Schema Binding required. Ok, no problem with that. ALTER VIEW xyz WITH SCHEMABINDING AS abc Now, in this VIEW I'm using one local table [dbo].][ReleantTable] and two joined tables from other databases on the same server: OtherDbName..OtherRelevantTable Altering ...

TSQL: Get all rows for given ID

I am trying to output all of my database reports into one report. I'm currently using nested select statements to get each line for each ID (the number of ID's is unknown). Now I would like to return all the rows for every ID (e.g. 1-25 if there are 25 rows) in one query. How would I do this? SELECT ( (SELECT ... FROM ... WHERE id =...

String compare with 'strange chars' in SqlServer

I have some content in our database with (for our language) 'strange' characters. For example: Å When using the like statement in Sql Server with the letter A, this does not return a result because the Å is not an A. Is there a way in SqlServer to treat the Å as an E (and the é as an 'e' etc)? ...

SQL Server: How to update a cell value at position (x,y)

SQL Server Question: I have a table with appx. 1000 already existing rows, and 5 columns How can I update the value at row Y in column ? I don't have any keys or conditions for the where. ...

SQL 2008 filestream

Hello. Is there a way to retrieve the filename for filegroup filestream? I try to create a new database in tsql, i need to create the filestream path for the database. but i dont know the path of the folder to put the filestream in. So i try to get the filename path of another database on the same server to configure the new database....

Database Version Control SQL Server 2008 Drop SP's and Functions

I'm working on versioning our database and now searching for a way to drop all stored procedures and functions from a C# Console Application. I'd rather not create a stored procedure that drops all stored procedures and functions. I has to be some sql executed from C#. I tried to drop the stored procedure before creating it, but I get t...

sql join history table to active table SSRS report

I'm trying to pull results from a database (sql server 2005) which takes 4 tables: Subscriber S, Member M, ClaimLines L, ClaimHistoryLines H Query is as follows: select S.SBSB_ID, M.MEME_NAME, (CASE L.CLCL_ID WHEN '' THEN H.CLCL_ID ELSE L.CLCL_ID END) AS CLAIM_ID FROM CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_H...

Why is my index getting fragmented?

I have a simple little table (just 8 fields) an hour ago I rebuilt one of the indexes on the table, which reset it to 0% fragmentation, but now it’s up to 38%. The table itself has 400k records in it, but only 158 new ones have been inserted since I rebuilt the index, there have been no updates to records but perhaps a couple of deletes...

'Database may not be activated yet or may be in transition' error

I have this heavily nested sql statement which works well in my sql server 2008 express. [code block below] However, when I move it over to our preliminary test server (sql server 2000) it doesn't work unless I use fully resolved table references in the from clauses of each statement. Which I can't do as the database name varies by inst...

Correct String Escaping for T-SQL string literals

I want to use a query as following, I am looking for exact information/link to escape strings BookTitle is NVARCHAR(200) SELECT * FROM Books WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow \r\n') Question: Does only "'" needs to be escaped or even \r\n needs to be escaped as well? MySql .Net Provider exposes a method to esca...

Which version of sql server 2008 will have profiler on MSDN?

Hi, Is there another download besides sql server 2008 developer that will have profiler in it? I just want a local developer installation not a full blown server version. It has to have at least SP1 on it also. I am having issues with sql server 2008 developer 64-bit on my windows7 64-bit o/s. ...

SQL Query to search schema of all tables.

I am working on a SQL Server 2008 Db that has many tables in it (around 200). Many of these tables contain a field by the name "CreatedDate". I am trying to identify all the table schema with this particular field. Is there a SQL query to do this? Appreciate your response! ...

Generating Primary Key with Non-Zero Index (SSIS Data Flow)

I've got a data flow task that takes a pair of tables, mashes the relevant data together, and comes out with some results to be put into an indexed table. The indexed table already has data that I'm not getting rid of and for simplicity's sake should retain their existing keys. So, I need to generate a key that starts from the highest Pr...

Design for file downloads from a web server.

My web app consists of images stored in the SQL Server db. And, i have a silverlight app on the client side. The web app would allow clients to download file from the server by triggering the download in the silverlight app. The Silverlight talks to the web service to download the file. I am trying to understand the file download logic ...

How to extract relational data from XML in SQL Server

Hello, I'm new to XML world. I'm having a problem with XML data type in SQL Server 2005. I've a stored procedure that expects XML parameter. The value of the parameter as given below... DECLARE @productIds xml SET @productIds = '<ROOT> <AGENTLIST> <AGENT> <HOSTAPPLICATIONRECORDKEY>GRI</HOSTAPPLICATIONRECO...

Installing sqlserver2008 SP1, the 'select features' is empty?

Hi when installing sqlserver 2008 sp1 on windows7, when it gets to the 'select features' part the list is empty. I can't go past that step as I can't select anything and then the validation fails. Man installing sqlserver2008 on windows7 is a real nightmare! I have .net 3.5 SP1, trying to install sqlserver 2008 SP1 and its just not g...