Given the following XML variable, how can I replace "UNKNOWN" in the StateCode node with "FOO" in TSQL for MS SQL 2005? declare @xmldata xml set @xmldata = '<Collection> <Plan> <StateCode>UNKNOWN</StateCode> <Type>Tubular</Type> </Plan> </Collection>' Unlike a similar question that I found, this is si...
I am working on an .net client server application, a new requirement is to allow user upload/copy videos into the system, there will be about 50 videos (500 MB each) per year. The application stores data in SQL 2005, but obviously storing the half GB video in SQL server is not great idea. Any suggestion on how to manage the upload/copy a...
We currently have our custom application that is being distributed with our database (SQL 2005/2008). It is an easy task, before we release a new version we just pack our database into SQL initialization scripts (these create tables and populate data). We use SQL Management studio to generate these scripts. As a next step we would like ...
is it possible to get a numeric parameter to my udf and do stuff according to its type, like: if type of @p1 is decimal(10,3) ... else if type of @p1 is decimal(15,3) ... else if type of @p1 is integer ... ...
I have a program in which I need to run multiple insert statements (dynamically generated) against a MS SQL Table. Right now, I do (pseudo-code): Loop Generate Insert Statement String SQLCommand.Text = Generated Insert Statement String SQLCommand.Execute(). End Loop Close Connection Is it better performance-wise to simply construct o...
is it possible for a udf to return the same data type as one of its parameters? i would like my udf to accept a decimal of any precision and scale and return the same type. ...
Does T-SQL accomodate for array values as parameters for stored procedures? If so how can this be achieved. ...
I have a partitioned fact table on a SQL Server 2005 (Ent, Ed., 32 bit, SP2) that I am constructing a partition for (the fact table is a snapshot type). The process builds the data in a separate table, applies appropriate indexes and then switches the partition into the table. This has worked in the past The table structures are ident...
I have an automated daily import where I am running a SQL Server Integration package programmatically. The error message it reports is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I believe this is the same message reported when ADO.NET commands timeout. I have lo...
Both queries below translates to the same number SELECT CONVERT(bigint,CONVERT(datetime,'2009-06-15 15:00:00')) SELECT CAST(CONVERT(datetime,'2009-06-15 23:01:00') as bigint) Result 39978 39978 The generated number will be different only if the days are different. There is any way to convert the DateTime to a more precise number, a...
Symptom: Sending an attachment using msdb.dbo.sp_send_dbmail results in the following error: File attachment or query results size exceeds allowable value of 1000000 bytes How can I increase that value? ...
I have a table with four columns (Year,Month,Name & Value) I want to add another column (new_value) which is calculated by the following formula NEW_VALUE = (VALUE of currentMonth/VALUE of (currentMonth-4))^1/3 Example Year Month NAME VALUE NEW_VALUE 2008 01 A 4.412 ? 2008 02 B 4.941 2008 03 C ...
Hi guys, I am looking for a clean way to forward some demo data using a stored procedure. The data that I want to forward are date types. Due to the nature of my app, some of the data in my app will only appear when certain dates in the data are in the future. I hope this makes sense. : S Since my database is ever expanding, I was thin...
I'm trying to send a XML of aproximately 1MB as XML parameter in a Stored Procedure, but always the connection returns timeout. Anyone knows what's the size limit for the XML type? Environment: Microsoft SQL Server 2005 Express .NET Framework 2.0 C# C# Code: using (SqlCommand commandSave = new SqlCommand("SaveScanning", this.D...
What's the best way to store a file size in bytes in database? Considering that the size can be huge MB, GB, TB... I'm using bigint (max: 9.223.372.036.854.775.807), but is it the best way? ...
How to set with FluentNHibernate the default value of 1 or 0 for a BIT column of the table generated from my entity for the field of type bool. I think it doesn't matter but in any case the database is sqlserver2005. ...
I have some XML that has creating using an XmlDocument object in C#. I am then inserting the xml data into an XML column in SQL 2005 using the XmlDocument.OuterXml method to get the raw xml. The problem I have is that the xml contains some special characters. Namely:  This is because the xml is built up from user input from...
I am using the ADO.Net SqlCommand type and I'm setting the CommandTimeout to 30 seconds. My problem is that the connection/command keeps timing out causing unhandled exceptions that crash my system! The data I am trying to retrieve is critical to the system – so I want to fix the timeouts rather than add exception handling retry logic....
I'm wanting to select rows in a table where the primary key is in another table. I'm not sure if I should use a JOIN or the IN operator in SQL Server 2005. Is there any significant performance difference between these two SQL queries with a large dataset (i.e. millions of rows)? SELECT * FROM a WHERE a.c IN (SELECT d FROM b) SELECT a.*...
I am supporting an ASP.Net 3.5 web application and users are experiencing a timeout error after 30 seconds when trying to run a report. Looking around the web it seems it's easy enough to change the timeout in the code, unfortunately I'm not able to access the code and recompile. Is there anyway to configure the default for either the ...