SQL Server 2008, Physical Location of FILESTREAM Data...
hi, How could I know the Physical location (so i can see it in windows explorer) path of a Filestream data that I've just inserted into db? Thanks ...
sql-server
optimize this SQL query
I have this SQL query but its running soo slow, SELECT wr.wr_re_id as ReID, sum(wr.wr_total) as PRTotal FROM Workorder2 w INNER JOIN Workorder_Row2 wr ON wr.wr_wo_id = w.wo_id WHERE (w.wo_type = 1 or w.wo_type = 2) AND wr.wr_row_type = 2 AND w.wo_lo_id like '%' AND w.wo_date_time >= '2010-01-01'...
How to reduce 'number of executions' in MS SQL Execution Plan
Does anyone know how to reduce the 'Number of executions' you can see in the execution plan of a SQL Query in MS SQL Server? We have a query that runs slow on one production server (others are fine). When checking the execution plan, we see that one of the first steps is a Clustered Index Scan. On the servers that run fine, this scan is...
Programmatically name FILEGROUP in SQL server?
I'm trying to write a stored procedure that will create a new FILEGROUP based upon a given date parameter. What I want to see is a FILEGROUP called something like '2010_02_01'. What I get is a FILEGROUP called '@PartitionName'. ALTER PROCEDURE [dbo].[SP_CREATE_DATE_FILEGROUP] @PartitionDate DATETIME AS DECLARE @PartitionName VARCHA...
Executing a stored procedure to insert records into a table - SQL Server 2000
Hi, I'm working in SQL Server 2000. The following query is not working declare @TempAccountKey Table (AccKey int,SitName varchar(1000),SitKey int) insert into @TempAccountKey(AccKey,AccName) exec [usp_Get_AccountForUser] @UserName It is throwing the error EXECUTE cannot be used as a source when inserting into a table variable. A...
Storing and deleting data recursively in SQL Server
Hi, I am creating a blog component for a client's website and I'm having a bit of a problem with the data. I have a simple "content" table which holds the content for a blog entry. I'm reusing this table for comments on blog entries as the fields are all the same. I have added a ParentID to the content table so that I can link comments...
TOP 2 faster than TOP 1 in Sql Server?
Hi, we have a table with +- 500k rows in Sql Server 2005 database and one of its columns has a full-text index. We were doing some tests and found that SELECT TOP 1 ... WHERE CONTAINS(fullTextColumn, 'anyValue') was taking more than two minutes to return. However the same select, but with TOP 2 instead of 1 return in a few seconds. A...
SQL Server - Rewrite trigger to avoid cursor based approach
If I have table Test with two columns num1 and num2 and the following trigger on it which just increments num2 on inserts of num1: DECLARE @PROC_NEWNUM1 VARCHAR (10) DECLARE @NEWNUM2 numeric(20) DECLARE my_Cursor CURSOR FOR SELECT num1 FROM INSERTED; OPEN my_Cursor FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1 WHILE @@FETCH_STAT...
T-SQL fulltext catalog search with freetext
I have a fulltext catalog for an indexed view. Problem: I want to search with the function 'FREETEXT' all words which contains the search term. But the result doesn't return any entries... but one entry must be the word 'freightcosts' If the search term = 'freightcosts', the result is 'freightcosts'? Example: SELECT [Description] ...
Calling DBMAIL from CLR Stored Procedure
Is there a managed interface that can be used within a CLR Stored Procedure to send mail via the configured DBMAIL profiles? I am looking to send emails with attachments that will be downloaded from a web service so I am hoping to be able to send these without having to spool them out to disk in order to use the standard DBMAIL stored p...
SQL Server: what can a stored procedure do that a user defined function cannot?
Can you tell me what is the need for a stored procedure when there is UDF? ...
Check if role exists in DB?
I want to add users in same role in more than one database. However the role can/cannot be present in all the database. How can i check if the role exists in database and if it does add users in that role? e.g. IF role exists BEGIN Add user in role END ...
Case statement with multiple whens...not working!
This is a simplified version of what I'm doing, but I can't get anything to work. The statement gives me an error without the comma after 'ERR'. I want the column to be 'Month' and I tohught this would work but I'm having a ton of trouble. Thanks for your help! select a.POL_PRI_RSK_ST_CD, a.MASTER_COMPANY_NBR, case when a.char04...
Best technique to search for matching substrings with Linq & SQL Server
I have a requirement to find rows in a table containing 200,000 entries. Some may not consider this 'large', but it is large enough to warrant performance considerations. The table contains strings consisting of digits only. For instance, the user can enter something like '12340-0560-78', or portions of this, e.g. '0560', and I need to ...
SQL UPDATETEXT losing data
I have a procedure that concatenates TEXT fields inside another text field. Table A.TextField is composed of the concatenation of Table B.TextField1 + VARCHAR(8000) + B.TextField2 data. I get the TEXTPTR for the TextField1 and TextField2 and what I do is just to UPDATETEXT 3 times the A.TextField. At the end of the operation, I´m losin...
Log Shipping - 24 hour difference between source and destination system times.
We are trying to have log shipping work between two database servers where the destination server has a system time set 24 hours earlier than the source system time. Is it possible to force the destination machine to restore the data (disregarding the transaction file stamps are 24 hours ahead). ...
Why when I try to output a query result to CSV in SSIS instead of decimal numbers I get ones and zeros?
I have created a very simple Data Flow in SSIS that is run inside a loop. I have a simple OLE DB Source control which is connecting to a SQL Server and running quite a complex query to split daily data by 30 minute intervals as shown below. I then have a Flat File Destination control which is taking the output from the OLE DB Sourc...
How do I implement this multi-table database design/constraint, normalized?
I have data that kinda looks like this... Elements Class | Synthetic ID (pk) A | 2 A | 3 B | 4 B | 5 C | 6 C | 7 Elements_Xref ID (pk) | Synthetic ID | Real ID (fk) . | 2 | 77-8F <--- A class . | 3 | 30-7D <--- A class . | 6 | 21-2A <--- ...
Combining SQL Rows
I've got SQL Compact Database that contains a table of IP Packet Headers. The Table looks like this: Table: PacketHeaders ID SrcAddress SrcPort DestAddress DestPort Bytes 1 10.0.25.1 255 10.0.25.50 500 64 2 10.0.25.50 500 10.0.25.1 255 80 3 10.0.25.50 500 10.0.25.1 255 16 4 ...
How safe are SQL Server 2005 MDF files? (re: security)
We've started using SQL Server 2005 Express for a project and I noticed that every database has it's own MDF/LDF files. Whenever we need to duplicate our database (to another SQL Server) we simply copy these files and then attach them to the database. However, this has me wondering about the security of these files. If somebody from t...