I have a large application that is multi-user and quite transactional, driven by a VB front end. It uses mostly stored procedures for retrieving data. I was running into locking contention on tables with 300,000 to 2 million records that are read and updated by all users. We allow the use...more >>

In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that allows changing the @field and @result variables because different fields will need different data types for a @result variable. I'm failing bad as ...more >>

Hi, I am tryng to insert a row into an SQL 2005 database, but it is not working. I am not getting an error messsage, rather it is just not adding the row. I have added the Data Source and made the database connection. What am I doing wrong? My code is below. TIA Roy using System; ...more >>

How is Conversation Timer () Timeout = 60; handled internally? Does each call start a new system timer? Or is one time used that always waits for the "least" time for all waiting conversations? TIA -- William Stacey [MVP] ...more >>

Hi, I am desperate for some help on an issue I have with using an udf in a select statement. The udf can return mulitple comma delimited values e.g. 14123,12312 I am using the following (simplified) query: select distinct firstname, lastname, number from emp, numb where emp_NUMBER=numb....more >>

I've installed SQL Server 2005 RTM and using Microsoft's sample on connecting using SMO, I tried connecting to a SQL Server 7.0 server. I received this error: "This SQL Server version (7.0) is not supported". Huh?! BOL says SMO supports 7.0. ...more >>

there are numerous complains about it on the net. mostly about ....incompatible beta components... ....can't find .Net Framework 2.0 (hello?! but which version!???)... v2.0.40607? v2.0.40903? v2.0.50215? v2.0.50727? ....could not be located in the dynamic link library..... why Microsoft ...more >>

Hi all, I have the following sql: SELECT a.fieldA, b.fieldB, c.filedC, dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField FROM tableA a INNER JOIN tableB b ON ... INNER JOIN tableC c ON ... WHERE dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField > 0 which...more >>

Hi all, I would like know if I can use @@ERROR to error in mult thread environment? For sample: If this SP is executed by 2 threads in same time, I wiil have correct value in @@ERROR Thanks IF EXISTS (SELECT name FROM sysobjects WHERE name = N'terminal_ativo_sp' AN...more >>

i can add trigger to system table by changing sysobject records. but it doesn't fire. this is really needed, why not? ...more >>

I have the stored procedure below that I am using from asp.net. To test things out I changed a table column name so it would fail. I got the error below. Furthermore, it hung my database so I had to stop SQL Server and restart in order to login to the website. Of course I expected it to fail ...more >>

Hi, Given a set of rows with RowID from 1-499 I want to return the last ten rows in ascending order, 490, 491, ... , 499 SELECT * FROM ( SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC ) SubQuery Order by RowID ASC The above query does not work, it returns the ten ro...more >>

Hi all, I have a question on how to obtain the results of a exec out of a UDF/Stored Proc? Currently I haev a UDF that builds a select statement and executes it using the [I wanted to return the Statement itself, but was too big for varchar] return @sqlStatement I then tried 'select ...more >>

Hi We have a problem whereby a particular application is writing certain ascii characters to our table which is causing an issue in another application. Obviously the solution is to fix the application, but it's taking some time. In the meantime I'm trying to develop a trigger to fix the dat...more >>

What follows is a trigger I wrote to mirror the MbfUser to the MbfRole table in my Business Portal database. I send a report to one of these roles and for some reason it doesn't show until after I do an IISReset on my server. /*Because of a constraint on MbfRoleUser, MbfRoleUser must be clea...more >>

Hi all, I am new to the forum, I am having a weird issue here. I have a stored procedure that 1. Insert data, 2. update data 3. delete data all 3 operations are within the same Begin Transaction/Commit and on the same table. The issue is that I have an other process that is reading the...more >>

I am trying to use MSMQ from within a stored procedure that I've written in C#. When the data in a table changes, I want to post MSMQ messages to remote clients telling them that the data has change as of that time, so that the next time they display the data they will know that they need to r...more >>

Hi, using the statement SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC I get the last 10 rows in the Import table as expected, but how to I reverse these last 10 rows? Morten...more >>

I'm performaing a restore of a database and am encountering some odd behavior. The resore has been running since 10:00 this morning...! It should not have taken more than an hour to complete. The database continually shows a status of LOADING. I've even stopped and restarted SQL Server and...more >>

I am upgrading an application that another developer wrote. Basically, it is an application used to display appointments / request appointments. The new request is to display available appointments. That is where I need help in figuring out how to return available time slots. Here is wha...more >>

Hi all, In Enterprise Manager, I could right click on a database and "generate SQL Scripts" for hundreds of objects at once. In 2005, though, I've only been able to generate scripts one at a time in Object Explorer, and I haven't found any way to change the scripting option. Surely I'm missin...more >>

Hi, Given the following dataset, how can I determine the maximum number of consecutive Bs for a given A? A B - - 2 1 2 2 2 7 3 2 3 3 3 4 3 6 3 8 3 9 3 10 3 13 3 14 3 15 3 16 4 1 4 3 4 5 4 6 4 7 4 8 4 10 5.... So the output should resemble: A MAX Count for B - -...more >>

I have to generate random number with a given seed value. I am able to do that, except that the values genered in SQL Server is not matching the same random values generation in VB with same seed value. In VB I am doing the following Randomize(40) calling Rnd function multiple times. In...more >>

Hi All, I have a need to group a column with he same name. I have a column called "AccountType" which has data such as : A1 A2 A3 A4 I am using an aggrate for this column: SELECT SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type] FROM Tbl1 GROUP BY AccountType I w...more >>

