Hi all, I just wish to know Few things Is there any way we can block the data manipulation from Back End? ( As I have an application which has senstive data Any changes from back end can cause problem ) So I have two choices block entry from back end or thoroughly check in the process...more >>

I am looking for a query that counts the amount of pictures the user has in the table, then select the 1st 20 pictures and create a record number. e.g., results would be summit like = 1, ~/pix/1.jpg, mypic. where 1 is the autonumber from the count and ~/pix/1.jpg is the image location and mypic i...more >>

Funny little problem with a temp table created by an sp and then altered within the scope of the same sp. It doesn't seem to work when I try it. Here's some sample code; /**** start create code ***/ use northwind create procedure dbo.uspTest as select top 5 productname into #myTemp ...more >>

I want to write a script for a table 'A'. I 1) want to drop all the foreign key constraints which are created in another table(s) and in which table 'A' is being used. I dont know those foregin key constraints while executing the script. 2) want to drop all foreign key constraints which are cr...more >>

Hi,=20 I don't know if this is durable without cursor. I have following record set: TABLEA have one column "Date" All the dates are in order DESC. =20 Assuming every year need to have 4 quarters, however in this example 1995 year only have 3 quarters ( missing one = quarter- 1995-06-30...more >>

Which approach is a faster, better solution to process XML data? I get XML data from an external web service. To ballpark the general size, an average data file would be approximately 64K when saved in UTF-8. A) TEXT parameter/sp_xml_preparedocument/OPENXML - Passing raw XML as a TEXT param...more >>

How do I compare multiple columns in tables to see if there are = differences in my query, in example; SELECT TBL1.ShipTo, TBL2.ShipTo, Tbl1.Address1, Tbl2.Address1, Tbl1.Address2, Tbl2.Address2, Tbl1.Address3, Tbl2.Address3 Tbl1.City, Tbl2.City, Tbl1.State, Tbl2.State, Tbl1.Zip, T...more >>

Hi, Anyone can give me same background of natural key/surrogate key/ industry standard key? thanks ...more >>

I know there is a technique to do this ... I actually did already bu cant remember the technique I used I have a table that has a field that I want to increment with a SP o function It should work like this. When the function/ sp is called i increments the number, updates the table with the i...more >>

I'm trying to list salesreps (if they have any sales for a particula date) with their total sales amounts for a queried date, but whe running this sql string in QueryAnalyzer, it says there is an erro with syntax on Line 1 near "s" SELECT o.Rep_ID, o.ID, s.ID, SUM(b.orderamount) AS totalsales ...more >>

I am trying to rank a table but cannot get the desired result. Rank Name Points 1 Bud 82 2 Bill 37 2 Fred 37 3 Sally 26 4 Tim 23 I am getting the above results from this query SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points FROM Users U1 INNER JOIN Users U...more >>

