Hello, Firstly, sorry for my english. I have problem with creating SQL statement. I am beginner and I think that it is very easy to do. Look - I have to get only the last 20 records from table ABC (f.eg.) and according to that how to create the SQL statement? F.eg. SELECT name, forename, date...more >>

I set up the MS SQL Desktop server engine on my computer. I successfully set up a database and the table structure using a query utility. My Visual Studio logs in to the server using a system account that magically exists. but... I can't make my web application log in to the database. I c...more >>

Hello to all I need help on some sql statements and wondering is anyone out there is able to help. I've extracted some data from the database into a new table consisting of 4 fields CustomerID Date Pallets Quantity A0001 20050108 3 800 A0001 ...more >>

Hi, all: I'd heard that the upper row limit in SQL of 6080 bytes may have been increased with SP3. Can anyone confirm/deny this? Is this still a 'carved-in-stone' upper cap? Thanks, DW. ...more >>

I'm doing a select which includes the following: case when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null) then '' else convert(datetime,left(replace(replace(T464.COMMENT_4,' QTR: ',''),' - ',''),10)) end as QuarterStartDate The COMMENT_4 field is a char(51), and contains ...more >>

Will osql product an SQL backup? By this I mean a text file with the transact-sql in it so I can use it to recreate the database elsewhere? Thank you. ...more >>

I have a query where I am trying to export a column that has around 2000 characters and it is truncated. What is not setup in MS SQL correctly so that DTS will export the csv correctly. Thanks, John ...more >>

Hello, we are investigating the use of SQL Server as a backend to our scientific imaging application. We have found that when we write a large image (60 Megabytes) the performance is quite a bit slower than writing 60 single megabyte images. The tests were performed running SQL Server 2000 on Wi...more >>

Hi All! I've Win XP Pro with SP2 installed. I've also ms sql server 2000 installed. The problem is that sqlserver doesn't listen on port 1433. There is no slq server listening when I check netstat -a. I opened 1433/1434 ports on Windows firewall but it didn't help. I set TCP/IP in enabled p...more >>

I'm wondering if someone has thought about this, one desires to enhance his/her sql skills proactively vs. skills gained over a period of time merely because of work experience. SQL skill here refers to mainly MS SQL Server related skill. What could be reasonably effective ways to go for th...more >>

Greetings All, I have a excel file which is originally a sqlserver table that was exported as a excel file. I have added more data to this excel file and now want to import it again to its original table,i.e, it will overwrite current data in the table but with no change in the schema. How s...more >>

Does SQL Server support the semi-structured data model? How can XML documents storage and retrieval be efficiently exploited on SQL Server? Where can I find more info on the semistructured data model implementation on SQL Seerver (if any)? Thank you. ...more >>

Hi all. I'm looking for a tool which should act like some kind of middleware/ logical layer bewtween the SQL server and the webbased user interface. - It should be possible to easily create simple web forms (only data input and output) without programming effort by "clicking" the fields and...more >>