Hi SQL Guru's, I create a global temp table in one of my procedures: select * into ##temp_update from ( select distinct * from ...... after this I issue a commit transaction, and when I try to select from the table I created (from my .Net code or Query Analyzer) it is not there! sel...more >>

I just converted a SQL Server 2000 database to 2005. I have a job whose only step does the following: Declare @Command char(240) Set @Command = '\SDBS\Scheduler\Scheduler.Exe ' + Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select Replace([name],' ','~') From msdb....more >>

Hello, I need to automate importation of a excel file into a table. Here's my scenario: I'm writing an ASP.NET application where users can pull reports on imported data. The imported data is pulled from an old UNIX based system, then converted to Excel. I want the user to be able to use the...more >>

Hi, I have two tables Products ---------- ProductId ProductTitle Bids ----- BidId ProductId BidAmount BidderName I want to grab all the products with the highest bidAmount and the BidderName. There could be multiple bidders for each product. ...more >>

Hi, Given the following table and test data: CREATE TABLE test ( recordId numeric(18, 0) NOT NULL, spId int NOT NULL, startTime datetime NULL, endTime datetime NULL ) INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33') INSERT INTO test VALUES (2,2,'2005-01-01 12:26'...more >>

I have a lot of templates that I copy from table to another, but ran into one table where I am having trouble. I normally do a copy like so: INSERT table2 (field1, field2) SELECT field1, field2 FROM table1 This works fine most of the time where I take all the data from table 1 and put ...more >>

I am trying add an item to a table that does not allow duplicates but if the item already exists I want to return its id. I need an SQL equivalent to a critical section but I am not sure how its done. It would like something like the below code. --Begin Critical Section SELECT @id ...more >>

Hi, I got a stored procedure like this CREATE PROCEDURE dbo.readImport ( @Start INTEGER, @Number INTEGER ) AS SELECT TOP @Number * FROM Import WHERE RowID >= @Start ORDER BY RowID GO However, it doesn't seem to like having an unknown @Number. Any ideas? Morten...more >>

I currently have a VB.NET application that gets record information from a SQL database. When it finds this information, it is placed into a dataset. I know I will only get one record because I am querying based on unique record ID, but I still have to get the first item in the dataset (because...more >>

Hi, I use an Identity column to create row numbers displayed in a DataGrid. The datagrid typically displays row 1-10, 11-20 etc. The datagrid is filled using SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End. If rows 1-10 is removed from the table, there are no rows displayed since ...more >>

Can someone help me with a query that removes the '-' from as string QAA - Thanks ...more >>

I am trying to use a case statement to pull bad phone numbers out of my company table into a readable format. I am using SQL Server 2000 SP4. The phone numbers, in my inherited database, are in any number of formats, not complete, etc, and I need to pull a report showing only the good phone ...more >>

I have two tables tblInvoice and tblInvoice Line. Both tables have a column InvoiceID that is Primary / Foreign Key . tblInvoice has a column TransType char(2) 'SI' or 'SC' I have now added this column to tblInvoice and want to update the values with the ones from tblInvoiceLine where the...more >>

Hello, I'm currently studying SQL Server in college and have been asked to find out how SQL Server uses JSP, JDBC, SQLJ & XML in the context of database applications. Try as I might, I cannot seem to get any info on this. I would be greatly obliged if anyone here could shed a little light o...more >>

can anyone pls tell me what this thing do insert bulk dbo.test -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787...more >>

could you give me a dig out with this concatenation, it's driving me nuts declare @ev_bat_desc varchar(50), @text varchar(100) select @ev_bat_desc = 'CQA_cmdmgr_Marc.bat' -- select 'c:\psexec \\10.2.27.230 -I ' + @ev_bat_desc + ' ;' -- emulate Exec master..xp_cmdshell 'c:\psexec \\10.2.27.2...more >>

Here's my scenario. I have two sql servers, one located in another state, but connected via a continuous vpn so it really looks like it's always on my network. I only make changes to the sql server on the remote server, but I want the local server to be as close as possible to having exactly...more >>

I have another question. I want to create audit table with columns (userID, date, tableAffected, ColumnAffected). This table should have data from tables that I want to trace. It easy to collect data about time and users, but I don't know how to collect data about which table and which c...more >>

Hello everyone, I need to figure out how to de-normalize several one-to-many relationships into a single row of data. For reference, here is a small sample of the Normalized Tables: Tabe Artist ----------------------------------- AristID int not null identity, ArtistFirstName varchar(50...more >>

Hi I need some help with my cursor in a stored procedure in SQL Query Analyzer, please. It takes so long to finish, that I break out of it every time and I can not see if it actually works. What it needs to do is the following: I have a file with say 150 000 items on it and for each item, t...more >>

Is there a way to find out what is the current database? In Query analizer, you execute "use <db-name>", and you can se the current database in the combo box. Is the an SQL command to find currrent database ? Boaz Ben-Porat Milestone Systems ...more >>

Hi, ------- Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for SQL2K5? ------- BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows. But I tried on my databases (even big ones) and its size is the same as ...more >>

Hi all I am using sql server 2005 and I try this: set quoted_identifier on go select "roger" and I receive a error: Msg 207, Level 16, State 1, Line 1 Invalid column name 'roger'. Why ??? ...more >>