Answer Questions

• Wilton Kwok simple text processing e.g. regex search and replace

  I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found). Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go Does Visual Basic .NET provide regular expression matching Thanks! Duane Douglas wrote: Kevin Rodgers wrote: Duane Douglas wrote: Kevin Rodgers wrote: Duane Douglas wrote: there is no need to use a script component ...Show All

• colin leversuch-roberts SOS_SCHEDULER_YIELD

  I have a job running in 2 separate environments. In the first it runs very quickly (~5 seconds), however in the second it runs it takes MUCH longer (~5 minutes). These two machines have identical hardware and similar load. When I look at sys.sysprocesses to see what else is running, the long running machine always has the job step listed as SOS_SCHEDULER_YIELD as its wait_type. I know this is a natural yield for the step, but I don't think it should be yielding for this long, especially if nothing else is running. I have no idea where to look or what to change on this one. Any help would be greatly appreciated. Thanks. Start by looking at the lastwaittype for the processes that correspond to the job steps. Is that what you looke ...Show All

• Rodolfo Montero Passing string with quotes " 'VALUE1', 'VALUE2', 'VALUE3' " to a table-valued function does not work for WHERE IN (@VAR)

  I have a problem passing a string containing single quotes to a table-valued-function ex: SET QUOTED_IDENTIFIER OFF SELECT * FROM myFunc(" 'VALUE1', 'VALUE2', 'VALUE3' ") GO ----------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON G ALTER FUNCTION dbo . myFunc ( @VendorID CHAR ( 100 ) ) ) RETURNS TABLE AS RETURN ( SELECT * FROM myTable WHER Vendor IN ( @VendorID )) ) It works if I pass only 'VALUE1' but not if I pass " 'VALUE1', 'VALUE2', 'VALUE3' " nor if I use 2 single quotes to delimit each string. The same code will work if MyFunc is a STORE PROC instead of a function EXEC myProc '''990001'',''990002''' will work (notice 3 ...Show All

• Bhanu Prakash Nunna - MSFT The Fastest way to perform an update ... advice needed :)

  Hi all, I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value. I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way... Here it is: -- Update a student, by ID. DROP PROCEDURE p_UpdateStudent CREATE PROCEDURE p_UpdateStudent @ID INT , @NewFName VARCHAR ( 25 ), @NewOName VARCHAR ( 25 ), @NewLName VARCHAR ( 25 ), @NewDOB DATETIME , @NewENumber VARCHAR ( 10 ), ...Show All

• prasannakumar63529 Paging issue on conditionally set second table visibility

  Hi. I am having a problem with a 2005 report that has two tables and is designed thru visual studio. The second table is set to page break before printing and also to repeater headers on new pages. It works fine just like that. However, if I place a condition on the visibilty of the table based on an input report parameter it does not page break before rendering the table. In fact it does not page break at all leaving me with an extremely long last page. The visiblity parameter does properly toggle. This is all occurring when rendering the report to the screen using the windows reportviewer. When I export to PDF it looks fine. Any help would be great. No, its not fixed, but I figured out a solutio ...Show All

• Annant SQL's america date format conflict with australian date format

  Hi I am trying get my VB6 application to insert a record into a table (SQL Express) which has a datetime column but it would not process if the data format is differ to *American Date format*. The date() function in VB returns 15/11/2006 which is in Australian Date format (DD/MM/YYYY) according to my setting in "Reginal and Lanuage Option-> Locale 0> English (Australia)" setting. I get the following error: Msg 242, Level 16, State 3, Server KIT\SQLEXPRESS, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. My computer's locale is set to English (Australia) and I expect the datetime format would follow what is set ...Show All

• kumarvk Is my database connection alive and viable?

  Currently, our application is issuing a 'select count(*) from systypes' command to check to see if the database connection is live (has not been reaped by the firewall.) This is somewhat inefficient. Perhaps a less expensive way to implement this functionality is to issue a ping statement to the instance At least a more simple SQL query is needed. I'm sure this function is typical in application architecture. What recommendations do you have for confirming that a database connection is alive and viable from the application Stephanie One way is "select 1"... ...Show All

