I am trying come up with a way to pull the tables out of an Access database, automate the creation of those same tables in a SQL 2008 DB, and move the data to the new tables. This process will happen on a regular basis and there may be different tables each time. I would like to do this totally in SSIS. C# SQL CLR objects are an optio...
This question might be kind of elementary, but here goes: I have a SQL Server database with a 4 GB log file. The DB is 16GB and is backed up nightly. Can I truncate the log regularly because the entire DB+Log is backed up each night? ...
For SQL server is it better to use an uniqueidentifier(GUID) or a bigint for an identity column? ...
Hi all and thanks in advance. Is there a way to provide a link from one .rdl to another details report. I'm using .net 1.1 and I believe it's the first version of SSRS. ...
i know there is a variable, function, or stored procedure that you can use to find the path that SQL Server is installed to: e.g.: c:\Program Files\Microsoft SQL Server\MSSQL.7\MSSQL or m:\CustomInstance\MSSQL In reality, i'm hoping to SELECT for the default backup path. But since i doubt that exists, i'll just tack \BACKUP ont...
I'm using PSEXEC within batch files to execute DTEXEC (SQL SSIS jobs) as part of a scheduling system. What I'm finding is when a bunch of jobs are triggered together (or even close to one another) I get multiple ERROR 128 messages and the DTEXEC jobs immediately abort. I'm guessing there is some sort of problem running multiple instances...
I am working with sql 2000 I am trying to access all of the "current activity>process info" from microsoft access. I thought, the sproc code in the link below would give me it but it doesnt. I have no problem basing a dataset on sp_lock2 and reading the info, but it doesnt pull all of the data in the process info view under enterprise m...
I'm currently writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/or readable) way? (I am using Microsoft SQL Server, 2005) A simplified example: SELECT col...
I have a set of records in my MS SQL table. With Date as the primary key. But the Dates are only for working days and not the continues days. Eg: 1/3/2000 12:00:00 AM 5209.540000000 5384.660000000 5209.540000000 5375.110000000 1/4/2000 12:00:00 AM 5533.980000000 5533.980000000 5376.430000000 5491.010000000 1/5/2000 12:00:00 AM ...
From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting. For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C: int f(float x, float y) { return x; } f(a, a / b) ; // This will result in ...
I have a table of vehicles with registration numbers, and want to select a subset of them that are between some user-supplied 'from' and 'to' values. So lets say the table looks like this: id reg_num 1 DD1111 2 DD1112 3 DE2245 4 EE5678 5 EF6547 The SQL I have so far looks like this: select * ...
We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second. We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame ...
My system does some pretty heavy processing, and I've been attacking the performance in order to give me the ability to run more test runs in shorter times. I have quite a few cases where a UDF has to get called on say, 5 million rows (and I pretty much thought there was no way around it). Well, it turns out, there is a way to work aro...
What's the best way to convert search terms entered by a user, into a query that can be used in a where clause for full-text searching to query a table and get back relevant results? For example, the following query entered by the user: +"e-mail" +attachment -"word document" -"e-learning" Should translate into something like: SELECT...
Hi I need a hand mapping a collection. I am using a join table to hold references to rooms ids and calEvent Ids. However, the look ups will mainly be based on the room's buildingID - so I'd like to add buildingID to the following mapping. Does anyone know how to do so. I've looked through the NHibernate docs but can't find anything. <b...
Hi, We have a Single Statement FUNCTION in SQL Server 2005 which uses CONTAINSTABLE(). All works fine when we pass a non empty search string. Is there a wildcard string we can pass to CONTAINSTABLE() so that it matches all records in a table. Kind regards, ...
hi there, i'm currently experiencing a little problem. I'm using nhibernate with around 40 entities mapped to a sql server database. however, due to performance reasons some operations are done via stored procedure. i have a stored procedure that takes 3 parameters. one entity ID and 2 DateTime's. now, when i have a Date like the 10th...
I've just started my first development job for a reasonably sized company that has to manage a lot of data. An average database is 6gb (from what I've seen so far). One of the jobs is reporting. How it's done currently is - Data is replicated and transferred onto a data warehouse. From there, all the data required for a particular repo...
When I save a file 'last saved' date time in a SQL database, I cannot compare with a future scan directly - eg if( SavedLastAccessDate <> FileSavedDate I have to do this: DateTime fileSavedDateTime = System.IO.File.getLastWriteTime(filepath); TimeSpan gap = fileSavedDateTime - SavedLastAccessDateTime; if (gap.Milliseconds > ) { do so...
Hi, I am trying to add a row to a table in the database using the table adapter's update method. I am not getting any errors it looks like working fine but there is no actual insertion into the database. However if I try to insert the same row again I get a constraint exception saying that I am trying to insert a data that is already in...