Answer Questions

• Jose Romero Trying to make a search engine - please help

  Hi am creating a asp.net website for a business intranet. The users must be able to select existing clients according to crieteria typed into a search box so far my code looks something like this... select ClientID, (Firstname + ' ' + Lastname + ' ' + Email + ' ' + Phone) as clientstring from clients where firstname like '%i%' which in this case returns all clients in which there firstname contains 'i' BUT i want something like this... select ClientID, (Firstname + ' ' + Lastname + ' ' + email + ' ' + phone) as clientstring from clients where clientstring like '%i%' but i get the column doest exist error when i do this... so in other words i want to be able to return results like this... say the user types in 'sam' ...Show All

• AbhijeetRaje accesssing mirrored databases via SQLNCLI linked server?

  Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance Say I have a mirrored database on two servers: PRIMARY and SECONDARY. I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair. This is what I'm using: EXEC master.dbo.sp_addlinkedserver @server = N'MIRROR', @srvproduct=N'', @provider=N'SQLNCLI', @provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;' select count (*) from mirror.pubs.dbo.authors and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server: OLE DB provid ...Show All

• MarcGBeauchamp should be simple - FLAT FILE - HEADER / DETAIL LINES

  I can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class. 10001,2005/01/01,some more data SOME PRODUCT 1, 10 SOME PRODUCT 2, 5 Can somebody place give me some guidance on how to do this in SSIS I would do exactly what Rafael states. Split the file up into two separate files. Then process them accordingly. The problem I see is that SSIS does not handle flat files with an uneven number of columns very well. Flat File and uneven number of columns: http://fo ...Show All

• n0n4m3 Calling a SSIS package via SQL ServerAgent

  Hi, I was looking at a previos thread in this very queston and the answers given to it, I tried a SSIS package that works fine on its own but on creating a new job and invoke it, the JOB fails ,it says its not able to locate the file specified, I tried copying the package to the server machine wher am creating the job,but again the same error; and when i try to alter the protection level of the SSIS package to Server Storage its throwing an error like '' This protection level cant be applied to this destination,The system can't verify that the destination supports storage capacity. this error occurs when saving to XML." I am using OLE DB Destination in the dataflow task of the SSIS package I ve created. Please guide whr am going w ...Show All

• techie234 Tracking Schema changes in SQL server 2005 db

  I wonder how can I track the changes in the columns whether added, deleted or their name is changed and how can I test that your help is appreciated hi, if you are using sql server 2005 you may want to use DDL triggers to log DDL activities for more details on ddl trigers you may visit my blogs published below. look for DDL triggers if your using an earlier version you will have to use a third party tools. you may use "Lumigent log explorer" to trace what have transpired in your server regards I tried that and it only fires the trigger when changing the column type and not in the case of a column name. Our question is how to monitor a column name ch ...Show All

