I have a stored procedure that looks like: CREATE PROCEDURE dbo.usp_TestFilter @AdditionalFilter BIT = 1 AS SELECT * FROM dbo.SomeTable T WHERE T.Column1 IS NOT NULL AND CASE WHEN @AdditionalFilter = 1 THEN T.Column2 IS NOT NULL Needless to say, this doesn't work. How can I activate the additional where clause th...
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...
How can I write an insert statement which includes the & character? For example, if I wanted to insert "J&J Construction" into a column in the database. I'm not sure if it makes a difference, but I'm using Oracle 9i. ...
And how do you keep them in synch between test and production environments? When it comes to indexes on database tables, my philosophy is that they are an integral part of writing any code that queries the database. You can't introduce new queries or change a query without analyzing the impact to the indexes. So I do my best to keep m...
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 ...
I have the following code that I wrote but it the SQLBindCol does not seem to work correctly (of course I could have screwed up the whole program too!.) THe connection works, it creates the table in the DB, addes the record fine and they all look good in SQL Enterprise Manager. So what I need help with is after the comment "Part 3 & 4:...
In MS Transact SQL, let's say I have a table (Orders) like this: Order Date Order Total Customer # 09/30/2008 8.00 1 09/15/2008 6.00 1 09/01/2008 9.50 1 09/01/2008 1.45 2 09/16/2008 4.50 2 09/17/2008 8.75 3 09/18/2008 ...
Is there an elegant way to have performant, natural sorting in a MySQL database? For example if I have this data set: Final Fantasy Final Fantasy 4 Final Fantasy 10 Final Fantasy 12 Final Fantasy 12: Chains of Promathia Final Fantasy Adventure Final Fantasy Origins Final Fantasy Tactics Any other elegant solution than to split up th...
What do the brackets do in a sql statement? For example, in the statement: insert into table1 ([columnname1], columnname2) values (val1, val2) Also, what does it do if the table name is in brackets? ...
Is SQL case sensitive. I've used MySQL and SQL Server which both seem to be case in-sensitive. Is this always the case? Does the standard define case-sensitivity? ...
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? ...
I frequently run into problems of this form and haven't found a good solution yet: Assume we have two database tables representing an e-commerce system. userData (userId, name, ...) orderData (orderId, userId, orderType, createDate, ...) For all users in the system, select their user information, their most recent order information w...
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...
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...
We are trying to update our classic asp search engine to protect it from SQL injection. We have a VB 6 function which builds a query dynamically by concatenating a query together based on the various search parameters. We have converted this to a stored procedure using dynamic sql for all parameters except for the keywords. The proble...
I'm writing a scheduler or sorts. It's basically a table with a list of exes (like "C:\a.exe") and a console app that looks at the records in the table every minute or so and runs the tasks that haven't been run yet. I run the tasks like this: System.Diagnostics.Process p = new System.Diagnostics.Process(); p.StartInfo.UseShellExecute...
When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls. Currently I do that by running the query twice, once wrapped in a count() to determine the total results, and a second time with a limit applied to get back just the results I need for the current page. This se...
I need to pad numbers with leading zeros (total 8 digits) for display. I'm using oracle. select to_char(1011,'00000000') OPE_NO from dual; select length(to_char(1011,'00000000')) OPE_NO from dual; Instead of '00001011' I get ' 00001011'. Why do I get an extra leading blank space? What is the correct number formatting string to accompl...