Hi I will be most grateful if any one can help me. I have 2 connections to two different databases. cn1 has database db1 cn2 has database db2 SQL = "SELECT field1,field2 FROM (db1 right join db2 on db1.field1=db2.field1)" I get an exception error, I think it is because I am using two co...more >>

is there a sql function that would return the type and length of a colmnu? execute 'table.column1', @result output where @result would be "CHAR,4,0" or "NUMERIC,10,2", or better, retrun a table (type, length, decimal) -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http:...more >>

Is there a SQL Server function that wuld return .t. when a @var is 1. blank or 2. 0 or 3. NULL -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 8:50pm up 7 days 13:04 load average: 0.99 0.97 0.98...more >>

Table1.Col1 char(7) Table1.Col2 char(7) Table1.Col1 is of the form "xxxmmmm" , m is a digit , and "mmmm" is not necessarily sequential Table1.Col1 is uniquely indexed i would like to create Col2 = "yyynnnn" where nnnn is created in the sequence "0000","0001","0002" -- following the Col...more >>

Hello, We have a query that works fine on SQL7 but not SQL2000. It has to do with Degree of Parallelism between the two boxes. Here is the scenario my associate has encountered. Would sure love a compatible SQL solution...we don't want to have to use the MAXDOP option: Query I ran on SQL200...more >>

Hi All, How can we create a BEFORE INSERT / BEFORE UPDAT / BEFORE DELTE Trigger in SQL Server and do the same like ORACLE? Any Suggestion or IDEA? Thanks Prabhat ...more >>

Hi, I have a stored procedure and one of the parameters is of format smalldatetime. What is the format of the parameter when I call the SP ? CREATE PROCEDURE sp_My_Insert @org int, @aDate smalldatetime, AS INSERT INTO MyTable(Org, ADate) VALUES (@org, @aDate) GO How do I call this S...more >>

Hi all, I confused how to define Insert,Update,Delete trigger. Please see the below syntax:- Table A Column 1 - Auto Increment Numeric Column 2 - Not null Column 3 - Not null with Default values Getdate() Create Trigger tg_test on Table A For Insert,Update,Delete As Declare @Name nv...more >>

