Answer Questions

• calbear Migrating a column from ntext to XML

  After SQL Server 2005 migration, I'm trying to change my ntext column to XML type. The "alter table TableName alter column XMLCol xml" command throw this error : XML parsing: line 1, character 39, unable to switch the encoding 1. Do I have to reformat my nText column before the migration to XML type 2. Is there a way to create an XML data type with UFT-8 in SQL Server 2005 Thank you for your help Thank you for your help Converting it to varchar(max) is still having the potential to lead to encoding issues or data corruption. Instead the column should be converted to varbinary(max) first and then to XML. Best regards Michael hi, if you would be converting the column from ntext and ...Show All

• dki Query to get Linked List kind of data from the Table

  hi Experts, I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of ID Duplicate_of State 77637 65702 Duplicate 65702 42217 Duplicate 42217 - Verified i wanted to write a query or some stored procedure when passed 77637 should help me get 42217 . Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate , 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate i appreciate if somebody can help me think in some line to ...Show All

• JasonG271009 SQL Server 2000 Query Problem

  I'm having trouble coming up with a query in SQL Server 2000. Trying my best to sum it up in one sentence; I need a result set that for every EventID/StartDate that exists in tblEvents, I need all StartDates within x days to have a minimum sequential, unique number starting at 1. Now I'll go into some more detail and possibly confuse everyone. What I have is a table of events that to simplify this question include only an EventID and an StartDate field. What I need to do is for each event, find the other events that occur within x days after the event in question. I then need to number the events starting at 1 so that each eventB that occurs within x days of EventA has the minimum number different from any events from the past x days. I ...Show All

• GS80 Local Cubes in SS2005, specifically for the dev team

  Hi, A question on local cubes... If I'm right in saying that if you use the CREATE CUBE statement to create a local cube using the MSOLAP.3 provider, that under the hood you will in fact still be using the MSOLAP.2 provider and hence creating AS2K local cubes, how will this tally with mainstream support for AS2K ending in 2008 Will these form of local cube cease to be supported at that time regards Colin Well I'm basing my assumption on the information in MDX Solutions, chapter 17, which in turn was based on information obtained directly from the development team As I understand it, when the connection is made to the empty .cub file and the MSOLAP.3 provider sees the syntax CREATE CUBE an ...Show All

• dto Log Shipping will not restore tran logs on secondary server

  We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL. I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically. When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore. The backup and copy both suceed. The restore claims to suceed. If I review the ...Show All

• Wayne Sepega Test linked server connection SQL 2000

  I’m trying to resolve an issue and wonder if anybody has ever run into and possibly had a resolution for. I wrote a custom conflict resolvers to manage data at the column level. What I needed to do to achieve this was to use a remote query to the mobile subscriber that raised the conflict. I’m trying to put in a rule that if there are any issues connecting to the subscriber I want to use the default conflict resolver and move forward. Here is my issue, it seems there is no way I can test a connection without getting a critical error that I can’t seem to trap. Example I was trying to do Select * from [linkedserver].[database].[dbo].[incident] If @@error <> 0 use default conflict resolver ...Show All

• arifyemen Constraint disabling

  Hi, Is there any way to disable/enable constraints on the table Command "ALTER TABLE table_name {CHECK|NOCHECK} CONSTRAINT ALL" produces error. ALTER TABLE Your_Table_Name NOCHECK CONSTRAINT Constraint_Name Babak Izadi LotraSoft Ltd. "No action" action is the default for reference constraints. "If NO ACTION is specified, SQL Server Compact Edition returns an error, and the update action on the referenced row in the parent table is rolled back." - this is what I'm trying to avoid. This drops (removes) constraint, but I need only disable it for a time. The command you ...Show All

