Is it possible to have sql 2000 script out tables without the collate specifications in SQLDMO? When I try to use the database.transfer transfer object the method fails on error on line .. COLLATE ... Tried to put the database in 70 compatibility mode (via SQLDMO) but didn't work The g...more >>

declare @cntColumn in ... ... while @@fetch_status = begi set @cntColumn = @cntColumn + set @strParam = @strParam + N'declare @column' + Cast(@cntColumn as varchar(10)) + ' varchar(50) set @strParam = @strParam + N'Set @column' + Cast(@cntColumn as varchar(10)) +...more >>

Hi all, I'm having trouble building an sql string that refernces variables declared outside the sql string. The code I've posted is what I started with, but I've tried heaps of variations including using sp_executesql The problems I'm having revolve around the fact that the variables I've de...more >>

Can someone help me understand these 2 error statements ? I find it hard to intepret it properly. An example to help clear it would help. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'fk1'. The conflict occurred in database 'DB1', table 'T1', column 'C1'. INSERT statement c...more >>

Hi All.. Following is the description of the problem. Any help would be appreciated. Table A has a primary key and a clustered index on one of the fields"Document No". Table B refereces Document No of Table A with a foreign key. When you do an index search for Table A, in other words u...more >>

having this store procedure: create proceedure proc_num @lot char(3), @cod_num char(8), @rsult bit output, @qty double output as declare @p_c char(8) declare @exist double declare @t_cost double declare tot_exist cursor for select prod_cod,exist,cost from invent with (rowlock) ...more >>

Given this table: HST_INFO ( hst_no char(5) ) Given this stored procedure: create procedure aaa_aaa as select substring(hst_no,1,3) as group_no from hst_info The column group_no will be of maximum length 5 rather than 3. Why is that? Must I use a convert(char(3),...) to force the wid...more >>

Hi All, I need to make a query to retrieve the data from 2 different tables, linking these tables by one column from the first table. For example: SELECT T.*, M.* FROM Tab1 T, Tab2 M WHERE T.Identity=M.Identity; The problem is that sometimes the T.Identity is not defined and can be NULL...more >>

