Without the sum function, this query runs fine. SELECT usereventid, sum(cost) as cost_summary FROM vw_PlayerList ORDER BY Name when I add a sum function to this query it throws an error which says: "Column 'vw_PlayerList.UserEventID' is invalid in the select list because it is not cont...more >>

I began to develop a system for a club in VB 6 with Access in the year of 2002. It is even last year I was finishing implementing the dozens of modules for several departments. The subject is that I intend to change everything for .NET, C #+ SQL Server 2000 for being more specific. ...more >>

The following does not work (it doesn't pass syntax). Is there a way for me to accomplish what I'm trying to do here? CREATE PROCEDURE [dbo].[SPIFE_Module_List_Public] @ClientID INT, @DistrictID INT, @PresenterID INT, @LocationID INT, @Password varchar(50), @Keyword varchar(50) ...more >>

Hello there I've got here before script for setting 0 as default value in all my database on numeric fields Does someone has this script? ...more >>

Hello A quick question about how to use SOUNDEX to search for strings with mistakes. If you have two words in the string you are searching for example "General Industrial", if you search for WHERE SOUNDEX (A.Name) = SOUNDEX ('Generalll') it returns all the names with "general" in them wh...more >>

I have a table A that has about 35000 rows. Table B has about 2 million rows. Three columns colX,colY and colZ in table B have referential integrity constraints with the primary key of table A. i.e. fk_1 for colX referencing pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ...more >>

In a stored procedure, I have the following pseudo code that adds a row into a table that has an identity column: ----------------- INSERT INTO myTable (column A, column B) VALUES (@valA, @valB) SELECT SCOPE_IDENT...more >>

I try to upload a big file to a binary field. Because it is very big, I use AppendChunk. But it just takes as much memory as before. _variant_t bigarray; // set bigarray to 5 Mega bytes while(true){ read next part of file into bigarrary; Recordset->Fields->Item["ImageField"].Appe...more >>

Hi, I want to know if there is any place where information regarding the SQL server version is stored, using which we can determine whether a SQL server installation is a desktop engine or a proper SQL server. I want to determine this programmatically and the approach should work with both SQL...more >>

Hi, I am looking for a solution that can generate a script on column difference between two databases and its tables. Basically, I have a one core db and one development db. The development db has been revised several time and now I need to generate a script that just adds all new table colum...more >>

Hi I need to create a trigger how will set a field on his own table on Insert and update like : if Flag='AA' set Flag1=True if Flag='BB' set Flag2=true ...... Any one can send me a sample ? I looked in the SQL Help with no luck :(( Thanks, paolo L. ...more >>

I have a table in which I want to collect the minimum and max scores for unit performances within selected time periods. That table is named "MinMax" and it's structure is: -------------------------- CREATE TABLE [dbo].[MinMax] ( [Period] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NUL...more >>

Can this be done? If I have a function MyFunction(Param1, param2) Can I call the function with something like set @test = myfunction(select param1value from tableA where uniqueID = @uniqueID, select paramt2value from tableB where unique ID = @uniqueID) Any advise will be appriecated ...more >>

Can this be done? If I have a function MyFunction(Param1, param2) Can I call the function with something like set @test = myfunction(select param1value from tableA where uniqueID = @uniqueID, select paramt2value from tableB where unique ID = @uniqueID) Any advise will be appriecated ...more >>

I have defined a view containing many columns and many rows. I have a table containing data which lives for only a short time. View as v Table as t ----- -------- A int A int B int ...more >>

I'm having problems with an insert statement in ASP. The only error I can get is Error 3631. BOL and google are not giving me any answers. The fields and values seem to line just like they should. Any insight would be appreciated! thanks! ...more >>

I have a midly large SQL script, created with SQL server to create tables and stored procedures. I've tried executing it from VB.NET with no luck. I keep getting "an unexpected error occured in procedure MyProcedure. --> 'CREATE PROCEDURE' must be the first statement in a query batch." I ...more >>

Hi, I would like to perform a search which returns records that are like a string variable I pass into the query - so to catch spelling mistakes etc. Eg. string = Commmercial would return Commercial from my reference table. Could someone recommend the best way to do this. I am using VB.NET...more >>

I am getting this error in the SP shown below and don't see what is wrong? Syntax error converting character string to smalldatetime data type. The complete output is as follows: -------------------------------------------- DECLARE @RC int DECLARE @Class char(2) DECLARE @StartDate datetim...more >>

I hava table Client_ID Word_ID 15598 A1 15598 -- Message posted via http://www.sqlmonster.com...more >>

Hello, I'am searching for a faster way to run this query below. Now it takes my computer almost 4 seconds to get the information, due to the last sub select with the having clausule. Without the sub select it is finished within a second. select distinct(U.Dossiernr) as ndDosID from uren...more >>

Hi, I am using the following query SELECT FirstName AS FirstName, LastName AS LastName, COUNT(*) AS NoOfOrders, MONTH(CreatedDate) AS Order_Month FROM Customer_Info I want the Order_Month field to conain month name & not integers... Is there a way th...more >>

Hi. I need som help a SQL syntax, which I hope can be done in one statement: I have a table with these columns Year, ClientID,Month, BillingsOnMonth I want a query which shows me this: Year, ClientID, Month, Month%OfTotal Billings My SQL is getting poorer and poorer, I'm thinking SUB SE...more >>

I have a ADODB recordset problem. I want to use a stored proc to retrieve a recordset. Code goes something like: Set SQLCmd = New ADODB.Command Set SQLCmd.ActiveConnection = SQLConn Set rs = New ADODB.Recordset Set rs.ActiveConnection = SQLConn rs.CursorType = adOpen...more >>

Hi, I have problem with performance inserting about 200k records. Application inserting records using SP (parametrers and Insert into ... values) and ADOCommand. When I put all records in one thread I get about 450 records/sek puting to db. I thought that I split my records it will be fast...more >>

I found a data table on the prodcution SQL 2000 db blanked out for unknown reason. I'd like to restore the data from a SQL 2000 test database which was restored from a previous day's complete backup of the production db. Both db's are residing on the same server under the Enterprise Manager. Is t...more >>

Hi All Wonder if you could give me any pointers on the following plan: 1) I want to auto-extract a number of resultsets from an SQL 7.0 DB using possibly 3 or 4 different queries, which in turn can use 3 or 4 different tables each. 2) Ideally I'd like to put these resultsets into 1 Acce...more >>

Hi all, At [1], the text states "Use the Parameters collection when you call a stored procedure" Unfortunately, this offers no protection if the stored procedure then constructs a SQL statement dynamically, even based on the parameters that were passed using the Parameters collection of t...more >>

Hello, Is it possible to exec a stored procedure within a view? Thanks, Craig. -- "Black holes are where God divided by zero."...more >>

When I use the 'for xml auto, elements' in my SQL queries against a Windows SQL Server 2000 Db, what do I add to the query inorder to return empty elements for the fields that are null? I also want to get result for each row as a seperate xml string row in the result. Thanks...more >>

I wrote 03.20.2005. datetime, and I want from Select to find first date that is smaller then curent date. Can I do that from Select? ...more >>

Hello there Is there a way to know whan was the last time i've mofied objects? tables, views, store procedures ect...? ...more >>

Hi all=20 I have the following two tables=20 Table #1 Item No Name =20 1 Alfa 2 Bravo 3 Charlie 4 Delta Table #2 Sr No Item No Item Name Shipped Time =20 1 1 Alfa ...more >>

Hi, I have a table in SQL Server 2000, a field StatDate datatype datatime, 8 all null true If the user do not enter a date in the text box it is returning a error type mismatch ever though the field is set to allow Nulls. What can I do to correct this Thanks Dib ...more >>

Is it possible to do a FT search on MULTIPLE fields in an Index? I know that the FREETEXTTABLE can only do it on either one or all indexed columns. But what if i want to do it for more columns. Or is it possible to create a second FT-INDEX for the same table? I want to index different fiel...more >>

Hi, 1. I am using the "ProductLevel" property of the "SQLServer2" SQL DMO object to determine the product level of my SQL server 2000 installation. However even when I have installed "SQL Server 2000 sp3a" on my machine, this property still reports the server product level as sp3 (instead of s...more >>

Hi, I get the following error when I try to update a value in a column (ntextcolumnname) in a table where I have a computed column with a formula doing a substring(ntextcolumnname, 1, 255) and when I have an index on this computed column. As soon as I remove the index, the update works fine...more >>

I'm working with a legacy application that stores its data in a SQL Server 2K database. I want to write a delete query to delete items from table A, where there is no match in table B, where the match is based on matches of the two PK fields. I tried the following, but got an error: Incorr...more >>

Dear All, I have tried to access a SQL server DB on on network A from ASP/Or ASP.Net Application Which is hosted on another network domain, Both of them are behind the firwall (all consideraation are fixed,..), IIS server can connect to SQL-Servers, using: SQL-Analyzer or Enterprize manag...more >>

Hi folks, My server is configured as accent insensitive sort order. I have to replace "Â" for "A" in a huge table and I was wondering if there's a way to do that by searching the exact character. I mean, when I search "Â", it brings me "A" as a result also.... Did I make myself clear??? ...more >>

can I run a query within DTS and APPEND it to a text file? please advise ...more >>

I have a couple tables I would like to write a query for to return the results into a web page. Using SQL 2000, and writing TSQL queries for the reports. Summing integer fields. On Table1, I have a list of new products that are being produced this year. CREATE TABLE [dbo].[sales_produc...more >>

I have a situation at here that appears once in a while: The table in question has a single clustered index on ReferenceID. It's populated by the following process: Truncatae table MyTable, run DTS to populate the table. This runs every xx minutes. The table has roughly 50K records but the...more >>

Hi, folks... I was trying to write a sql query which returns different data types, but it returns every time, a smalldatetime format... SELECT CASE WHEN Tipo = 'N' THEN Numerico WHEN Tipo = 'L' THEN Logico WHEN Tipo = 'P' THEN Percentual WHEN Tipo = 'D' THEN Data ELSE NULL END as V...more >>

Hi, I have multiple tables for accounts and each table have different columns according account type. I need to query user accounts, so I created a view for it: select .... from table1 union select .... from table 2 .... and if the columns not exist for a particular table I made it n...more >>

Hi, I have a problem with calculation call durations in my call logging database. My application generates a lock event when a call is opened and an unlock event when it is put on hold. These are stored in my CallEvent table (CallID int, EventType varchar(4), EventComplete DateTime). The...more >>

I am doing a left join in this query, but only the records that have a join are displayed. There is a record in "cases" with no "casecomments" but it is not displayed ... please help: SELECT * FROM cases a left join casecomments as b on a.id = b.caseid AND b.lastupdate = (SELECT M...more >>

I am writing an App that uses SQL server 2000 as the backend. It is used in several countries and I am facing a problem that there is no universal string literal that is understandable by both SQL server and ASP.NET. For example, ASP.NET understands the ISO date format yyyy-mm-dd HH:MM:SS as ...more >>

Hi I am creating an indexed view but due to restictions I have to use the NOEXPAND hint. However when using that hint I get the following error. Server: Msg 8171, Level 16, State 2, Procedure qfm_GetActionWarningCount, Line 9 Hint 'noexpand' on object 'ActionList' is invalid. Does any...more >>

Hi, I have following command in a Sql Agent job: delete from [10.60.2.6].lldb.dbo.tblACTmpSite insert into [10.60.2.6].lldb.dbo.tblACTmpSite select * from tblACTmpSite The result is very interesting! The command transfers 94,000 out of 13,000 records without any error!! If I run the ...more >>

I'm running a SELECT statements in one QA window. This means I have three splits in the same window: one for the SQL and two more for the first and second output. The first output only has one record returned. The next output has 15. The problem is all the white space below the second outp...more >>

I have 2 procedures. 1 procedure calls second procedure and in second procedure I use raise error statement: RAISEEROR(60005,1,1) But first procedure doesn't get an error, @@error=0, so transaction in first procedure is not rolled back. Any idea? I can use parameter like this: ...more >>

Can I get some advice guys? We have a de-normalized database that is currently fed from Siebel (CRM). We want to pump in data from Pivotal but there is a slight problem. We used the Siebel Table=E2=80=99s identity column for PK in each corresponding table (contact, account, etc). The reco...more >>

Hi All; I have some problems want you to help me e.g. I have tables, Table1, Table2, ..... and have users: user1, user2, .... I want Sub or Fun tell me what permission of user1, user2 to Table1, table2,... i.e. user1 permission to Table1 is Insert, Update, Delete, Deny.... etc Than...more >>

Hi All Does anyone know a clever way of splitting financial values (say in an invoices table) by an "interval" entered by the user to show the interval and count of how many invoices fit into that interval. E.g. if the interval was 1000, the table should show: 0 - 1000 50 1001 - 2000...more >>

New to stored procedures. Is it necessary to place a default value into a variable at the time you declare it? SAMPLE: “ @Sec int = 100” Can @Sec just be declared? ...more >>

Hi to everybody, I've been using the XML Bulk Load feature successfully to import XML files into SQL Server 2000. The XML Bulk Load needs an ActiveX script (VBS) to work and now I want to run from it a query ("SELECT COD_NOTARIO FROM tblNOTARIO"). This is the code for the XML Bulk Load th...more >>

I'm working with an application that has multiple databases for different clients. I have a stored procedure that I would like to use for all of these databases that is stored in a separate database. In the past, I have passed the database name to the stored procedure and used dynamic sql to...more >>

I was wondering if I could get some opinions on best practices for naming tables and derived table (sub-queries) in complex queries. I have bounced back and forth a few times over the past year or so and now prefer using A,B,C,D,... and T1,T2,T3.... However my co-workers really hate it! I ...more >>

Hi all, I have a currency field in my database, when I select data from that column, I want it to right align. Any help? ...more >>

I need some tips to make the following cast operation more efficient. The idea is to compare a textual value stored as an image data type (don't ask why:). The text will never be more than 20 characters. Is it an idea to set the size of the varbinary? .... CAST(CAST(f.data AS VARBINARY) AS ...more >>

I have the following informaton in a field called: full_path /Capital Improvements Program/Management/Facilities/Lower South Platte/New Util Admin Bldg/Ops Ctr/Planning How can I pull out the information independently from the 2nd and 3rd level or the 2nd and 3rd level combined. re: /Mana...more >>

I have a query that returns the averages for a selected group of records. select AVG(h.stkhstClose), h.stkhstcsisym from stkhst h JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId and u.unvmemUnvID = 29001 group by h.stkhstcsisym order by h.stkhstcsisym this works and returns 99 averages....more >>

I have the following situation: The name of a product can change with time. These changes are stored in a table with 3 columns: Product_id, Date and ProductName with Product_id and Date forming the Primary Key. I want to run a query that returns the product_id and each product's latest ...more >>

Hi Guys, i want to insert a string for example 'abcd'edfg'gg'into a table in sql server 2000. but it is not working but giving the error "not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." will this require any sp_configue...more >>

We are creating a .NET application that uses SQL Server 2000 pretty much. Some checking is done in the SQL Server layer. We use a custom-made user authentication and each user is represented with a GUID. What I would really like is to have something like session variables. Just a normal var...more >>

If I have a table column type smalldate checked to allow NULLs and also have a default of getdate(), will this column value always be NULL if nothing is inserted into it upon record insertion of other columns? It seems to be. Must I delete that column if I want to not allow NULLs? Thanks,...more >>

I want to return records from a table which were processed yesterday. The query is like this ************************************ SELECT cast( cast(datepart(yyyy,getdate()) as char(4)) + case when len(datepart(mm,getdate())) = 1 then '0' + cast(datepart(mm,getdate()) as char(1)) ...more >>

Hi all I am using a control that i hand a record set to. One of the columns in the record set displays totals. I need to format these totals into currency. What is the best way of doing this? I would like it to look like this £2,324.40 If possible. -- Thanks Heaps Ia...more >>

Hi I am creating an indexed view but due to restictions I have to use the NOEXPAND hint. However when using that hint I get the following error. Server: Msg 8171, Level 16, State 2, Procedure qfm_GetActionWarningCount, Line 9 Hint 'noexpand' on object 'ActionList' is invalid. Does any...more >>

Got a minor duplicates problem. Easy for you guys I'm sure! TIA CREATE TABLE #duplicates ( row_id INT IDENTITY, ni_no VARCHAR( 5 ) ) SET NOCOUNT ON INSERT #duplicates ( ni_no ) VALUES ( 'AA001' ) INSERT #duplicates ( ni_no ) VALUES ( 'AA002' ) INSERT #duplicates ( ni_no ) VALUES ( 'AA...more >>

Hya, I have a counter in a table, just one field and one record. I use this counter for generate a unique serial number, after having generated the serial number the program insert the record in a second table (units). Counter table -------------------------- 106 Units Table ------...more >>