Hello, I have built a dynamic query builder for a project I am on. The user picks various fields they want retrieved and where criteria and the end result is a query string that I build (much like what the query designer does in EM). However, one property of my object is to return back a boole...more >>

Ermz hi everyone, i am reallie new to stored procedure and i am suppose to create a procedure that would allow mi to delete records from the database when it has passed a certain date.. For example: The loan enddate has passed the current date, then the system would delete this record away. So...more >>

Is there a way to call a stored procedure in SQLServer 2000 from an HTML page using ADODB.recordset? ...more >>

I'm creating a stored procedure with several table variables which I use through out. In my last statement that I using one of the variable I get an error stating that I need to declare my variable. Why is this happening, the variable is declared. Do all variable need to be declared at the very t...more >>

If a column has a default value spec, it it necessary to drop the default contraint first, before dropping the column? -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 9:30pm up 1 day 1:09 load average: 1.00 1....more >>

Hi Everyone, I am trying to connect to a sql server in a clustered environment and the server name that I have is a virtual instance or at least that is what our vendor has told me. They gave me the name of the server....and when I type in the connection string (using a sql server user login r...more >>

i have a column in db where dayes can be a any different format ( any one) depending on the culture settings of the application ... I need to compare dates in one of the queries , this is what iam trying to do select convert(datetime,'17-3-2005') and iam getting an error Server: Msg ...more >>

I have to write an update statement that will roughly affect 800K rows. There's going to be other applications accessing this data during the update, and I was wondering if there is a way to do the update without locking the entire table. Any help would be appreciated. Gary ...more >>

I'm looking to run update statements on a recordset of about 800k or so ...more >>

Is it possible to temporarily suspend trigger execution on a specific table? I have a table that has triggers that add/delete charges to a Transactions table during the normal course of execution of the app. However, I do not want the triggers to execute during import from previous version...more >>

Hello NG, First of all - sorry for the long post! I have a requirement where I need to provide a user-definable search mechanism on some data. Thankfully the fields which the filter can be applied to are fixed, but the number of criteria for each filter is flexible. If a particular filter i...more >>

Hello All, I am running SQL Server on my machine successfully. I am also able to connect to it from my machine and from other machines as well. I know that the default port at which the SQL Server listens on is 1433. However, when I do a netstat -a, I don't see the port 1433 being used at...more >>

Sorry if this has been asked before but I couldn't find it. I have a little problem, that goes like this. I need to save the results of a query or stored procedure to a file using | (pipe) as the deliminator. I can do this manually by going to options in query analyser and setting the res...more >>

Does anyone know if there is a way to get a page alert if SQL Service stops?...more >>

Hello was wondering if you guys could help me, when i run QA i always use the sa login, and databases has stored procedures and when i try to run them it cant, since the "owner" of the procedures is another user. how can i change that so both user sa and the database owner can execute the stor...more >>

is it possible to create a procedure/query that based on Getdate(), return last 24 mm/yy? for example, result should be mm_yy ------ 02/2004 01/2004 12/2003 ....... ....... 02/2002 ...more >>

We are seeing and issue with using the set user and suser_sname() with user accounts that have been migrated from an NT 4 domain. Has anyone seen this before? Thanks, Jim Scenario: Sql 2000 in same active directory 2000 domain as user accounts. newaccount - account created in acitve d...more >>

Hi All, This is driving me a little bit nuts... Using SQL Server 2000 I have two tables that have similar data. Let's call them 'Table A' and 'Table B'. They share a couple of the same columns and have a unique identifier in common I want 'Table A' to go out and look at 'Table B' and, for ...more >>

Hi Why would someone chose a stored proc instrad of a function or vice-versa? Whats the difference Thanks...more >>

Urgent! I'm using SQL2K. Could anyone help me out this? I need to construct a query which is used to extract data for inventory movement report. The report is only for a specific stock item each time. By using the report, users can easily locate the stock item at that moment. However, there'r...more >>

Hi How can I write a stored srocedure such that if nothing is found with the first select statement then use an next select statement? I want to know how to use the IF...THEN ELSE statements in stored procedures Thanks...more >>

Hi all. We have four different ERP databases, one for each location of our company, and each database has the same structure though different data. What I'd like to be able to do is write cross database queries and consolidate and move data between these different databases. But I don't want t...more >>

If Sql Server IP is : SQL200.mywebsite.com, I can retrieve SQL200 (ServerName) by using ServerProperty('ServerName'), how can I retrieve another portion through query analyzer i.e. mywebsite.com ? Best Regards, Luqman ...more >>

hello, is there anybody here who can tell me how to create linked server in microsoft sql server 2000 to microsoft access database (microsoft.jet.oledb.4.0); but access database have own workgroup file; i don't know where to place a path to this mdw file while creating linked server; and may...more >>

I am trying to run the script that came with the .Net SDK to install their sample databases on SQL Server 2000 instead of MSDE. The Categories table has the following column definitions: CREATE TABLE [dbo].[Categories] ( [CategoryID] [int] NULL , [CategoryName] [nvarchar] (75) NULL ) O...more >>

I have a mildly complex condition I would like to resolve through the use of a query. I have one table ("Scores") that contains a list of results for units that compete in our contests. That table has the ID of the unit, their score and the date of the contest. A unit may perform in contests in m...more >>

Hello! I have three or more tables, each table holds data for one month. Now I want to create a view where I would be able to select data from different months. I tried to make a view like this: select * from t1 UNION ALL select * from t2 UNION ALL select * from t3 ... .... And it would ...more >>

Hi. I'm working on a project where I design the database from scratch. The database is generated by a number of scripts, that I combine into one script, that starts like this use master if exists( select * from sysdatabases where name='DBName) drop database DBName go create database DB...more >>

