Here is the SQL: CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int) INSERT INTO dbo.TmpFeesToRules1(Name, LookupId) SELECT DISTINCT Name, 0 FROM Lending.Fee UNION SELECT DISTINCT Name, 0 FROM Lending.ApplicationFee INSERT INTO dbo.tblLookup (LookupType, LookupCode, LookupDesc, EditFlag, DeletedFlag, DefaultFlag) SELECT 'FEE_...
Let's say I have the following simple query SELECT TOP 1 name FROM months which returns name = "march". Is it possible to convert this result? Instead of "march" I want name = "3". Is SQL capable of doing such things? I'm using a MSSQL database. [update] Corrected the query. While writing this simple example I mixed it up with MySQL ...
Can anyone suggest options we might have in capturing all SQL statements being sent to our SQL Server, outside of running Profiler? I'm aware of a couple ways of doing it, but want to make sure I'm not overlooking something, such as an existing DM view etc. Thanks very much. ...
Hi, I have a database in sql server 2008. I want to convert it in it's xml format. What I have to do? What is the procedure ? Please guide me. I don't want to write the code for this in any language. Is there any facility given in sql server 2008 ? ...
When I try EXEC sp_refreshsqlmodule [myschema.myprocedure] it throws Could not find object 'myschema.myprocedure' or you do not have permission. ...
These two queries seem to return the same results. Is that coincidental or are they really the same? 1. SELECT t.ItemNumber, (SELECT TOP 1 ItemDescription FROM Transactions WHERE ItemNumber = t.ItemNumber ORDER BY DateCreated DESC) AS ItemDescription FROM Transactions t GROUP BY t.ItemNumber 2. SELECT DISTINCT(t.ItemNumb...
We are developing a service layer for a new system that will handle all interactions with the MSSQL (2005) database. We are a bit perplexed as to how to capture all of the 'who done it' information that is required by our users in some of our legacy audit tables. While we could pass in the users name that was modifying data and log the...
How can I convert a day [1-31] and a month [1-12] and a year (all int), to a date serial IN SQL (without converting to varchar)? ...
Hello all, This is probably a simple one... Right now I have a working web service (programmed in c#) and it offers up two webmethods that either read or write data to a MS SQL database. What I want to know is this... Do I have to worry about errors when multiple users try to connect to, reading from, and writing to the database? Furth...
Hello all, I'd like to turn trigger recursion on/off in my SQL CLR trigger. According to http://www.devx.com/tips/Tip/30031, I have to call EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'/'false' Is there a way to get to know what the current DB name is? When creating trigger, I ask users to choose one, but I don't want...
When I tried upgrading from SQL Server 2008 R2 Express to SQL Server 2008 R2 Enterprise, it passed all tests and then it failed at step "Select features", saying the following error: There are no features selected for upgrade. And a window error message: There are validation errors on this page. Click ok to close this dialog bo...
I have a merge statement that needs to compare on many columns. The source table has 26,000 rows. The destination table has several million rows. The desintation table only has a typical Primary Key index on an int-type column. I did some selects with group by to count the number of unique values in the source. The test part of the Mer...
There are two ways to create a constraint, Method A: ALTER TABLE dbo.<tablename> ADD CONSTRAINT <namingconventionconstraint> UNIQUE NONCLUSTERED ( <columnname> Method B: CREATE UNIQUE NONCLUSTERED INDEX <namingconventionconstraint> ON dbo.<tablename> ( <columnname> ) ON [PRIMARY] However, it appears that these constraints need to ...
I execute the following code via CLR, is there a reason why the message is not printed to the SQL Server, does it need to wait until the Stored Procedure returns all the rows (there is about 7 Billion rows to return) SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedu...
I have two tables I am using to fill a gridview. The tables have a common field named RangeActivityID. My problem is that the database is very old and some of the older entries do not match up IDs between tables, so I am unable to add an association between them in the database. I do not care about the old data which doesn't match up, ...
I have a desktop app that I'm making and I want users to have to login before the program can be used to its fullest extent. I have created a database on www.winhost.com (where my site's hosted), but now I don't know what to do. I've been on google and msdn and I would like to know how to programmatically add new rows/colums to this new ...
There appears to be several stored procedures that were renamed with sp_rename, causing the syscomments to be left with the old proc name instead of the new proc name. Thus, my db transfers are failing to transfer correctly. Is there a way to find which procs (or any objects) have their names out of synch? Or better, a simple way to f...
Hi, I'm trying to generate my database tables from my Entity Framework model, but I'm getting this error when I execute the generation query: Database 'Database' does not exist. Make sure that the name is entered correctly. I'm able to connect to the local server just fine. My connection string, which was generated by VS when I sele...
Using SQL Server and T-SQL, how can I update ALL tables tables with a specific name but different schemas under the same database? :Update: The updates include adding/deleting/modifying columns as well as changing specific values of rows. I want to be able to deploy changes to a table across all schemas. Using the SQL Server Managemen...
Hey All, I am using WinForms & C# to access a SQL Server 2008 on my site which's hosted by winhost.com. I am using the following code to connect to the database, which I figured out for myself: try { SqlConnection scon = new SqlConnection( "My ConnectionString Info is in here."); scon.Open(); MessageBox.Show(scon.D...