Hi, I have tried using row-level locks to avoid different processes from blocking each other, but when viewed in Enterprise Mgr, thet are still showing up as page level locks and there is some serious blocking going on. My process was even blocking itself??? One of my stored procs has multiple...more >>

I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement I can only get one calltype. I want to do a CASE statement to get all average scores for all ...more >>

I am writing a relatively simple sproc which will select/insert/update a few tables on a linked server, as well as a few local tables. The problem is, that I can update records on the linked server UNTIL I begin to use some exception handling. For example, I could run this line with no probl...more >>

Hi. I have a simple union query shown below. Is it possible to determine which table produced the record? For example, Can I determine if the record came from BooksNew or BooksUsed? Thanks! SELECT * FROM BooksNew a WHERE a.Stock_Number = @StockNumber UNION SELECT * FROM Boo...more >>

I have a stored procedure that is calling a remote stored procedure, I need to get the row count back from the remote stored procedure. My problem is the call to the remote stored procedure has to be dynamically built because of our Dev, Stage and Production environment. If I do a PRINT @SQ...more >>

I have a table FIFO with contains the fifo layers for inventory. ie item ABC could have multiple records, one for each time I purchased ABC from the supplier. I need to summarize this info. Sample: Table FIFO ITEM, QTY, COST ABC, 2, 1.50 ABC, 3, 1.49 DEF, 1, 12.00 GHI, 2, 10.05 ...more >>

Hi All, I'm trying to parse some fields form a cookie column. The problem is that these fields can vary in length and position. The fields are separated by ";". I want to parse fields 'session_id' and 'profile_id' from the column. I can deal with it when the length is fixed: substrin...more >>

I have two queries and one of them is the subquery of the other. These are the details, Query#1 SELECT dbo.sat_bank_info.id_rssd, dbo.sat_bank_info.inst_nm, dbo.sat_bank_info.headOffice_cty_txt, dbo.sat_state_codes.st_abbr_cd, [rb\k1fzl00].View_country.cntry_nm FROM...more >>

We have a table that we import a monthly excel spreadsheet into (table definition included). The only fields that are important for my reports are as follows: Period CPM_Region OP_Model_PL, Rev_Act Cost_Act Rev_Obj Cost_Obj Each month this table is truncated and the new excel file i...more >>

The following query works great if the Excel file is open. select *from openrowset('microsoft.ace.oledb.12.0' ,'excel 12.0; database=C:\DataFeed\DailyData.xlsx' ,'select * from [Daily$ExternalData_1]' ) The Excel file is created from a web service that generates some useless heade...more >>

I need to compare a float column in one table vs a float column in another table where the data is the same, but the precision using in one table is 16 and the other is 8. I only want to return the rows where the numbers are significantly different. I can't do table1.column1 = table2.column1 b...more >>

Hello All: I am trying to launch a windows application from a stored procedure using the sp_OAMethod technique. The method called via sp_OAMethod calls CreateProcess to launch the external application. When the stored procedure is run, the external Windows Application does not launch. Is...more >>

Hi, I have the following data, and I want to merge these two tables by column text#6. table#1 column names ================= key#1 key#2 key#3 column#4 column#5 text#6 table#2 column names ================ key#1 key#2 key#3 column#4 column#5 text#6 I have tried to use UNIO...more >>

I have a stored procedure that accepts input parameters (i.e.exec special_proc 'l','m','n') and outputs data in a table format. I want to use the output data in a join. For example, select a,b,c from tablex x inner join (exec special_proc 'l','m','n') w on x.a = w.a Can I use the st...more >>

Why can't I use a CTE to replace having to create a temp table? Below code builds a CTE called MyCTE - then I want to further manipulate = the result set and dump it's results into MyCTE2. For simplicity I'm = just calling one column on the 2nd CTE: With MyCTE (Det_ID, Dis_Code, Coev_Code...more >>

I am new to writing sql statements and I was wondering if this was possible to do with sql. I have a table that I simplified for this example. It contains ID - int ProductCode - int BalanceDue - int If i write the query SELECT ID, PRODUCTCODE, BALANCEDUE FROM dbo.table1 I ...more >>

I'm simply trying to do this on my local install of SQL 2000: BULK INSERT July2007 FROM 'c:\TEMP\July2007.XLS' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2) The query runs successfully but the July2007 table is empty. It never appears to load the data. What could be wr...more >>

