would like to update a field of the type ntext, but all register them that are in a select. I tried the code below, but he only updates the last record: CREATE PROCEDURE SPU_EtiquetasRevBaixar @pDataEnvio AS NVARCHAR(10), @pEtiqueta AS NVARCHAR(255), @pStatus AS INT AS DECLARE @Msg AS ...more >>

There are three tables and all tables contains 100000 records and contains exactly same data., u can say each table is a carbon copy of other one. i executed 4 queries as follows 1) SELECT * FROM tbl1 2) SELECT * FROM tbl2 3) SELECT * FROM tbl3 4) SELECT * FROM tbl4 what i want to kn...more >>

I have a work order table and a contacts table. I would like to return one record for each work order but there may be 0 or more contacts for each work order. So select wo.wo_id, Contact_FirstName + ' ' + Contact_LastName + ', ' + Contact_Title as ContactName from tblwo wo inner join tblWO...more >>

Hi, I have just set up an installation of MSDE 2000 release A (which is equivalent to SQL SERVER 2000). I have an application (mailmarshal) that wants to connect to the database as Local system. I can't get the application to connect as local system, but I can get it to connect as sa. I...more >>

I am following up on some earlier posts I had: I have a procedure that calls two other stored procedures. I would like the values from the two stored procedures to be stored into a temp table and then as teh final step in my stored procedure, to join these two temp tables together and do a ...more >>

Is having dbo as the owner for all application objects a good practice ? We have a web/com/db architecture. All the objects related to the application are owned by dbo. This requires the COM objects to have dbo access to the database. Is this a good practice ? Should all the objects be ...more >>

Hi all, Since nonclustered indexes have no effect on the order of the data rows, If I don't have index in the table and I do a select statement, it will do a table scan, go from 1st row to last row. But if I have nonclustered index in a table, what will sql server do? I assume it will do the sa...more >>

Hello! Suppose I have a stored procedure create PROCEDURE ABC AS RAISERROR ('!!!Err_9301', 16, 1) RAISERROR ('!!!Err_9008', 16, 1) I call this procedure through a TADOStoredProcedure component in a Delphi TRemoteDataModule container. When ABC is...more >>

