Answer Questions

• Eric Liprandi Numeric attribute keys

  Does it make sense to favor numeric columns for the attribute keys (e.g. 1,2,3, etc. for the month attribute) as opposed to names (e.g. January, February). I've noticed that the sample AdventureWorks UDM uses both approaches so I am not sure which one is best from a performance standpoint. In AdventureWorks, numeric columns are typically used for snowflake dimensions while name columns are used in star schemas (except Date dimension which uses numeric columns). Thank you, Edward. Is there any recommended member count threshold, above which integer keys should be considered Obviously, having a separate integer key column for each attribute hierarchy could be counterproductive. The best is to use numeric keys for your dime ...Show All

• Thibaud Full-Text Search gets syntax error and kills the transaction

  I am having problems with syntax errors being generated from the search clause of a full-text search. When the syntax error is raised, the batch and transaction are stopped. Placing the search within a SQL Try/Catch allows the batch to continue, but the transaction is uncommitable. I need to trap and handle the full-text search syntax error then control the commit or rollback of the transaction based on other transactions. Below is a simplified version of the type of logic I am working with. The XACT_STATE goes to -1 because of the syntax error generated by the full-text search. This forces a rollback of the whole transaction. Declare @sSearch NVarChar ( 50 ) /* @sSearch is passed by a web page and could come ...Show All

• Steve Kibble Need Editable full Backup and Restore

  Hi there I just want to create Stored Procedure which will do following thing         i)  Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)         ii) the same file must also have, all the data from the selected database           tables,in again plain text format (could be CSV, or Tab SV, etc...) I mean to say, the file should be act as the full backup of database, in plain text. The same need to be done in the reverse order,this SP will do following things          i)Open ...Show All

• Eisa Monitoring SQL Server activies from an outside environment

  Hi, I don't know if this is the right place to post this kind of question (moderators, please move it to the right place if it's not here). I want to know how can I monitor some operations that are executed on a SQL Server database. Operations like: INSERT, DELETE, UPDATE, transactions, and so on (I know that I can do this using triggers, but doing this way I'll have to fill a table with the information from the triggers and stay loading the table data all the time. I want a way to monitor using other resources, in realtime). My idea is to build a program to monitor these activities (C# to be more exactly). How can I do this Thanks I understand. Thanks for your patience. Allen, I know some commercial products, but ...Show All

• leovernazza Send in table name to stored procedure

  Is it possible to send a table name into a stored procedure to use as the "From" What I mean is i have a stored procedure that will do exactly the same to 3 different tables so I want to send the table name in so I don't have to create 3 different stored procedures, this is a simplified eg of what I mean; CREATE PROCEDURE DoSomething (@tablename ) AS SELECT * FROM @table I have seen a few other posts similar to this but I didn't really understand what they meant. The actual SP i want to use this for is this; CREATE Procedure ListsImport ( @campaigncode varchar ( 100 ), @tablename varchar(100) ) AS INSERT INTO Lists ( title , firstname , lastname , emailaddress , homephone , businessphone , state , ...Show All

• LightTamer SQL 2000 multi-statement table valued function execution plan

  If a multi-statement table valued function contains conditional logic based on the input parameters which will ultimately execute a different SQL statement to do the bulk of the work, does this mess up the execution plans .. In other words, will it create an execution plan based upon the input passed in on the first execution only or will it always have optimum execution regardless of what is passed in If it creates an execution plan based on the parameter set passed in on the first execution, is there a way to override that such as in stored procedures where you can specify that the proc is to be recompiled every time Thanks! -Brian Flynn See if this helps. e.g. create function udf(@i int) ...Show All

• Kikkoz Get 'DB Error 10083' with SQL Server 2000

  Setup: SQL Server 2000 sp3 on a Windows 2000 machine, transactions running through an ODBC connection running SQLSRV32.dll version 2000.86.1830.00 Transactions: Being generated via a Citrix server (MetaFrame Presentation Server, Enterprise Edition 4.0(Build 2198), Service Pack 2005.04.... This sits on top of an install of Windows 2003 terminal server installaion. Error: Appears to only be happening on a ADD(Insert) .... Any thoughts on this would be a big help. Dave Thanks for giving me some direction on this issue! I will do some testing to how I can resolve this issue in a good way. David Did you ever find a resolution to this We are getting this ...Show All

• Bijan avoiding "NaN" , "infinity" in reports

  When I use calculation in generating reports, I get "NaN" and "Infinity" values, I wanted to avoid this and display "0" if the result of calculation is null or infinity.. I used the below calculation =(Fields!approved_tier_cnt.Value)/(Fields!approved_cnt.Value) Please let me know how to avoid "NaN" and "infinity", while displaying in reports Regards Durai I would use some explicit type casting and conditional logic. Firstly to avoid NaN I would make sure the fields are numeric by using one of the standard VB conversion functions e.g. CInt(), CDbl() etc. like this: =CDbl(Fields!approved_tier_cnt.Value)/CDbl(Fields!approved_c ...Show All

• EGutierrez91 MouseDown event Handler

  Has anyone tried using MouseDown and MouseUp custom code in a report for anything If you have I'd love to see your code. Thanks! ...Show All

• Deuce BOI Cannot find the queue 'SqlQueryNotificationService-{guid}'

  I am trying to get my application running on a customer site. They are being very picky about security on the database.  What are the correct permissions to setup to allow my sql user to use the Service Broker. Currently I am receiving the following error: Cannot fin the user 'owner', because it does not exist or you do not have permission. Cannot find the queue 'SqlQueryNotificationService-<guid>', because it does not exist or you do not have permission. If I make my user db_owner it seems to work on other installs. Thanks so much, ~ Steve Of note is that you don't need to give control over the dbo schema (which seems like it would be dangerous), rather you can create a new, e ...Show All

• bfarr23 Create Database in Management Studio

  Hi Does anyone know how to create an empty database in Management Studio. Or how to get rid of tables that are in the default database that the default script creates using a single command. By deault the database is created with all the tables from Model database, and to get rid of tables individually, one has to go and look at all the dependencies before they can be deleted. Thanks Alvin Hi, once we did that our own. The model database could not be changed for some reason I can’t remember :-) So we wrote a stored procedure which copied a template to a specified folder and attach the database to the server. The template database was clear from the users / objects we did not want to h ...Show All

• Steveinbeloit OLE DB Destination not writing data

  I am using OLE DB Destination to write data to a SQL server database. However, nothing is written to the database though there is no error reported. See the following output: SSIS package "Tbl_Dim_Dates.dtsx" starting. Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Tbl_Dim_Dates, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Tbl_Dim_Dates, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Tbl_Dim_Dates, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Tbl_Dim_Dates, DTS.Pipeline: Execute phase is beginning. Information: 0x402090DF at Tbl_Dim_Dates, OLE DB Dest ...Show All

• LuisMendes Include Comments in SQL Script

  Hi, When I generate SQL Script for my database, I wish to include the comments added against each column in the script. But there is no option available where I can specify to include "COMMENTS". Please suggest. Regards, Sudhir Chawla Actually, When you create table and add columns, at that time there is a "DESCRIPTION" field, where we can specify the comments for that column. So how do I include these column level comments to be present in my script. I can add my own comments using -- or /* adjkasjd */, but I wish to include column level comments in my script. Thanks for your advice. Regards, Sudhir Or you can wr ...Show All

• ngkay SQL Profiler Scan:Stopped Event

  I am trying to improve the load performance of a data mart. It is setup with a standard 3 database ETL process (prestage, stage and mart). I am using Profiler to capture scan stops and identify any tables that may be having excessive full table/index scans. One table in the stage database has had 900,314 scan complete events in 37 minutes. This database has no user connections and the only process that is running is our ETL. All of the ETL is written in stored procedures which there are only 90 or so. The ETL process takes about 70 minutes so it has not completed and restarted during this time. The next closest event counts are in the 100,000 range, which still seems high. My question is what could be causing all of these tabl ...Show All

• selva_kumar Installing on a Domain Controller

  One last question ( for today)... The recomindation to not install SQL express on a Domain Controller. Is there a specific technical reason or is it just the general don't use the DC has a Terminal server / sql server / application server line of thought The reason I ask is we have been using SQL 2000 on a W2k3 server configured as a DC as well as for Terminal Services. (yes I am aware of the security risks) but in a small network with 5 or less ft clients and 30 thin, I can't justify a seperate DC and App server. Kay Hi Sven - do you know if this problem has been fixed, and if so, how to make it work on a DC I'm in an environment where I need it to be installed on my Windows 2003 DC, and the insta ...Show All