Folks. I screwed up big time, I deleted a very long and smart stored proc (pls don't ask how). Is there anyway I can recover it? Any advice appreciated. Tada. ...more >>

I have two tables (tblRequest and tblDev) whose items have many-to-many relationships with each other. I have set things up like this (simplified): Table 1 fields: tblRequest.PrimaryKey tblRequest.Description example: 10 45 Elm 11 ...more >>

I have two tables (simplified example): table person PK data 1 A 2 B 3 C 4 D table choice PK FK 1 1 2 1 3 1 4 2 5 2 6 2 I need to count the total number of people that made a choice. Each person should only be counted once, regardless of how many choi...more >>

I need an IF statment at SQL I have 2 tables (names A B) i need (In SQL stetments) to search from table A if rows in table B with this conditions: if B.col1=1 or B.col1=6 then search all row (1=1) otherwise select A.col1=B.col1 this select is part of a big select from A,B tables Best ...more >>

I'm trying to select records from two tables. the following code works for what i want to to: set RSMain = conn.execute ( "select top 20 product, prodcode, edition, ( select count(id) from cartTemp where convert(varchar(4000),cartdetails) in (a.prodcode)) as counter from Products a where ...more >>

Hi I am trying to copy a 20 GB database from server 1 to 2. I took a full backup, copied to server2 and restored ( ready for use) using EM. After the message restore completed, I see the database's status as LOADING. We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv Servers. I ra...more >>

Hi, How can if include If statement in SQL update ? What I want to do in english is this: @param1 int update table1 set field1=getdate() if @param1=3 MTIA, Grawsha ...more >>

Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with each having 3 fields each, their own PK; the FK back to the parent table; and the unique data for t...more >>

I have 2 tables (names A B) i need (In SQL stetments) to search from table A if rows in table B with this conditions: A.col1 containe 4 char. B.col1 containe 4 or more char need all the records that A.col1 IN B.col1 Best Regards, Yuval ...more >>

Hi. Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I write my own variation of a such a function. If I can appreciate how to do this, then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or a more general function like a 10% percentile function with syntax such as ...more >>

Hi. A question I have is with regard to the use of views with SQL2000. If I have a view called "A_view" and used in the following manner; ---------------- SELECT ... FROM A_View WHERE .... UNION SELECT .... FROM A_View WHERE ..... ----------------- is the view computed twice? ...more >>

Hi. Let me tell you my domain environment. It's NT 4.0 domain with a Windows 2000 member server running as ASP.NET/IIS/MS SQL 2000, all in one box, and it worked fine. Recently, we bought a new box with Windows 2003 standard version and trying to move ASP.NET and IIS to this box. Howev...more >>

Dear Group Just can't see what I'm doing wrong. Any Ideas? SELECT Address1, Address2, Suburb FROM i2b_address IsNull (NullIf (Address1 + '', ''), '') + IsNull (NullIf('','' + Address2,''),'') + IsNull (NullIf('', '' + Suburb, ''), '') AS PropertyAddress Server: Msg 156, Level 15, State...more >>

I can get the debugger to work: I get a message: "sp debugging may not work if you log as as Local System Account" while SQL Service is configured to run as a service. You can open Event Viwer to see details." (Viwer means "Viewer" this is not one of my typos...) My text book says that this me...more >>

Hello, I need to set up a development platform at home with a copy of a DB from a SQL server at a friend's company. So far, all my attempts at backup/restore or export/import have failed despite my reading all the documentation I could find (I am a newbie). Using DTS/Export wizard, I trie...more >>

How execute extended proc on stopping ms sql server (it mean before stoping). Is it possible to handle this? Or is in system proc a'la sp_on_stop? Thx ...more >>

Hi everyone, My brain refuses to remember the (undocumented?) stored procedure I'm thinking of. It takes at least two parameters: a sql statement to execute, and a table name (or something of that nature). Then, for each value in the table, it executes the sql statement and passes the valu...more >>

I am trying to export a grouped report to PDF with the group section expanded. I can't seem to accomplish this. Can anyone provide any suggestions? Thanks ...more >>

Greetings: I have a SQL 2000 server with several databases on it, and I have a maintenance plan that includes both a backup and a CHECKDB Integrity Check. The backups appear to run correctly each night, but if a user leaves a connection open to a database all night, the CHECKDB fails. I'...more >>

I am using SQLServer2000 with sp3. The SQLServer logs on as user Test on the local computer. I am logged on as user Test on the local computer. I have created a Exchange Profile and tested that it can send and receive mail. When I go to Enterprise Manager to set-up this profile for SQL M...more >>

First off I am a rookie at Sql Server. Ok let's give this a try. My company has bought a new software package called Viewpoint. It's On Sql Server and written in VP. We do not have access to the code. There is a option in the software package called "Application Security". When this is clic...more >>

Here is what I need to accomplish iif(len(sMemoText) > 15, left(sMemoText, 15) + '...', sMemoText) How to convert this into a case or anyother way to get this into a select statement... Thanks MUCH! Jeff ...more >>

Hello- I'm fairly new to writing SQL statements and would greatly appreciate some help on this one. I'm working on a project for a non-profit that I volunteer for. Part of the database tracks membership using tables like this: PersonInfo ------------------- PersonID (primary key) Fi...more >>

Hi, I'm in the process of designing a DB (typical management system DB; 2 transaction tables and about 5 look-up ones )for one of the departments in our company. The user wants this DB and thusly the client (forms, reports..etc.) solely for his department. However, I do expect soon after de...more >>

I am trying to find all affiliates that have more commissions from this week to the prior week. The problem is in the having part where "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like trying to use "aff.affiliateid" to refer to the parent table. The error I get is "Invalid...more >>

Hi, We are going to be running two SQL Server 2000's from the same physical server, with Windows Server 2003, and I need to have certain tables between them syncronized. Database A is a backend to a website and database B is going to be used by a different department. Both the da...more >>

I would like to send the contents of a file using xp_sendmail however I do not want the file contents to be an attachment. I have no problem sending the file as an attachement. Can anybody give me an xp_sendmail example of how to do this. The results of a query can easily appear in the body of t...more >>

Does anyone know how to run reports (.rdl files) in VB.NET? I am trying to run multiple reports in batch and have only the last one display. ...more >>

Hello there! I am working with MS SQL Server 2000. I have a table function that takes an integer parameter and returns a table, and I can successfully use it like this (passing a literal as a parameter): SELECT * FROM MyTableFunction(1) or like this (passing a variable as a parameter...more >>

Hi all, I'm a newbie to SQL and I need help with investigating what ways are possible to build an interface of some sort that wraps around a SQL query script. I have a simple SQL query which we normally run inside Query Analyzer to update certain fields in the SQL DB based on FQDN provide b...more >>

When I add this code in a view and try to save . . . SELECT TOP 610 * FROM dbo.Master INNER JOIN dbo.TypeByCase ON dbo.TypeByCase.CaseNum = dbo.Master.CaseNum It gives the error: ODBC error: [Microsoft][ODBC SQL Server Driver]Column names in each view or function must be unique. Column na...more >>

In my application I need to make the report in different languages. I made a table for report parameters, like: create myTable ( language_id int not null, parameter char(50) null, constraint PK_lang_ID PRIMARY KEY (language_id)) and then tried: insert into myTable values(1, 'Bank info') ...more >>

I have a VB6 program on my PC that connects into and looks at a slq server 7 table on a development server. This server is rebooted everynight. Everytime the connection is interupted, an error message pops up. This is expected to happen, but I wanted to delay this error message from popping u...more >>

I was browsing Microsoft's SQL Server site, looking for some details about SQL Server 2005. Didn't find what I was looking for... I'm thinking about moving an existing SQL Server 2000 workload to a new box, using SQL Server 2005, and maybe the 64-bit version. My questions are: 1. What ...more >>

I have created a stored procedure that contain this field (below) in order to meet certain criteria. But my problem is when I try to run the stored procedure I encounter an error "Error converting data type varchar to float". CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGrade The Fina...more >>

I have some long running scripts which I fire at my database using osql. (These are big files and mostly doing inserts but some also do a few other things.) It would be nice to have some activity indication (other than the disk activity light) that these are running. When I used to use Oracle,...more >>

I have changed the following setting in SQL Server 2000: Right click SQL Server Agent and go to properties/Job System and deselected limit size of job history log. I want to keep all job histories in msdb indefinitely. Is there a way to leave this setting on, but configure how much history s...more >>

In MS SQL 2000, I would like to obtain the timezone offset from UTC for a given date. For today's date, I can do DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future date may not be the same as today because some countries go in Daylight Saving mode. Can you suggest a way to obt...more >>

Hi, This is probably a trivial question, as I am simply trying to execute a BACKUP LOG database WITH NO_LOG. Except T-SQL will not accept anything I type when database has a - in it, for example data-base. I have tried: BACKUP LOG 'data-base' WITH NO_LOG BACKUP LOG "data-base" WITH NO_LO...more >>

Hola a todos: En mi base de datos tengo dos filegroups llamados FG1 y FG2. Recientemente he creado una tabla y por defecto quedó alojada en el filegroup PRIMARY. ¿Cómo puedo hacer para mover la tabla íntegramente con todos sus datos desde el filegroup PRIMARY hasta el filegroup FG1 por ejem...more >>

What exactly is happening when a query is sent using the N in front of the string to be found? Under what conditions would someone use the N' in a query? I have been testing out some chinese text. I set up some fields of nVarchar, nText and it works with an N. Without the N, it wont work. N a...more >>

John Almeda System Adm It seems that I have done something really stupid or something went boonkers. I was restoring a DB whn I got a message that the log file could not be restored because it was XXXXXX????. This left my DB greyed out and with a (loading) sign. I tried to detach and then...more >>

Is there a whitepaper or other documentation that talks about SQL2000 performance limits? For example, transaction/sec limit? (Which I understand that hardware config would be a factor) thanks ...more >>

I have a table that tracks GPS records broadly speaking as follows ts DATETIME username VARCHAR(16) ... GPS data ... I want to select the most recent GPS data for each distinct user. That is, the table will have may records for a given username, but I only want the most recent for ...more >>

After install Oracle 8.1.7 on Win XP, message "strtdb80.exe has encountered a problem and needs to close.." occurs after restarting comoputer.. Any solution??? ...more >>

What am I doing wrong in the statement: q = "Select * Into etclog_holding from etclog where box# BETWEEN (" & Box1 & " and " & Box2 & ")" It gives me the following error message: Incorrect syntax near the keyword 'and' the following code is what i am working with but it is not working as ...more >>

Hi, I have an SQL assignment to do and at my school we use SQL *Plus there however I don't have Oracle at home, where I would like to do the work ,so I was wondering whats the easiest way to get an SQL environment up so I can code in that then just paste it into SQL *Plus later. I don't ...more >>

hi here is a problem: i have a databes with many, many tables the problem is that i dont know where 'abcd' string is (but it is for sure in one of that table) is there any SELECT that could help me with finding this string in database? -- greets ...more >>

Hello All, I have an issue with dupliate Contact data. Here it is: I have a Contacts table; CREATE TABLE CONTACTS ( SSN int, fname varchar(40), lname varchar(40), address varchar(40), city varchar(40), state varchar(2), zip int ) Here is some sample data: SSN: 1112223333 ...more >>

I would like to compare data across two tables. I have part information in a table. I get a new set of information periodically. I would like to compare my new info to my old info. I recognize that doing a compare of every attribute of every part will take FOREVER. Is there some way I can do...more >>

Hi, I have a problem I would really appreciate help with. I am generating dynamic SQL and need to optimise it. The specific example I am trying to optimise looks like this: SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE (EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND...more >>

I am migrating a student database from Access to SQL Server. In Access I have a query that displays grade information (grades are calculated on a 12-point scale). In the query I average the students' scores and store it in a column called Avg. I look up and display the equivalent grade letter us...more >>

At the moment I only know enough about SQL Server to create the databases I need as a backend for the various internal desktop/web applications I write. I haven't ever had the time to get seriously involved with internal workings of SQL Server and we are far too small to employ a DBA. With any...more >>

Is it possible to take backup of database for 1 day change? also possible to restore it back for the particular date? for example.. In one month data of database I want to take a backup for changes in 13 of the month. if required restore it back. ...more >>

Dear Group I wondered whether there's a function or script that will show me whether a user is currently logged-on to a MSSQL 2000 database? I'm using SQL Authentication. Thanks very much for your help & efforts! Have a nice day! Martin...more >>

How do I format an integer. Add commas. 1234565 1,234,565 TIA...more >>

I have a query of the kind brains of this group. Is it possible to access a microsoft sqlserver database (for select, insert, update, delete, and for miscellaneous administrative actions such as table and index creation and deletion) remotely from a compiled C program (and/or, from Perl) runni...more >>

Grrr! I'm trying to run a script: print 'Declaring cursor' declare cInv cursor forward_only static for select distinct inv.company, inv.contact, inv.address1, inv.city, inv.state, inv.postalcode, inv.cmcompanyid from dedupe.dbo.ln_invoice as inv left join dedupe.dbo.custom...more >>

Using SQL Server 2000 SP3a, I run the following in 2 query analizer windows on the Northwind database, the second one always gets the deadlock Msg 1205: Window 1: declare @cnt int select @cnt = 5 while @cnt > 0 begin begin transaction select * from orders (updlock) where employeeid = ...more >>

Does anyone know an effective way to capture any records that failed to insert into a table when inserting from an xml document in SQL Svr 2000? thanks. -ak ...more >>

To any and all; I have a very large table (16Mil+ records), for which I want to delete about 8 Million records. There are a few indexes on this table. Is there a way that I can run either a query or a series of queries that will run against each record and delete based on criteria (date)? ...more >>

I am trying to script out the creation of database scripts. I am trying to use @@servername in the statement. I found out the select @@servername returns NULL. I used sp_dropserver to drop any servernames first, restarted SQL, ran sp_addserver 'servername' to add the servername, restarted SQL. s...more >>

Hi all, I am a newbie to sql and I need your help. I want to update column (email) from one table to another validating the Customerid column in both table. Update the email address in production table with the email address in temp table if the customerid is same in both tables. What wo...more >>

Does anyone know if SQL Server 2005 will support date values before 1/1/1753 in the datetime data type? ...more >>

Hi , Have a Visual C++ app that use odbc to access sql server database. Doing a select to get value of binary field and bind a char to that field as follows , field in database in binary(16) char lpResourceID[32+1]; rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, &lpResourceID, RESOURCE_ID_LEN_PLUS_...more >>

Hello, I have a SS2K (SP3) that is appending very wierd looking error messages to the SQL Server Log (Current). The messages are not formatted as other log entries. The messages suggest some kinda dump information. The messages have the following characteristics: Date Information is inval...more >>

Hi, I am designing a SQL server backend, MS Access (ADP) frontend application and I came across an interesting problem. Lets say I have a table T with the following fields(columns) [a] (int Not Null, Primary key, Identity auto increment) [b] (uniqueidentifier, Allow Nulls) [c] (text) ...more >>

Env: SQL Server 2000 The following sql stmt seems to find a particular table's size programmatically: select top 1 [rows],rowcnt from sysindexes where ID = object_id('aUserTable') and status = 0 and used > 0 However, a) I'm not 100% sure of its consistency; b) Both [rows] col and [...more >>

If I use attach/detach to move my log file to a different drive, will it break any of my permissions for the sql logins. If so, how can I fix this. This all stemmed from needing to setup log shipping to another server for redundancy. Step 1 says to create a share where the log files reside. W...more >>

Are there any conditional aggregate functions, such as SUM()? An example would probably be the best way to describe what I'm trying to do... I have the following table, named Orders, with the following records: ItemNo qty_ord paid ------ ----------- ------ T101B 1 199.0...more >>

Anybody know how to backup a table with particular range. Ex one column contains date.. I need to backup only data belong from date to date. is is possible via programming in C? if yes give example ( don`t tell backup SQL command) RLN ...more >>

Group, Passing inline values to a udf is straightforward. However, how or is it possible to pass a column from the select of one table into a udf that returns a table variable in a join to the original table. The goal is to explode the number of rows in the result set out based on the res...more >>