• confused2xover Can SSIS do SFTP? and how?

  I want to know whether SSIS can do SFTP. If yes, how does it work Thanks, Kev Yes We are using Xceed FTP component in a Script Task ( http://xceed.com/FTP_NET_Features.html ) Certain operations (zip, ftp, email) in SSIS are not built-in or doesn't suffice the requirements in which case you can extend the functionality using components, script tasks or custom-built tasks. Check CozyRoc SFTP task for secure communications. It has user interface and functionality very similar to the standard FTP task. Regards, Ivan TPK1216 wrote: I want to know whether SSIS can do SFTP. If yes, how does it work Thanks, Kev nSoftware have a suite of SSIS tasks, one of which is an SFTP task. More ...Show All

• hdp203 SQL 2005 Backup maintenance plan

  In SQL 2005... How do I specifty in the maintenance plan to only keep 3 days worth of backup. Andre Use 'Maintenance Cleanup Task' under Maintenance plan tasks and configure 'File Age'. Make sure to check the updates under SQL Server books online (February 2007) update about maintenance plans. Thank you! I've been looking for that answer in books and online and was about to ask the question myself when I found yours. Linda ...Show All

• PrashG Encrypting SQL Server table columns

  I have never used any type of encryption for SqL Server 2000, but I have recently been asked to research what would be involved to encrypt data like social security numbers. Since I have no idea where to begin, I was wondering if someone could point in the right direction Do I need to buy a third party tool to perform this, are security certificates involved Overall, I need information on everything that I need to encrypt data using SQL Server 2000, and How to perform the tasks!!! Thank You! As Euan already mentioned, I always liked the way doing encryption on the application / business layer in my application. You will have full control over your encryption and can easily hook some extra m ...Show All

• Cesar Francisco The return of Problem Assigning Value to Package Variable From Data Flow Script Component

  I have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount User::giRowCount is populated by a Row Count Component previously in the Data Flow. After reading http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=97494&SiteID=1 it is very clear that you can actually only use  variables in the PostExecute of a Data Flow Script Component or you will get an error "Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute." What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as&nbs ...Show All

• Fahd return value of empty list

  Hi, I'm trying check the records which satisfies the following condn. select @p1 = su1 from RER where (no=10 and atp ='N') It returns empty list . bcos no match found.that's ok. But i need to write the logic if record is not there. i tried to do as follows if ((@p1 =null) or (p1=' ')) ...some logic else .. other logic It's always executing else case. How do i catch empty value using sql query. Is there any command there like null I tried in SQL query analyzer. Using SQL 2000. Thank You Hi 1. Try to catch number of records effected straight after your assinment and use that count to decide on actions 2. And a second thought : your select @p1= will always return ...Show All

• cisco0407 Problem starting MS SQL server 2005 service

  Hi, Anybody kindly help me. We have windows server 2000 and sql server 2005 on the same machine with 15 clients. There was a power failure. The sql server is automatically stopped. When we tried to start the service we are getting the error as action failed please check application event log for errors. This has happened for the 2 times, kindly tell me why this happens and what precaution we can take and is there any setting we are missing. Since I am newbie to sql server and windows server I am unable to solve this problem. Thanks I have the same issue about " Unable to complete login process due to delay in opening server connection ". And I just connect to the Server on local machine. This error happe ...Show All

• Alibong Making Reports available to users

  Briefly our architecture is 1 Public web site 2 Private web service layer. 3 database We want to make reports available on public web site with the minimum of work. The snag is that all our reports needs the system user ID passed to them which is normally calculated by the web service layer and not passed up to the public front end. We pass a system generated session Guid from services tier to web site. Are we now restricted to have to use the web services option and render our own data capture screens or does anybody know how we can use the tools out of the box to pass hidden parameters. Or am I missing something obvious here Cheers Darren ...Show All

• Daniel Blei Views and Indexes in SQL2005

  I'm trying to clarify a situation I have dealing with indexes and views. Let's say I have Table_A and Table_B, both contain a column [name] which is indexed in both tables. I also have a view vw_Table_AB that unions Table_A and Table_B. If I write a query like: select * from vw_Table_AB where name = "Dobbs" are the indexes of the base tables used for this query Hello, Yup - both indexes (on Table_A.name and Table_B.name) will be utilised. Cheers, Rob ...Show All