Do the statistics (which help decide whether an index is to be used) take into account the number of rows per actual column value, or does it just use the average number of rows per value. Suppose I have a table with an bit column called active which has a million of rows, but with 99.99% set to false. If I have an index on this column,...
using replication in sql srv causes the addition of this guid field, it also adds a value to it but when i insert new records to the db, i have to give somthing or the guid field it should be like aaaaa-aaa-something and unique!! this is a problem for me , how am i supposed to do this keeping it unique every time? should sql srv aut...
Hey all, I am wondering how to set variables to the output of the following query string: SELECT count(*) fees_quantity, 1.5 fees_price, 1.5 * count(*) fees_amount, round(SUM((CONVERT(int,Points) * .1)),0)) redep_amount, round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*) total_amount FROM tblHGP HGP, OrderDetail...
Im getting this: "Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally." First the message told me i must connect to master db to execute the restore but i change it and i got the message above. The parametrized sql statement im using is : cmd.CommandText = "RESTORE DATABAS...
So I've created a trigger that compares update before and after and determines if specific fields, specified in the where clause, have changed. If so, I insert a snapshot of the prior information into a history table. The problem is when the field is created with null values (given the business rules, it is legitimately null) and when ...
Is there a way to decrypt a column when querying data from a SQL Server (2005/2008) database that has been encrypted using DES via .NET? If so, how? I have the key, but don't know how to use it within the context of a sql server query. ...
duh, I figured it out, but I can't delete my own post sorry I'm trying to use the XML concatination trick in a query. but, I'm getting this error: SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on ...
I have an sql server view which I've recently added a field to the underlying table and the view itself. I can open the view just fine on the server, but when I try to run it in SQL management Studio on my PC, I get the following error: > Msg 7352, Level 16, State 1, Line 2 > OLE DB provider 'SQLOLEDB' supplied inconsistent met...
I am working on a small piece that will generate an API Key using SQL Sever's NEWID(). The key will be used to access certain parts of our web app and will be passed in through a URL. The key is generated when a new API consumer is created (in a Stored Proc). The key is unique for each consumer. While the initial number of consumers is e...
I have a column of user names. They are as follows: 'first last middleinitial' Notice the large spaces between the name parts, these are always a different number of spaces. Question: How would I separate first, last, and middleinitial into separate columns (even if the spaces are different for every name)? ...
I have a question similar to this but in the context of L2S. I want to create a lookup table to store values that could be one of several possible datatypes. As suggested in the referenced question, I could use sql_variant datatype. However, L2S maps sql_variant to Object, which is suboptimal. I'm guessing it's possible to get at the tab...
this is what my query looks like: select top 5 cast(content_html as xml) as [prodxml], prodxml.value('data(ClassTemplate[1]', 'nvarchar(max) ') as prod2 from content where end_date >= getdate() and folder_id != 71682 and i keep getting: Msg 4121, Level 16, State 1, Line 1 Cannot find either column "prodxml" or the user-defined f...
I have just lost pretty much a day and a half trying to get pull replication going for an off site server. After experiencing the pain that was this experience I am now thinking it shouldn't be this hard so maybe I am doing it wrong. I never did get it to work, I had to go to push replication. Here is the situation. We have a virtual...
I have a stored procedure that is called by data collection system. The procedure has many parameters that for the collected data. I'm using the INFORMATION_SCHEMA to pull the parameter list into a temp table SELECT substring(PARAMETER_NAME , 2 , len(PARAMETER_NAME ) - 1) 'SpParam', PARAMETER_NAME, DATA_TYPE INTO #tempParam...
For a food online-ordering application, I have worked out how many ingridients we need (which we call StockItems), but need help converting that to what we should order based on what sizes they come in (which we call SupplierItems -- i.e. StockItems + PackSizes). If we take apples as an example, we need to order 46 (that bit has already...
Hi, so, I'm facing the challenge of having to log the data being changed for each field in a table. Now I can obviously do that with triggers (which I've never used before, but I can imagine is not that difficult), but I also need to be able to link the log who performed the change which is where the problem lies. The trigger wouldn't be...
SQL Server 2005 supports CLR so it means we can use CLR in backend so how to do that, I have some function in c# which do some complex manipulation with date-time variable now I want to use those functions in SP. First of all IS IT POSSIBLE TO DO THIS. ...
By some setting as using IDENTITY_INSERT we have duplicate entry in identity column, What is the best method of removing duplicate entry. I have a table Details With column DetailID | FKey | Col1 | Col2 | Col3 | Col4 DetailID is "Identity" and FKey is foreign key with another table. Now we have already 240000 record. Some one used "I...
I have an interview with a test, and I need to fresh up ASP.NET/SQL Server/.NET - (which I mostly know well, though there is stuff I haven't done fore some time, and there are basic things I have never done). What I learn best from - and what is difficult to find with programming, is material organized like mathematics text, where you c...
I'm about to throw in the towel on this one. Running SQL Server 2008 enterprise on Windows 7 x64. Can't get past this issue. When I try to Import / Export Data from databases through SQL Server Management Studio I get the following Error. Error: TITLE: SQL Server Import and Export Wizard ------------------------------ The SSIS Data ...