I am trying to specify a date as a criteria in an SQL statement used within an SP. The date is an input parameter. When I run the SP I get an error "Syntax error converting datetime from character string." The declaration shows: DECLARE @sdate datetime DECLARE @stype char(2) DECLARE @regyear...more >>

I am trying to build an SP (see code below) but I get an error when I check the syntax. The error msg says there is an "error near ')' I am using doubled single quotes to get a single quote in the result so I can enclose the text and date variables in single quotes in the parsed statement. BT...more >>

We wanted to create a database for a jewellery manufacturer. A common table will be required to store all sort of raw materials (e.g. different type/grade of diamonds, stones, metals, and etc.). Due to different type of material uses different UOM (unit of measurement), we created a header table ...more >>

Hi, Can anyone advise regarding possible ways to search PDF files stored in SQLServer 2000 SP3 as binaries? Thanks and regards, A ...more >>

I just imported data from my excel file into SQL. It created price column type as nvarchar automatically. First I tried to update my pricelist table update pricelist set price = abc.price I got the above error. Then I created another column in ABC table as numeric type ...more >>

Common sense tells me that it's impossible to delete only one of two identical rows in a table using DELETE (without using SET ROWCOUNT, at least). Here's a repro where that happens. What's going on? Table tb1 has two 2's in it before the delete, and only one after. create table tb1 ( ...more >>

Hello, I would like to show how are the performance differences among a Query, a Stored Procedure and an Extended Stored Procedure inside SQL 2000. To do that I would like to use the same math calculation using the 3 methods and compare the time taken to execute each one inside the same SQL ...more >>

Can any one tell me more about the GUID on how it is actually generated and why it is always unique? ...more >>

Hello, Are there times when you would like an expert to tackle a complex chunk of SQL development? Do you ever wish that there was someone with lots of experience who could help you with a problem that can't be solved by a simple question on this newsgroup? I sincerely appreciate all respo...more >>

BlankHi friends, I know that this an awkard question but I really want to know how to post a reply to a particular message in the group using OUTLOOK express Amit ...more >>

Hello, Are there times when you would like an expert to tackle a complex chunk of SQL development? Do you ever wish that there was someone with lots of experience who could help you? I sincerely appreciate all responses I get! Thank you for your response! Richard Rogers http://www.SQLSo...more >>

Has anyone had experience with trying to backup a database where the path to backup to consists partially of a hard- coded path but also partially of a variable (date and time, in this case)? It works fine when the entire path is hard-coded but the minute I try to make part of the path vari...more >>

Anyone know a workaround ? I believe that I am unable to make updates to an SQL table using MS-Access because no Primary key exists in the SQL table (can we please leave that to another discussion.) Is there any way to get around this ? ...more >>

Since I rarely use the update statement with a 'from' clause, I just wanna make sure that my understanding is correct. Suppose I have database table "Table", with columns A (Primary key), B, C, D. It contains 3 rows. A B C D A1 B1 C1 D1 A2 B2 C2 D2 A3 B3 C3 D3 My goal is to copy d...more >>

i write storing procedure and i need select column to determine order of result: CREATE PROCEDURE show_orders @prefix char(5), @lim_d int, @lim_u int, @ord char(20) AS select nr, data, nazwa_produktu, termin_realizacji, ilosc, odbiorca, status, stopien from zamowienia where (nr>@prefix ) and...more >>

Trying to learn OpenRowSet, I pasted the first example directly from the Help file: SELECT a.* FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass', 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a and am receiving the error: "Line 2: Incorrect syntax near ';'." Any ...more >>

Hi, I am wondering what the best way is to flatten the following table in one query. LASTNAME COURSE MILLER 123 MILLER 432 MILLER 231 I am looking for the results to be like: LASTNAME COURSE1 COURSE2 COURSE 3 MILLER 123 432 231 Thanks in advance!...more >>

Hi, I have a procdure below in which I am trying to find all of the dates that fall between a date range, this is working fine but I want to send an email for every row and then exit. At the moment it is sending email but it seems to be sending to many, for example if the query returns 2 rows ...more >>

Hi, I've got the following problem: I'd like to write a binary stream directly into a field of a MS SQL server table. The application, which produces the binary stream, calls anASP page. The ASP page reads the binary stream and writes it into the field through the following SQL statement: IN...more >>

Once a month we upload a huge amount or transacctions into a Sql Server 6.5 database. There is no need to log all these transactions and the log grows so big we can an do get an error message, lof space full. Is there a way to turn off transaction logging in 6.5?...more >>

Thanks a lot ...more >>

Hi, I'm trying to create a simple view on a table containing a long datefield CREATE_DATE. The view should show a count(*) and croup by CREATE_DATE grouped on the date (discarding the time info) I have experimented with CAST and CONVERT without much luck. Any help appreciated, reg...more >>

I have a simple user defined function I want to use as a SQL 2000 computed column. Is it even possible to use a UDF in a SQL calculated column? I have created this function: ======================== CREATE function TestVal(@Field int, @Compare int) returns int as begin declare @nReturnThi...more >>

Hi all How/where can i change/set permission on a named pipes connection ? i've an SQL 2000 SP3 server running on a WXP box. On my clients the default network library is set to Named Pipes and i don't wanna change this .. on my SQL server the protocol order is set to 1st TCP/IP and 2nd Named P...more >>

Hi, How do I get the key for a max value in one select statement? create table x(id int identity,v varchar(1)) insert into x values('z') insert into x values('y') insert into x values('x') select max(v), max(id) from x This returns 'Z',3 But I want it to return 'z',1 Thank...more >>

Hi All, The following query is working fine in english setting. But in German setting, it is giving error. How do i solve this problem?. I'm using MSDE 2000 with Service pack 3a. 1. German settings: set language Deutsch go select * from sample where status_date < '3...more >>

Hi, How can I store the result of a DBCC command into a table. Thanks in advance ...more >>

Hi everyone.. Suppose i have millions of records in my table and selecting all the records at a time will be inefficient for any application. I want to have a functionality to show 1000 records at a time on a page and when a user clicks on Next button then show the other records. How can ...more >>

Good day, Am getting an invalid use of the Getdate function within a function error message(Msg 443,Level 16). There is a knowledge base article about this on Microsoft site (308759). Microsoft claimed that this error was fixed in service packs 2, but we are running service pack 3. Doe...more >>

I have an w2ksql db running on a shared server,. I have been asked to use RPC calls into a NON MS server to Upload orders and download stock reports. I dont have a clue where to start, can anyone sugest some direction to go in either research or samples etc.. They will give me a login and passw...more >>

hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page number and page size), or to return IDs of previous and next records (based on current record id). the...more >>

