Hi all, I have table1 that needs to be locked until some processing is done in ASP.NET. I know how to lock table but i dont know how address the transaction. To sum up. I know i can do this: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION //some transaction to do C...more >>

Hi, I understand that we can choose the .mdf database file through the connection string in SQL 2005. Do we still need to use sp_detachdb when we prepare mdf files for this feature? Where can I get more information related to specifying mdf file path in connection string? ...more >>

Hi folks I have a stored proc that does a plain insert into a table. I am setting an output parameter to SCOPE_IDENTITY() at the end of the proc. For some reason the proc returns a zero value. The table has a trigger on it, but I have deleted the trigger and I still have the same proble...more >>

Hi Folks, I was wondering if there is any way we can override the built-in functions (like substring,len etc.) either in sql server 2000 or sql server 2005. I know we can wrapit in user-defined function , but I want override T-sql built-in functions if possible. Please reply. Kuma...more >>

We have developed a VB application which connect to Sql Server 2000 db. I need to install this app. in a client laptop with local sql-server database. I want to restrict the client looking at my database objects and schema, how to achieve this ?? Thanks Linda ...more >>

Hi guys, When you view an estimated or actual execution plan using SQL Server Management Stuido you get a number of cost values for each of the operations. One thing I've never been sure of is what unit these costs are measured in. So for example, one of my queries has a CPU Cost of 3.XX...more >>

hello, how would I accomplish the following: I have 4 tables of which are not related (imported from excel), but i need to get data from all 4. :) table 1 (contains all employees): - userid - fullname table 2 (contains all employees): - fullname - dept# - deptname table 3 (...more >>

Hi all, I need to return only 1 row with a switch and doesn't matter which Fr is returned but should be the same oc value that is in the same row with Fr value. Again all that matters is that a row with that switch is there. See Sample data below: Create Table Test1 ( Switch Varchar(2...more >>

I am trying to recreate the same functionality Google has in regards to suggesting words, when you misspell something it comes up with suggestions. We have a list of words in the database to match against. I've looked at SOUNDEX but it is not close enough, DIFFERENCE is even worse. The onl...more >>

What I need to do: - Create stored procedure that accepts Catalog_Num for input which is a varchar field. It also accepts a parameter for the range of records to return. - Return a data set with the fields Catalog_SID (primary key int), Catalog_Num (varchar), and Catalog_Desc (varchar) ...more >>

I need to use Oracle for next project. Any quick books/sites to learn Oracle as a sql server developer? What's the key areas need to focus and how long it will take at least? Thanks,...more >>

Right now I am using a VB script to collect WMI information and log it to a SQL database. VB is slow an inherently serial in nature, so I am looking at converting (reprogramming) what I have in C#. The problem is that I am new to C# though I do have C/C++ experience. Below is the original VB Scr...more >>

Hi, I have a table with account#, date, accounttype, amount. I need to get the report on the accounts ( group by ) with sum of all credit amount ( >0) and sum of all debit amount (<0) on separate colums as below. I can't modifiy the table to have one more column to indicate the credit or d...more >>

Need help with a MSSQL Server query: I need a single query that will combine two tables giving me the MAX() value from the second table for each record in the first table. Example: Table1: cars cars_id year make model Table 2: trips trip_id cars_id driver odometer description sa...more >>

Hi, I have a couple stored procedures with UPDATE commands that do not execute in the SP, as if they are just skipped over. However, if I highlight the particular UPDATE command in the SP in Query Analyzer and execute, it does work. Has anyone seen this happen, and are there any suggest...more >>

is there a built in function in sql server 2005 that will format a number with commas as the thousand-separator? ie., here's what i want to do: input=1000 output=1,000 or input=1000000 output=1,000,000 if there isn't a built-in function, can someone point me to a user-defined-functi...more >>