i am normalizing the following table: CREATE TABLE [rcusermanager] ( [id] [int] IDENTITY (1, 1) NOT NULL , [username] [varchar] (50) NOT NULL , [email] [varchar] (50) NOT NULL , [accesslevel] [smallint] NOT NULL , [active] [bit] NOT NULL , [admin] [bit] NOT NULL , [firstname]...more >>

Hi, Is there a way to get table Creation Date using Information_Schema? Thanks, Roger ...more >>

All, I am trying to execute a simple Oracle stored procedure from SQL Server 2000. The SP has no input or output parameters. I have created the linked server correctly as I am able to execute Oracle functions just fine. I have tried the following two methods to execute the SP, SKELETON: ...more >>

Hi, I have a table named security which has following columns: security_id, name, symbol So, data could be: 1, Microsoft, MSFT 2, 3COM, COMS 2, Cisco Systems, CSCO Now, I want to do a simple select from this table but want another column in result which is a result from a function. ...more >>

In our domain, we have an AD backend for maintaining users and groups and OU's... we have our main OU which is the corporate one then under that we have servers, employees, and disabled users so our AD tree looks like this + Company OU |---- Servers |---- Employees |---- Disabled Users ...more >>

Hi All, I'm trying to use the ALTER TABLE.....ALTER COLUMN command to drop the DEFAULT attribute from a table. I have 2 questions: 1. I try to alter multiple columns in one call but it never works. I get error messages. Do you need to use multiple ALTER TABLE calls to modify multiple column...more >>

can this correlated sub query be Optimized ? SELECT cam.*, cad.ProductCode AS productcode=20 ,dc.customername ,dp.productname ,dp.productcolour ,dp.productsize ,cad.estimatedqty ,cad.revisedqty ,(select coalesce(sum(qty),0) from vh_vwhistorylines hl where = hl.customercode=3Dcam.cust...more >>

Hi All, I have same functions writed in C++ on a DLL. My questions is: Have same way of calling these functions of an store procedure or trigger? Thanks ...more >>

Hi, I am trying to debug some stored procedures using the native SQL debugger in Query analyzer. However when I click the "Execute" button, the procedures just run through to completion. I am able to set breakpoints, but these have no effect. All of the Step Into, Step ...more >>

Hi, I am trying to debug some stored procedures using the native SQL debugger in Query analyzer. However when I click the "Execute" button, the procedures just run through to completion. I am able to set breakpoints, but these have no effect. All of the Step Into, Ste...more >>

Hi, I am creating a simple SPROC in .NET (SQL Server 2005) and I want to send the content of a DataTable to the pipe. I'm not sure how to do this (all this is still quite new to all of us!). Pipe.Send takes a SqlDataReader or SqlDataRecord as an argument. However, the DataTable can onl...more >>

Hi, I am using the following simple stored procedure to dispaly data between 2 date ranges. CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS] ( @manucode varchar(50), @brand varchar(50), @StartDate datetime, @EndDate datetime ) AS SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS...more >>

I'm writing some stored procs to assist in our maintenance routines. I've got one that rebuilds all indexes that have a logical fragmentation percentage above a specified limit. The problem is that it can only be run in the current database since it references database-specific system tables...more >>

The code below is invalid. However, hopefully it will give a clue as to what I'm trying to achieve though - i.e. I want to create a new table which has a name consisting of "utbl0000000" and the value of @@IDENTITY (appended to the name) from the INSERT statement preceding it e.g. a new table ...more >>

Hi, When I restore the database, there is an option "Leave database read-only and able to restore additional transaction logs" I understand exactly what it means but What is that undo file for? How and when I use it? Thanks Ed ...more >>

Can somebody tell me why this Function will not work. I get a syntax error around the equals sign and I have not wrote many functions and i dont know why i am getting this error message. Create Function DetectedYrds (@Start Real, @End Real) Returns Real As Begin Declare @yrds Real If @St...more >>

This is a problem for almost every project that I work on. I get a list of location for water and/soil sampling which look like this mw-1 mw-2 mw-3 mw-4 mw-10 mw-11 mw-20 mw-31 so I must use a character/text field. But when I sort this varchar filed, I get mw-1 mw-10 mw-11 ...more >>

Simple query for you query gurus here: Given two tables, related by ID_Adjacency, how can I delete all rows in table 1, where the related row in table 2 has some given value? ie.: with the following two tables, delete all records from Table1, where the related record in Table 2 has a "Value...more >>

Hi Champs! This is probably simple for you champs, but! I hava tricky SQL query I cannot get to work. I have a Query from two tables A and B: I Make a SELECT A.time , B.name , B.number , B.ammount , A.date , A.transactionNumber This “TransactionNumber” is in table A and B So a re...more >>

Hello All, Hoping for a little help with getting a record number assigned to an invoice that occurs more then once in a table. Details below.. What I have: record# Invoice# 452 452 452 489 489 ...more >>

I am upgrading another Access database to SQL Server and have hit a small obstacle. The database has a column named Remarks and is of the datatype text. I used the following query to find the maximum size of the Remarks column, SELECT MAX(Datalength(Remarks)) FROM Admissions This retu...more >>

Hello. C# code: SqlString InternalVersionString = ProductInternalVersionBox.Text; SqlMoney InternalVersion = InternalVersionString.ToSqlMoney(); The conversion fails. (TextBox.Text had the value 25.5.5). 25.5 works. But shouldn't 25.5.5 work too? Sqlmoney should handle much bigger dec...more >>

I have a purchased application that was purchased from a 1-man company that keeps freezing up a couple of times a week. When I can get the guy on the phone he doesn't know why it locks up. If I stop and start SQL Server 2000 it frees up the application. I have one other small application runni...more >>

Hi, im having a problem with my sql server 2000, i have a dedicated server that i rent from a hosting Company, but they have setup me server in it's own NT workgroup, i mean, the computer name is WWW and its the only one inside the WWWGRP workgroup, i also created a Database maintenance plan f...more >>

Hi, I have a situation where the user can query both the entire table or portions of it based on a structured key (a 10 char key where each two character value means something in a hierarchy). To return the hierarchical result sets I'm using a query with LIKE in it... SELECT * FROM Y...more >>

Is it possible to create a view in SQL Server that contains a view of tables in a FoxPro database? If you can, how do you set this up?...more >>

I have a MS SQL 2000 Enterprise server running in a production enviroment. Settings for ANSI Warnings,ANSI Padding,ANSI Nulls, Arithmetic Abort, and Quoted Identifier are all uncheck. We are now moving to a new ERP and one of the prerequisites is to have all of these settings turned on. Other ...more >>

Hi Folks, Looking for assistance on using the datediff command to convert a date\time stamp into unix time. A few of you where a great help the last time directing me in the right direction on doing this. My problem is that the time stamp is one hour out (one hour ahead) . The below syntax...more >>

I've read here that a temp table is drop automatically when a store procedure that created the temp table ends or when a session that uses a temp table ends. If I have a temp table that was created by an activex script inside a dts and the dts is a scheduled job, would the temp table be dr...more >>

Hi there, We create our own system stored procedures. I have a problem with one of them. I want the stored procedure to get data from the systemtables of the databases from which it is executed, much like sp_spaceused does. So, when I execute the stp in database 'OurDB', I want it to get the ...more >>

Dear gurus, Which are the most difficult tasks for a guru? Which would be the most complex task concerning RDBMS? I am not talking about administrative tasks, common routines, left join issues, nested cursors, etc, but amazing milestones, strange jobs or nightmares using the maximum cap...more >>

Hi, I need to create a temporary table dynamically, as the columns created, and the names, depend on factors obtained through a FOR statement..I am doing this to test the theory. declare @strCreate nvarchar(500) set @strCreate = 'create table tbltest (someid int,someotherid int)' execute ...more >>

i need to search through a table and return the number of records that contain a . in a certin field ie fieldtobesearched 000.000 00000. 0000000 the count would be 2...more >>

In VFP we can use in the program the variable var ='some_statement' var && here will be 'some_statement' &var && here will be some_statement In SQL Server if we have : declare @var varchar(8000) set @var='some_statement' In the program @var --here will be 'some...more >>

Hai... I am working on VC++. I need to restore the backup to the database(SQL SERVER) using SQLDMO in VC++. I am able to restore the backup only to the database where i taken the backup but to some other database it fails. Can anyone provide me the solution for restoring the backup to some ...more >>

I have a stored procedure in SQL 7 which runs fine when called from Query Analyzer, but fails when it is called as a scheduled job task. The sp (Customer_UpdateLastEntry_All) gets the last "event" for each customer from the CustomerEvents table using a cursor (curItems), then loops through ...more >>

Hi Can anyone tell me how many types of cursors are there? What are they? When they will be used in SQL Server Programming. Regards, Sevugan.C...more >>

Why when I use different uniqueidentifier id will generate the same checksum value? 911433607 SELECT checksum(CAST(('{A933B626-9F52-4D62-8B59-A7B1E1F243D0}') AS uniqueidentifier)) SELECT checksum(CAST(('{844A9A36-3B5D-4359-8A62-A61A8836714F}') AS uniqueidentifier)) ...more >>

Hi, when I run sql query below it returns 0. What is wrong here? select CHARINDEX(text, 'base'), * from syscomments where number = 12 ...more >>