Just found this out, so i am answering my own question :) Use a comma where you would normally use a colon. This can be a problem for named instances, as you seem to need to specify the port even if it is the default port 1433. Example: Provider=SQLOLEDB;Data Source=192.168.200.123,1433; Initial Catalog=Northwind; User Id=WebUser; Pas...
What is the best way to replace all '<' with < in a given database column? Basically perform s/<[^;]/</gi Notes: must work in MS SQL Server 2000 Must be repeatable (and not end up with <;;;;;;;;;) ...
I'm trying to display a boolean field in Report Designer in Visual Studio 2008. When I tried to run it, an error occurred: "An error has occurred during report processing. String was not recognized as a valid Boolean." I tried to convert it using CBool() but it didn't work. ...
When I backup or restore a database using MS SQL Server Management Studio, I get a visual indication of how far the process has progressed, and thus how much longer I still need to wait for it to finish. If I kick of the backup or restore with a script, is there a way to monitor the progress, or do I just sit back and wait for it to fini...
I need to add the ability for users of my software to select records by character ranges. How can I write a query that returns all widgets from a table whose name falls in the range Ba-Bi for example? Currently I'm using greater than and less than operators, so the above example would become: select * from widget where name >= 'ba' and...
Is there any facility of transposing rows to columns in SQL Server (it is possible in MS-Access)? I was befuddled because this facility is available in MS-Access but not in SQL Server. Is it by design that this feature has not been included in SQL Server? ...
I need to know the default port settings for the following services SQL Server SQL Browser SQL Reporting services SQL Analysis services I neeed to know the port settings for these services for different versions of SQL Server (2000,2005,2008) Also let me know whether the default port setting will change based on sql server versions...
When I try to install the Reporting Services on a second node of a failover cluster, I get the following error message: Existing clustered or cluster-prepared instance failed. The instance selected for installation is already installed and clustered on node 2. But, we never installed it before. Does anyone have any ideas? This is ...
I have an MS SQL database and have a varchar field that I would like to do queries like where name like '%searchTerm%'. But right now it is too slow, even with sql enterprise's full text indexing. Can someone explain how Lucene .Net might help my situation? How does the indexer work? How do queries work? What is done for me, and ...
The solution we developed uses a database (sqlserver 2005) for persistence purposes, and thus, all updated data is saved to the database, instead of sent to the program. I have a front-end (desktop) that currently keeps polling the database for updates that may happen anytime on some critical data, and I am not really a fan of database ...
I love the way I can profile a Java/.Net app to find performance bottlenecks or memory problems. For example, it's very easy to find a performance bottleneck looking at the call tree with execution times and invocation counts per method. In SQL Server, I have stored procedures that call other stored procedures that depend on views, which...
I need to copy some records from our SQLServer 2005 test server to our live server. It's a flat lookup table, so no foreign keys or other referential integrity to worry about. I could key-in the records again on the live server, but this is tiresome. I could export the test server records and table data in its entirety into an SQL scr...
The book that I purchased to help with my SSIS understanding seems to have glossed over this, and I wanted to know exactly what is the method to turn on FastParse in SSIS? ...
The title pretty much says it all. I want to create a SqlConnection and then check that connection without opening a database, cause at that point I don't know yet where will I connect to. Is it possible to do that? The SqlConnection class has a 'Open' member which tries to open the database you'd set in the Database property, and if yo...
I've been beating myself over the head with this app migration for a few days now. What I have is an old MSSQL Server 2000-backed application that is being upgraded to a Windows 2003 Server running SMSQL Server 2005. I know little about SQL Server but obviously not enough. I tried backing up the database on the old server by going to Da...
In SQL SERVER DB, I need to alter a column "baseColumn" and a computed column "upperBaseColumn". The upperBaseColumn has index on it. This is how the table looks create table testTable (baseColumn varchar(10), upperBaseColumn AS (upper(baseColumn)) create index idxUpperBaseColumn ON testTable (upperBaseColumn) Now I need to increase ...
So I have created a SSIS package and it works great on my dev machine. But, when I try to run it on the production server, it errors our on me. Here is there error: Error: The AcquireConection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. I have figured out the cause, but am ...
I'm a newbie when it comes to SQL. When creating a stored procedure with parameters as such: @executed bit, @failure bit, @success bit, @testID int, @time float = 0, @name varchar(200) = '', @description varchar(200) = '', @executionDateTime nvarchar(max) = '', @message nvarchar(max) = '' This is the correct f...
I am getting the following error: Open OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. HRESULT error code:0×80020009 Exception occurred. I have tried following the directions here with no luck: http://wiki.rubyonrails.org/rails/pag...
I have a test environment for a database that I want to reload with new data at the start of a testing cycle. I am not interested in rebuilding the entire database- just simply "re-setting" the data. What is the best way to remove all the data from all the tables using TSQL? Are there system stored procedures, views, etc. that can be u...