I'm trying to figure out the best way to represent a network of nodes in an undirected graph. Each node can have n peers. I'd like to be able to derive the following: 1) Count all nodes in a given network 2) Retrieve all nodes closer than X steps away from a given node 3) Retrieve all n...more >>

I am trying to replace any text after and including a minus from a column can someone suggest an easy way. The idea is that I may have one column that might have ch-120 or gh-130 how do i go about gettinbg rid of anything after and including the -...more >>

I have a table which contains approx 3,00,000 records. I need to import this data into another table by executing a stored procedure. This stored procedure accepts the values from the table as params. My current solution is reading the table in cursor and executing the stored procedure. This tak...more >>

Hi, I am trying to generate a unique value based on a set of customer defined values plus an incremental number. I have a table that contains an integer value and contains the customer defined values as such. CREATE TABLE [dbo].[Settings] ( [AppID] [int] NOT NULL , [AppPrefix1] [char] (...more >>

I know I am often asked to post my entire creation, attempted query and result. As often is the case I would not know how to do this on this one. It does not even seem the right things to do. My situation is as follows. I am generating quite a large page of results to be seen on the web. Thi...more >>

H, I have the following query that selects some users from my database who voted for the player with the most votes. The query works, but if there is a tie in the select TOP statement I would still like to retrieve those records. Is this possible? -- get most voted player DECLARE @m...more >>

hi, there from sql2000, user can also use user-defined function to return result like table. i am wondering, between Function and SP, which performance is better? appreciate your help ...more >>

Hi all, I have exported all the content of one of my tables to a text file. If I were to clear the content of that table, then did a import data in Enterprise manager, everything worked fine. However when I tried to import the data programmatically, the ImportData for the BulkCopy object alw...more >>

I have a sp (A) that needs to execute logic in another sp (B). In terms of performance, does it matter whether sp (A) calls sp (b) or should I copy sp (b)'s the script into sp (A)? Will I take a performance hit for calling one stored procedure from another? Or making recursive stored procedure ca...more >>

I have a dynamic sql stored in @vcSQL varchar(4000) variable. Parameters are @res varchar(20), @sdt smalldatetime, @sdt1 smalldatetime, @cPlant char(3) set @vcSQL = 'select at09.datetime,isnull(at09.Value,0) as [TK # 09] from ' + case @res When 'Hourly' Then 'fn_PIGetHourlydataForPlant...more >>

I'm running SQK2k on a Windows 2K Server box. My client machine is using W2K Pro and some client-side VBscript (v5.6) with some pretty standard/common calls like "CreateObject("microsoft.xmlhttp"), open, send, responseText and ADO" in order to fetch some data from the server. I have about 4...more >>

Is there a way to calculate a unique hash value based on the combination of 4 column values (two Nvarchar and two Int)? I attempted to calculate a hash in T-SQL for the combination of 4 columns by concatenating the 4 column values in a string and then calculating the CHECKSUM on the string b...more >>

Hi all.. I have an application used by people from differents countries, the matters is that I need to display on reports money simbols according to the currency of each country, i.e Users From Japan Customer A S10 Customer B S20 Where S is the simbol and should be Yen sim...more >>

Hi, I'm wondering how useful the Full Text Search feature is. It seems to me that combinations of "LIKE '%searchtext%'" do the job just fine. Are there advantages in using it, other the full text search can be a lot faster than using LIKE? Thanks! TomTom ...more >>

I have a view which queries an OLAP cube using OPENROWSET and the MSOLAP provider. ---------------------------- ALTER VIEW dbo.TQA_Contact_All_Count_Cube AS SELECT 'All' AS TheText, [[Company]].[All Company]]] AS TheValue FROM OPENROWSET('MSOLAP', 'DATASOURCE=PGHHQMANDREWS2;...more >>

hello, i have a table with a list of group_names (varchar) and a list of group_dates (datetime) and a list of group_type_id's (bigint). i want to retrieve a list of group_names, grouped by group_type_id, but i only want to retrieve the EARLIEST group_name (depending on that date column). ...more >>

Hi , what wrong do i m doing in this Create Function CREATE FUNCTION getPriceByEffective (@EffectiveDate varchar(10), @CurrentPrice money, @NewPrice money) RETURNS money AS BEGIN DECLARE @Price Money IF @EffectiveDate IS NULL THEN SET @Price = @CurrentPrice ELSE SET @P...more >>

OK here's the scenario, I would like to archive file system files that relate to completed claims in out database. An run a job to perform this task every night by simply moving the files into an archive directory that can be burn to cd once a week or so... I have created a linked server for t...more >>

I have a database table with about 50,000 records. As it grew from 0 records to the current size I noticed that updating a single row took considerably longer. According to Profiler an update initially required less than 10 logical reads. At the table's current size the number of reads is mor...more >>

We are using SQL2000, SP3a with transactional replication.. In the subscriber database, we have a table with an update trigger that updates another table (TableX). This trigger does not include "NOT FOR REPLICATION" phrase. This table is being replicated by the publisher. The changes at the pu...more >>

Hi, I am having trouble renaming a database file (.mdf). The data was originally created just using Create Database MyDb Now I have renamed the database using sp_renamedb @dbname = 'MyDb', @newname = 'MyDb1' which has been successfull. Trouble is it did not rename the file "C:\Program Fi...more >>

Last night I upgraded to SP3, now it seems like nothing is working correctly. I do generate large tables every night. Now a simple query will work. It looks like any query with a "join" in it gets this error. The error I'm getting is: Server: Msg 3624, level 20, state 1,.. Location...more >>

What if the data is stored as shown in the W x H x D format and,if stored as 3 different columns ... now, of course, data types in both scenarios .. if varchar, how to find height? etc. ...if stored as separate values, then how to find for the entire dimension as awhole? hope this will help so...more >>

I'm working with two databases in distinct servers, I'm using Linked Servers to access the data between servers (Server1 has a linked Server2, and Server2 has a linked server to Server1) I've a trigger on Server1 that updates tables on Server2, one of that tables on Server2 has a trigger t...more >>

i am using the following syntax to combine two selects into one for performance purposes: SELECT @premium_balance = SUM(CASE gl_account_id WHEN 20 THEN amount ELSE 0 END), @markup_balance = SUM(CASE gl_account_id WHEN 13 THEN amount ELSE 0 END) FROM tGLDetail WHERE contract_id = @co...more >>

I have purchased Office XP Professional which has Access. Access has some Visual Basic programming ability and some SQL ability. This was an Dell OEM installed computer so I recieved no documention or help whatsoever. I have tried to use the SQL functions with virtually no success, especial...more >>

I have SELECT statement which returns the result: productName quantity colRef colAll product1 4 0.5 0.2 productxy1 2 1.4 0.11 product2 3 2.1 0.33 productxy2 1 0.2 ...more >>

I want to use SQLDMO to check the version number of all the SQL Servers on the network....I get the list of servers..how can I check the version number I am using following code Dim i As Integer Dim oNames As SQLDMO.NameList Dim oSQLApp As SQLDMO.Application Set oSQLApp = New S...more >>

Hi: I need to write a trigger that inserts a row in a log table when an update occurs. The log table is a generic table with old value, new value and the date of changed. Any help appreciated Thanks ina dvance...more >>

Hi declare @today datetime set @today=getdate() Now I want to get rid of the time part. It needs to default to midnight, which is the start of today. How do you do this ? Thanks Tania...more >>

I am defining udfs for calculations that can be used multiple times at different places without rewritting the whole calculation again and again. I need to capture the body of the udf defined to search for any words that begin with 'Met%', 'Sen%' etc. I thought Information_schema.routine...more >>

I've been talked into implementing my dB calls in ADO.net using stored procedures to do things I'd normally do with adhoc SQL in-line code. Therefore, I have the following code to INSERT a record into a table. The PK for the table is an identity, so it will automatically assign a new IS ...more >>

What does the N macro do?: SELECT @device_directory = SUBSTRING(phyname, 1, CHARINDEX (N'master.mdf', LOWER(phyname)) - 1) FROM master.dbo.sysdevices WHERE (name = N'master')...more >>

Assuming you have two identical structure tables 1. A and B that have field1 and field2 (field1 is the primary key) 2. A gets some new records that you want to add to B (no dups) 3. I have tried all these below : Insert into B (field1,field2) select field1,field2 from A where field1 not i...more >>

If I create a table variable in a stored procedure with an identity column, how can I reset the identity? For example, declare @TestTable table(SomeIdentity int identity(1,1), SomeField char(5) NULL) insert into @TestTable (SomeField) values ('test1') insert into @TestTable (SomeField) v...more >>

Hello!!! I have a variable called @strBody VARCHAR (8000) in a stored_procedure, but I need to have a variable more capable than her maxim length. What could I do? Has someboy some suggestions to give me? Thanks!!! ...more >>

Whats the best way to import data from an excel spreadsheet that contains a Web Query. How can I get the Web Query to Refresh before the import takes place(DTS or whatever) Any help on this would be great...more >>

Hello, I want to bulk insert the log files from my IIS and therefore wrote this script: DECLARE @file char(46) SET @file = 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex' + Convert(char(6), Getdate(), 12) + '.log' BULK INSERT [dbo].[tablename] FROM @file WITH ( FIELDTERMINATOR = ' ', ...more >>

Hello! In the SQL Server documentation is said that it "returns the last identity value generated for a specified table in any session and any scope" To obtain unique numbers client makes one insert and than calls IDENT_CURRENT. So rises the question is it possible that 2 or more c...more >>

hi, i need one help regarding the string comparasion using Cursors. the string contains "a,b,c,d",by using this string i want individual string to display basing on the comma operator. i have to display the result in this way a b c d how can i split the string by using cursors. thanks ...more >>

Hi, I've a transaction table, something like TRANS_TABLE: TRANSNO (k),TRANSDATE,PRODUCT,QTY,OK The column OK has values 'Y' or 'N'. There are many rows with the same date and product and ok-code. From this I need to create an aggregated table: AGGR_TABLE: TRANSDATE (k),PRODUCT (k...more >>

Hello. I have a trigger which creates a log file, but it failed when more than one row was updated. As a quick fix I used a Cursor but I am sure that it could be re written without the Cursor. I am new to SQL so any help would be greatly appreciated. The Trigger query is as follows:- ...more >>