I need to insert records from tbl2 into tbl here is a sample data for tables tbl col1 col 1 1 1 2 2 tab col after insert table 1 should look like this tbl col1 col 1 1 1 1 1 2 2 2 2 2 satement that I created(see below)does not do the j...more >>

Hi, We have INSERT / UPDATE forms working with our SQL database. One of the fields is a date field. Is there a tool (like a calendar/date selection) out there that will allow users to select a date (calendar format would be ideal), convert it into smalldatetime format, ready for insertion ...more >>

Hi all, I am trying to run the following query which uses a user defined function to calculate business days between two dates. The query works very slow. The contacts table has more than 100000 records but there are only 50 records with rectype='C'. SELECT recid FROM contacts W...more >>

Hi, I was wondering if anyone could help me with this problem: I've got a database with 51 tables. The first one is a control table with 2 columns: a timestamp column, which is written to every second, and a key column, which is a primary key (clustered ID). CREATE TABLE Control ( TagK...more >>

-SQL Server 2000 I insert this value 29.98 and it diplays this value 29.989999999999998 It does this majority of the time, is this something that is know and can be fix? Thanks in Advance Mike ...more >>

I want to get part of text field and asign it to local variable. Please see following Code. I get error when I try to set @str variable. Is there any way around this probelm CREATE TABLE #t(x ntext insert into #t (x) values ('123456' declare @str varchar(10 declare @ptr varbinary(16 set @str...more >>