I have the following table with no designated primary key but the columns SalespersonID, Year, and Quarter make each tuple unique. CREATE TABLE dbo.Sales ( SalesPersonID INT NOT NULL, FirstName CHAR(15) NULL, LastName CHAR(15) ...more >>

hey all, if i'm designing a table and i make a field size 500 and fieldtype nvarchar and i know the true maximum field size will only be about 100 but i made it 500 length just in case, will that affect peformance or capacity? thanks, rodchar...more >>

I am trying to get a list of rows from 1 view and 1 table (that is not included in the View). The View is called "vAAWO" and the table is called "PullSaveData". The current stored procedure I have is: Select [WO Number], [Body], [Part Number], [Complete Date], [WO Quantity], --Select some fi...more >>

Hello: I am trying to incorporate an "if" statement into a select statement but not sure quite how to accomplish this. The if statement in the following sp is generating a syntax error . . . ------------------------------------------------------- CREATE PROCEDURE ProviderGet (@Provider...more >>

I have a stored proc which will insert to the table one pair of value at one row at a time. How can I change this stored proc so that one call of the insert store proc will allow many rows to be inserted? CREATE PROCEDURE dbo.InsertTableName (@ival1 int, @ival2 int) AS BEGIN INSERT...more >>

Hi all, I am trying to use bcp to output data to a compressed (zipped) folder. The bcp command is called from a step in scheduled job in SQL 200 (T-SQL) similar to: SET @chvCommand = 'bcp [working_t] out D:\Eprojects\Edata\Cdata\200701.dat' + ' -c -STPISQL -T' EXECUTE master.dbo.xp_cm...more >>

In SQL 05, I know Alter Index All can reindex all indexes in a table, is there anyway to reindex all indexes in a DATABASE? Thanks ed...more >>

I'm stuck on how to do the following and was wondering if anyone could help out. I'm trying to figure out if this is possible. I'm writing a program in VB.Net. I've got two databases, DBFrom and DBTo. I need to pick up certain values from 2 columns from tables in DBFrom and insert them i...more >>

I have the following query: select case when grouping (ba) = 1 then 'Totals' else rtrim(rtrim(BA) + ' ' + rtrim(cast(month(@@monthend)as char)) + '-' + rtrim(cast(year(@@monthend) as char))) end as BA, sum(AG_ship_val) as AG_ship_val, sum(CH_ship_val) as CH_sh...more >>

I am trying to use bcp to output a query to a flat file but when I call bcp it hangs and never comes back. I create a table in my store procedure into which I store the results that I will later try to output using bcp. CREATE TABLE REM3_3.dbo.ResultTbl { .....stuff } I then populate...more >>

Hi everyone, How can I rename primary key and foreign key constraint names in SQL Server 2000. Thank you so much for all the help! -- Mitra...more >>

I want to start and say that I am not a developer, so I realize this is a basic question. Can you use AND in an IIF expression to evaluate 2 different fields. Say I want field1 > 0 AND field2 = "yes". If I can't use AND, what should my approach be...nested IIFs? ...more >>

How can I cange this SP to run line by line? The SP below pulls data from a table and then updates 2 other tables with the data or if it is NULL leaves the existing data there. I want to make the SP write an error out to an exception table if a line fails on the update but still move on to t...more >>

If I am dividing 5/255000 to get the percentage of 5 to 255,000, how do i force SQL to give the value of .001960 instead of giving me back the value of 1.96.................e-5 ...more >>

These are the tables .... Table1 a b ------- 1 A 3 C Table2 a b ------- 1 A 2 B Table3 a b ------- 2 B 3 C 4 D And this is the query select coalesce(table1.a,table2.a,table3.a) as a,coalesce(table1.b,table2.b,table3.b) as b from tabl...more >>

I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC @...more >>

I just found out today that adding a NULL to any value results in a NULL. I have a SELECT statement that looks like this: SELECT WCAmount + WCHours AS Total FROM worktime WHERE WCid = 'WC11111' Now if either WCAmount or WCHours is NULL, then I get NULL back. Is there a way to still...more >>

Hello, i'm using SMO to retrieve information from various databases. It works well except for one thing. When I call the Column.DataType property to get the SQL type of a column it takes a very long tine. I have a few databases, each with some tables. There are about 75 columns I think. If I ...more >>

Is is possible to automate Word to mail merge using a SQL database? If so where can I find examples of this?...more >>

SQL 2005 I have been given a problem and need help. i have to find out if then current system time is between two given times and return a Bit value Given Time are variable The Given times are Delare @openTime varchar(8) Delare @closeTime varchar(8) Set @openTime = '10:00:00' Set...more >>

I Select --------------- select * from Table1 inner joion Table 2 ON condition Left Outer Table 3 ON condition inner join Table 4 ON condition II Select ---------- select * from Table1 inner joion Table 2 ON condition inner join Table 4 ON condition Left Outer Table 3 ON co...more >>

I am building a system that accesses personal data in a view based on two tables. The view is there because the number of used/necessary fields changes with every import of data (which is, perhaps once or twice every week, approx. 50 weeks per year). The view is thus regenerated with every imp...more >>

I've got two tables and a relation between them. For example I got a table Customer and a table Invoice. Customer contains an unique rowid. Invoice contains a column with the related customerid. Now I need a query about all invoices, sorted by customer name. If I use SELECT * FROM [Invoice] ...more >>

HI, I have the the following issue Could not get the result out of dynamic query execution. CREATE TABLE #QueryTable ( QueryText text ) INSERT INTO #QueryTable (QueryText)Values ('SELECT * from types') DECLARE @sql varchar(1000) select @sql = 'SELECT QueryText FROM #QueryTa...more >>

In SQL Server 2005, I'm creating a database locally and will upload to a production server at some point. The user roles associated to each sproc locally and on production will be the same. Is there an easy way for me to script out the roles that go with each sproc rather than (on the producti...more >>