I need to develop a WEB application with ASP e Sql-Server. Some users (always around the world) use this application also off-line with laptops (and with MSDE) also for insert and update data. Is it possible to use Replication with a server (Sql-server + IIS) and some laptops with MSDE? And how repl...more >>

New to xml.. I am trying to read in A REAL xml file using the sp_xml_preparedocument and OPENXML statement as shown in BOL. I am not trying to read in a PLAY xml file with the stuff at the top stripped out ( i.e., <?xml version=... <UpdateProcee xmlns= etc.) I cannot seem to make it work ...more >>

I have a sql server database that is hosted from an hosting company. Somehow, my log file is over the limit, so I want to delete it. I have shrunk the files but that didn't seem to make a difference, when I ran the "select * from sysfiles" I got this number for the transaction log 266488...more >>

Hi I'm trying to develop some comparisons of our current database technology with MS SQL. For a basic test, I was trying to update 100K records. Each record has 1 int field. Currently, my update test is taking for too long I'm using VB.Net SQLDataAdapter.Update method to post the 100K records...more >>

I've written a stored procedure that runs fine when I call it from query analyzer, but I receive a syntax error (without any detail information about it) when I call the stored procedure during an export operation. Does anyone have an idea on: 1) Is there a log of a detail error msg 2) Why ...more >>

Hello, This is what I have now: REPLACE my table name ('my column name', 'search for this', 'change with this') This is not working. Why not? Thanks! Arjen ...more >>

Hello: I retry to send to group, because I don't get a solution to my problem. I have some code in a asp.net function in C# like this: DateTime datetime = DateTime.Now; SqlCommand CommandEvent = new SqlCommand("spAddNewEvents", Connection); CommandEvent.Transaction = Trans; CommandEven...more >>