• i-developer how to find the edition of SQL Server 2000/2005 through registry?

  Hey, I need to understand what version of SQL is running on some machine. How can I do it looking through the registry. I know you can do it for windows and exchange but I don't know how to do it for SQL 2000. I know in SQL 2005, it tells you the edition for SQL, but how do you do it in SQL 2000. Also, I'm pretty sure, you can look at the Product ID and tell if it's an enterprise edition or standard edition and so on, but I don't have the information. Windows and Exchange do it like that. Can someone provide me with such info Thank you in query analyzer type: SELECT @@VERSION or try HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion also see http://groups.google.com/gro ...Show All

• subhanet Locale not supported Error

  I am using Vista and when I try to open one of the Business intelligence reports, I get error "Locale 16393 not supported". Any clue on this Thanks moving to SQL Reporting Services forum. ...Show All

• Greg Van Mullem create view using data in seperate severs

  Hello: I'd like to create a view on server x which references tables on an entirely seperate server. Is this possible Is seems strange to have to copy the tables over just to create a view. In the view wizard I can't seem to 'browse' to the tables on the other server. The code I'm working with would conceptually be something like this: select server name.database instance.owner.table.field from server name.database instance.owner.table where <field name> like 'xxxx%' or something along those lines. Any help would be appreciated! Thanks. I'm using SQL 2000. Below is the conceptual code. I continue to get errors with this approach. SELECT * FROM ...Show All

• LouisVanAlphen TargetQueue get disabled automatically ?

  I have a weird problem going on : my SB configuration involves 3 servers. Server A sends a message to Server B. there's a stored proc on Server B that is being activated (using Activation), does some processing and sends the message to server C. on C there's another SP that is activated and it's supposed to insert a record into a table. My problem is that on Server C, the record doesn't get inserted when activation is set to ON. when activation is off on C and I run the proc manually, everything works fine, but when activation is on, the record doesn't get inserted. after this happened I tried to fire the proc manually and I got the following error : Msg 9617, Level 16, State 1, Procedure spMSG_ReceiveMessagesOnSubscriber, Line ...Show All

• Matty4242 Locale and Culture

  As I am developing reports to be used in severel different languages I would like to start a discussion about locales and cultures. Hope to find answers to very simple questions like whats the difference between locale and culture and what determines the language used by the reporting service and the preview in visual studio. Please share your expirence . You may want to read this: http://msdn2.microsoft.com/en-us/library/ms156493.aspx -- Robert ...Show All

• Luc Pettett SSMS cannot connect to SQL2000 clustered instance !!!

  Using sql2000 query analyser I can connet to my SQL2000 virtual server . Using SSMS (with the same laptop/user/session) I cannot connect !! Using SQLCMD i can connect ! TITLE: Connect to Server ------------------------------ Cannot connect to mysql2kserver\mysql2kinstance ------------------------------ ADDITIONAL INFORMATION: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) For help, click: http://go.microsoft.com/fwlink ProdName=Micro ...Show All

• Alexandra5 T-SQL question

  I have some T-SQL experience and can do most basic stuff fairly easily. I've been tasked with generating a report that compares 2 different numbers in 2 tables. The join query that I wrote works well, but now they have asked me for a subset of the data returned. Basically my result set give me a format similiar to the following. Bob, Jim 000000012345 0012345 Doe, Jane 000001234567,1234567 If the second number returned has zeros preceding the number i need to include those in the report, and exclude ones that do not. I am unfamiliar with how to sort that, i am familiar with using trim to specify the number of spaces on the left or right to trim. Can i use a sub-query, or would i use a cursor to do this ...Show All

• Troy Lundin Backup Device vs backup command

  Hi, Will backup get completed quickly (the time taken for backing up large databases) on backup device or using backup command If backup device, why You are talking about 2 different things here. You use the backup command to backup the DB and the backup device to store the backed up files. Unless I dont understand your question. Hi Dinakar, I need to know which one completes backup faster, backup device or by using backup command. I heard that backup device completes backup quicker than using backup command. Thanks If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more ...Show All