Hi All, How can I update multiple rows with values result from a view query. I always got an error 'The column prefix 'viewA' does not match with a table name or alias name used in the query.' Here's my syntax: Update table1 Set table1.A = (Select view1.A from view1) WHERE table1.B =...more >>

Is there a query to show actual record size for every row in a table? -- Thanks in advance, Les Caudle...more >>

Hi, We are using this set of extended stored procedures to manipulate files. http://www.bramc.ru/soft/xprocFileUtils.html We actually store documents information in a table and then a file link that we used the extended procedures for. When one of these records is deleted, I need to ensu...more >>

I am just trying to learn VBA with database SQL2K. Can someone please point me to the right web site ? Thanks. ...more >>

How can I Call a SQL Server function - Say NextInvNo which returns a character Value - From a VFP Procedure and Retrieve the Value Returned ? Need it urgently .. Thanx in Advance ...more >>

Hi I have a problem with optimizing a query. This is the layout of tables: (table 1) - tblEmpPlan empplan_id(PK,ident)----plan_id 21------------------------12 22------------------------12 (table 2) - tblEmpPlanEnroll empplanenroll_id(PK,ident)------empplan_id(FK)---coverage_start----c...more >>

Hi, I have the following I am trying to run: declare @username varbinary(128) set @username=convert(varbinary(128),'marina') set context_info @username On one database, this works fine. On another server, I get 'context_info' is not a recognized SET statement. Do only some versi...more >>

I have 2 tables, table1 and table2. Both have common column called partno. Table 1 has 32000 records and table 2 has 20000 records. I want to see which of 2000 records of table2 is not matching with table1. I have tried the following statement but it's not work. SELECT COUNT(*) as E...more >>

Hello everyone, Is there a rownum in SQL Server like oracle ? I attempt to insert a recordset to another table like the following, Insert into A(id,name) select rownum,name from b. Anyway? Thanks in advance. Wen bo ...more >>

Hi, I have 2 tables with identical structures, what is the best/quickest way to compare the data in these 2 tables? I need to find out the differences between those 2 tables. many thanks ...more >>

Hi, Anyone know how to catch a trigger error message when encountered during an EXEC(@sqlstring) call? I'm using VB6, RDO (yes, rdo), and calling a stored procedure which makes this EXEC call using a dynamically-built query string containing an insert or update statement. I'm not EXEC'ing ...more >>

Hi, I am attemping to get a list of all databases on the server. I use the query SELECT name FROM master.dbo.sysdatabases however this brings back system databases as well, I would like to know how to limit this to only user defined databases. when I select * from this table the only f...more >>

I have a query that returns the following error code Arithmetic overflow error converting numeric to data type numeric. However I do not get the above error code when I include filters within my query for example a.order_stat_cd = 'Y' (this returns about 1605 rows) if I ommit this fi...more >>

When new records are added to a SQL 2000 table I'm required to export them to a text file. This is imported into another database which does not allow direct write access. I thought that a trigger would be a good idea, calling a bcp command to do the export. I had no success, presumably be...more >>

Hi there, I was wondering, if it's possible to define stored procedures that run outside of transactional contexts? I would like to write a stored procedure, whose actions won't be reverted in case of a roll-back. Does SQL Server support "non-transaction-aware" stored procedures? Thanks, ...more >>

Is there a function in SQL Server that will convert a value in a variable that is negatvie... to a result value of zero? e.g. DECLARE @Temp AS INTEGER SET @Temp = -345 SELECT (<some function>, @Temp) RESULT 0 ...more >>

Hi All, Can someone help me with this. I am sending into a stored proc a DateValue, or two DateValues depending on if I am looking for a range. My problem is that the evaluation on the Date Field is based on Date and Time; where I am passing in only a Date to be evaluated. --SUDO CODE WHE...more >>

Can you create a loop in SQL Server 2000, or is there a better solution. What i am trying to acomplish is, with a field called X. I need to find the max of that field , say 10. After i return records, that are filtered update there column x with the max + 1 increasing each record by one. I nee...more >>

Is there a way to find the number of open cursors at any given moment, either at a database level or a server level? Thanks, Joey...more >>

I have a stored procedure in a staging database that returns results in 3-4 seconds. The exact same database is in production (backed up staging, restored to production), and the same SP takes anywhere from 30-60 seconds. One of the steps in the Execution Plan shows a particular index seek o...more >>

Hi...and good morning. I'm problem with AutoStart of my MSDE Server, i look in errorlog: 2003-10-24 12:33:53.25 spid5 Encountered an unexpected error while checking the sector size for file 'C:\Programmi\Microsoft SQL Server\MSSQL\Data\tempdb.mdf'. Check the SQL Server error log for more...more >>

Can anyone tell me how I can find data from table1 column1 that is not in table2 column2? I thought that doing a NOT IN subquery would do the trick, but its not returning any data. Why doesn't this work. SELECT T1.Column1 FROM Table1 T1 WHERE T1.Column1 NOT IN (SELECT Column2 FROM Table2...more >>