All, I am responsible in designing a model where users will have check-in and check out method for Plans created in database. A brief description of the scenario is below. Users create a plan (Plan is saved in database table called Plan along with several other tables related to Plan having...more >>

Weve got about a dozen or so procs that are run continously. Some as much as every millisecond. I want to allow dirty reads for these guys. If I "set transaction isolation level read uncommitted" at the beginning of the proc, will that count for every Select..From statement in the proc? Or do I n...more >>

I have a table with 3 columns item_number, vendor, price The table has data like so 111 vendor1 23.00 111 vendor2 25.00 111 vendor3 23.50 112 vendor1 60.00 112 vendor12 100.00 I need to return each item number only once. The row that I need to return ...more >>

Hi, I have to retrieve two values from a Sybase database, so I run an OPENQUERY query with a linked server. The query has a parameter, and since OPENQUERY does not accept variables for its arguments, I have to run it using execute or sp_executesql. So far the only way I found I can retrieve...more >>

Hi! I have the following table: CREATE TABLE source ( id int not null, value varchar, source varchar not null ) with the following data: INSERT INTO source (id,value,source) VALUES (1,'testOne','One') INSERT INTO source (id,value,source) VALUES (2,'testOne','One') INSER...more >>

I'm wondering what kind of image type SQL database diagrams are? The thing is that I have a SQL database documentation application that reads metadata from a database & documents it, i.e tables, index, views, stored procedures and so on. Now I would like to add the feature of retreiving dia...more >>

Hi! I have the following table: CREATE TABLE source ( id int not null, value varchar, source varchar not null ) with the following data: INSERT INTO source (id,value,source) VALUES (1,'testOne','One') INSERT INTO source (id,value,source) VALUES (1,'testOne','One') INSER...more >>

my stored procedure has an input and output parameter.Iam using visual foxpro 8.0 as my front end.How should my execute command for the stored procedure look like in the front-end....more >>

Hi, I am trying to create a trigger on a table to update a field on a insert or update. This field will be updated by SP that returns a value from a table based on the parameter passed to it from the trigger. The SP is a basic SELECT statement with the WHERE clause set to the parameter passe...more >>

Hi to all. My DB size is over 5GB. This size doesn't make any sense. How can i see the amount of space, each object takes? Thanks, Sharon. ...more >>

I am creating a DTS package to import from a text file into a table on my SQL 2000 database. I used the Import Data Wizard, saving as a DTS package. The text file has four fields in it, all of which can have a mixture of numbers and letters. These four fields would import over just fine, exce...more >>

Greetings all. I am receiving a General Network Error (and getting disconnected) running EXEC xp_cmdshell '<MS DOS cmd>' remotely against an un-named instance of SQL Server. I can go to the server locally and run it and it works. It also works when omitting the EXEC statement (which I c...more >>

When appending rows to a table I require the rows to have assigned a unique random identity value... Any ideas ? Thanks Henry ...more >>

I have to copy the contents from one database to another database each night. I have created VB app using DMO and DTS. I create a list of tables using enum_dependancies because of foreign key referances. I then create a DTS task to copy the data but first I must delete all the rows from the tabl...more >>

Hi folks -- Given the following code: ALTER TABLE #TEMP WITH NOCHECK ADD CONSTRAINT PK_TEMP PRIMARY KEY CLUSTERED (StudentID) ON [PRIMARY] If two users are simultaneously executing the stored procedure containing this code, one of them receives the following error message: There i...more >>

Hi I am tryin to figure out if it is possible to execute a DTS package( either file or stored within the SQL server itself ) using OLEDB. I see the DTSPackageDSO provider but am unsure how to use it. The only info I found on its usage is how you use it in SQL Servers Query Analyzer tool. Is th...more >>

Does anyone know how to execute/embed DOS Batch command using a SQL Store proc? I have SQL command that runs via the scheduler and creates 2 .txt files on the network. The DOS Command merges these 2 files. The dos command I use is very simple: i.e. At the command prompt: c:\> COP...more >>

There is an error during back up database, It ask me to see the log file, Where is the log file ? Thanks -- .. ...more >>

i have a table A with a field B iam updating a row say where B = 1 when iam updating this row i do not want another user updating this row at the same time.i want to place a lock only for updating and only on this row. the other users must be able to update other rows which are not equal to 1...more >>

I am copying data from a pervasive database to MSSQL and updating tables in the same process. What I am doing per table is. Importing data into a temporary table for imported data. Then I execute a stored procedure like the example attached, to insert new records or update existing record...more >>

I've got a nightly job that refreshes a development database from our production database. The problem I'm having is that some people leave at night and don't disconnect from the database that the job is trying to refresh, causing the refresh to fail stating it needs to have exclusive right...more >>

I have a table that stores labor transactions, key fields would be date, employee number, job worked on, total hours. I also have an autonumber primary key field. This is an upsized table from Access. I am using this table as the backend to an access front end, and recently the reporting a...more >>

How come: INSERT INTO ftsolutions.dbo.ApplicantResume (ApplicantID,PositionID,TicklerPhrase,ResumeText,ResumeUnique) SELECT @ApplicantID,@PositionID,@TicklerPhrase,@ResumeText,max(ResumeUnique)+1 FROM ApplicantResume WHERE ApplicantID = @ApplicantID and PositionID = @PositionID does ...more >>

In SQL analyzer , any tools / function can let me compare two table structures and then find out the difference ?? Thank in advance -- .. ...more >>

The following is my DDL and sample data: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Orders] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ord_assembly]') and OBJE...more >>

Hi all of us, I had a job running since yesterday evening and it bring spent almost thirteen fucking hours so finally i've decided apply a when I saw lots of blocks with sp_lockkill. Well, the problem is the rollback. How can I give more powerful without to have do a off-line status or ...more >>