I need to be able to programmatically get structure details about tables, PK's, etc. I've found pretty much everything I need from information_schema.tables, .columns, .table_constraints, and .key_column_usage. What I can't find is for multi-part/column PK's (indexes would fit into thi...more >>

Hi ! A very strange situation is ocurring here ! Please if someone could help me ! I'll be very thankful -------------------- FIRST TABL create table f_vendas_at sk int not null dat_refer datetime not null check (dat_refer between '2003-01-01' and '2004-12-31') constraint PK_F_VENDAS_ATU p...more >>

All, This is an unual post but maybe someone has some insight. I have a DB with 30 million rows. I need to add a column with a bit 1 or 0 depending on a condition. Is it a good design to calculate the condition and update each row individually or calculate the condition and store in a...more >>

I have a query which has a column of varchar(75) called CompanyAdd. In this field I have some values with numbers in the name , ex.'2700 Forester Lane' and another call 'Yale Blnd. LLC' I'm trying to UNION ALL on two tables with the same fields in both. I get the following error: Syntax error co...more >>

Can use SET TRANSACTION ISOLATION LEVEL to change the isolation level after the transaction has started with a call to BEGIN TRANS. ...more >>

Is there a way I can execute a DTS Package from a stored procedure or web page of any kind? I think it would be faster than fiddling around with the EM interface. I see in BOL where I can use a DTS Run utility, but I don't see any 'dtsrunui' stored procedure in my shared environment. Is the EM...more >>

I need to rebuild a couple of indexes on a table that has corrupt indexes. Am I able to run this in a production environment without affecting users? I am using SQL Server 2000. Cheers! Brett ...more >>

I am doing this from Query Analyzer Select * Into Table2 from Table1 Problem is Table 1 has 3485 records but only 1070 make it into table2. All records in Table1 are unique. ...more >>

Hi all If I have a table below PrimaryKey Name TestCode Result UnitNumber 1 aaaa %UN 12345NN 2 aaaa TOOL 1 3 bbbb %UN 11AAAAA 4 bbbb %UN DDD2323 5 bbbb TOOL 1 After I do update statment (hopefu...more >>

Hell I don't know how to make a following query: ( it must be single query ). We have table with a 2 columns 1 is autonumeric ( which is the primary key ), and in the second we have numbers of places to sit ( from 1 - 500 let's say ). Some places are already reserverd, and I need to query for a giv...more >>

Apologies for this no doubt simple question. [Our DB guy has just gone on holiday leaving me in the lurch for this SharePoint (WSS) application that needs info from SQL Server 2K] I have two tables each containing the same kind of information in fields A, B, C and D. I want to create a View...more >>

Does anyone have URL links to good articles discussing the pro's & cons of stored-procedures ? Once again, it's time for the annual religuous debate in my company on whether stored-procs are good or bad. Arguments will no doubt range from the DB storm-troopers "thou shalt not speak to the DB g...more >>

MyTable ======= ID Symbol Qty 1 A 5 2 A 2 3 A 3 4 B 1 5 B 2 Desired Output ==> Basically, I want to list "all records" plus subtotals by Symbol ============= Symbol Qty A ...more >>

I'm moving VB 6 project from Access to SQL Server. First step is to continue to use Access with linked to SQL Server tables. A made already all required changes in code. I have prepared a copy of Access database with data in SQL Server by running DTS. Then I restored lost primary keys and made s...more >>

I have a sproc on Server A that returns lots of data via select statements. When I call this sproc from Server A it takes 1.5 secs to execute. When I call this sproc from Server B (Server A is linked) it takes 9 secs to run. Server A is linked as an OLE DB provider. We use linked s...more >>

I have a field in my database where one column has null values. What I want to do is insert 40 chars into these null value fields...how can I do that?...more >>

Hi I have the foll stored pro create procedure sp_insertintopos(@col01 int,@col02 char(20))A insert into pos(col1,col2) values(@col01,@col02 when I try to execute it from Query Analyzer with exec sp_insertintopos(1,'test') I get the foll erro Server: Msg 170, Level 15, State 1, Line Li...more >>

Couple of questions: first is elementary, just suffering from a brain cramp. Have a numeric column. Need to convert the values (they are all Zeroes) from data type nvarchar to data type money. second is a little more involved. doing a bulk insert from table a to table b and have data r...more >>

If I do not have a tables data in cache and update/insert data in that table, is the resultant updated/inserted data cached, presuming there is cache space available? Acorum1 acorum1@yahoo.com ...more >>

Hello All, Trying to solve what I think would be easy to medium trigger problem that maybe one of you could help me with. Table is CustID Int CustName VarChar(35) Address1 VarChar(35) Address2 VarChar(35) City VarChar(35) State VarCha...more >>

Is there a command line utility that can be used to automate scripting database objects (e.g. all tables, functions and procs to a single file.) -- Semper Fi, Red Please post to newsgroup only ...more >>

Hello Experts, Is it safe to use these sps to rename SQL Server 2000? sp_dropserver 'oldservername' sp_addserver 'newservername,'local' Or it is better to rebuild the master db? Thanks in advance....more >>

I know this may be a strange question, but is it possible to have a database with one name say "main" then have a mirror of it on the same server such as "backupmain"? this would be for development purpopuses so we can mess up all the backup data but keep the real database intact.. thanks (live m...more >>

Hi All, How to restrict anyone accessing my db from other Undesirable applications-programs, such as Excel, Access, VB (.NET), any sql tool,... Anything that can access my db. Thanks in advance. ...more >>

"Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated." Above is the Error I got when I ran the Insert SQL statement bellow, does anyone know hot to fix the problem??? Thanks. Greg Chang insert into ent_gts_lease(lse...more >>

Hi, This seems to work but because this is the first time I do a update trigger I would like to get some others opinion if the syntax is correct. (I cannot use RI) Thanks Lasse After update of table A the trigger is suppose to update Table_B using the new value of Table_A.OrderID. CREATE ...more >>

is there any way to make paging in t-sql instead of using temp table like "select newid(),* from tbl_test" if its possible to get "row order number" instead of newid () column ,like (1,2,3....) i can use this column in where condition when filtering query as select xxx,* from tbl_test...more >>

I have installed v1 of reporting services, along with the sample reports, everything seemed to install correctly. However when I try to create a new report project in visual studio, it throws the error Project factory is unable to create instance of Microsoft.ReportDesigner.Project.ReportProjectM...more >>

Hello, I was wondering if anyone help me with a problem. Executing the following causes an error exec master..xp_cmdshell del 'D:\SERVERNAME\Database Backup files\Julie.txt', file not found. After testing I found that it happens when its in the sub directory, both the file and sub-direct...more >>

I’m trying to create a stored procedure as follows below CREATE PROCEDURE dbo.qry_View_Message_In AS SELECT Message_In.Message_In_Id, Message_In.Status, Message_In.Mode_Code, null AS Expr1, Message_In.MFrom, [Subject] & [Subject_2] AS MSubject, Message_In.Time_Sent, Message_In.Time_Rcvd, Message...more >>

In our applications we have a table named ChgMstr (Change Log) that logs changes made to each record. It includes the Field Name, Date & Time, the user making the change, and the Before and After values of the field. I am trying to setup an Update trigger that will loop through the Delet...more >>

sorry my problem not exactly about this newsgroups,hope related. in my asp project i use dll(vb) and dont set connection nothing also in asp code don't set server object(dll) nothing,is it important,some times our server process get high values(dllhost.exe,inetinfo.exe).this problem is ha...more >>

Hi, could anyone please explain what is the reason of having N before a string? It must be trivial but I cannot find any explanation in the BOL. Example: SELECT name FROM master.dbo.sysdatabases WHERE name = N'testingDB' Thanks. ...more >>

I am investigating a problem by which SQL Server reports during a set of queries 'There is no SQL Server or you don't have permission ...' near the completion of the client call. I also investigated the SQL Server log files and I found it very strange that the event 'login succeeded for user ...more >>

Hi select top 1 * from regio order by newid( The newid() function returns a guid, how does this become a 1 or 2 in the order by clause TIA JB...more >>

How can I Retrieve Server IP, Computer Name and Sql Server Name through = Query Analyzer ? Best Regards, Luqman...more >>

Hi, I wanted to know how I can a SP to return rows 1-10, 11-20, etc. Khurram ...more >>