• Praveen.Yarlagadda SQL2005 Performance problem with updating subscribers with identities

  Since we've upgraded to 2005 we've had major performance problems with updating subscriber tables that contain identity PK columns set to manual or auto range management. If a table has identity range management set to 'None' (the "old" 2000 way), the peformance is fine. Below are sample tables: CREATE TABLE [dbo].[ID_RANGE_TEST]( [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [DATA] [int] NULL CONSTRAINT [PK_ID_RANGE_TEST] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[ID_NONE_TEST]( [ID] [int] IDENTITY(1,1) NOT NULL, [DATA] [int] NULL CONSTRAINT [PK_ID_NONE_TEST] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KE ...Show All

• jaydeep Fulltext indexes on separate catalogs

  For performance reasons if I have 2 separate fulltext indexes on 2 separate tables, should they also be in two separate catalogs Will putting them in the same catalog slow things down. I assume that catalogs cannot span filegroups. Sure you can create unique catalog for each of your fts index. However, you cannot span a catalog across multiple filegroups. Though, a filegroup can have more than one file. If you seperate your catalogs onto different LUNs you essentially minimize i/o contention. Thus, this will give you the perf gain. If you just split them up into their own cat but they're on the same disk, you do gain some perf due to catalog isolation but this will also increase your managemen ...Show All

• curiousss Restart package automatically

  Hi there, I would like to be able to automatically restart a package if it fails with a certain error. This is due to a job that fails every now and then due to a loss in connectivity to the database (when run again no issues). I know how to catch the message, but is there i way that i can in a script task or some other task restart the whole package when moving into the OnError event for the whole package. I know this isn't a great way to do it, but its just temporary until we can isolate the network issue that is causing the loss in connectivity. Thanks You would have to restart the packge host, not from within the package itself. A quick way of doing this would be to use a SQL Agent Jo ...Show All

• Coach24 Report Viewer Web Part - Error - Help!

  I am trying to integrate reports that I have built with VS 2005 and deployed to my report server locally. The path to the report is http://SERVERNAME/Reports/Pages/Report.aspx ItemPath=%2fBilling+System+Reports%2fBasic+Financial+Reports%2fBasic+Financial+Statistics+-+TimeKeeper+YTD When I put that into the "Report" parameter the web part display an error: The report server has encountered a configuration error. See the report server log files for more information And when I click on the ellipses, I get a pop up with the same error. I installed SQL2005 SP2 and the RS Add on for MS sharepoint CTP Any help would be great! Thanks, Erik Assuming your report server is conf ...Show All

• EliteSniper177 'downgrade' to server 2000 from 2005

  Hi, I'm looking into how i can revert back to running my db on sql server 2000, it's currently running on 2005 and having problems. Any help/pointers would be much appreciated Cheers for that Madhu Refer : http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=186526&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1273824&SiteID=1 Madhu ...Show All

• AlistairSKing Help on one colum

  Hi, I have a column in SQL that needs to be created. This column needs to start with 10 zero's. If I add a number, say 135 the column needs to read : 0000000135. Is this possible If it is how do I need to go about to do this. If I need to create a view or something like that I do not mind. I am open to all suggestions and appreciate the help. DECLARE @AnyStringNo NVARCHAR(10) SET @AnyStringNo = '135' SELECT REPLICATE(0,10-LEN(@AnyStringNo)) + @AnyStringNo SET @AnyStringNo = '47593' SELECT REPLICATE(0,10-LEN(@AnyStringNo)) + @AnyStringNo Will give ever output in 10 digits with replicating of 0s. One more question Mani, If you have a currency field with the following : 1.35 it im ...Show All

• Jobr77 Using SSRS 0n SQL 2005 Express-can not connect to report server

  I am using WIN XP PRO with IIS installed. Installation (including SSRS) seems OK. If I go into new project and select Business Infomration / New Report I can select datasource, etc, design a and get to a deployment window. Default is Report Server: http://localhost/ReportServer Deployment Folder: Report Project2 I can preview a report just fine. If I go to Debug or Deploy I get a error message box that starts with "A connection could not be made to the report server http://localhost/ReportServer" Am I missing a component on my system or can you suggest something else I should look at Thanks I just installed SSRS 2005, I have run the RS configuration tool and I am getting an "Failed to map the path ...Show All

• JavaBoy Updating column values on Insert trigger

  Hi all. I am new at programming ms sql. I have a table with a date field : EventDate DATETIME NOT NULL DEFAULT GetUtcDate(); Now I need to make sure that if anyone tries to insert a null(ie : the object-mapping mechanism) I replace it with GetUtcDate(). I think I remember that Interbase had a Before_xxx trigger, and in that one you looked at the value of the fields and modified it accordingly. Could not find such a thing here. How do you do that in SQL Server 2000 All I need to do is look at the value in the column and if it is null replace it with GetUtcDate(); Thanks very much for your help, EnriqueM Here is some example code that will show you how to do this. You don' ...Show All

• brb.1977 vb code in ssis

  HI, I have a function written in vb and want to know if there is a way to use it in an ssis package Is there a task that I can paste this function in and execute it Like the vbscript task or T-sql task The function opens up 2 tables and updates table1 recordset with data from table2 if the (if statement) is correct and It uses 2 do loops. Any ideas Thanks Roughly: database : SqlConnection recordset: Dataset/DataTable/DataRow I think you must read up on this on your own since this is well documented all over the web, just google vb.net and you should find something. Yup, script task will do the job. Head over to the VB General forum. They can probably help you better than in here. http:// ...Show All

• RBowden Transaction

  i ma new in vb.net and sql server so check this way is true Dim SqlCmd As New SqlCommand OpenConnection() 'this is my function of opening database connection Dim TrnSqlServer As SqlTransaction = SqlConnection.BeginTransaction Try With SqlCmd .Connection = SqlConnection .CommandText = CommandType.Text .Transaction = TrnSqlServer .CommandText = "Insert Into MeterialPurchase " & _ "(Date,Account_ID,No_Of_Items,Gross_Total,Less_Amount,Net_Total)" & _ "Values ('" & TxtDate.Text.ToString & "','" & _ LstAccount.SelectedValue & "','" & 10 & "','" & TxtGTotal.Text.ToString & "','&quo ...Show All