Any help is appreciated here. My alter column code fails below. I don`t know what I am missing here. The column is a primary key and all I want to do is make it an identity column. Thanks for any idea. I need to submit code at my company to make such changes. Phlip ---------------------...more >>

Hi, I'm working on a stored procedure with a SELECT instruction, like: SELECT ID FROM tblObjects WHERE (UserID IN (1, 3, 6, 10)); I want to set the values in the list (1, 3, 6 , 10) by one inputparameter and I'm thinking of using a string, like "1, 3, 6, 10". My question is: How to "co...more >>

I'm working with a data structure for some commerce software. They have a Coupons table with a column called Expires. The Expires column is nvarchar(50), so it either contains "Never", to designate that the coupon never expires, or an expiration date in format MM/DD/YYYY (the entry of the date i...more >>

What's happening is that my proc creates a local view of fields from a global temp table, but I'm getting invalid column name 'name' -- begin copy set nocount on declare @namefield varchar(20) ,@valuefield varchar(255) select @namefield 'name', @valuefield 'value' into #tempgmnv truncat...more >>

Hello, I am about to write a trigger which will prevent INSERTs/UPDATEs of some specific, unwanted data. My question is: How to prevent writing a record when unwanted data is detected? This example does not work: CREATE TRIGGER mytable_id_not_null ON mytable FOR INSERT, ...more >>

Hello, Would someone please know how to set isolation level for one particular transaction to "serializable"? And how to find out what the default isolation level for the following transaction: begin transaction --do something here commit transaction Thank you very much ...more >>

Hi all, i have proble with this query,=20 update TableA set ManualReplied=3D1 where suffix =3D'00000022' and RequestDT < (select RequestDT from TableA where = MsgID=3D7533) it prompt me the error :=20 Subquery returned more than 1 value. This is not permitted when the = ...more >>

I get an error when trying to use xp_startmail. My User / Password on the server are bbp / kuku When I call: exec master.dbo.xp_startmail @user='bbp', @password='kuku' I get the error: Server: Msg 18025, Level 16, State 1, Line 0 xp_startmail: failed with mail error 0x80040111 However...more >>

Please hlep Error Message show : Bulk insert data conversion error (type mismatch) for row 1, column 3 (Salary). CREATE TABLE [dbo].[tmp] ( [StaffName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Department] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [...more >>

I have a field in one of my tables that contains a list. I need to do a query that selects records where "variable" is in that list in that field. Right now I have a work around since there is only 2 items in the list select * from quizzesdev where channel LIKE 'SOL% The field with the list is "c...more >>

I'm building a medium-scale data-entry web application, which involves creating data entry forms, record listings and detail screens for lots of database tables. Rather than designing a series of similar web pages for each table I'm looking into recording metadata about tables / columns in the...more >>

Hi, how can I catch error converting text value to numeric ? - isNumeric not function properly - if @@Error > 0 = not function for convert error <<<<< Thanks. ...more >>

I have an SP in my DB that is appearing as Cache Miss consistently in a profile. I've checked that it is using the correct owner name. I've followed the guidelines (I believe) for the SQL within the procedure. I've checked the calling application is using the correct usage, and case (just in ...more >>

Whats wrong with the 6th line of my syntax. I have a backup of QKrateFL and I want to restore that backup on another server which has a different install directory for the data and log files RESTORE DATABASE QKrateF FROM QKrateF WITH NORECOVERY, MOVE 'QKrateFL' TO 'F:\MSSQL\Data\Q...more >>

Guys, I have a "DateSold" field (nvarchar), in this table I'm trying to manipulate. Half the dates are in the "mm/dd/yy" format, and the other in "mm-dd-yy". What can I do to change the date format, so that they all uniformly shows up as "mm/dd/yy"? I will then want to insert this data i...more >>

Hello All, I want to propose a system based on ASP.NET and SQL Server to maintain the resumes of all the candidates who apply for a job in my company. How can I store the word document in the sql server. and then how to implement search functionality (suppose I want to get all applicants w...more >>

when I select records from a table, I also want have a column , this new column is an auto number, the value is result's line number. Can SQL select do this ? ...more >>

hello can i create a cursor within a stores procedure?...more >>

Hi All, I have one silly doubt on CHAR and VARCHAR. Can any one tell me what is the actual and technical difference between the data types? As per my knowledge CHAR is Fixed Length while VARCHAR is Variable Length. 1) Also I want to know if I will do a search on a CHAR f...more >>

Hi, Using SQL server 2000 it's possible to generate SQL Script to generate the tables, triggers etc. Is it possible to generate script for data as well ? ...more >>

Hi I have a table temp_d with the following data company year mon Delta 2003 4 Delta 2003 7 Delta 2003 10 Delta 2004 1 Ace 2003 6 Ace 2003 9 A...more >>

Hiii Can anyone tell me the difference between normal stored procedures and extended stored procedures?. REPLY 2 NEWS GROUP ONLY Ramesh :) ...more >>

hi i have the foll tabl id proid is date qty dat 1 92235 2 2004-01-08 00:00:00.000 NULL 2004-04-02 14:11:20.44 2 92235 1 2004-01-07 00:00:00.000 NULL 2004-04-02 14:11:20.76 3 92235 52 2003-12-18 00:0...more >>

Hi there, Suppose I have a table 'Test' with two columns, ID and op_date_time. Given a certain datetime @input_datetime, I'd like to find the row whose OpDateTime is the closest to the specified @input_datetime. Maximum precision is important in this case. Attempt#1: In order to find the t...more >>

When a client passes a select statement to SqlServer via ADO (or whatever), an ordered set is returned. When I write a select statement within a UDF, an unordered set is returned (as evidenced by the fact that an order by clause is disallowed in this case). When I use a UDF to return the resu...more >>

Hello Is there a way to do case sensitive searchs. I can't find anything about case sensitivity in expressions Thank you Brandon...more >>

Hi, I have the following t-sql code written: 1.SET @DynamicQuery = 'SELECT @CommaListValues = ' + @fieldNames + ' FROM #TempBaseSet WITH (NOLOCK) WHERE RecordId=' + CONVERT(NVARCHAR,@RecordId) EXEC Sp_ExecuteSql @DynamicQuery , N'@CommaListValues NVARCHAR(200) OUTPUT',@CommaListValu...more >>

I have dts packages in stored as a local package in a SQL Database. I want to move/copy them to another SQL Server. What is the best way to do this? ...more >>

In a procedure, can I select output from another procedure within the FROM clause? If so, please provide syntax. If not, please provide alternative. Looking for something like: SELECT * FROM (EXEC MyProcedure @var1, @var2) Thanks. ...more >>

Hi, This is a previous post. The codes not pretty, but as you can see in the main SPROC, I have 4 EXEC statements. I only want the results displayed from the last EXEC statement in the main SPROC. I don't want the first three returned. Is it possible? Main SPROC: =================...more >>

Hello, I need to get data from a view and create a .CSV file from the data. Does anyone have an idea how I can accomplish that from a stored procedure or know of t-sql commands that I can use? Thanks, Biva ...more >>

I have a problem on the usage of DISTINCT queries and posting this question. I am removing duplicate entries in a table. When I used the DISTINCT queries to do it, the query ignored the trailing space and the entries with the trailing space are processed the same as the entries that do not hav...more >>

I'm using this expression in my SQL Server 2000 stored procedure Select.... (CONVERT(char(10), dbo.BillPayment.VoidDate,101)) As PaymentVoidDate From .... I use this value in Crystal Report. When I used an approach to base Crystal on SP, everything was fine and I used to get this value in a f...more >>

I am supporting a client whose SQL Server Agent Jobs no longer will run, and I am unable to assign a valid account ID for the jobs. I have found that the problem is related to a server name change, and that a recommended solution is to run the following commands: sp_dropserver 'oldname' go ...more >>

Hello All Is there any way to release the memory taken up by a query run after the query has completed execution. Thanks ...more >>

Hello, I am working with SQL Server 2000 English and I want to change the Database date culture to german. right now I have to this in english INSERT INTO persons (person_date) VALUES ('MM.dd.yyyy') But I want that to change to German like this: INSERT INTO persons (person_date) VALUES (...more >>

Hi, I wonder if I can do something like the following SQL: Update t1 Set f1a = f1b when f1a = null, Set Set f2a = f2b when f2a = null, Set Set f3a = f3b when f3a = null.... Thanks in advance. Wanda...more >>

The following script conditionally builds a string to execute. It was working in MSSQL 7.0, but after upgrading there are times when some of the similar proc's are not working and I'm fixing as I go, but I'm not fully clear on what's going on. So, quoted identifier should be Off or On? I'd...more >>

In the interest of efficiency, I want to SUM on a Parent and Child table simultaneously. Unfortunately, this leads to erroneous results. If there are 10 child rows created by a join to one parent row, it results in 10 occurences of the parent table. How would one overcome this problem in a single...more >>

I have 4 tables UserInfo, GUISettings, MonitorSettings, and OtherSettings. Each table has a UserKey column to relate them and there is a 1-1 relationship between UserInfo and MonitorSettings and OtherSettings. There is potentially a 1-Many relationship between UserInfo and GUISettings. The que...more >>

Hi, Is there a way to not shown the results of an EXEC statement? What I'm doing is calling a SPROC which nests an EXEC statement then returns some OUTPUT to the calling SPROC. The calling SPROC also has an EXEC but that's the only EXEC result set I want to see. I don't want the called...more >>

In reading "Inside SQL Server 2000" reference is made to the idea of a table's Primary key being made a Clustered Index by default. However, the discussion on Non-Clustered indexes says that they are very efficient when the search criteria is "Highly Selective". Both these statements being tru...more >>

Hi! I have a bit of troubles using Null's that has been passed into a stored procedure. Example: CREATE PROCEDURE test @parameter char(20) AS select * from myTable where myColumn = @parameter EXEC test NULL This returns no records even though some of the myColumn are NULL. If I ...more >>

I've got a table with approximatly 2.5 million rows. While fulltext indexing is building (approx. 8 hours to complete) any select querys run against the table time out. After the ft index is built, even if I'm doing a simple update to the table, all select querys time out Is there any way I can ...more >>

When I run the following it works Select * From Cartons Where ('8893768010700' like '%' + CartonID + '%') This is in a trigger and I can't seem to get it to work right The @cartonID variable comes from data captured by a scanning unit into a table and this trigger is exec...more >>

I have been experimenting with a 'real-time' data warehouse idea that would pull data from a DB2 database into SQL server in response to a query against a table-valued function. (Changes in the DB2 database are recorded by DB2 triggers In addition to just pulling data from DB2, I need to update S...more >>

One thing I cannot find answer to..: HOW DO YOU RESIZE / COMPACT data file for the MSDE databases??? They obviously keep growing fast and fat, gobbling more space than necessary....more >>

After I reboot the server in ex-normaly method. The database can't startup. And the errorlog have the following error message ..... I/O error (torn page) detected during read at offset 0x000??????????? in file'f:\test.mdf'... I have tried copy database to other server for testing attach. It al...more >>

Hello Experts, I don't know how to search this, so I post my problem directly to this newsgroup. I Post the below DDL to make our lifes better: DECLARE @cols as nvarchar(4000), @Tbl as nvarchar(100), @Indx as nvarchar(100) Set @cols = 'Field1, Field2' Set @Tbl = 'Table1' ...more >>

Hi, I can't figure out where to even start, can you help? I have a table (Cartons) that contains a cartonid field i.e cartonid = 9376801. I have a mobile device that scans the cartons then sends the data back to SQL server. My problem is that the data that comes from the scanned carton...more >>

Am I right in thinking I'm not necessarily able to re-create CHECK constraints using the CHECK_CONSTRAINTS object? For example: CREATE TABLE Species1 (Gender CHAR(1), CONSTRAINT ck__99 CHECK (Gender IN ('F','M'))) ; CREATE TABLE Species2 (Gender CHAR(1), CONSTRAINT ck__98 CHECK (Gender IN...more >>

I need help on this.. update TabA set Col A = (Select Col B from TabB where ColC=ColC) TabA is a Detail Table, Col A belong to TabA TabB is a Header Table , Col B belong to TabB ColC is the DocumentNo of both Tables (share same name and is the link in my query).. But I got error when...more >>

Is there a solution to it ? I don't understand why my update is not ok. Can anyone please help ? My aim is to Update Col A of Table Detail (let's call it TabA )with value of Col B from Table Header (let's call it Tab B) The link is the Col C (same name for both Tables) (eg Document No)....more >>

Hello MVPs, Which option do I have to change (using sp_configure I guess), so I can execute sp_indexes? Err. Msg. : Server MYSERVER is not configured for DATA ACCESS. Thanks in advance....more >>