Hi I need to catch the error returned by RaiseError in the stored procedure in my VB 6.0 ActiveX DLL I am using MDAC 2.8 and MSDE as the database Here is the stored procedure CREATE PROCEDURE sp_GetFirstNames A SELECT sFirstName FROM tbName if @@ROWCOUNT = raiserror(50009,16,1 return (0...more >>

Hi, I have a big table (about 14 million records) Queries are very fast (instantaneous) when I make a query using a field where I have a index on it. However, from my program the user can query this table using any field. If they select one of the fields which are not indexed, the proces...more >>

I am trying to import data from a Notes database using the notessql odbc driver. I can use the import wizard and see the fields I want to import, but my problem is that the data needs to match up with rows that already exist in my SQL table For example My Notes database contains these field S...more >>

Hi all, I need to be able to pass a dynamically created list to a stored procedure. I am doing this with SQL 2000 and CF 5.0. The @SegmentKey will contain the list values. There will be a different number of elements in the list. Can someone explain how I get SQL to read a list? See the SP ...more >>

Hi, I have all these traces (trc_warning, Trc_long query) that are automatically generated in the log directory. How can I translate them to something usuful? How can I input them somewhere to tell me what to do exactly? They look like this: Database: Microsoft SQL Server Warning: Select on ta...more >>

Just curious and making sure I avoid "memory leaks". I am writing a = trigger, that calls another sp_, as in the following sample code. As you = can see I am calling RAISERROR in the middle of a FETCH loop which is = working fine however can this cause a memory leak because when IF(...) = is tru...more >>

Any tips on setting up a database maintanence plan? each time i use the wizard to set one up everything fails except the database backups. ...more >>

Hi all, I'm no expert in SQL syntax and I would solve the following 2 problems through code. But I thought I could be better off writing a SQL statement. The problem is... I don't how to do it!! Well, here's the scenario: I have a Table A containing a list of items. Any of these items can...more >>

Hi! I have the following problem. I would like to retrieve rows, returned by an EXEC statement, that drives a stored procedure. However, I cannot create a temporal table and INSERT INTO that with INSERT INTO #temptable EXEC procedure, becayse my procedure does the same inside, and so, the resu...more >>

Hi; I am a bit new to tsql. I am having trouble with getting a variable in a loop to update. I am reading a table to put a list of tables into a cursor. Then I am doing an exec on a dynamic sql statement for each of these tables. I am trying to get a record count ( @recordCount ) of r...more >>

I have an SP which takes a date as a parameter: Create Proc Reports_ListStockAtDate @CustomerID int, @QueryDate SmallDateTime = GetDate As ....etc The idea is that if a date is specified, it is used in the SP. If not, it defaults to using todays date. However, when I call the SP in Query...more >>

Hi I have problems executing the following: declare @update nvarchar(500) Set @update = N'update Friends Set @trans_lang = @coid where cid = @cid' exec sp_executesql @update, N' @trans_lang varchar(10), @coid int , @cid int', 'sim_ch', 4, 17 When I execute it, it seems fine because it ret...more >>

The following commands fail in the SQL query Analzye Command create database 2ba Error Server: Msg 170, Level 15, State 1, Line Line 1: Incorrect syntax near '2' Command create database too ba Error Server: Msg 170, Level 15, State 1, Line Line 1: Incorrect syntax near 'bad' Why ...more >>

Hello, I have a requirement to do the following: CREATE TABLE [Employees] ( [EmployeeID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [AccpacNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SIN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , ...more >>

SQL 8.0 Compat: When adding a column via EM Design Table, I'm getting a warning that ansi_padding is On, but that one or more columns in the table were created with ansi_padding off, but that the new column will be created with ansi_padding On. Is this what I want. This db was created by ...more >>

I want to create a column in a table that is encrypted to store password information. How do I set this up? Thanks...more >>

I need to know if it's better to exec the sentence drop table #temprary_table in stored procedure or not. TIA...more >>

In need help returning all rows for a particular customer regardless of whether data exists... Table1 ------- CustomerSerialNo CustomerTitleSerialNo TitleData Table2 ------- CustomerTitleSerialNo Title Table2 may have three title in it, e.g. CustomerTitleSerialNo 1 = 'Test A' Cu...more >>

Why in the world dosent this work? How can you pass dynamic info into this proc ? this will not work declare @@xx varchar(20) set @@xx = 'Nhh' + 'Ngg' xp_smtp_sendmail @To = 'user@site.com', @from = 'user@site.com', @subject = '11', @message = @@xx @server = '192.168.x.xx' nor doe...more >>

Hi, I am writing an application to get huge of data from SQL Server through Web Services. And my application requires to refresh those data after a period of time(say, 5 min ) My question is: how can i get the recently updated records only, without adding a timestamp to database? Thx i...more >>

I have created a view which takes data from various tables, when I try to modify it I can not make a change as it contains the TOP claus if I remove the top 100 persent from the select satement, sql 2000 put's it back in again and I'm back to square one I have looked on how to remove the top sta...more >>

I have several SQL 2000 databases with a table that has a column called Message. The Message column is currently a "text" data type. I have written a script to convert this data type from "text" to "ntext". The problem is depending on the size of the database that I am trying to convert the oper...more >>

Guys, I am qeurying the SQL database from Excel but I thought maybe someone here could help. I am getting this stubborn error anytime I try to do anything with my recordset: '3704' Operation is not allowed when the object is closed. I don't understand why this recordset is closed no matt...more >>

Hi all I want to use cascading deletes acording to the script below. When running the script I get this error: Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'FK__Test__Col1__3EC74557' on table 'Test' may cause cycles or multiple cascade paths. Specify ON DELET...more >>

Hi, I have a select statement that returns no more than 10 rows. How can I have the select results transposed? Thanks Alan, ...more >>

Hi, I would like to build SET TRANSACTION ISOLATION LEVEL statements into my database abstraction layer (PHP). I sometimes have read only queries locking up and also want to gain speed on some reports where dirty reads are fine. I'm currently holding a connection open for multiple sql state...more >>

HI! If I want to make a select to other database I know that SELECT DATABASE..TABLE.FIELD will work. But If I want to make that select to other database that is in other sql server ? It's possible ?... How? Excuse my English. Thanks in advance. Joao Pinto Joao Pinto...more >>

I've created a view and am now trying to create an index on it. I created the view using WITH SCHEMABINDING, and it worked just fine. However, when I try to create an index on the view I get this message: Server: Msg 1936, Level 16, State 1, Line 1 Cannot index the view 'LIVE.dbo.IQV_WorkOrd...more >>

I have created a field of varchar(1200) on SQL 2000. I am unable to save any text data (using Query Analyzer) of more than 256 characters in the field. Can anyone explain this to me? Thanks. ...more >>

Given the following join statement, and the fact that there are 4 rows in the "Fields" database, ranked by FieldOrder: select r.PK_Registration, f.FieldOrder, d.FormValue FROM dbo.Fields as f LEFT OUTER JOIN dbo.Demographics as d on d.FK_Fields = f.PK_Fields LEFT OUTER JOIN dbo.Registration_D...more >>

Hi I need help optimising the following SQL query problem: I have a table (FACT table) with 7 million rows in it. I have another table (DIMENSION table) with 5000 rows in it. I need to set multiple foreign key fields (relationships) from the FACT table to the DIMENSION table. This is done b...more >>

Hey all, I'm at a loss here.... When I was using SQL 6.5 I was able to create views that honored optimizer hints, but now I can't seem to (in SQL 2000). Here's my situation: I have a pretty complex query that I would like to consolidate into a view so all of the store procedures that need i...more >>

Hi Can SQL server read Outlook/Exchange appoontments?? We have a web site for our technicians on a webserver and want to display the appointments for them on a web pages. So i want to transfer these to SQL server (exchange and web/SQL server are on different servers. Peter...more >>

I am experiencing a problem using ASP.NET Web Services to access my SQL 6.5 database. I have found many posts on many forums with people experiencing a similar problem, but no one has provided a workable solution. Here is the synposis: I have a .Net assembly that makes a SQL call to my 6....more >>

How can I prevent my development Sql Server from showing on another machine on the same network? Ever computer that opens ODBC to add a new Sql Server DSN has my server in it and I wish to prevent this. Any ideas? Thanks, Mythran ...more >>

Is storing images in sql server a good thing? Please give me some info on why you should or should not do this Thanks...more >>

How do I create a view or procedure that asks for or promts the user for input, then pulls the appropriate record? I am just learning SQL, migrating (so to speak) from Access and need help making some of my queries respond in SQL server....more >>

I noticed that the stored procedures in the syscomments are stored in a multi-line format.I have certain changes to make in my stored proc's.Do I need to concatenate all the lines into one single line before executing my stored proc's? Thankyou for ur expert advice....more >>

Hello Again Workgroup I'm now racing ahead generating a large T-SQL programme to enable quicker off-site database updates. I have just a quick questionn re ALTER TABLE, does ownership need to be specified in order to avoid "Broken Ownership Trails"? Example is: - Use DatabaseNameSample...more >>

I=B4m a new (SQL Server 2000 and VB) developer. What=B4s the=20 difference between varchar or nvarchar and char and=20 nchar? What does the "N" mean? Thanks in advance, Tim ...more >>

Hello Everybody I am fairly new to T-SQL and I've come across a stumbling block... I am writing programmes that will enable me to create/modify databases on remote sites. I have managed to create programmes tha will create a database and relevent tables in the instance that the databa...more >>

Hello Experts, Can I kill a process using the tranacation ID? PS: the Unit Of Work ID is: 00000000-0000-0000-0000- 000000000000, the tran. ID is: 33754352 Thanks in advance! ...more >>

Hi How can i know the total/total number of databse on Sql Server Is there any query or program anywhere? Its very urgent Please reply ASAP Regards Dhawal Mehta...more >>

Hello I am using a cursor defined as 'Update for of <col name>' on a temporary table whose col. needs to be updated after executing my stored procedure 3 times as follows(the commented lines are when i try using sp_executesql) OPEN Cursor_tex FETCH NEXT FROM Cursor_tm INTO @col nam WHILE @@F...more >>

Do exists the possibility to avoid the use of cache for certain Database or Query I have a production Database and a historical database. I want to cache all transaction in the production database, but avoid the cache in the historical database. Is possible to do that with SQL2000? Tha...more >>

Hello My server(s) are build 8.00.91 I run the following code CREATE TABLE #test (Col1 VARCHAR(30) INSERT INTO #Test SELECT '28%orange 72%water SELECT REPLACE(Col1, '%', '% ') FROM #Tes SELECT REPLACE('28%orange 72%water', '%', '% ' DROP TABLE #Tes If my database is set at Collation=La...more >>

Hello Experts, I execute a batch of Triggers that looks like: if exists (select * from sysobjects where id = object_id (N'[dbo].[T_MyTable_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger [dbo].[T_MyTable_Insert] GO CREATE TRIGGER [T_MyTable_Insert] ON [dbo].[MyTable] ...more >>