Search results for query: *

Just a follow-up in case anyone else encounters this issue: the answer indeed was to run sp_dropdistributiondb. Part of the sp_adddistributiondb sproc is to flag the distribution database as having been pre-existing, or created by the sp_adddistributiondb; when sp_dropdistributiondb runs it...

Thank you for the reply, SQLBill. The database is part of a regular backup scheme, and we're making additional backups just in case. I think the main issue are the system databases/tables. When we restore this database on a different server (something we do every day anyway to serve as a...

Short version: sp_adddistributiondb ran on wrong database; how to "undo" that. I goofed. I had a script that sets up replication on our test box; after a successful test, I modified the server and database names within the script to run on our production box. Except I goofed on the database...

Caveat: I've never used EncryptByKey. The first issue, I think, is that you're defining @ccnbr as char(16) but then trying to store 19 characters (the 16 digits plus 3 hyphens). Separately, you can use unicode literals (using the N'string' format) with the nchar data type, or non-unicode with...

Two ideas: 1) You are defining @Text as varchar; try char(200) instead, since varchar by definition is variable-length. 2) I note in your code that you are referring to the file as a CSV; what about forcing quotes around the @Text value on output. Try #1 first, though. --------------...

For my complex reports (those that are beyond a simple SQL statement) I create a stored procedure and do all my data gathering and manipulation there. From what I understand of your project, I think you can fairly easily have a SProc that uses table variables/temp tables to gather diverse...

I've never used it in a production system (due to a lack of need), but you might also explore Application Roles as another alternative. -------------- www.liquidsql.com SQLS metasearch

Generally, use VarChar if there is significant variance in the actual stored data widths; using VarChar in these circumstances can lead to more efficient storage and fewer reads. Use Char if the data values don't differ much in length, as SQL Server can process fixed-width columns a bit faster...

Database servers aren't really designed to that sort of thing, John. The core engine doesn't have a user interface component, in the sense that you might think it does. True, there is the PRINT statement, but I've never used it for anthing other than as a debug aid. We can probably come up with...

I'm wondering if it is something in Synchronization Services that is messing up, such as looking up the RegTitleNumber, capturing its RegTitleID if found, then trying to insert it while specifying the RegTitleID (which it could only do if it sets its IDENTITY INSERT on). Unfortunately, I know...

Congratulations to ESQuared and SQLDenis for two effective and practical work-arounds: openquery and using IP address\instance name rather than servername\instance name. Both work, although the IP address is easier to implement (the queries involved are moderately complex). Unfortunately, the...

ESquared, openquery works! That can be a fall-back work-around. One more bit of bizarre: using a linkedserver query (without openquery) on the same-named database and table on a third, different server...works fine! So, it seems to be something specific about DW not seeing RPT properly. If I...

Hi, Denis; thanks for hanging in with this. I used the UI to create the linked server. SP_linkedservers shows: SRV_PROVIDERNAME: SQLNCLI SRV_DATASOURCE: RPT\INSTANCE SRV_PROVIDERSTRING: (null) SOME POSSIBLY IMPORTANT ADDITIONAL INFO * the same query against yet another linkedserver with the...

Are there any tables that are largish (say, more than 300K rows)? You may need to specify OUTER JOINs if the joined column is nullable, but that depends on whether you want them included or not. In and of itself, 18 isn't tragic...but if several of them have many rows, performance can be poor...

gmmastros: no aliases configured, but 10 points for something I wouldn't have thought of. SQLDenis: there is no connection string specified; I simply chose the SQL Server option, which disables all connection-related boxes. Esquared: that and sp_linkedservers shows what I would expect. When I...

I seem to be unique with this problem, because my Google-fu isn't showing this having come up before. The gist of the problem: linked server query actually pulls data from the primary server, assumedly because db/table name is same. The setup: SQL Server 2005 on both server "DW" and server...

It isn't really a test-oriented book, but one that is in a problem-and-solution format is SQL Server 2005 T-SQL Recipes by Sack. -------------- www.liquidsql.com SQLS metasearch

I had a data file to import (USPS ZIP Code data) that had no line terminators (but otherwise fixed-length), and the ONLY way I could get Bulk Insert to work was with a format file. If all the CSV files are in the format, then one format file can be used for all. If they are all in different...

If the fields contain quoted strings I'm surprised the embedded commas within the quoted strings are causing a problem. Are you using a format file? -------------- www.liquidsql.com SQLS metasearch

If I properly understand what you're after: case when trans_type=0 then date else null end -------------- www.liquidsql.com SQLS metasearch