If I have a database containing only varchar columns, with strings encoded in Latin-1 (and collation set accordingly), will converting the columns to nvarchar using ALTER TABLE commands change the encoding of the column contents to unicode (UTF-16), or do I have to convert them myself? ...
I am writing a Silverlight client that interacts with an SQL database via ASP.NET 2.0 web services, which I am also developing. One of the services needs to return the results of a stored procedure and it works well. However, one parameter of the stored procedure needs to be retrieved from a different location before the stored procedure...
I've got data in SQL Server 2005 that contains html tags and I'd like to strip all that out, leaving just the text between the tags. Ideally also replacing things like < with <, etc. Is there an easy way to do this or has someone already got some sample sql code? I don't have the ability to add extended stored procs and the like, so...
I have a customer that has set up a full backup of the database to an network path every 4:th minute on a heavy used database. I strongly recommend the customer to not to do that and instead do a transactional backup. But what is the possibly bad things that can happened when doing a full backup in this way? Other than that the backup ...
I've got SQL Server jobs running that include periodic 'print' statements so that I can look in the job history and get a sense of what happened. But the output is cluttered with [SQLSTATE 01000]. Given that there is a limit to how much will fit in this output, I'd like as much space as possible for information I care about. Is ther...
I'm trying to extract the substring trackingCode to the point of the delimiter ~~. So for example: www.test.com~~34235645463544563554 I wish to return: www.test.com i am using: SUBSTRING(trackingCode,1,FINDSTRING(trackingCode,"~~",1) - 1) But it is not working and I'm getting an error: Error: 0xC0049067 at aw import from ...
I would like to randomly sort a result in a repeatable fashion for purposes such as paging. For this NEWID() is too random in that the same results cannot be re-obtained. Order by Rand(seed) would be ideal as with the same seed the same random collection would result. Unfortunately, the Rand() state resets with every row… does anyone ...
It seems that due to ActiveX that Firefox can't display the print icon/button on the Report Viewer control in SQL Server Reporting services. Has anybody figured out a way to work around this or gotten this to work? If it's not possible, does anybody know of a way to add a standard button that would trigger the print behavior on the rep...
I have a table which uses three columns as a composite key. One of these column values is used as a sequence tracker for ordered related records. When I insert a new record I have to increment the sequence numbers for the related records that come after the new record. I can do this directly in SQL Server Management Studio, but when I ...
I am trying to write some code that will determine if a list of SQL servers are up. I have tried to WMI, SQLDMO, SqlDataSourceEnumerator, and Pinging port 1433 of each server, with varying degrees of success (see results below). Using SQLDMO and SqlDataSourceEnumerator, i found 3 out of 6, it has to be said that 2 of the 3 missing SQL s...
Assume the Stored Proc takes 10 minutes to run and returns no data. What is the proper way to call a stored procedure in SQL Server and have your code not wait around for the result? Is there a way to handle it from the T-SQL or the connection? Something that works in ADO.NET and classic ActiveX ADO? The only way I thought of is: 1) Cr...
I have a basic "property bag" table that stores attributes about my primary table "Card." So when I want to start doing some advanced searching for cards, I can do something like this: SELECT dbo.Card.Id, dbo.Card.Name FROM dbo.Card INNER JOIN dbo.CardProperty ON dbo.CardProperty.IdCrd = dbo.Card.Id WHERE dbo.CardProperty.Id...
For a large database (thousands of stored procedures) running on a dedicated SQL Server, is it better to include SET NOCOUNT ON at the top of every stored procedure, or to set that option at the server level (Properties -> Connections -> "no count" checkbox)? It sounds like the DRY Principle ("Don't Repeat Yourself") applies, and the opt...
I have a bunch (750K) of records in one table that I have to see they're in another table. The second table has millions of records, and the data is something like this: Source table 9999-A1B-1234X, with the middle part potentially being longer than three digits Target table DescriptionPhrase9999-A1B-1234X(9 pages) - yes, the parens a...
I have an SSIS package that I've saved under Maintenance Plans on SQL Server 2005. Though I can select SSIS packages, I am not sure how I can delete them. ...
I have an index -- let's call it IX_MY_INDEX -- in a SQL Server table (both compatibility modes 80 and 90) that I would like to determine the size of. How do I do this? Update: Allain Lalonde's second solution below only works when the compatibility mode is set to 90; however, the particular database I am working on is in compatibility ...
I have an small question I didn't found an answer yet: how do I get in c# and using Microsoft.SqlServer.Smo the table a foreign key column is referring to? foreach (Column column in currentTable.Columns) { if (column.IsForeignKey) { //GET TABLE FOREIGN KEY REFERS TO } } ...
I have the 4 sources of IP addresses , I want to store them in SQL Server and allow the ranges, that can be categorised by the originating country code, to be maked in an Exclusion list by country. For this I have 2 tables. IPAddressRange CountryCode What I need to know is, if this data was returned to the client then cached for qui...
I am trying to insert a conditional page break, actually i had a report of 50 pages with invoices each invoice is 3 pages long so i need a paghebreak for the 2nd invoice t printon the next page as i need the whole report ot print back to back,so i inserted one page break,so this starts the next invoice to start on 4th page but i want nex...
I'm looking into horizontal partitioning for a table that has time-series data in it. I've discovered that partitioning is much easier in 2005 than it was in 2000 but I can't seem to find this answer: Can I add/drop columns of a partitioned table? Are special steps required because it's partitioned? ...