Hi, If I have 2 tables with the same structure in 2 different databases, how can I use aggregate functions on data queried from both tables as if they were in one single table? -- Thank you, Doru ...more >>

I am getting the following error while connecting to a MS SQL Server 7 using ODBC connecting thru a PERL application. The sql statement works perfectly in Query Analyzer. Income_id is a int identity column, net_income is money and the rest of the columns are integers. Any thoughts on how I can ...more >>

Hi, Anyone know how to catch a trigger error message when encountered during an EXEC(@sqlstring) call? I'm using VB6, RDO (yes, rdo), and calling a stored procedure which makes this EXEC call using a dynamically-built query string containing an insert or update statement. I'm not EXEC'ing ...more >>

Hello, I'm trying to get a single column listing of the decendent's unique names. this was the closest I got, but I know uniquename must get in there somehow to show the name in a single row. // this is my custom cube select {[sales]} on columns, Descendants({[Contact].[Region]}) o...more >>

One of our developers accidentally added a 'rowguid' column to all of our tables (mssql 2000). I'm trying to write a script that will drop this column from all the tables; however, I've run into a problem where I can't drop them because there are dependant contraints/indexes. The following cod...more >>

Is there an easy way to use the result set from an exec call in a stored procedure? As an example, I want to find the type name of column C in table T by using: declare @TypeName as varchar(32) exec sp_columns @table_name = 'T', @column_name = 'C' One row of twenty columns is returned, the...more >>

UDF are supposed to be frowned upon because for large tables they will be executed once for each row. Doesn't the same apply for a case statement? I have a table that has abbreviations for items statuses. I want to take the abbreviation and convert it into a complete word, ie 'O' becomes OPEN, ...more >>

Hello, one of our users is using Access and is running queries against the SQL 7 database. We recently replaced the SQL 7 server with a faster machine but the Access queries are running much slower than before. I have zero experience with Access. Can anyone out there provide some sugges...more >>

Hai Friends, Our database is SqlServer and it is storing Date data in 'mmm/dd/yyyy' format. How can I Configure it to store it in 'dd/mmm/yyyy' format Satya Programmer InfoLabs ...more >>

Under 6.5 the timestamp was unaltered by restoring the data. In 7 or later this is no longer true. We had been using the timestamp to ensure that our clients could not make unauthorised installations of our software just by duplicating the database. We therefore need something that doesn't cha...more >>

I need to log the sql server ip# to a table, any idea where I can get this from. Don ...more >>

Hi SQL Gurus, If I decide to use a Case Sensitive column in an SQL2k Table (COLLATE SQL_Latin1_General_CP1_CS_AS), is there any performance drawback that I should Consider ? Thanks, Krist ...more >>

Here's the situation. We have a stored procedure that is executed in one database, but does an insert into a table in another database. The login has execute rights to the proc,and has db access to the other database but no insert rights into the table in the other database. The stored ...more >>

Hi, I have apps that load data using bulk insert. (sql2000 sp2) Now I've added some columns not null default 0 and the bulk insert no longer works ! Is this what I might expect ? btw: SAS-system feeds this sqlserver using libnames. Jobi ...more >>

Hi I am trying this query to insert rows in a foxpro dbf file callled files, i have created a ODBC source called FOX1 which points to the files, select and update works from SQL but just not sure with the Insert syntax, if somebody can help me with the exact command. select * from openque...more >>

Hello everyone, Is it possible to right one DELETE Statement that will delete multiple records in multiple tables. For example, I have tables A, B, C. I want to delete all related records in table A that have rec_id = 10. And at the same time delete all recs in table B an...more >>

Hi, Is there a standard for naming Variables in SQL? Where can I find that? Thanks ...more >>

Is there a replacement technology for SQLDMO? I thought I had heard or read that Microsoft had developed technology to replace SQLDMO. Ado.Net?...more >>

How do I do this? I need to receive the data this way: 12 10 I fI have the following structure. select sum(total)as total1 from table1 12 select sum(total)as total2 from table1.... 10 ...more >>

Hi All I am trying to run a batch file from a Scheduled Task in SQL.... the batch file runs fine until it has to call another application (any executable , even Notepad) where it seems to get stuck A help you may be able to offer on this subject would be appreciated Thank you kindly...more >>

I'm being prompted to re-confirm the logins(SQL) password when i try and change its role membership. e.g. If i wanted to add login Joe to the sysadmin role, it prompts me to reconfirm the password. Even if i enter the correct password it returns an error that the password is incorrect. Th...more >>

Hi, I have a procedure where I'm creating temp tables with maximum row size higher then 8060 bytes (with sql_varchar column it is almost unavoidable). Thus when running this proc server returns warning/error nr. 1708. This procedure is run by a job and job history is full of meassages of...more >>

Hi I have the following code and it is giving me an error below do not know how to solve this has any one ideas..thanks Shaun update ce_sls set [gp pct avg mtd] = ( case when [rv mtd] =0 then 0 else avg( [gp mtd]/[ rv mtd]) end) * 100 from ce_sls error: Server: Msg 157, Lev...more >>