Hi all, The following SP is used in an external transaction logging solution that runs the INSERT into a 'header' table and returns the ID back to the caller so that any 'detail' information is stored in a related table but links to teh 'header' table using the ID. CREATE PROCEDURE ins_tran...more >>

Hi Sql Gurus, From Inside Sql Server The histogram is created when the index is created on existing data, and the values are refreshed each time UPDATE STATISTICS runs. If the index is created before data exists in the table, no statistics appear. SELECT statblob FROM SYSINDEXES (NOLOCK...more >>

Previously I've used the sysobjects table to find info out about a database but from this group I've found that I should be using INFORMATION_SCHEMA tables instead. But the list of defaults seems to be lacking, am I missing something? I'm trying to drop the default for a column so I can drop t...more >>

Could I have written the following stored procedure better in which I may benefit regarding performance or cleaner code? or do the following sp look great? CREATE PROCEDURE UpdateAccount ( @AccountID int, @EmailAddress varchar(255), @Password varchar(16) ) AS DECLARE @actualPass...more >>

Using SQL Server 2000, SP3. Try this : == create database junk GO use junk create table a (apk int primary key, a1 varchar(10), a2 varchar(10)) insert a values (1, 'a1', 'a2') create table b (bpk int primary key, b1 varchar(10), b2 varchar(10)) insert b values (1, 'b1', 'b2') GO c...more >>

Hi all, I'm writing a sql based import system that imports data from a view to a one or serveral tables in the target database. Its the final stage i'm uncertain about. The 'business logic' stored procedure looks at each record in the view and depending on the contents, needs to update or ...more >>

We're pulling data from a Lotus NotesSQL form into a table on SQL Server using DTS. The text is getting trucated at 254 characters using a simple select statement. The column on our SQL Server table is varchar(1000). The datatype in the Lotus Notes form is "Text". I've confirmed the truncatio...more >>

I have trigger on first table on first server which imports data on second table on first server and on second table I have trigger which inserts data on third table on second server. But when the second trigger executes, I get an error message:: New transaction cannot enlist in the specif...more >>

I am working on writing a stored proc to QC the load of some tables. The load is done monthly, so I want to compare the count from last month's table to this month's table and then if they are within a specific range, use an output parameter to say good or bad. I am using dynamic SQL because...more >>

Hi Freinds, SQL2000 Where does DTS info saved? Which database? master? msdb? which tables? Thanks, Pat ...more >>

Is there a way to tell the diagram not to show the relationships? I want to just print out the tables with the field definitions in alphabetical order, which I can do by hand - but I can't get rid of the relationships (which I show on another diagram). Thanks, Tom ...more >>

I'm getting this error when I run a stored procedure: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead (WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. Connection Broken The procedure is usin...more >>

When I run ACT (just spin up a bunch of threads) and try to simulate alot of users hitting on the database at the same time I get: 1205 Source: DataAccessXml Target Site: visa.dps.ppc.data.DataReplyTypes Execute() Description: MarkDirty(1759) SQL fault. Stack Trace: at visa.dps.ppc.data....more >>

I have a diagram with 2 tables on a second page. I find that anytime I make any change at all to the diagram - it takes the whole diagram and centers it. This puts half of my diagrams on the 2nd page and splits some of the tables. After I select all and move the diagram back to the left (...more >>

Is there a way to have a diagram show the field types? I would like to use it for my documentation instead of having to hand write it. Thanks, Tom. ...more >>

anHi All, I have a stored procedure that is doing some purging of data from our database. When we try to delete a record that violates a foreign key constraint the stored procedure cancels with the foriegn key violation error. I want the stored procedure to by-pass this error instead of t...more >>

I can not access my SQL database objects. Unless I now state the user.tablename What have I changed. I get the same error with our SA user as well. The user is within the DB role of DBOWNER...more >>

This may not be the right place for this but can someone help me out? The following code is in MS Access, and it's not updating. UPDATE [myTable] SET [myTable].Commission = [myTable]!PAYMENTAMOUNT] - [myTable]![Commission] WHERE ((([myTable].PAYMETHODCODE)=1 Or ([myTable].PAYMETHODCODE)=...more >>

Hi, Is it possible to create a package using Enterprise Manager or something else that will recreate a database! I want to create a kind of installer so I can just run a file and install the database on other SQL Servers??? I would be grateful for any advice or links on how this can be don...more >>

We currently have about 4 million records in the database (this number keep changing, by the 4 th day usually this becomes 10 million records, and we only keep 4 days worth of data in the database). After executing the queries for a couple of days to return hundreds of thousand of records, I ...more >>

The following is supposed to strip the numerals at the beginning of addresses and display the result. It runs, but displays nothing. Can someone tell me what appears to be wrong with it? --declare variables DECLARE @position int,@result int, @add nvarchar (50) --declare cursor DECLARE st...more >>

Hi All I am posting My Prob again I have a situation I am using Access 2003(MDB) as front end & sql server 2000 backend Seneria is that I want to lock as table row thn do some updations in table using that connection, running some procedures etc & then unlock that row I HAVE TO USE ADO...more >>

Hi i have an database that looks like this: http://www.cyren.no/diagram3.gif (Se picture) I want to get an query where a user can search in a date range. The output should be like this: Department(ANavn) JANUAR 2003 VF54423(RegNr) 21.01.2003(GjelderFra) 23.01.2003(SiOppforsikringFra) ...more >>

I am creating an ASP.NET application that runs on a server with Windows 2003, Active Directory, SQL Server 2000 (SP3), and IIS. Traditionally I have used trusted connections and database roles for application access to SQL Server. However, my MS Access users now inherently have access to all...more >>

I have to deal with the way different brokers provide references for our clients. Some provide just one for everything and others one ref per account/facility/portfolio. I need to pull a ref from either a one-to-one table or from the one-to-many table depending on the value of a field in a th...more >>

Hi all - I have the following: SET @FinalSelectString = (@SelectStringPrice + @InnerJoinString + @QueryStringParts + @QueryStringParms + @QueryDatePeriod) SET @ParmDefinition = N'@beg_date datetype, @end_date datetype, @per_sales dec(9,0) OUTPUT' exec sp_executesql @FinalSelectString,...more >>

All of a sudden my schedule jobs failed. Only non maintenance schedule jobs continue to work. All other scheduled jobs have failed with the following error: "Error string: Cannot generate SSPI context Error source: Microsoft OLE DB Provider for SQL Server Help file: Hel...more >>

We are working on some SQL Server utility software. The software uses the SQL-DMO library to interact with a SQL Server instance. We are having trouble handling information messages from the server. We have stored procedures that produce output with PRINT statements. The output may be le...more >>

Hello Im having a different behavior when using the convert timestamp to datetime on two diferent databases on the same server. In one database (pubs) I can do the convert and on another (BCCMA) I cant. Below is the script that Im using to test the convert: set nocount on use BDCMA drop...more >>

I am having simple qry Select * from matches where start_date >= '01/02/2004' Now on one the server date setting is dd/mm/yyyy format and on another it is 'mm/dd/yyyy' format. Depending on that result, may be different. What is the best practice so that on both server the result will be sa...more >>

Dear Friends I want to write a trigger which will be fire on update and delete. I have a table with 12 fields. I have created a backup table with following column. 1. Tablename : Name of the table 2. UserName : Name of the user who is doing update or isert (Suser_Sname()) 3. Value...more >>

I have a Match table with 2 fields Match table 1)teamno1 2)teamno2 This table has 2 records. Both teamno1 & teamno2 are FK to Team table Team table 1)teamno 2)teamname What I want to display is teamno1, it's corresponding teamname from Team table, teamno2, it's corresponding tea...more >>

Hi , i need some help . mytable contains a column that is price as money . But i want to resize scale to 2 digit .How can i do ? i tried lots of like this one alter table urun alter column ( price money (19,2) ) but doesnt work ...more >>