Hi, I'm looking for some help on a query that I wrote and is returning me an error but I don't understand why. Here is my query: declare @Birth varchar(20) set @Birth = '0194/07/04' select @Birth as OriginalBirthDate, ISDATE(@Birth) IsDateValue SELECT CASE when @Birth is not null ...more >>

Hi all, Please could one of the clever regulars explain the following seemingly counter-inuitive behaviour? If I create a CLR UDT in VS05 (call it Type1) and add the following two shared functions: Public Shared Function MakeSqlString(s as SqlString, lcid as SqlInt32) As SqlString Retur...more >>

Hi, I know that running an INSERT INTO that creates a temp table is a bad idea for large queries as it is both defining and populating the temp table. This causes tempdb to get tied up and stops most other work happening. We have had something similar with a large query that covers 2 datab...more >>

With this query I am trying to update the onhand record in a summary table with the data coming in from a transaction table. However, the way I have written this is running extremely slowly. I think I am missing something. Does anyone have any recommendations on how I can merge the data from t...more >>

Greetings, I am trying to generalize when do I need to use Instead Of triggers and request suggestions on this. I have been using triggers for a few years now, and it appears to me that if I need to manipulate the data that is being inserted into a table (Insert/Update/Delete data on t...more >>

We are moving from a SQL Server 2000 environment to SQL Server 2005 environment. Does anyone know of any nice web sites out there that will give our people a quick tutorial of Microsoft SQL Server Management Studio??? And how it differs for what we're used to in Enterprise Manager and SQL ...more >>

Hi everyone! I must create something that would export all tables from a database to .csv files with columns headers. It would need to be run often, it's not only a one shot deal. I tried with the BCP command which works fine because I can loop on all tables. DECLARE @Out nvarchar(30), ...more >>

All, Does anyone know why a WHERE predicate expressed in the negative would be ignored? This works... SELECT * FROM tableA A INNER JOIN tableB ON A.id = B.id ... WHERE tableA.column2 = 'x' AND tableB.column2 = 'x' AND tableZ.column2 = 'x' This does not (last WHERE expression igno...more >>

hey all, i noticed that when logged into one of my sql servers i can go into object explorer in management studio and connect to another sql server that's also on a win2003 server using a trusted connection. does that just happen automatically with win2003 servers that are in a domain? i n...more >>

Data Cleansing: In the example (SQL Server DDL below) there are two tables - ExampleCustomer, and ExampleCar. ExampleCar is a lookup table. The ExampleCustomer table has a foreign key to the ExampleCar table. There should be two rows in there, "Ford Focus", and "Ford Galaxy". This table ...more >>

I need to get the month returned not as a number but as text.... I checked bol but came up with nothing... What am I overlooking? Bob Sweeney ...more >>

Hello there, How would you convert a field of float data type to a string? I tried: SELECT CAST(Amount AS VARCHAR(32)) FROM table_name But it only works for samller figures such as 10000.5; 100000.5 shows 100001; 100000.5 shows 1e+006 How do you display the original figures? Than...more >>

Hello, Is there any built in feature in SQLSERVER 2005 for creating .csv file based on a query? Anything for zipping that file on the fly? My other alternative will be to write a managed stored procedure, but i just néed to know if there are any built in feature before i go for the B pl...more >>

Hi all, I have one table with an column 'cep' this column is INT and this table have 1000 records, now I need chanche INT to char(10) (without lost data, need be converted), have any way to do this: convert type of column and convert data in records? CREATE TABLE clientes_dados_residen...more >>

Hi, In Profiler if you trace the 'Error and Warnings' events and you find an event of type Exception with the bellow error which is Could not find stored procedure how do I get the proc name? Error: 2812, Severity: 16, State: 62 Thanks, Yaniv ...more >>

I want to install XP Pro on my Gateway laptop which currently has Vista Premium. When I boot with the XP cd I get to the stage where I opt to install XP. An error message appears 'No Hard Drive found'. The laptop has a 120Gb hard drive. Any ideas? Nirmal ...more >>

Hi, I need to sen emails (Outlook)out by using sp_send_cdosysmail. this is the sp i am going to use. I need @body = 'this is a test'. I need the word 'test' to be bolded and colored blue. How do I passing the @body as "this is a test' ...more >>

Hi friends, What is Collation in SQL Server and how does it affect Search and Sort in database? ...more >>