Answer Questions

• Anthony Carabbio Issues with removing replication

  SQL Server 2005 Standard replicating to an identical server. My issue: When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily I stopped the log reader but it still gave me the same error. The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process. Please Help! ...Show All

• Vian Zhou SQL Service Broker : "Task Scheduler"

  Hi, I'm looking to run a "job" on a SQL database regularly (like scheduled by a "task scheduler") to updated my database. It appears I can use SQL Service broker to run such a job. Can I embed a "task scheduler" inside my SQL Server database to trigger this job each X seconds Hi Gerald, I put together an test app based on your suggested design, and works nicely! After running " BEGIN CONVERSATION TIMER ( @conversationHandle ) TIMEOUT = @queueSeconds ;", I see the message in the queue @queueSeconds later (great!). One additional feature that I'm trying to accomplish... There are many variants of the "job" I need to run (these j ...Show All

• white2grey Using fussy lookup in sql server 2005 integration services

  i have 4 different databases, in each one i have a table of customers i want to combine the 4 tables of customers from the 4 databases in a new table with eliminating similar records There's a fairly popular site that may help you. Ever heard of Google http://www.google.co.uk/search hl=en&q=ssis+fuzzy&meta = -Jamie Check out Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005 @ http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/FzDTSSQL05.asp Thanks, Loonysan can i have a url of th online material showing how to use the fuzzy grouping. The UNION ALL component can be used to combine the 4 datasets int ...Show All

• Al6200 How do I programatically fail my custom destination component?

  I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package. Question is, how do I do this If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message Thanks, ~ Gary Is there any special reason for wanting to actually fail the component It may be cleaner to check in the control flow, previous to the dataflow step, if the name pipe exis ...Show All

• 6stringer9 dynamically resize a chart

  I am doing a report in SQL Server Reporting Services 2k5 , the report has a chart that displays data from a query. The question is: Is possible to dynamically resize the area occupied by the chart depending on the amount of information (variations of a category) to be displayed on it One way of achieving this is to use a chart embedded inside another data region’s group. For instance, you could use a list data region with a detail group based on the following expression: =Ceiling(RowNumber(Nothing)/20) This will result in one group for every twenty detail rows. Embedding a chart inside that list will create chart instances at runtime for every twenty rows. If you are looking for an inline bar visualiz ...Show All

• Lisurc Is is possible to make index over datepart in datetime column?

  For example, i want to make index over dd-mm-yyyy not including hh:mm:ss, or sometimes only mm-yyyy. Is this possible Yes, that's possible, create a view and then index your view. ...Show All

• dustinto Formulas for Column Properties

  I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples. Thanks Hi, if you want to store 1/1/1900 but just display NULL you should consider using a view or a query to decide on the value. If you want to change the data upon insert in the table, you should consider a trigger to change the data on the events like update / Insert to change if its a 1/1/1900 to NULL. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• kleinma Problem with SP_Addlinkedserver when used in stored procedure

  I wrote the following stored procedure. Note that SERVER_BK is an ODBC connection on the server that links to the remote server database. --Creates the stored procedure that will be called in the Tablecounts script to send the comparison out to admins if exists ( select * from dbo . sysobjects where id = object_id ( N 'sp_compare_table_record_counts' ) and OBJECTPROPERTY ( id , N 'IsProcedure' ) = 1 ) drop procedure sp_compare_table_record_counts go create proc sp_compare_table_record_counts as ----Creates the connection to the Remote Database. We need to make sure that the ----ODBC connections on each of the Production and Development Servers ----are named the same, so that w ...Show All

• blender Function call to Progress linked server

  I have a Progress DB set up as a linked server. To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB. I've tried SELECT {fn PRO_ELEMENT(fldarr1,1,1)} as fld1 from ls1..pub.tab This just returns an unknown function message which I believe is on the SQL Server end of the call. This statement works fine through Business Objects. Any help greatfully received. try using OPENQUERY function. select * from openquery (lnkd_server, 'SELECT {fn PRO_ELEMENT(fldarr1,1,1)} as fld1 from ls1..pub.tab') ...Show All

• Beast Forever Need debugging help--stored procedure to move database objects

  Hi all--I'm adapting a stored procedure to work on transferring SQL Server 2005 tables and other objects owned under the dbo schema on a database to another non-dbo schema. Given that, I'm trying to use an "if...else" to prevent dbo-owned stored procedures and tables like dtproperties from moving with the rest of the objects to the non-dbo schema. Here's the code: if exists ( select * from sys.objects where object_id = object_id ( N '[dbo].[chObjOwner]' ) and OBJECTPROPERTY ( object_id , N 'IsProcedure' ) = 1 ) drop procedure [dbo] . [chObjOwner] GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO CREATE proc chObjOwner ( @usrName varchar ( 20 ), @newUsrName varchar ( 50 )) a ...Show All

• AmirAlis Using a sproc as a source within Dataflow

  I was wondering what has worked for all of you in regards to using a sproc as a source within a dataflow.  I have had limited success doing this, but maybe there is a workaround I'm unaware of.  Basically, using a SQL command in an OLE DB Source, I run an EXEC statement that returns a resultset from a stored procedure.  I've noticed that depending on how the sproc is structured, I will either get metadata info in the columns tab of the OLE DB Source or not.  Without this metadata of course I can not link it with a destination, since the destination believes that no data is being returned, even if this is not the case.  This all seems to depend on the "final" select statement in the sproc being at the very top of th ...Show All

• Keith Chapman SMO instead of DMO with VB6

  Just added some code to a VB project that uses DMO to enumerate SQL servers and Databases. When looking for a way to get the path to the datafile and logfile, I found the following statement on some MSDN documents. SQL Distributed Management Objects (SQL-DMO) This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. I guess I need to switch over to SMO. Can I do this in VB6 Thanks SMO requires the .Net Framework 2.0, so you'd really be better off upgrading your code to use the Framework. The bonus is that you'll have access to all the new features of S ...Show All

• bookysmell2004 How Can I Ignore Accsents and diacritical marks in WHERE statement?

  How Can I Ignore Accsents and diacritical marks in WHERE statement i use sql statment for searching and i wont to search in text filds where some accent and diacritical marks have been enterd. like: i want to search for 'e' or 'o' ... letters in this text: "euoa" if i used: "Select * from table where fild like 'e' " the result will be 0 items. Also, same proplem in Arabic accents for eg: if i want to search for ' ' in this text ' ' , using select where statement. what can i do Thank you.. This statement works properly with French and German, but it does'nt works with ARABIC or FARSI. There is no option for Arabic accent insensetive in SQL Collations. What Can I do now ...Show All

• su45937 problems testing db restore, file based backups

  I'm trying to test out a restore scenario for a large database. It contains several files, each one is backed up when we are finished inserting data into it. No changes are made to the data after insert. All changes happen in tables in the primary file group. So, I backup the primary file, the log, the first "partitioned" file, the log, the next "partitioned" file, the log, etc, etc, etc. Now, I delete one of the tables residing on one of the partitioned files and want to restore it. For arguments sake, let's say I have a primary file, and 5 partitioned files. I delete the table which was on file 3. I'm having trouble wrapping my head around the steps to restore under these circumstances. I thought I understood it ...Show All

• FedorSteeman SSRS and Teradata

  Is anyone out there using SSRS with a Teradata data source R I am using the .NET Teradata provider. I have the provider registered and it is working. I am just trying to work out the syntax when using parameters. R I have connected successfully for reports that do not require parameters. I am now looking ot find out how to correctly pass parameters to Teradata. R First on the date thing, I cheated. I made a function that changed the datatype to the Teradata specific format. Function FormatTeraDate(byVal aDate As Date) AS String return String.Format("1{0:yyMMdd}", aDate) End Function I probably didn't nee ...Show All