Answer Questions

• Zooz Calculated measures not showing up on report models.

  I am working on a SQL 2005 database and report server. I have created a cube with calculated measures, however when I create a report model these calculated measures do not show up. Does anyone know if this can be done and how Thanks Umesh ...Show All

• Chris An How does sql server determine order of cascade deletes to many tables

  If have the following scenario Table A Table B fk to Table A Table C fk to Table A fk to Table B - nullable Table A cascades to table b and Table A cascades to Table C. Table B then restricts to Table C to avoid circular reference. However, the way our data is configured, the restriction from B to C will never matter when deleting from A if Table C is always cascaded to first and then B. I have done a few tests and this appears to work. I haven't been able to find any info on this, so is this order something I can always rely on There is no guarantee which one will get cascaded first. If you want full control, you should consider implementing trigger. So this co ...Show All

• Anthony von LA Create the package on the server, and execute through sql server agent

  Hi, Some of you guys seem to be gurus with the new Integration Services technology, so I hope someone can lend me some advice, as I haven't worked much with integration before. Ok, here we go. What I want to do is select some data from my database and export the result to a flat file (really a .csv file with values delimited by semicolons). Sounds like a simple thing to do Well, I'm sure it is when you know how to do it :) I know I could manage the same thing by writing a C# class that creates that .csv file, but the decision has been made to use Integration Services for these kind of operations. I created an SSIS project in Business Intelligence Development Studio, and created a package (I defined the task flow etc.). By choosing ...Show All

• James CACN The problem with poison message handling.

  Hi, I've written SSSB application with poison message handling inside. I've used an idea from the BOL example : I save the transaction  befor receiving a message. When I run the procedure the following error occures: "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." The questions are: 1. What a problem may cause the error like that 2. How could I debug SQL stored procedure at SQL Server 2005 Best Regards, Alex.   One Example was using to test SSB: --Step1 Create procedure used for message send use ssb go create PROCEDURE dbo.up_SSB_ProcessIncomingMsg AS BEGIN SET NOCOUNT ON DECLARE @Mes ...Show All

• donkaiser Enclosing a table create inside BEGIN END

  The following SQL (after the "The SQL" marker) works fine. But I want to enclose it in a check to make sure that a previous script hasn't already created this object and its CRUD. If I enclose it in a block like this... IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZIPCODE]') AND type in (N'U')) BEGIN blah blah blah END It won't parse. It seems to me like this should be a no brainer, but it is hurting my brain. The SQL... CREATE TABLE [dbo].[ZIPCODE]( [Zip] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Latitude] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Longitude] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [nvarchar](25) COLLATE SQL_Latin1_Gen ...Show All

• Sniper167 Add linked server at Excel VBA

  I had SQLDMO at reference library. Private Sub test() Dim s As SQLDMO.SQLServer Dim ls As SQLDMO.LinkedServer Set s = New SQLDMO.SQLServer s.Connect "Server1", "ID", "Password" Set ls = New SQLDMO.LinkedServer With ls .Name = "Server2" .ProviderName = "SQLOLEDB" .DataSource = "Server2" ' .ProviderString = "" End With s.LinkedServers.Add ls s.Close 'End Sub I block ProviderString as i don't know what is it. I got an error message when it's running the line "s.LinkedServers.Add ls" that "Run-time error '-2147206257 (80043b8f)': Automation error" My question: 1. What is providerstring what ...Show All

• donkaiser Error executing Parse in SSMS

  Executing Parse in Sql Server 2005 Management Studio I get the following error: .Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Reading SP1 fixes, I found a similar problem but despite I installed latest hotfix (sql version is now 9.0.2153) I can't able to execute Parse to check T-SQL statements... Thanks, Giulio Sorry, it doesn't fail for me. Will the statement execute Is is just the parse that fails What version of SQL Server ar you using Do an @@version: select @@version If no one replies I will ask around and see what I can find out. I tried using this sample statement and I get er ...Show All

• Greg Allen Peer-to-peer replication Questions

  Our current setup is as follows: serverA - DB1, DB2 ServerB - DB3 ServerC - DB4, DB5 Question 1 : In peer-to-peer, is this the right setup ServerA - DB1, DB2, DB3, DB4, DB5 ServerB - DB1, DB2, DB3, DB4, DB5 ServerC - DB1, DB2, DB3, DB4, DB5 Question 2 : Are we backing up all DBs or just DB1 Question 3: When serverA.DB1 goes down, does it affect the other DB1s Question 4: Can any of the DBs publish to any server say a reporting_serverG that is outside the peer-to-peer topology. Question 5: Is it best to have .mdf, .ldf, tempdb, etc. on local drive or in the SAN Question 6: What is the recommended NLB hardware needed to handle peer-to-peer I am confused by the quest ...Show All