Why I am getting 0 % back? 1st view: SELECT role, CONVERT(varchar(10),OpnDt, 101)AS Dt, SUM(CASE WHEN stype = 'Completed Work' THEN cnt ELSE 0 END) AS Comp, SUM(CASE WHEN stype = 'Process Capability' THEN cnt ELSE 0 END) AS Cap, SUM(CASE WHEN stype = 'Staged Work' THEN cnt ELSE 0 END) ...more >>

This is crazy. The SQL below works fine in SQL server 6.5: select h.fund_code, p.dist_meth_code, count(DISTINCT p.agent_cl_ref) from rfsspd..holding h, rfsspd..portfolio p where h.client_code = p.client_code and h.portfolio_no = p.portfolio_no and h.Act_units <> 0 group by h.fund_code, ...more >>

Hi, I'd like to know how to insert files (like ".jpg",".doc" etc.) into a table in a database. Can someone point me in the right direction, please. Regards Calvin...more >>

Why is that the following code shows the length of column c2 as 80 and not 40. Isn't it very strange?USE pubs GO CREATE TABLE t1 (c1 varchar(40), c2 nvarchar(40) ) GO SELECT COL_LENGTH('t1','c1')AS 'VarChar', COL_LENGTH('t1','c2')AS 'NVarChar' GO DROP TABLE t1 ...more >>

I have a db named 90db. When I try to execute "Use 90mydb" an error occurs (syntax error near mydb). Apparently the compiler interprets the beginning as a number and cant swallow the dbname. Is this a known phenomenen? Any documentation? Thanks!...more >>

ss2k ... i need to implement a "1-stage undo" for various stored-procedures (sps that typically manipulate multiple relational tables) ... so, basically, revert the various records associated with a given unique-key of the ultimate-parent-table to the state they were in immediately prior to th...more >>

Hello, Do you ever consider the idea of outsourcing some of your SQL development to a remote developer? Do you ever want to complete complex chunks of work, and not do it yourself? I sincerely appreciate all responses I get! Thank you for your response! Richard Rogers http://www.SQLSolut...more >>

Hi , Pls help me to find solution .I get Syntaxt error , when try to declare datatype as table DECLARE @tblResource AS TABLE OR DECLARE @tblResource AS TABLE( iPlant int, iProduct int,) Any idea how can i declare data type as TAB...more >>

Dear All, There is a table Customer with CustomerID: (Primary Key) CustomerName: and a table Transaction which holds all the transactions with TransID: (Primary Key) CustomerID: TransDate: What is the BEST SQL or Best way to get all the custo...more >>

I created a vb dll on my db server, how do I instantiate and call it's methods from a stored procedure? Thanks in advance, Chris...more >>

Hello All: I am attempting to use the BINARY_CHECKSUM function to determine the checksum associated with a database "record" that spans more than one table (to manage concurrency). For example: SELECT BINARY_CHECKSUM(*) FROM dbo.Consumers INNER JOIN dbo.ConsumerPhoneNumbers ON dbo.Consu...more >>

BlankHi Group, Please help me writing to flat file in a particular directory in a SQL SERVER Procedure. Also what I am writing in the file, I am getting as a parameter. --Amit ...more >>

I am trying to create a table on the fly using a select statement. What I want to do is to signify the table name with the month it was produced. I have the following but cannot seem to make it work. Can anyone tell me where I am going wrong declare @box_name varchar (20) set @box_name =...more >>

Hi all. I have the following join: Select * From Projects Left Outer Join Ownership On (Projects.ProjectID = Ownership.RecordID And Ownership.RecordTypeID = 6) Which joins a projects table with an ownership table. The ownership table however contains other types of ownership details (for ot...more >>

We have been experiencing an unpredictable behaviour in our SQL Server 2000 sp3a. When we use the following delete stmt: DELETE tb1 FROM tb1 INNER JOIN tb2 ON tb1.c1=tb2.c1 INNER JOIN tb3 ON tb3.c2=tb2.c2 WHERE tb3.c3=1 We get error 'Server: Msg 8624, Level 16, State 1, Line 3 Internal S...more >>

Hi All, During deployment , should I always install Client Connectivity for all Clients ? Is there any condition where Client Connectivity need not to be installed ? Thank you, Trist ...more >>

Hi Again! Is it possible to delete records with reference to another table view? for example i want to erase all the records resulting from this query SELECT * FROM tableA, viewA WHERE tableA.col1 = '2' and tableA.col2 = '1' and tableA.col3 = viewA.col3 thanks a lot! ...more >>

using DBCC stinstance and create the alert after? thanks LMT...more >>

Does anyone know of a method to find the next available number, out of a list of numbers that may not necessarily be in sequence or order? Per the DDL below, the next available number should be '3'. I realize that I can query the table, ordering by IDNo, and loop through the records until I find ...more >>