Get row where datetime column = today - SQL server noob
In sql 2005, instead of building a query from dateparts year, month and date, is there an more succinct way of writing the where clause? many thanks ...
sql-server-2005
SQL Server 2005 Table Alter History
Hi. Does SQL Server maintains any history to track table alterations like column add, delete, rename, type/ length change etc? I found many suggest to use stored procedures to do this manually. But I'm curious if SQL Server keeps such history in any system tables? Thanks. ...
sql connection problem only from another computer
I have a task that runs nightly. It connects to a sql server on our network and when I run it from my machine it runs fine. When it runs from the other computer I get the following error. System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this f...
SQL Server 2005 Full text search - Valid Thesaurus characters
FORMSOF THESAURUS throws errors for specific character. E.g. FORMSOF (THESAURUS, hel!lo) throws an error whereas FORMSOF (THESAURUS, hel?lo) works. However, I did not find any documentation about which characters are allowed. Can you help me out? ...
subqueries in UPDATE SET (sql server 2005)
I have a question about using subqueries in an Update statement. My example: UPDATE TRIPS SET locations = city + ', ' FROM (select Distinct city from poi where poi.trip_guid = trips.guid) Is it possible to refer to main table value (trips.guid) in sub...
C# Sql Server 2005 Bulk XML Delete?
I know you can do bulk XML inserts into SQL Server 2005 tables from your C# code using datasets/datatables. Is it possible to do the same but as a delete? ...
Inline Conditional Statement in Stored Procedure
Here is the pseudo-code for my inline query in my code: select columnOne from myTable where columnOne = '#variableOne#' if len(variableTwo) gt 0 and columnTwo = '#variableTwo#' end I would like to move this into a stored procedure but am having trouble building the query correctly. I assume it would be something like select...
Problems when going from SQL 2005 to SQL 2008
Hi! I did go over from SQL server 2005 to 2008. Doing that gave me some problems with the fulltext search. This site is based on Fulltext search. It occurs more deadlocks, the search is slower and sometimes it return empty lists, don't know why. A lot of people has been writning about they having this problem with 2008. But I haven'...
PHP mssql_query double quotes cannot be used
Hi all, In java-jdbc, I can easily run the following SQL (NOTE the double quotes around columns and table names) Select cus."customer_id" , cus."organisation_or_person" , cus."organisation_name" , cus."first_name" , cus."last_name" , cus."date_became_customer" , cus."other_customer_det...
How to avoid the same calculations on column values over and over again in a select?
I sometimes write SELECTs on the form: SELECT a.col1 + b.col2 * c.col4 as calc_col1, a.col1 + b.col2 * c.col4 + xxx as calc_col1_PLUS_MORE FROM .... INNER JOIN ... ON a.col1 + b.col2 * c.col4 < d.some_threshold WHERE a.col1 + b.col2*c.col4 > 0 When the calculations get rather involved and used up to 3-5 times within the sa...
sum of Times in SQL
Hello all, I have some records like this: ID Personel_Code Time --- ------------- ------ 1 0011 05:50 3 0011 20:12 4 0012 00:50 I want to have the sum of times for each person. in this example I want to have the result like this : Perso...
sql server : get default value of a column
Hello, I execute a select to get the structure of a table. I want to get info about the columns like its name or if it's null or if it's primary key.. I do something like this ....sys.columns c... c.precision, c.scale, c.is_nullable as isnullable, c.default_object_id as columndefault, c.is_computed as iscomputed, but for default value...
error -4861 - I do a BULK INSERT the contents of a csv file into a table through stored procedure
Hi, could you please help me? I have an application, in which I BULK INSERT the contents of a csv file into a table through stored procedure. The stored Procedure uses BULK INSERT (SQL Server 2005). This works fine in a standalone system. However when I use the same in a multitier architecture (Web server, Application Server and DB Se...
Unique id of a column
Hi. Does each column of a table in SQL Server have a unique id? I've looked into sys.columns and the column_id there is merely the order of the columns which changes if the order of the column is changed. I'd like to know if SQL Server maintains a unique id for each column as it does for each table and other objects. And if it does, how ...
Caching a user control
I have a user control which I want to Output cache( fragment cache) and set the dependency to a query. I mean that on change or modification of data returned by that query the cache should get invalidated. I have read that in the output cache directive in the user control Sqldependency = "CommandNotification" cannot be used. So, how sh...
SQL Server 2005 triggered audit tables moved to SQL Server 2008, now trigger does not respond when trying to insert row into audit table
We began with SQL Server 2005 database and tables. [UPDATE, INSERT and DELETE] in this case we were using the UPDATE trigger(s) to insert rows into audit tbl(s) when application (VB6) data table is modified. We moved the audit tables to SQL Server 2008. The only change in the trigger statement(s) (on the SQL Server 2005) we modified the ...
.Net installation issue with SqlServerPipelineHost and SqlServer.DtsMsg
I added a web service consumer to a vb 2005 Windows app and tried to install it on another computer, which had an earlier version already installed (ClickOnce deployment). An error came up saying I needed to install Microsoft.SqlServer.PipelineHost in the GAC. I added PipelineHost to the list of references and marked it Copy Local = true...
C# Threading and Sql Connections
I have a method that attempts to update a sql server database in an ASP.NET application. If the update fails, it catches the exception and then queues the update in MSMQ, and then spins up a new thread that will later de-queue the pending update and try again. When the thread starts, it fails to open a database connection because it is a...
Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection?
I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration? Can I run a command that basically says, "use OPTION(RECOMPILE) for all subsequent commands until I tell you to stop doing so?" I'm acutely aware of all the performance trade-offs here, and I know this is not a ste...
Is defragging tough on replication?
I've been told that defragging causes the log to grow tremendously. Is this true? If so, is there something better to do than defragging that will not impact the log as much? We are running SQL Server 2005 replicating between 2 sites. ...