• ProSlamBanO Remote messages not working – message vanishes

  I need some assistance with getting service broker to work across servers.   I have 2 separate servers and I am trying to send a message from one server to the other.   No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server.   Where is the message going     Here is the code I use to create the service broker objects that are being used…   RUN THIS ON THE SENDING SERVER:   CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE GO   CREATE CONTRACT [MyContract] (   &nb ...Show All

• AamirHabib Management Studio - Index properties grayed out

  If I right click on an index, the properties context menu is grayed out as well as the "New Index...", "Disable", and "Delete". If I right click on a table and click modify, I can add and edit indexes from there just fine. Strangely enough "Included Columns" is grayed out though. That was it. I had it open in another tab. Thanks! Was the database designer or table designer opened at the time We prevent the index dialogs from being launched when the table is being designed. The database and table designers work by making the state of the tables (including indexes) match the internal model in the designers. This includes index an ...Show All

• NickNotYet Bypassing locks when doing insert or update

  Hi, I want to bypass locks while doing Insert or Update. I am only updating a log db and I don't care about one or two fields getting junk as I won't use it later (atleast as long as I am working with my current company ;) ) I am using MS SQL 2000 I am getting too many deadlocks and messages like these "Process ID was deadlocked with another process and has been chosen a victim. Please rerun the transaction". Please tell me how to achieve this. Regards, Noorul Please take a look at the links below on how to resolve deadlocks. You can't eliminate them by just using locking hints in your various statements. Deadlocks are typically due to errors in your execution logic. http://support.micro ...Show All

• desilets Any clues why DTExec.exe is slower than Business Intelligence studio(SSIS Shell)?

  All, I have a simple package in SSIS that reads approx 2.5million rows from SQL2k5 source and exports it into 4 Foxpro files. The way I do it is as below 1) use and oledb source to read data from SQL2k5 2) use a conditional split component to divide the source into 4 streams ( I do a recId % 4 = 0, 1, 2, 3 to give 4 streams) 3) use an OLEDB destination to export to the 4 prexisting foxpro tables. This thing runs amazingly fast when I run(F5) it from Business Inteligence studio, but it is atleast 10 times slower when rightclick the package and execute using dtexec.exe I can't figure out why! any clues I would elimiate the 64 bit /32 bit problems because export to foxpro only works in 32 bit mode and my package has been configur ...Show All

• MaggieChan SQL problem !

  I dont know weather this is the right place to post this issue. If not please suggest me , I did not find anything right then this one. Ok now the problem. I've got a table TABLE_A --------- name -- vharchar(50) age -- integer DOB -- date I need to select records from this table depending on a selection criterion. thats easy , but i want to know that how many columns matched in the searching because i need to store the rating for how many columns matched for a particular search. Ex. if name , age and DOB matched rating is A3 if name , age matches rating is A2 and so on. This table is a huge one having 20 Million records, so I want a solution that is optimized too. I guess you can us ...Show All

• Jim Perry How to save the SSIS package results to a log file or text file using dtexec command

  I'm using the import wizard to create a new table from a flat file source.  The table gets created but no data gets copied.  What's wrong   Here's the report: Operation stopped... - Initializing Data Flow Task (Success) - Initializing Connections (Success) - Setting SQL Command (Success) - Setting Source Connection (Success) - Setting Destination Connection (Success) - Validating (Error)         Messages         * Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source - inSsrc_txt" (1).          (SQL Server Import and Export Wizard)      ...Show All

• p.cosmos Service Pack 1 -- account information could not be verified

  When installing Svcpack 1 ontop of my 9.00.1399.06 RTM Developer Edition I get the following error everytime: --------------------------- Invalid Login Information --------------------------- Your account information could not be verified for one or more instances. Ensure that you can connect to all the selected instances using the account information provided. To proceed further deselect the instances to which connectivity cannot be established. --------------------------- OK   --------------------------- I have followed all possible suggestions on the following post http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=358003&SiteID=1 but nothing works.  I'm running as the local admin on t ...Show All