Search results for query: *

It's not a good idea. The dts trick drops and recreates the the target object. You lose any sequence numbers, constraints, privileges etc. Probably throws a lock on the system tables that could totally screw up other queries while it's running. Not sure what it will do to replication or...

The functionality on bit data types is really limited - can't do joins or group by's, you can only use in the where or bitwise operators. Unless you have alot of bit columns the easiest thing to do is change them to tinyint's. This will take up 1 byte per row instead of 1 bit per row (the...

I generally put char(39) (single quote) into a local variable and concatenate it into the query string. You can also use quotename(string, char(39)). Quotename bit be a couple of times because it is limited to 128 characters.

Do the crosstab on risk_competitive_program in a subquery first to get the columns, in the outer query join in the risk_competitive_exception.

Look at books online under BCP utility: Copying Data From a Query to a Data File You can run it from a stored procedure using the xp_cmdshell command. There's some effort in getting xp_cmdshell to work on a server and it's a security hole. If you set up a dts package you can use the dtsrun...

DTS seems to give the most generic data types when you load from excel. The numerics usually end up as float (which looks like your result) and the strings end up as nvarchar (double byte characters). You can override the default data types by clicking the transform box in the dts wizard when...

Re: Recursive Loops The @@fetch_status = 0 is global to the server. If you nest the loops you need to store the results of each fetch in a separate local variable. instead of: fetch next from lvl1_loop into @lvl1id,@lvl1parent while @@fetch_status = 0 Use declare @lvl1 smallint fetch...

You don't need to do a separate "SELECT @vchTextFile, @txSummary1 ....." The fetch will populate the variables. BTW, there is always a way to solve the problem not using a cursor, it's usually much faster. I've had speed improvements of 2-3 orders of magnitude.

Try putting the stored procedure call in an access pass through query. The pass through uses the sqlserver syntax: exec (procedurename).

You will probably need to reenter the data source security information. If you are using odbc you need to set up the same dsn's on the target server. When it asks do you want to reset the transformations, say no, otherwise they'll all get wiped out.

Look up "partitioned views" in books online. If you can create a check constraint on the column / columns you used to partition your data into the 3 tables, the optimizer is smart enough to only hit the one table that contains the data. Under certain conditions the view is updatable...