I have the following Sql Query that returns the type of results that I want: SELECT b.ID, a.Name, b.Col2, b.COl3 FROM Table1 a LEFT OUTER JOIN Table2 b on b.Col4 = a.ID AND b.Col5 = 'test' In essence, I want a number of rows equal to Table1 (a) while having the data from Table2 (b) listed or NULL if the condition, 'test', doesn't exis...
I've had an application that's been running great for several months. Thankfully I use try/catch blocks in my database calls because this morning I get an email alert from my catch block that said, Message: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Server is in single user mode. Only one administrator can ...
I've missed this somehow in my SQL Server learning process. I'd like to be able to document tables/fields/procedures in our SQL Server 2005 environment, and I can't find a way to do this. Is this possible, or would I need a third party app of some kind? This sounds like a no-brainer feature, so I'm probably overlooking it. Thanks! J...
I've been asked to troubleshoot performance problems in a SQL Server 2005 database. The challenge is not a huge amount of data, but the huge number of tables. There are more than 30,000 tables in a single database. The total data size is about 650 GB. I don't have any control over the application that creates all those tables. The appl...
I have a table that stores stock ticks in sql server 2008. It is keyed on the symbol column and the datecolumn which is a datetime2. In a c# app inserts are sent in at a high rate to the table. Currently a row is made using the symbol and DateTime.Now and then inserted. Occationaly the situation can arise where 2 ticks for the same s...
Good day, In SQL Server 2005, when I write a new query, I like to drap and drop table and column names from the object explorer. However, when I drag and drop a table, is there a way to automatically include the database name? (example: when I drag and drop the table Table1 in the query designer, I would like to have Database1.dbo.Ta...
I have a stored procedure which has to get a password from my Users table. I am using a Username as a parameter. What is the best way to get the row where the Username field's contents match the Username parameter, and then the password (as this is the Username/password pair for one user). Cursors are one way to iterate over a rowset b...
I'm working on a solution to create reports in a web application but don't have the budget or personnel to manage a SQL Server Reporting Services install. What alternatives do I have, given these constraints? I'm using ASP.NET 3.5 on SQL Server 2005. ...
I have what looked to me at first glance a very simple problem. I want to be able to obtain a unique key value with a prefix. I have a table which contains 'Prefix' and 'Next_Value' columns. So you'd think you just start a transaction, get the next value from this table, increment the next value in the table and commit, concatenate the ...
Hi I have a table with data in it as such: Cars: id | name ---------- 1 | Buick 2 | Honda 3 | Toyota What I would like to do in a Stored Proecdure is get the result like this: Temp Table: Buick | Honda | Toyota ---------------------- I realize it has no values, but I just want to get this part first. I am guessing this would in...
I'd like to create a stored procedure that takes the name of table as a parameter, and then creates that table, if it doesn't exist, according to a schema stored somewhere. Is that possible--can I store a schema and use is as a template to create tables from? Or must I hard-code the CREATE TABLE and its scheme in the stored procedure it...
Is there an easy way to format SQL Server numeric(18,6) data type to format similar to c#'s {0:#.00####} but in T-SQL? E.g. 12.550000 should produce 12.55. 14.456700 should produce 14.4567. 15.00 should produce 15.00. ...
Background: I have a table with 5 million address entries which I'd like to search for different fields (customer name, contact name, zip, city, phone, ...), up to 8 fields. The data is pretty stable, maximum 50 changes a day, so almost only read access. The user isn't supposed to tell me in advance what he's searching for, and I also w...
I have tables (for simplicity sake) as outlined below: Category -------------------- CategoryId (0 for root nodes) ParentCategoryId ProductCategory -------------------- ProductId CategoryId I'd like to be able to retrieve a distinct list of all categories and their inherited products (all the way back to category 0). Category 0 shou...
Is there an easy way to remove an identity from a table in SQL Server 2005? When I use Management Studio, it generates a script that creates a mirror table without the identity, copies the data, drops the table, then renames the mirror table, etc. This script has 5231 lines in it because this table/column have many FK relations. I'...
We have recently converted an Access Application to store the date on SQL server. The forms still reside in the MS Access, but are linked through file DSN's to SQL server. A question came up of the capabilities to track user activity within SQL server. I have speculated that if we set up individual users in SQL server and use these ind...
I've dug around for a few hours now and cannot find an option to do this. What I would like to do is to add words to the stemmer used by Full Text in SQL Server. I work for an agency that would like to search on variations of names. In other words, if an officer enters the name of "Bill" I would also get a hit on "Will" or "William". ...
I have a table that looks like this: ProductId, Color "1", "red, blue, green" "2", null "3", "purple, green" And I want to expand it to this: ProductId, Color 1, red 1, blue 1, green 2, null 3, purple 3, green Whats the easiest way to accomplish this? Is it possible without a loop in a proc? ...
Hello, Just set up a new server. Trying to transfer some workload from one to the other. Everything looks great, except when I come to run this code: CREATE TABLE #KEYWORD5 (ITEM_MASTER_ID NUMERIC(25) NULL) INSERT INTO #KEYWORD5 exec SQL2K801.soupftidx.dbo.P_REMOTE_SQL_EXEC 'SELECT IM.ITEM_MASTER_ID FROM ITEM_MASTER IM WH...
I have a MS SQL Server 2005. It is very easy to backup individual databases, just right click on database then Tasks->Backup. My question is how to back up SQL server database objects outside the databases? For example, under the Security->Logins, there are list of login users; under the SQL Server Agent->Jobs, there are list of jobs, a...