I would really like to make a query (I think that is the right word) to determine the average number of sales for each month (for example, Jan thru Dec). Not the average number of per month! I have a table as follows: CREATE TABLE sale ( id VARCHAR2(32) NOT NULL, item VARCHAR2(32) NOT...more >>

I am setting up a sql database for my softdrink vending company and I would like to keep track of the client who has placed the most orders over time. CREATE TABLE client ( id VARCHAR2(32) NOT NULL, name VARCHAR2(32) NOT NULL, orderdate DATE, PRIMARY KEY(id), ); Although the ...more >>

I have some sql code that I'm working on that I need to create a text file as it's output. I'm creating a text file that's more or less just a flat file, that has 01, 02 and 05 level records in it. The 01 level is a header, with a dollar amount in a field on it. Haven't figured out how to do...more >>

I need to maintain a numerical sequence, which is an easy thing to do, until some records are deleted in the middle of the sequence. In that case, I want to pick up the next available number. For example, if I have records with ProdCodes of 1,2,3,7,8,9 then 4 would be the next number. I know tha...more >>

Here is some sample data, I was unable to determine how to use that sp file on your website, hope this works. As you can see some have been rented(this is determined by the Item.Description field being replaced by the due date, which is a string and addition of '2DY....' ) and some returned(w...more >>

In SQL Server2000 I want to try to create a trigger or at least a stored procedure that uses the following select statement which pulls all the column names from a single row in a table: We'll Call it A SELECT name FROM syscolumns WHERE [id] = OBJECT_ID('dbo.A') To cr...more >>

Hi, This is the connection string I am using: objConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=FormsDevp;Data Source=CSPL;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PERI;Use Encryption for Data=False;...more >>

I've been trying to optimize my project so it doesn't open up too many ADO connections. With cmdTemp .ActiveConnection = gDBConn.cn .CommandText = "GetOperator" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter(, adInteger, adParamInput, 4...more >>

Here's my situation: Table: Customers Fields: CustomerID, Sales, Rank, Percentile I need two queries: 1) First query will determine each customer's current Sales rank and place that value in the Rank field. 2) Second query will divide the Rank by the total number of customers in the...more >>

SELECT InvoiceNum FROM Invoice WHERE (SOID = 178) AND Invoice.InvoiceNum NOT IN (SELECT InvoiceNum FROM JobHeader WHERE InvoiceNum LIKE '11896%') We get 7 rows. However, if we run SELECT InvoiceNum FROM Invoice WHERE (SOID = 178) AND Invoice.InvoiceNum NOT IN (SELECT InvoiceNum FROM...more >>

I'm looking for a way to have my MSDE database creation *.sql scripts told where to write the *.MDF and *.LDF files. I am using osql.exe to execute the database script once MSDE is installed. So basically, is there a way to have a *.sql script pull values from environment variables? If ther...more >>

At some point in time, before I came on-board on my current project, someone came up with the idea to implement the following to handle optional parameters in stored procedures - assign a default value of NULL for the parameter and then use the following in the WHERE clause for that parameter - ...more >>

I have a table with columns A, B, C, D, E I have a clustered index on A I have a non clustered index on B, C I have a query SELECT A, D FROM Table WHERE B > 1 AND C > 2 and SELECT A, B, C FROM Table WHERE B > 1 AND C > 2 I obviously want it to use my index on B, C for these quer...more >>

1st question: I have inherited a database where a lot of the tables have no clustered indexes (the primary keys have been declared as non-clustered). There are no good candidates for clustering on some of the tables which is why the original developer defined them as non-clustered. Shoul...more >>

i have a SP that takes between 2 & 8 parameters depending on what the user enters on screen the example only uses 3 parameters ************************************ CREATE PROCEDURE PHAR_ReportPDA_IR @PatientID int, @FromDate date, @ToDate date AS SELECT I.InterventionDate, I....more >>

when I do sp_who2 , I see CHECKPOINT SLEEP in Command Column, it takes about 1027546 cpu time, and 4038 DiskIO. What is CHECKPOINT SLEEP? if it's sleeping, how come it still takes so much cpu time and diskio? Also, what is LAZY WRITER? ...more >>

I want to use number position in a select.. for example its possible this?: SELECT 1,2,6 FROM books where 2='xxxx' order by ...more >>

I am trying to find a way to build a class library using vb.net that can be called as functions from a cell in an excel spreadsheet. I want to be able to create a formula in a cell that calls my function and returns a result. I found a bunch of articles on how to build add-ins for excel, but none of...more >>

There is an existing table that contains an Identity column. A script is run to delete the data and then re-populate the table. Before the script is run I may have to change the seed of the identity column. I do not want to drop and recreate the table or the column. I simply want to change the seed ...more >>

Hi all, I am having a big problem with an aggregate query that I am currently = building manually (in VB.NET), but this is totally killing the = performance so I am hoping this is possible using an SQL query. I am not = new to SQL, but this query is way over my head. Here is what I am doing: ...more >>

In my two tables in a one-to-many relationship, table A is the 'one' and table B is the 'many'. Table 1 is subm_no col2 col3 127662 ABC XYZ Table 2 is subm_no peril_id 127662 1 127662 11 127662 13 How can I produce a result set containing a single row, one column of which...more >>

I'm trying to add preceeding zero to a field that used to be a number field which i've converted to a text field. Sample Data would be something like this below: ID 2 15 101 128 11115 1057561 12345678 I want to add zeros up to the count of 8, so the sample data would look like this...more >>

It may be argued that truncating the original and running an INSERT would be more efficient, but I am required to create the table afresh each time. There will be some occasions where the table may be already dropped. If that is the case, I'd like to skip the DROP TABLE command and go straight to th...more >>

Hello, I'm looking for a way to get a list of all the tables in my database which contain a field named UserFld1. Of course, in different tables, UserFld1 represents different things. But I'm trying to put together a list of all the tables that can (and are) customized with these "user fields"....more >>

I'm creating individual user tables dynamically that have different field names and values. Then users access the dynamic tables with a web interface. One of the ASP pages has several nested queries that itterate through the dynamic table. Users can choose to query all ATTRIBUTES for t...more >>

...more >>

What is the accepted method for determining if the database you're connecting to is the correct type of database That is, if there can be any number of instances of your database and other unrelated databases on a server, how do you know you're connecting one with the schema that the software is e...more >>

I have made simple stored procedure for testing sqlxml3 in soap mode. Client is vs2003 (c#). Invoke method fails with the following message. What can be wrong? Additional information: Client found response content type of 'text/html', but expected 'text/xml'. The request failed with the err...more >>

Using SQL, how can I get the NetBIOS domain name that the server is running on ? Thanks. -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

I know it's probably real aesy but, how do you insert 200 rows of the same exact data I want to run this statement 250 times use itdatabas insert into itdata ([IP Address] values ('10.1.2.x' Or is there a way to insert this data 250 times but add 1 to the end of 10.1.2.1 for each insert r...more >>

Hi all... When I do as below, can i set var1 as variable? Set var1 = 'AnyName' SELECT SUM(CASE ColKey WHEN @VarColKey1 THEN value1 ELSE 0 END) AS 'AnyName' I give me error when i try below. can I do this way??? SELECT SUM(CASE ColKey WHEN @VarColKey1 THEN value1 ELSE 0 END) AS @...more >>

sorry if this is the wrong sql server NG. I often meet rdbms DBAs and when the topic of oracl vs sql server comes up (it always comes up :-) the oracle people say that sql server can't handle the big user loads. By big I guess I mean like 20,000 concurrent users or more. Now my number might ...more >>

In a multi user situation users can create a View with the same name and a View may exist that is owned by dbo so I may have something like dbo.MyView john.MyView jane.MyView If paul comes along and does a query I assume he will get dbo.MyView. I'm looking for a way that paul can know if...more >>

Hello, SQL Server 2000. I'm have built a couple of Views with same column names and types. I need to make a UNION of them and return this result set from a Stored Procedure. It is possible that one of the two used Views stay empty and, in this situation, the UNION fails (returns zero recor...more >>

Hi there, I want to have a "password" column on my users table, and make comparisons on it case sensitive. currently my (WHERE userpassword = @password) check is case-insensitive. Do I have to change collation for that to happen? Will my index be effected? ...more >>

I use CCommand to execute a stored procedure with the following definitio CREATE PROCEDURE spSampl A SELECT *FROM tblSampl RETURN @@ROWCOUN G I use the ATL Object wizard to create an accessor to retrive data. However, the m_RETURNVALUE always give me 0 even if I have multiple records returne...more >>

Hi all, i want to write a asp.net web application that is capable of generating .sql script file with db shema and data and all objects. Any idea where can i start? I have looked a lttle at SQLDMO, can it be done with this COM interfase? Thanks ...more >>

I am getting close to done on a new project. It is time now to think about how to create and deliver updates. What I would LIKE to do is with every update deliver the new code plus a script for the newer database that might have additional fields, or field was lengthened, etc. Then somehow ...more >>

Hi I'm receiving "A domain error occurred." in one of my stored procedures in SQL Server 2000. The error is caused by an introduction of a POWER function use. I can remove the POWER function and the error goes away (my calculation is not accurate but it works). Here is the portion of t...more >>

Hi all, How can I get the result like this withou display this message or any better ways to rewrite this queries. Thank you very much for your help. Warning: Null value is eliminated by an aggregate or other SET operation. ----------------------------------------------------------...more >>

We were looking for some pointers to a provider or a package which will let us convert our time values in local times across the globe to GMT. We can do it for a year but we were looking for a package which gets regular feeds annually for each new years Daylight offset values. Is there an...more >>

Hello to all I'm having an issue with vb not returning the same results as qry analyzer. I am using vb6 to call a sproc and a recordset is never returned yet when I run the qry in qry analyzer it works as advertised. My vb6 code isn't the culprit, at least I don't think it is because I took the qry...more >>

First, here's the query: SELECT T.TKT_TICKETID, T.Orig_Source,T.Contact_CustID,T.orig_CustID AS OrigCust, T.Orig_TimeStamp, T.Actual_TimeStamp, MAX(H.TIMESTMP)AS HistTime,T.BriefDesc, T.LongDesc,T.InternalDesc,T.Environment,T.AssignedEmployee,T.StatusID,T.Curr ent_Source, T.CustomerCode AS ...more >>

I have a table with part numbers and prices. I have a text file with part numbers. I want to run a query so that it returns only the part numbers from my text file along with the corresponding prices for those part numbers from the table. Once I get this result, I need to increase those pric...more >>

how can I convert this data: 21.03.2003 to 3rd of March in reporting service? ...more >>

Okay, I'm trying to modify a tables primary key, actually, I'm trying to get rid of it. According to everything I've read, this is the correct syntax, but it won't work. I'm using the following statement: ALTER TABLE table_name DROP PRIMARY KEY; Any ideas? Tom ...more >>

Hi I have a problem and I hope somebody could help getting a solution... I'd like to describe the problem in an abstract way: There is a given table with the following fields id (Primary, int) name (varchar) valid_since (datetime) The table could have the following rows inside: 1, Te...more >>

Hi group, I want to see the discussions of the SQL Server group in the NNTP format. However, while I can see the posts in this Programming group, I can not see those in the SQL Server group. Is there any setting in Microsoft Outlook Newsreader that I need to change? Thanks. ...more >>

how do i find what ones of my of my tables that are heaps? ...more >>

This is what I have: **************************************************************************** ******************** DECLARE @MySQL varchar(255) -- Copy data from MyTable to MyTable in another database SET @MySQL = 'INSERT INTO MyDb2.DBO.MyTable SELECT * FROM MyTable" EXEC (@MySQL) ...more >>

We have a table in our SQL Database that records when Users delete certain rows, or change certain types of data afer certain other events have occured. We use it to trace what users are doing. This table contains and Identity Row called id, which increments by 1 every time record is added to ...more >>

Help!!! I'm trying to insert data to ServerA from ServerB. I can't figure out the syntax to set this up. I have tried sp_addlinkedserver and sp_addremotelogin but still get an error indicating "login failed for user sa." Any help would be much appreciated thanks PP...more >>

Is there any way to remove the transaction log from a database? We don't use it at all and it's taking a lot of space from our servers... Is there any option or procedure that can remove it? Thank you for your time ...more >>

I want to copy a row from a table, but I want to set the value of one field explicitly, can someone help me with the syntax.... In otherwords, I want UserID to alway be equal to 2. INSERT INTO tblHoldQuotes ([CompanyID], [UserID] as 2, [ContactID] FROM [dbname].[dbo].[tblQu...more >>

I would like to have a column in any query wich containes the row number, for instance, when i write 'select name, description from tb_product' i get the following resultset: rownumber name description 1 A01 wallpaper 2 A02 blown vynil 3 A03 ... ...more >>

I have two apllication connecting to SQL2000. First use ODBC, second - OLEDB. When the server is Windows2000 + SQL2000 everything is ok. But when the system is Small Business Server 2003 (Windows2003 + SQL2000) there are too many deadlocks on transactions. Have you any idea where should be pr...more >>

Hi everyone. Based on the "exec into" post below, is it correct to assume you can't do "select * from table1 into table2" if table2 already exists, correct? Does anyone know of a way to get around it? What I have is table1: create table #table1 (ordernum int, mytype varchar (10), amount int...more >>

HI all I have this table: CREATE TABLE [dbo].[Table2] ( [PKID] [int] IDENTITY (1, 1) NOT NULL , [CatKey] [int] NOT NULL , [ItemKey] [int] NOT NULL , [value1] [int] NOT NULL , [ColKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO and it has data:...more >>

I want to write a stored procedure that takes a where condition as an argument and need to check it against a pattern and change it if it matches. My (simplified) code is below. My problem is using LIKE on the variable as it returns false when I'm expecting true. example: if @whereCondition ...more >>

select 1 as one into #tbllookup .What this will d Can u guys explain the meaning of thi thanks in advance...more >>

H Is there any simple syntax for executing a stored procedure INTO a table For instanc select * into procresults from (exec sp_getmydata ??...more >>

I need a select statement that does the following: ID Parm1 Parm2 Rev DataID 1 1 1 1 1 2 1 2 1 1 3 2 1 1 1 4 2 1 2 1 5 2 ...more >>

Table Attribute Type Notes Species Sp_name C(10) P.K. (Primary Key) Sp_woodtype C(10) Sp_maxht Int Forest Fo_name C(10) P.K. Fo_size Int Fo_loc C(10) Fo_comp C(10) Tree Tr_species C(10) F.K. Species.sp_name Tr_forest C(10) F.K. Forest.fo_name Tr_numb Int P.K. Tr_plan...more >>

Hi, I really need to find a method of returning the highest value in a table....without using the Max() function. Any method would do....even if it is inefficient:) Thanks for your help!...more >>

i have added a column in a table using ALTER Table command. By mistake i kept the column name 'TRANS_COUNTER' instead of 'Trans_Counter' As column name case is important to VC++ programmers. my script has chage about 100 tables how can i change the column name to 'Trans_Counter' from 'TRANS_COUN...more >>

I=B4m using a SQL Server database to read several data in a=20 SQL Server DNS and put the information in MS Project=20 Fields and I have a problem. First I declare a variable:=20 @testvar see below... =20 1. declare @testvar varchar(30) 2. set @testvar =3D (select param from table1 where=20 v...more >>

I want to use the following to copy a row in the same table that has 49 columns.... INSERT INTO table (column1,column2...) SELECT column1,column2... FROM table WHERE (something); Is there a way I could exclude just the identity column like.... INSERT INTO table SELECT [All field except ID...more >>