How to configure a database so that filestream data is stored on a non local path? To enable filestream at db level I do first: ALTER DATABASE MyDatabase ADD FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM; GO Then: ALTER DATABASE MyDatabase ADD FILE ( NAME = MyDatabaseFileStreamFile, FILENAME = 'c:\Test') TO FILEGRO...
I have birth dates stored as datetime in SQL Server 2008 like so: 2010-04-25 00:00:00.000 What is the best way, using C#, to convert and format this into a string with a YYYYMMDD format? In the end, all I need is a string like: 20100425 Any help is greatly appreciated! ...
I have a query that returns data as XML using the "for XML Clause" and then sends the result as the body of an email like so. Declare @messBody as nvarchar(max) Set @messBody = (Select * from tablehere where state = 1 for xml Auto) Begin Exec msdb.dbo.ap_send_dbmail @profile_name = 'ProfileNameHere' @recipients = 'simon@ex...
What would be the best way of doing this? select 'blah' as foo, CASE WHEN foo='blah' THEN 'fizz' ELSE 'buzz' END as bar As it is written right now I get an invalid column name 'foo' error. Is there anyway to do this where the select statement could be used as a view? ...
Hi, it seems that despite the fact that SQL Server does not match on case in a WHERE clause it still honours UPPER/LOWER in a WHERE clause which seems to be quite expensive. Is it possible to instruct SQL Server to disregard UPPER/LOWER in a WHERE clause? This might seem like a pointless question but it's very nice to be able to write ...
Hello, I need to read a nvarchar(max) field from a SQL Server 2008 database using Delphi 6 and ADO. I can handle the unicode text just fine but it seems the ADO component is "preconverting" the string to code page before I even get to have a look at it. I've tried accessing the field as a TBlobField but it gives me the converted ve...
On aspx page I have asp:textbox multilined, where I type data. When I assign TextBox1.text to a string i get something like this: "line1\r\nline2\r\nline3" But if I try to set it back to literal, I get: line1 line2 line3 (without the line barkes) How to solve it? Is the only way to replace the \r\n with <br />? A note: If I ...
Ok I know "what X should I use" is very broad so let me narrow down our usage scenario. Basically, we should have been using an ORM a long time ago. Now though there is no way we can go through and rewrite every line of generated SQL Queries in our C# code. But we want to at least take a few steps in the right direction. So when we write...
So I'm trying to convert a web service that was an Oracle application to T-SQL. The fun part is I only have the web service code and no database code at all. I see there is an input parameter that contains <ROWSET><ROW NUM=\"1\"><TRANSACTIONID>123456</TRANSACTIONID></ROW></ROWSET> I'm looking through the docs for T-SQL and I can't seem...
Hi Everyone, DB: SQL Server 2008 I have two servers A and B. I want to able to insert data from server A into server B using a particular user. I can't seem to find a syntax for doing that. Can anyone please help me out on this. Thanks ...
My sql statement SELECT * FROM mytable WHERE (CONTAINS(keyword,' "green" ')) How do I list the top matched result like this Green Army Green Hunter Green instead of the below Army Green Green Hunter Green I know that we can't use the order by asc or desc with my case. I plan to add a space before 'green' and use the dict...
Hello Friends, Using VC# I've created a staff management app that, upon its first run, is expected to query the user for the path to a (.mdf) database which will reside on a remote computer. A resulting path may be something like string dbPath = @"P:\remoteComputer\public\StaffTool\ExamplePersonnelDatabase.mdf"; Then I'm placing th...
Hi, The following query performs badly because of a full non-clustered index scan of 6.5 million records in P4FileReleases followed by a hash join. I'm looking for possible reasons the optimizer picks a scan over a seek. SELECT p4f.FileReleaseID FROM P4FileReleases p4f INNER JOIN AnalyzedFileView af ON p4f.FileRelease = ...
How do I add a subtotal to a matrix in reporting services 2008? If I right click on a cell I see an option to add a total but no subtotal. (I feel like I'm missing something obvious but I've looked everywhere.) ...
Our database does not have foreign keys, but we wish to be able to use SubSonic. Are there any ways except for foreign keys to express relationships between tables? Also see this related question ...
hey guys I got this. I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table.. what am I doing wrong? It increases timeValid for +30 days but inserts another row too.. thank you.. Select @pre_var = count(*) From PREMIUM_...
Using Sql Server 2005. I have a long running update that may take about 60 seconds in our production environment. The update is not part of any explicit transactions nor has any sql hints. While the update is running, what's to be expected from other requests that occur on those rows that will be updated? There's about 6 million tota...
I am joining a table that has two record id fields (record1, record2) to a view twice--once on each record--and selecting the top 1000. The view consists of several rather large tables, and it's id field is a string concatenation of their respective Ids (this was necessary for some third party software that requires a unique ID for the ...
I am trying to call an IF statement on my trigger so it won't archive expired files. (I only want to keep files that have been deleted but have not been expired) My error is The multi-part identifier "d.ExpiryDate" could not be bound. My Code: ALTER TRIGGER [dbo].[ArchiveDB] ON [dbo].[TBL_Content] AFTER DELETE AS BEGIN...
Simply put, I have a select that will return multiple single characters, and thus won't work. Is there any way to bunch all the single characters into a single returnable string? My current slow and ugly solution: ,'('+(Select Left(max(AE_D1),1) FROM ACCESS_EVENTS WHERE LEFT(AE_D1,1) like 'W' AND replace(HR.first...