I tried writing the following statement in a stored procedure, and I get an Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'." CREATE VIEW VW_TotalAccepted AS SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total] FROM [DelDetail] GROUP BY [DelDetail].[CustID], [DelDet...more >>

I have a stored procedure that will copy data from one table to another, but I am having problem doing so from a 'text' field from table 1 to table 2. Here is my SP: <% Dim Addtemplates__FirmId Addtemplates__FirmId = "4" if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("F...more >>

Ladies and Gentlemen, I would like to offer you the following string functions Transact-SQL GETWORDCOUNT() Counts the words in a string GETWORDNUM() Returns a specified word from a string AT() Returns the beginning numeric position of the first occurrence of a character expression wi...more >>

Hi I get an ODBC ERROR - Incorrect syntax near , from crystal reports 8.5. any help is realy appreciated SELECT National_Oxygen_Pte_Ltd_Customer_Budget."Customer No_", National_Oxygen_Pte_Ltd_Customer_Budget."Mar Amount", National_Oxygen_Pte_Ltd_Customer_Budget."Item Category Code",...more >>

Hi I get an error : ODBC ERROR - Incorrect syntax near , from Crystal SQL reports. Your help is really appreciated SELECT National_Oxygen_Pte_Ltd_Customer_Budget."Customer No_", National_Oxygen_Pte_Ltd_Customer_Budget."Item Category Code" FROM "nox"."dbo"."National Oxygen Pte Ltd$C...more >>

One more before the night is through. I know I can use the DISTINCT function in the select statement to not give me duplicates. But what if I want the rest of the info to follow. For example, I have a table with a duplicate usernames, but a song column with diffrent songs. 1 user may have ma...more >>

In a stored procedure I declare @basedate as datetime. I need to set @basedate to "1/1/1980" but cannot seem to determine the form for doing so. I've tried to use Set @basedate = "1/1/1980" etc. but the queryanalyzer barfs. I'm sure it's something simple, but cannot find any references for ...more >>

Hello all!, This is probably pretty simple. I think I'm trying to hard on this, and can't see the logic. If I have a table(products) with a productID, prodname. And a another table called user, with UserID and name. And I add a UserID column to my Product table. How can link the userID ...more >>

/* Please help. Definition: inresponse users (processors) are capable and enabled to process action. Processors can be capable, enabled and active. Actions are of certain types and each action type is capable of being executed by one or more thread(s). On a certain status changes, From...more >>

Is there any way to start running the distributed transaction coordinator service using tsql statement, rather than having to start it at Service Manager? This is because I'm going to schedule to do some data processing (Insert, Update and Delete data) between 2 database 3 times a day. It w...more >>

This is a shameless plug. My new book, SQL PROGRAMMING STYLE is out now. Joe Celko's SQL Programming Style by Joe Celko Save 20% and get Free Shipping if you order by Friday May 6. Place your order now or learn more below _______________________________________ If you're looking for ...more >>

What kind of Datatype can I use in my DB to store info like a letter or Rich Text, including the formats? thks -- ...more >>

Hi All, I have a table that stores IDs and dates: CREATE TABLE table_X ( StationID nvarchar(10) NOT NULL, FullDate datetime NOT NULL ) In the fulldate field there are dates one for each day and station. sometimes a station does not report the date to this tabl...more >>

Hi - unles I'm using a tool to restore data, is there any use for the log files SQL produces? I am only in a position to use MSDE right now (cost vs income generated) and so disk space is premium (2gb limit in MSDE). If this doesn't include the Log files, I'm not too bothered - if it does, c...more >>

Is there a known performance issue using the aggregate SQL function MAX within a temp table? I am looking for confirmation that this is/is not a (known) defect within SQL Server 2000. Or, perhaps this is a SQL Server 2000 configuration issue that I have? Assume that I create a temp table a...more >>

This should be easy for someone... select count(*) from vw_priceTrends where closeprice < 20 and pricebarrier = 1 select count(*) from vw_priceTrends where closeprice < 20 -- How do I combine the two queries as one query?...more >>

Hi, Can anyone here explain why the 2 Japanese strings are equal? select case when N'あきよ' = N'アキヨ' then 'true' else 'false' end select convert(varbinary,N'あきよ') select convert(varbinary,N'アキヨ') Thanks, James...more >>

Hi, (SQL Server 2000) I have a database that two programmers have db_owner privilages to. At this time each created a stored procedure and they both work fine for each via VB ..NET 2003. As soon as either try and access the others Stored procedures the program says it can't find the SP. If...more >>

TableBefore CtryOrigin Code Grade Percent1 Percent2 AUS ABA A 100 0 AUS ABA B 90 50 AUS ABA C 100 1 TableAfter CtryOrigin Code Grade Percent1 Percent2 AUS ...more >>

Hello, How to make a float number again? declare @val float set @val= 900.57 the select statement should return 900.57 but it returns error: select cast(cast(@val as varbinary(8)) as float) How to make it work? Any help is greatly appreciated, Adam ...more >>

Hello I have a datetime field in my SQL database. I need to update this, setting it to "current value + 5 seconds". If I used something like "update table set timefield = timefield + 5 where ID = 0" it adds 5 to the days. How do I get it to add 5 to the seconds instead? TIA Allan ...more >>

Is there a way to count how many rows of a query has resulted in, even when the query is still running?...more >>

I got this problem that really puzzled me. I used a stored procedure to insert records into SQL. When the field of input string contained more than 128 characters, it gave me this message: The identifier that starts with 'Today is a nice day.............Good bye' is too long. Maximum leng...more >>

I am trying to tune a query that uses two seperate CHARINDEX functions. The first: SELECT @fieldValue = (SUBSTRING(@delimitedList, 1, CHARINDEX(',', @delimitedList) - 1)) Obviously starts at the first postion and returns the data to the left of the ','. The second statement: SELECT ...more >>

I have a series of select queries that use the nesting method but are creating such a huge query that the server can't handle it. The IN section in some cases are so large that I can't even troubleshoot it in Query Analyzer because it's larger than 64k. Is there another way to write: SELE...more >>

Now you can call a WebService from SQL (trigger, function, procedure, DTS, SQL Analizer, ISQL) http://www.rdlcomponents.com/EXSP/default.aspx Thanks Jerry...more >>

I am trying to run a simple query select DISTINCT TOP 10 email,code from user_info where code like 'xx12%' ORDER BY x_date desc I get this error- Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Can anyone help? ...more >>

Hi I am importing data into a column and want to change all the values to read in percentages i.e. 15 should become 0.15 How do I do this during an import? or how do I update the column afterwards? ...more >>

Hello, I would like to "Efficiently Publish[ing] Relational Data as XML Document" and got hold of the article which is really great. I was ofcourse very glad to see the Algorithms in there already that I can use but.... there is one problem that I am having. In the absence of the paper let m...more >>

just out of curiosity, I was wondering why this works - Declare @Subject_Id As Varchar(300) Set @Subject_Id = '19164' Select * From Subject Where Subject_Id = @Subject_Id --and this works -- Set @Subject_Id = '%' Select * From Subject Where Subject_Id Like @Subject_Id ...more >>

Is possible, inside a stored procedure, say read file from http://www.zzz.com/myfile.txt? I need to implement a parsing strategy to insert into a db data collected from remote unix server. I thinked about a call to xml object inside the sp, but is this possible? Thanks Andrea ...more >>

Help!!! I have an application I support that the vendor is not being cooperative sin giving me data purge routines. The application uses Epoch dates (# of ms since 1/1/1970). I found this script on Oracle but need help converting it to T-SQL where it works without the arithmetic overflow e...more >>

Hi guys We have a following problem. For security reasons in each table in our DB we have addition field which is calculated as hash value of all columns in particular row. Every time when some field in particular row is changed we create and call select query from our application to obtain...more >>

Hi ! Following problem: Table Times USERID varchar IN datetime OUT datetime THRA 18.05.05 18:01 18.05.05 22:00 I calculate the Hours, Minutes from IN to OUT with following function: [dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT)) CREATE FUNCTION [dbo].GetHoursFromMinutes(@pmin...more >>

I want to store Word document in a table. First I tried to add the physical path of file to the table and later retieved it. Now As I dont want others to physically open the files, I decided to store the file itself in a text datatype field. Is this a good idea? And I dont know whether text fiel...more >>

How Can I get a computed sum on the last column in the select list distinct(a11.Policy_id). We can't use sum(count(... or compute sum in a select into statement so I am really stumped... select a16.Week_id Week_id, max(a16.week_desc) week_desc, a14.Po_tr_bus_cat_id Po_tr_bus_ca...more >>

Hi, Is there a single command to extract the whole date part (day, month and year) from a datetime value? I have come across the Datepart function, which allows to extract, the day or month or year depending on the parameters passed to it. Converting all of the above to string, concatena...more >>

I got a table code name active 1 thomson 1 2 sony 1 3 vettori 1 4 nash 0 I need to take all the active ones So I write select name from table where active =1 Mean while I...more >>

Hi, Is it a requirement of SQL server, that UDFs return values? kd...more >>

Is it possible pass to procedure open cursor from other procedure ??? Any suggestions will be appreciated -- Message posted via http://www.sqlmonster.com...more >>

In a ADP (MSSQL7). I have been trying to create a trigger that would update multiple rows on table 2 when table has a row updated. It doesn't wok - I get the 'Key column information is insufficient or incorrect. Too many rows affected by update' error. I tried for hours to get around it but no jo...more >>

Hi, Considering that there are many invocations of sp_INSERT_OR_UPDATE_RECORD, which is the best routine in performance and stability? (please ignore trivial syntax errors :) ) Please reply. Thanks in advance. Regards, Hyun-jik Bae ------------------------------------ -- Case 1: ...more >>

I have a text file that is generated from a host transaction. I have a batch program that loads the text file into SQL and does a boatload of validation. Is it possible to create DTS job or do this some how in SQL to make it pretty less painful? ...more >>

I need something like this: ALTER TABLE MatchResults ADD CONSTRAINT ck_MatchResults CHECK ( NOT EXISTS ( SELECT B1.Id, B2.Id FROM MatchResults M, Bedrijven B1, Bedrijven B2 WHERE M.Deleted!=3D1 AND B1.Id =3D M.ParentId AND B2.Id =3D M.Id AND (B1.ProfielId!=3D3 OR B2.ProfielId=3...more >>

Hello, I have trouble converting a unicode value (0xB4F5000000000000) that I know is unicode and defined as varbinary(8). I tried as following but it didn't work. select cast(0xB4F5000000000000 as decimal(8,2)) Any help is appreciated, Adam ...more >>

Is there any way passing array data types from/to a stored procedure? Please reply. Thanks in advance. Regards, Hyun-jik Bae ...more >>

I'm receiving a parameter@ @deletions AS CHAR(1) I want to use this in a select case statement: ----------------------------- SELECT columnA, columnB, columnC FROM tableA WHERE columnC = 'a', AND columnD = 'b' CASE @deletions WHEN 'N' THEN AND (C...more >>

Hi, Can I change the name of the column using alter command . Is there any such option? Provide me different ways of changing column name in a table. Thanks & Regards, Vani Jyothsna. ...more >>

In my procedure there are 2 parameters @intStart, @intEnd i need to return set of rows , rows that start from @intStart to @intEnd. Suppose in my table i have 20 rows i call to procedure with parameters @intStart = 4, @intEnd = 9 and i got only rows 4, 5, ... 9 suppose i ord...more >>

Hi I have a problem , like i write a Stored procedure and there are insert commands in it , if the valid values are not entered then the stored procedure should roll back the invalid changes thanks and regards Madhavi ...more >>

MY statment is UPDATE arinvinfo set recstatus = IIF(arinvinfo.postsw=1,'TA-1','') where valdisw = 1 However, as i run in SQL analzyer, i got the following error Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '='. ...more >>

I have a table_variable with some data now i need to INSERT it to the table in database this table has identity column. How can i get the all identities ??? -- Message posted via http://www.sqlmonster.com...more >>

Any good suggestion ?? I am willing to pay but can't afford too much . Thanks in advance ...more >>

from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still showing? CREATE TABLE Customers ( Cust_ID int PRIMARY KEY, Cust_Name char(20) ) CREATE TABLE Orders ( OrderID int PRIMARY KEY, Cust_ID int REFERENCES Customers(Cust_ID) ) GO INSERT Custom...more >>