SET NOCOUNT ON declare @tab table(vc varchar(2000)) insert into @tab select '10.1' insert into @tab select '10.1.1' insert into @tab select '10.1.2' insert into @tab select '10.1.1.1' insert into @tab select '10.1.1.1.1' The problem is that i need to select only 10.1.1 and 10.1.2 when i ...more >>

Hi Gurus Need some help. I have a account table with the following columns. I want to only see the first date the account threshold is >= 105 or 120 under theOCLWatch column. However, if I have consecutive dates that exceed my threshold I only want to see the first time it exceeds 105 or 1...more >>

Hi, I'm making a form which simply just has one textbox and one button. When you type a name into the textbox and hit insert its suppose to add a new record to the "Name" Table. Here is what I have in my VBA code below: Private Sub btnInsert_Click() Dim strSQL As String strSQL...more >>

I have three schemas that each have Table1: schema1.Table1 schema2.Table1 schema3.Table1. I have an SP that does work on that Table1 and for now I have three identical copies -- one for each schema: schema1.sp_Table1 schema2.sp_Table1 schema3.sp_Table1. I don't want to have the same SP...more >>

Hi, I would like to calculate field based on the entry in another in SQL server 2000 i.e.; field3 = field2 * field1 field1 field2 field3 10 2.5 25.00( =sum(field1*field2)) How do I go about this in the SQL DB itself, can it be done ...more >>

We are on SQL server 2000. We have been writing reports using VS.Net 2003 and reporting services 2000. We are now upgrading to VS.NET 2005. But we can't seem to figure out how to do reporting services from VS.NET 2005, using sql server 2000 and reporting services 2000. I have looked a...more >>

Hello, A colleague passed me this make-table query which works in Access, and we'd like to automate in SQL. I did some basic debugging but now on parse, SQL returns that Trim and Format are not recognized function nameS. I think you can see the what formatting is doing to provide a standard...more >>

Hi all, I need to know what would be the most efficient statement to generate a new table based in the id of 3 different tables, and then generate a summary report based on categories. i.e. Table A Table B Table C id weight ...more >>

Hi, I have a varchar(1000) column which is being populated from a CSV file using C# code.In the CSV file i first replace all line breaks with a '@' otherwise my code fails to insert into the table..I dont have any '@' in my input data. After inserting into the table I want to re-insert the l...more >>

Hello all, I'm trying to figure out if a trigger (insert or update) works on 1 row at the time (where 2 rows where updated by a sql statement or alle the rows simultanious. Where getting the idea that all the rows at ones are processed. If so, how can we detirmen which row is which? TIA ...more >>

(SQL2K, WinAdvServer 2K, XP pro workstations) I have several sql dbs on backup schedules that were setup via EM. I need to be able to read these settings and also set new ones via a UI. Anyone know how to 'get at' this info? tia ...more >>

How can I use datediff function so that it returns me date with less then or equal to 13 months from today? thanks ...more >>

Sql Server 2000 We have our 'DB Maintenance Job' schedule to run on weekend and it's enabled for 'Reorganize data and index pages. This process creates a huge Transaction Log which often cause issue to our Log Shipping process. Is there any method which can be done to 'reindex' tables o...more >>

Hi, I have a table (PartNumberTbl) includes all the part number. I want to add a part number 'ALL' in the following output so that user can select "ALL" in the DropDownList control: SELECT DISTINCT (PartNumber) FROM PartNumberTbl WHERE Customer = @Customer I don't want to create a Part N...more >>

Hi, I need to find a solution to trigger / monitor stored procedure execution inside a given SQL Server 2000 database. I need to log into a separate table the stored procedures name and parameter values every time a (any) stored procedure is called, without having to alter the stored procedur...more >>

I have created a stored procedure and need to send the results to a flat file, preferrably to a tab/spaced delimited excel file. Would anyone be able to help with this?...more >>

