In SQL Server, how do I "floor" a DATETIME to the second/minute/hour/day/year? Let's say that I have a date of 2008-09-17 12:56:53.430, then the output of flooring should be: Year: 2008-01-01 00:00:00.000 Month: 2008-09-01 00:00:00.000 Day: 2008-09-17 00:00:00.000 Hour: 2008-09-17 12:00:00.000 Minute: 2008-09-17 12:56:00.000 Second: ...
Hello I have a SQL Server 2005 machine with a JDE DB2 set up as a linked server. For some reason the performance of any queries from this box to the db2 box are horrible. For example. The following takes 7 mins to run from Management Studio SELECT * FROM F42119 WHERE SDUPMJ >= 107256 Whereas it takes seconds to run in...
I can make a DAO recordset in VB6/Access do anything - add data, clean data, move data, get data dressed in the morning and take it to school. But I don't even know where to start in .NET. I'm not having any problems retrieving data from the database, but what do real people do when they need to edit data and put it back? What's the e...
I'm designing this collection of classes and abstract (MustInherit) classes… This is the database table where I'm going to store all this… As far as the Microsoft SQL Server database knows, those are all nullable ("Allow Nulls") columns. But really, that depends on the class stored there: LinkNode, HtmlPageNode, or CodePageNode. ...
Hi all, I'm an experienced programmer in a legacy (yet object oriented) development tool and making the switch to C#/.Net. I'm writing a small single user app using SQL server CE 3.5. I've read the conceptual DataSet and related doc and my code works. Now I want to make sure that I'm doing it "right", get some feedback from experienced...
I have a DTS package with a data transformation task (data pump). I’d like to source the data with the results of a stored procedure that takes parameters, but DTS won’t preview the result set and can’t define the columns in the data transformation task. Has anyone gotten this to work? Caveat: The stored procedure uses two temp table...
I have a simple SQL 'Select' query, and I'd like to dump the results into an Excel file. I'm only able to save as .csv and converting to .xls creates some super ugly output. In any case, as far as I can tell (using Google) this doesn't seem to be so straight forward. Any help would be greatly appreciated. ...
I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL. Any ideas? Results would be something like: TableName, ColumnName ...
Is it possible to use an IF clause within a WHERE clause in MS SQL? Example: WHERE IF IsNumeric(@OrderNumber) = 1 OrderNumber = @OrderNumber ELSE OrderNumber LIKE '%' + @OrderNumber + '%' ...
In C# you can get the original error and trace the execution path (stack trace) using the inner exception that is passed up. I would like to know how this can be achieved using the error handling try/catch in sql server 2005 when an error occurs in a stored procedure nested 2 or 3 levels deep. I am hoping that functions like ERROR_ME...
I'd like know what people think about using RAISERROR in stored procedures to pass back user messages (i.e. business related messages, not error messages) to the application. Some of the senior developers in my firm have been using this method and catching the SqlException in our C# code to pick up the messages and display them to the...
Hi, we have several "production environments" (three servers each, with the same version of our system. Each one has a SQL Server Database as production database). In one of this environment the tempdb transaction log starts to grow fast and infinitely, we can´t find why. Same version of SO, SQL Server, application. No changes in the e...
I am trying to run a SQL Server Reporting Services where the data for the report is on a SQL Server database that's on a different server. Integrated Authentication is turned on for both the Report Server and the report. I have confirmed that Kerberos delegation is working fine by using Internet Explorer to run the report from inside t...
Is it possible to get notifications using SQL Server Reporting Services? Say for example I have a report that I want by mail if has for example suddenly shows more than 10 rows or if a specific value drop below 100 000. Do I need to tie Notification Services into it and how do I do that? Please provide as much technical details as possi...
Has anyone been able to get a variable record length text file (CSV) into SQL Server via SSIS? I have tried time and again to get a CSV file into a SQL Server table, using SSIS, where the input file has varying record lengths. For this question, the two different record lengths are 63 and 326 bytes. All record lengths will be importe...
I am trying to connect to a Microsoft SQL 2005 server which is not on port 1433. How do I indicate a different port number when connecting to the server using SQL management Studio? Thank you, Brett ...
I have a table that got into the "db_owner" schema, and I need it in the "dbo" schema. Is there a script or command to run to switch it over? ...
We would like to be able to nightly make a copy/backup/snapshot of a production database so that we can import it in the dev environment. We don't want to log ship to the dev environment because it needs to be something we can reset whenever we like to the last taken copy of the production database. We need to be able to clear certain...
I am using mssql and am having trouble using a subquery. The real query is quite complicated, but it has the same structure as this: select customerName, customerId, ( select count(*) from Purchases where Purchases.customerId=customerData.customerId ) as numberTransactions from customerData And what I want to d...
Update: giving a much more thorough example. The first two solutions offered were right along the lines of what I was trying to say not to do. I can't know location, it needs to be able to look at the whole document tree. So a solution along these lines, with /Books/ specified as the context will not work: SELECT x.query('.') FROM @x...