sql-server

SQL Server 'FOR XML PATH' in PHP/MySQL

I have a SQL Server query that I need to convert to MySQL. I've never used SQL Server/T-SQL before, so I have no experience with FOR XML PATH. There's surprisingly little documentation on this sort of thing. If I remove the FOR XML PATH statement, MySQL returns the error "Operand should contain at least 1 column(s)." It seems like the n...

Creating a Primary Key on a temp table - When?

I have a stored procedure that is working with a large amount of data. I have that data being inserted in to a temp table. The overall flow of events is something like CREATE #TempTable ( Col1 NUMERIC(18,0) NOT NULL, --This will not be an identity column. ,Col2 INT NOT NULL, ,Col3 BIGINT, ,Col4 VARCHAR(25) NOT NULL, ...

Restoring multiple database backups in a transaction

I wrote a stored procedure that restores as set of the database backups. It takes two parameters - a source directory and a restore directory. The procedure looks for all .bak files in the source directory (recursively) and restores all the databases. The stored procedure works as expected, but it has one issue - if I uncomment the tr...

Update a column of a table to be a sequence

I want to update a table's column using the results of a stored procedure create procedure seq as DECLARE @NextValue INT; INSERT SequencesTable DEFAULT VALUES; DELETE SequencesTable WITH(READPAST); SELECT SCOPE_IDENTITY(); go I can't use a UDF since it is nodeterministic. Something like this won't work UPDATE [dbo].[t1] SET [c1]...

Is there any way to expand the Processes pane in Sql Server 2008 Activity Monitor?

This should possibly be a Microsoft support question, but does anyone know if there's a way to expand the Processes (or any of the other panes) in SSMS 2008 Activity Monitor? All i actually want to see is a list of running processes, but even if i collapse all the other panes i can't expand one to fit the screen. It seems like such a s...

Cannot insert datasheet into form (Access Project)

Following the instruction found here: Insert a datasheet into a form I get an error when trying to insert a query as a datasheet in an Access Project (SQL Server 2005 backend) The form name you entered doesn't follow Microsoft Office Access object-naming rules I selected the view View.dbo.viewname from the dropdown in 'Source Objec...

ADOQuery, trigger and requery bug

I have an ADOQuery that inserts a record to SQL Server 2005 table that has trigger inserting data to another table. I use following code to refresh the query and avoid Row cannot be located for updating (There are PKs on each table, UpdateCriteria property are set, Cursors are set to Dynamic, yet I still get the error sometimes. However ...

SQL Server Profiler discrepancy

Hello, I am struggling with a strange problem using Sql Profiler. While running some performance testing scripts, I run profiler to find bottlenecks. One particular statement seems to be taking a lot of time - with CPU 1407, Reads 75668 and Duration of 175. However when I run the same statement in Management Studio, SQL Profiler return...

Help me writing this query

CREATE PROCEDURE [dbo].[sp_SelectRecipientsList4Test] --'6DBF9A01-C88F-414D-8DD9-696749258CEF','Emirates.Description','0','5' --'6DBF9A01-C88F-414D-8DD9-696749258CEF', --'121f8b91-a441-4fbf-8a4f-563f53fcc103' ( @p_CreatedBy UNIQUEIDENTIFIER, @p_SortExpression NVARCHAR(100), @p_StartIndex INT, @p_MaxRows INT ) AS SET NOCOUNT ON; IF LEN(@p...

Tricky question on predicting next records

I am doing a testing scenario 6 people in each Site Site 1 - A, B, C, D, E, F Site 2 - G, H, I, J, K, L Site 3 - M, N, O, P, Q, R Site 4 - S, T, U, V, W, X I want to write a query that can suggests me datewise the people who can test the site - two at a time. Here are the rules: Rule 1: If a person has tested his site on Day 1, then...

How to Check a Condition in Access Database

Using Access Database How to use case condition? My Query Select ID, Name, Intime, case when Outtime=Intime then ‘000000’ else Outtime end as Out from table The above query was accepting in SQL 2000, but not accepting in Access Database How I have to check the conditions? Need Query Help. ...

Row_Number() in SQLServer

select sp_una_stl_key, row_number() over(order by sp_una_stl_key)as stl_key from t_unit_data_archive where stl_key>=10 This query is not executed, throws, Msg 207, Level 16, State 1, Line 2 Invalid column name 'stl_key'. i could not understand what is the problem. please help me! ...

SQL Join taking long time

I have two tables (1) MonthlyTarget {SalesManCode, TargetMonthYear, TargetValue}; this table has 1966177 rows. (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; this table has 400310 rows. I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, De...

SQL query result monthly arrangement

I have two tables (1) MonthlyTarget {SalesManCode, TargetValue}; (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, DecAch} What should be the query? ...

Like Operator in Entity Framework?

We're trying to implement the "LIKE" operator in Entity Framework for our entities with string fields, but it doesn't appear to be supported. Has anyone else tried to do something like this? This blog post summarizes the issue we're having. We could use contains, but that only matches the most trivial case for LIKE. Combining contain...

Is there a problem when I call SqlAdapter.Update and at the same time call SqlDataReader.Read

I have two applications, one updates a single table which has constant number of rows (128 rows) using SqlDataAdapter.Update method , and another application that select from this table periodically using SqlDataReader. sometimes the DataReader returns only 127 rows not 128, and the update application does not remove or even insert any ...

Shrinking the transaction log of a mirrored SQL Server 2005 database

I've been looking all over the internet and I can't find an acceptable solution to my problem, I'm wondering if there even is a solution without a compromise... I'm not a DBA, but I'm a one man team working on a huge web site with no extra funding for extra bodies, so I'm doing the best I can. Our backup plan sucks, and I'm having a re...

How do I use a comma separated list of values as a filter in T-SQL?

I have a basic SQL query, starting with: SELECT top 20 application_id, [name], location_id FROM apps Now, I would like to finish it so that it does this (written in Pseudocode) if @lid > 0 then WHERE location_id IN (@lid) else WHERE location_id is all values in location_id column As requested, here is an example applicat...

Deployment/Management of Web Sites for Multiple Clients

I have a small business that sells website solutions to clients. The websites serve the same purpose; to allow the customer to send and schedule SMS messages. Each website is slightly different. For example, 1 site has mandatory information such as address details and group name while the other has different requirements such as logging...

Should Lookup Table Foreign Keys Always be Indexed?

If I have a lookup table with very few records in it (say, less than ten), should I bother putting an index on the Foreign Key of another table to which it is attached? For that matter, does the lookup table even need an index on the Primary Key? Specifically, is there any performance benefit that outweighs the overhead of maintainin...