Hi, I am connecting to my SQL server 2005 database using a remote connection. Can someone provide me with a SQL script which will enable connections via TCP/IP to the database? (i.e. I'd like to do the step of going to the configuration manager and enabling the TCP/IP protocol for the SQL ...more >>

http://danutp.blogspot.com/ SQL Server - many as one to many Usually in a SQL database we can have two main associations between entities: one to many, many to many. In one of the recent projects I encountered a business need for a new concept. I call it "many to one-as-many". From dist...more >>

The current stored procedure I have is: CREATE PROCEDURE pDeleteEmailLinks @EmailLinkID int AS DELETE FROM webEmailLinks WHERE EmailLinkID = @EmailLinkID GO This works fine, however I was wondering if it's possible to pass a collection to a Stored Procedure for processing? If so, how is...more >>

I need to execute a lot of dynamical SQL (select only). These select statement have different column number and name. Any easy way to get the column names of a select statement string?...more >>

I finally wrote my first service broker application and everything seemed great until the server crashed this weekend since it would not take on any new SQL connections. Details: PerfMon counter SQL General\User Connections shows 100 new connections being added every 10-20 minutes. sp_who o...more >>

Guys, My select query doest return the expected result set when replace-expression is null. Example SELECT .. WHERE ISNULL(@CUSIP, <<table fieldname>>) = <<table fieldname>> I have similar query which doesnt work if the field value is null. Otherwise it works fine. I know work around f...more >>

Hello all! I've gotten great ideas and advice here before so I'm back. Converting from access to sql 2005 takes a fair amount of importing records; testing programming code and reimporting records. It's a pain in the ___ to keep running code to delete the records imported into 2005 and re...more >>

Hello, is it possible to run a query such with a case statement in the group by clause? DDL and sample data follow: Select os, count(*) as osCount From tblm Where os is not Null Group By Case When Left(os,3)='win' Then 'Windows OS' Else 'OtherOS' End This query gives me the error tha...more >>

Hi Everybody, I have a table as "AdjSummary" with 5 columns. I have to create a table with 2 columns from the above table. The structure of the table "AdjSummary" is as below INVNO ADJUST MODE PAYMENT MODE 10 100 OTH 0 NULL 11 ...more >>

We have one primary mail profile set up on the SQL server to use for server notifications (xp_sendmail). We're now wanting send e-mail notificationss out using a "From" address that's different from the primary mail profile for a website. How can this be accomplished? I created another profil...more >>

There is a lot of limitation on for xml clause. Is it possible to combine several select statement with for xml to a big xml file? Basically I want to implement something like: select ... for xml auto union all select ... for xml auto union all ..........more >>

Hi all I am looking to select TOP 10 DISTINCT values from the db. I am getting a syntax error when i try SELECT TOP 10 DISTINCT (city) FROM properties What am i doing wrong here? Any pointers? Thanks Harry ...more >>

Hi, How can I make the auto increment number start from 1 again after deleting records in the table? I should have written it down somewhere when I knew it last year. Yontaek ...more >>

Hello, Im testing a form that inserts some data in a sql server express database. One of the fields is called Date and the type is smalldatetime. When i test the form locally and stores in the database that is locally too, the data is correctely inserted in the Data field. But when i tes...more >>

I need some assistance, i have this Stored Procedure that will take my table and create a cross tab. Unfor, I am getting duplicate value from the output, when checking table1, there are no dups, i dont' know what i am doing wrong. Please help below, code that I am using. thaks ============...more >>

I have a table (a) with a value that is the key in another table (b). B can have multiple values for the found value. I want to return just the first one. So if A has 100 records and I do a join with B, I end up with only 100 records. Currently when I do a join I'm getting hundreds of records ...more >>

Hi, I have Script Component in the Transformation phase that returns me values like NO, OTH, OWN (comma separated string coded value) Now I want to use the Lookup component (preferably or any other task really) to get the description by looking up a table in another d...more >>

I would like to change the t-sql statement listed below to execute quicker. Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY. If a Pum value of 806478 is found within the last 60 minutes. The output COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 =...more >>

Could someone point me to a list of SQL Server proprietary data types? Tried searching this forum and the web but couldn't find a full published list. Thanks. Naveen ...more >>

Hi, I have created an Assembly as below CREATE ASSEMBLY ExampleYukon FROM '\\SomeLocation\ExampleYukon.dll' WITH PERMISSION_SET = SAFE When I call below I get "Msg 102, Level 15, State 1, Procedure MyProc, Line 2 Incorrect syntax near '.'" What is wrong with below?? CREATE PROCEDUR...more >>

I have two tables, project# Labor$ 123456 10025 789123 2052 and Project# Parts$ 123456 3569 456123 5698 and I am trying to get: Project Labor Parts 123456 10025 3569 789123 2052 456123 5698 I have tried ...more >>

Hi, I'm working on a product that collects information about thousands of objects on an interval, typically 5 minutes. Each object requires a separate table in the database. Recently we have experienced that the insert rate drops dramatically when the number of objects exceeds 10000. Th...more >>

Hello everyone, I have a general question about INSERT statements. Is it possible to insert fields into a table both through a SELECT statement and explicitly in the same INSERT statement? For example, if I have the following part of an INSERT statement: INSERT INTO table1 (column1, colum...more >>

The I.T. department I work for is going to start administering and supporting another department's SQL server database. I'm wondering what questions should we ask them to determine the level of support needed? Thanks for your input, -- Jerry ...more >>

I had been successfully backup up my DB with commands in VB similar to the following: DB.ConnectionString = DbConnStr & "; Asynchronous Processing=True" Dim backupSQL As New SqlCommand("BACKUP DATABASE [DB] TO DISK='" & FileName & ".bak' WITH CHECKSUM", cn_DB) cn_DB.Open() backupSQL.ExecuteN...more >>

Hi group, Not sure if this should be posted here but anyway here we go: I just installed the trial of Crystal Reports Server XI, but now after the installation I had to register and after that it ask me to logon to the CMS. I need to fill in username + password and Authentication (Window...more >>

Hi, Wen I use the syntax CREATE ASSEMBLY on my local SQL Server as below... CREATE ASSEMBLY EntityStatus FROM 'C:\SomeDLL.dll' WITH PERMISSION_SET = SAFE ....I get "Line 1: Incorrect syntax near 'ASSEMBLY'." ASSEMBLY is not in blue in that it does'nt seem to recrognise the keyword. ...more >>

I have a bunch of views that concat fileds, some of which allow nulls so I am using the isNull function when joining them. I dont want to have to do this, I want to set the database so that concat null does not yield null for all views and procedures. How do I do this this in EM ? I read and...more >>

Hi all, which is a best connection in VB/VBA to connect the remote SQL server. Regards, Shital ...more >>

Is this the right group for discussions on Free-Text issues? I see suggestions to mail to newsgroup “sqlserver.fulltext”, but I don’t find such a group. Anyhow: Working for a news agency, I’m building a news archive. Every article are stored in a SQL Server 2000 table on e.g. ID, Dat...more >>

Dear All, I am working on a VBA (MS Access) application, which connects Remote SQL Server by ODBC Connection. When I open this application on my workstation the error "Connection timeout' or "ODBC - failed" will occur, But when I open it on the remote desktop it will run. Can you suggest me...more >>

Hi, I need to change some table and field names - is there a way to update all the occurances in Views and Stored procedures? ...more >>

Hello all, I'm tring to coordinate between a database and a my application throw a dataset object and a group of dataAdapters, one for each table. to display data, I use the xml driven from the method DataSet.WriteXml(). to store data I want to receive xml based on the dataset structure and let...more >>

I have set Statement Completion\Auto List Members and Statement Completion\Parameter Information to true but still Intellisense does not work. Is there any other setting that I am missing? Thnks. ...more >>

Hello All, How would we use extended property in SQL Server 2005? Thanks Shail Shrivastava ...more >>

All, Can we do this trick and if yes then how? Just schematically: the SP should return the number of records if the parameter @Count=1, if not, then the records themselves. The problem is that there is some complicated JOIN and the whole set of WHERE clauses that I wouldn't like to repeat ...more >>

Hi, I have got this problem to solve and any help will be greatly appreciated. Here is approximate schema, it is simplified: create table tCompany (cID int NOT NULL PRIMARY KEY, cname varchar(20)) -- not relevant here but for general info, refers to companies create table tClass (clID...more >>

Hi I have a field (FieldA) which I need to Filter on, based upon a parameter, however the filter is only a substring of the FieldA. e.g FieldA = H2/Q3/10 the filter supplied maybe supplied as Q3, H2,or 10, I how to split up FieldA in it's constituent parts, however in a WHERE clause I ...more >>

Hi all, I need some help with joining two fields of type datetime, one with date relevancy and the other with time. If i join the integer part of date field with the fraction part of time field, the joined datetime is not the same. What's the trick here? TIA, Jozza ...more >>

Hi Here is the setting: Clients connect to SQL Server 2000 via ADO. Here is the problem: After a client's successful login, the SQL Server 2000 should delay the processing of the client-commands by, e.g., 1 sec. How do I tell it to the SQL Server 2000? Thanks in advance. Adrian ...more >>

Hi I have a WHERE clause, based on Params, passed in from a report, however, I cannot get it to work properly. I have to return two product codes, when a certain Types are passed in, however, when it's anything else, I am only to return one product type. Here's a sample of what I am tryin...more >>

Hi all How can I view content of a sql database log file?? and How can I recover a database from a log file?? thanks in advance ...more >>