I have a small C program that uses the DBlib routines to send around 40 transactions per second to a SQLserver 2000 database. The 40 transactions are either INSERT or UPDATE statements to one of about five different tables in the database. When I run the SQLserver Profiler, I typically see be...more >>

Are microsoft serious when they release software where I have to type in Server and database names comma delimited into a textbox They never ever cease to amaze me with there UI functionality. Wait a sec I will just open Enterprise Manager to get the names and what were those servers called...more >>

We currently have a push-model architecture with Crystal in which we simply push an ADO.NET DataSet object to Crystal. Crystal then binds that data to the report (i.e. rpt file) , then we render the report. For example, report.SetDataSource(myAdoDataSet); However, I can not seem to find a...more >>

Good morning. I have the above mentioned error message during the execution of stored procedure in SQL Server 2000. Is there any workaround? Thanks n regards Betty...more >>

How do I display output parms from a SP in Query Analyzer? This does not work. declare @parm1 datetime exec mySP @parm1=null select @parm1 go ...more >>

I get invalid syntax on the CmdShell piece. I have that commented out. If I look at the Print output it appears correct to me. What do I have wrong.. thannks. ----- DECLARE @OldFile char(44), @FileLoc char(44), @NewFile char(44), @cmd sysname --...more >>

CREATE FUNCTION dbo.ufFileIdsByXml ( @idoc int ) returns @retVal table ( fileid int primary key ) as begin SELECT x.fileid FROM OPENXML ( @idoc, '/files/file' ) WITH ( fileid int '@id' ) x RETURN end /* I would like to know whether or not the above function is d...more >>

Hi, I have been designing a lot of data-wrappers for my sp's using vb.net and the data access application block. seeing as all of these follow a similar pattern I though I might automate the process by writeing some code that will "generate code" for the data access wrapper. to do this I w...more >>

Hi everybody, i am working with sql profiler trace functions on SQL Server 7 and SQL Server 2000. and it is very strange, because these functions are very differents from from both versions!! but my question is: Working with SQL Server 7 exist "xp_trace_setqueuetoautostart" to autostart a trac...more >>

how can i disable sql error message in windows server 2003 on ASP pages? Instead of outputting the errored command, can it just say error or somehting generic to prevent injection attacks. Thanks Aaron ...more >>

Hi all, I'm programming in Visual Basic 6 with ADO and I need to perform many thousand INSERT statements. I'm trying to find the fastest way to do that. So far I see a number of possible ways: 1) Simply execute each statement one at a time. 2) Concatenate together a long string of multiple...more >>

I'm confused about results I get from the SQL server (MSSQL2000 sp3a). The query goes like this: SET NOCOUNT ON CREATE TABLE #t1 (docId CHAR(10), artId CHAR(8), qty INT) CREATE TABLE #t2 (docId CHAR(10), artId CHAR(8), qty INT) INSERT INTO #t1 VALUES ('DOC01', '40001211', 5) INSERT INTO...more >>

I am using BULK INSERT with a format file to import lots of records from a flat file with fixed-length fields. I tried mapping fields in the flat file to the table fields but it caused errors until I changed all field definitions in the table to varchar. In DTS there's a way to transform the...more >>

Thought I'd ask here before I dig for the answer in all the heavy research stuff out there... SQL can't express recursive queries such as the transitive closure. Every time I make a new nested set data model I ask myself, will the next SQL standard try to address this limitation? Should it? Ar...more >>

I am using SQL Server 2000 and I have a VarCar(10) field that stores a Time Value. Is there a way to pull up a list of records that have invalid Times entered in? For example: 10:00 AM 11:00 M 00:90 PM The report should show the below as wrong times: 11:00 M 00:90 PM Thank ...more >>

Hi, I am trying to select a date and format it as "ddmmyyyy". i have tried with the DATEPART and DAY, MONTH etc but cannot work out how i can make a single digit date/int into a 2 digit int/char... ie i want to convert 1 into 01. Can anyone help. Thanks Steve. ...more >>

Hello I've enabled autoshrink on a db 8 hours ago and is wondering when it will start to shrink? midnight or something? log file is 90mb and db is 7mb sigh TIA /Lasse ...more >>

Hi, just recently bump into some problem, when I use more then one table variables in a join I'm getting wackiest execution plan ever. No matter how I rearrange join, SQL joins ( In the example below ) @TYPES and OBJECTS that gives thousand of records then applies USERS and only then @FILTER t...more >>

Hello, the following procedure doesn't work: CREATE PROCEDURE TestProc @sortby varchar(30) AS SELECT AuftrNr, ... FROM TABLE1 UNION ALL SELECT Auftragsnummer as AuftrNr, ... FROM TABLE2 ORDER BY CASE @sortby WHEN 'AuftrNr' THEN AuftrNr END ... What is the correct "order by"-...more >>

Hi, I'm a bit of a MS SQL newbie. For my ASP application, I would like to store default values for certain variables in my database. What is the best way to do this? My first instinct was to do something like: CREATE TABLE [dbo].[DEFAULT] ( [Variable_Name] [varchar] (50) NULL , [Variab...more >>

Hi, I have a dynamic stored procedure where i build an SQL statement based om user input. When I execute this statement I use: exec ('....') Now I want to put the result from the statement into a temp table, like exec('.... into #temp ...') Ok, but if outside exec, the temp table is not a...more >>

Hi There I have a table I need to monitor. If there are 0 new rows inserted every 10 mins then I need to trigger an alert. What is the best way to do this? If I create a New Job, how do I define the step to send the alert if no new rows exist? Thanks, Sam ...more >>

Hello. I run into many instances where I have 50+ .sql scripts that I need to run on multiple SQL installs. I want to automate this process some by being able to drop all my .sql scripts into a folder and then click on my batch file that will run all scripts in that folder, and create a log fil...more >>

I would like some advice on how to read in a bit column via SELECT. I am using a PHP script and basically this is the only var that even soing: Select OutageOccured from table xyz; gives me nothing. I have tested and verified that php script is not the issue. Thank you in advance. ...more >>

Hi friends I have a column (staffid varchar(5)) in a table staff.it stores our staff info. our requirements grown we need to increase column length to varchar(10). there r many stored procs,function etc., that r currently using this field. my question is what is best way to get list of objects t...more >>

Hi all I'm trying to find out how to Retrieve Data from a Single Table and was wondering if someone could help with the following as I cant seem to find the answer to what is probably an easy question for the experts. I'm a Visual FoxPro 6 user and use the following code to almost accomplish what...more >>

====================== begin tran declare @err int insert into -- table pippo doesn't exists, I want to call an error pippo select 1 set @err = @@error print @err -- for debug if @err <> 0 rollback tran else begin select 1 as 'colonna' into pippaz...more >>

Hello! The next procedure: CREATE proc habibi_ac_test as raiserror('Some message...',16,1) with nowait WAITFOR DELAY '00:00:10' select top 5 * from documents GO The client program use ADO to access data. My client application raises error only after 10 seconds... I think it will be im...more >>

Is possible to dynamically generate column names? Here is my sample code and desired results: declare @myYear varchar(4) select @myYear = 2000 select datediff(hh, '1/1/' + @myYear, '12/31/' + @myYear) as 'hrs in ' + @myYear /* desired result set hrs in 2000 ---------- 8760 */...more >>

I need to automatically update a datetime field for a record to the current time whenever the record is updated. create table t ( id bigint identity(1,1) not null primary key, name varchar(50), value varchar(50), ts datetime not null default getutcdate() ) go insert t (name, value) values ...more >>

>It isn't. Traversal is a procedure; I write SQL which is a >non-procedural langauage. I get you to the end of the chain from any >node in it. Dont know what kind of terminlogy this is. Traversal/procedure/non-procedural .,etc., etc., So using your words, *if* u get me to the end of the chai...more >>

I have a query I was wondering if I could optimize further regarding the search string (in red): SELECT Company.CM_ID,Company.CM_Name_1,Company.CM_Name_2,Company. CM_Branch,Xref_Addr_Comp.AD_ID,Address.AD_Line_1, Address.AD_Line_2,Address.AD_City,Address.AD_State,Address ..AD_Postal,Addres...more >>

Hi, Can anyone let me know if its possible to run a query so to extract all the text that I have used to describe my Database diagrams. I know the Diagrams are stored in dtProperties but am not sure where the text annotations are stored. Thanks for any help. Steve ...more >>

I have a table1 with fields [Alpha, Charlie, Delta] I add column Bravo. If I do alter table table1 ADD Bravo varchar(10) it will add it to the end of the table. How can I add it so the columns are in alpabetical order? Or is the some other procedure that can be run after the alter statement t...more >>

I am trying to register a oracle server from my SQL enterprise manager. Any help in this regard would be highly appreciated. Specifically - 1. Where do I download the oracle driver 2. Step by step instructinos on registering a oracle server to SQL server. Thanks! ...more >>

Hello everybody. I'm not sure this is the right place to ask this question, but maybe someone can put me in the right direction anyway. What I need to know is how to have a camera take photos when given a signal/event and save the name of the image in a database. I don't know which camera/...more >>

My two questions are: 1- Is it possible to rename an instance of SQL 2000, if yes how can I do it? 2- After installing SQL 2000 I've applied SP3 on it, then later I installed another instance of SQL 2000 on the same machine (windows 2000 advanced server) , should I reinstall SQL SP3 for the new ...more >>

I need to insert a record in one table and then insert a record into a second, My question is the second table needs to insert the record with Identity of the autogenerated key in the first table. ...more >>

I am trying to create a stored procedure that will implement the following: I want to retrieve a certain amount of records from the database Sorted out (View Comment after SELECT Statement). -- CODE FOR STORED PROCEDURE declare @CatalogID smallint, @Keywords varchar(75), @SortOrder va...more >>

I have a column of data type varbinary that was accidetally used to enter text. (persons last names). I need to convert the data to varchar. Using convert or cast, converts only the first character in the string. (ea : MARC is saved in the varbinary column as 0x52004F004700450052005300. Using c...more >>

Hello, Version: MS SQL Server 2000 Developers Edition running SP3a I get the following error when I try to execute sp_help_job and insert it into a temp table so I have access to the data for reference purposes: ERROR: Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_inf...more >>

Hi, Is there any script to encrypt all the stored procs and views from the database, instead of Altering them with "WITH ENCRYPTION" manually for each and every Stored proc and view. The situation is, until now we never encrypted any views or stored procs for our clients, but from now o...more >>

Is there a limit to the number of rows or characters that can be in a result set? In the case of ADO.Net in a Dataset? Thanks ...more >>

Hi All, I have a dimension table, below : CREATE TABLE dbo."Product"( "ProductID" INT NOT NULL IDENTITY(1,1) PRIMARY Key, "ProdCode" CHAR(16) NOT NULL, -- product code "DeptCode" CHAR(2) NOT NULL, -- department code "Period" INT Not Null, "Pr...more >>

I am struggling with a sql query. I have a three tables, T1, T2 and T3 where T1.Key=T2.key and T1.key=T3.key What I want to do is create a query with results as follows: T1.Key T2.Fld1 T2.Fld2 T2.Fld3 T3.Fld1 T3.Fld2 T3.Fld3 111 222 333 444 NULL NULL NULL 111 2...more >>

How can you see the explain for a user defined function? No matter what I try, I can not get the explain plan for it. create Function XXXX ( @parm1 int ) returns varchar(2000) as BEGIN Select col1, col2 from table1 join table2 on table1.id = table2.tab1id where table1.id = 333 ...more >>

I have a VIEW that calls a UDF that refers to my VIEW - is this a bad = circular referance? I needed to roll up a 1 to many column into one = large VarChar field - here is my working sample of code - but others are = saying it's bad I have a circular referance. Here is my example SQL:=20 SEL...more >>

I have a table with an update trigger on the table. The update triggers writes a single record to a log to record that the user update one or more records. The return from the Java executeBatch call is 1 (the result of the trigger) instead of the number of rows in the table updated. How can I get...more >>

I want to find if a given user has permissions to execute a given procedure. I've been looking for a meta-data function or information_schema view that will show that. I can't use the PERMISSIONS function because that only shows perms for the current user, not any specified user. Because the ...more >>

Can sql server do reqular expressions or anything similar to them? thanks ...more >>

Hello, I have a table called "State_table" which contains items that will appear in a drop-down list. The table has one cloumn called "State". e.g. State_table: State: Michigan New York California Ohio Maine The drop-down list uses a vi...more >>

The log file on a customer database seems to be growing without bound. I've backed up the database daily, tried dbcc shrinkdatabase and dbcc shrinkfile, but to no avail. The database recovery model is full. Any suggestions would be greatly appreciated. The log file is now at 28G and we're abo...more >>

our database was working until we had a power outage and now it says "Cannot generate SSPI context." each time you try to log into it.. what causes this and how do you correct it? we are using integrated authentication with Active directory on W2K servers ...more >>

I have just started teaching myself VB.NET and SQL. I just need a little help in the syntax. I am at a loss on how to do this: I have a dropdown list that needs to show a "FirstName, LastName" from a table. Here is what I have so far: Private Sub SalesmanLoad() Dim ds As Da...more >>

Changes are not detected..... Link provided by O.J. http://support.microsoft.com/default.aspx?scid=kb;en-us;195565 Here's a situation that I have at a client: When an unindexed field is updated an automated process (AP) w/in the app does not detect the change; ironically the AP scans the ...more >>

Hi, If I create a view that selects from one or more tables, each with a locking hint "with(nolock)", e.g. Create table Client( ClientID int identity(1,1), AcctNo varchar(10), AcctName varchar(50)) go Create table Bill ( BillID int identity(1,1), ClientID int, Amount money, DT datetime ) ...more >>

hi all, i am a graduate student and now I am writing my final thesis in the area of the web application security. I have read recently the article written by Chris Anley (more) Advanced SQL Injection avaliable at http://www.ngssoftware.com. There is one example regarding the extended SP xp_exe...more >>

Hi, I'm wondering how to receive a list that only contains 2 entries per employee (say, ordered by DATE dsc) The kicker is that I'm working on a SQL 6.5 db and the TOP function is not supported. Any ideas? Table ex. Emp Date Rating 1 2-3-2004 4.5 5 3-1-2004 5.2...more >>

Hi, I have a problem with (What I thought) would be a "simple" query. I have three tables. Example data for each is as follows EMPLOYEE (EmployeID is a unique AutoInc numeric keyfield) EmployeeID,Forename,Surname (among others but am only interested in these 3) 1, Bob, Up 2, Ben, Dove...more >>

I'm still struggling from yesterday with my Dynamic SQL triggers. My FieldContents variable is not getting filled. Can anyone suggest how to fix my code? I'm just trying to get the character fields working first and then I'll add the other field types. Here is the Delete trigger: CREATE TRIGG...more >>

Hi, I have a primary key constraint on a table defined as follows: ALTER TABLE AUDIT ADD PRIMARY KEY (AUDIT_ID, MEMBER_ID) go Now, I have to modify Primary key contraint definition to have only AUDIT_ID column. How can i do that as I don't know the primary key constraint name. p...more >>

Hi I posted earlier to extract letters and that worked perfect I have been trying to figure out how to select distinct postcode letters from the displayed results as some of the values I have are N1 n2 n3 so I want it just to display one n and not 3 in this instance SELECT p.salesno,c...more >>

Hi I want to be able to extract Letters only form a table currently data in my table will be either 1 letter 1 or 2 numbers or 2 letters and 1 or 2 numbers example BG99 B1 NW23 N9 so all i want to do is to be able to extract and display the letters only. Any one know how to do this ...more >>

Actually, I don't know if the word "script" is correct or not, but here's my problem... I have a Query Analyzer script file that I keep open for lots of potential issues each day. I never run the whole thing. I just highlight the piece I need, and click play. I have about 300 statements i...more >>

It is better to use a kay as INT type or as CHAR(3 Byte) type ?...more >>

I want to get the maximum of sum with one select statement. The database is NorhtWind and this is the query. select orderid, sum(quantity) from [order details] where orderid between 11000 and 11005 group by orderid order by 2 desc I know that i can use this statement: select Top 1 or...more >>

Dear Sirs, I have Microsoft managment console 1.2 (enterprise manager) Version 5.0 with service pack 4 that came with microsoft sql server 7.0 is there any way to register a new sql server 2000 to it ? to be able to check server databases and so on without upgrading ? Thxs *** S...more >>

In the database I am querying we have two tables as follows: MAIN_TABLE ProjID | ParentProjID | Name ------------------------------------------ 1001 | NULL | Cleethorpes Office 3234 | 1001 | Carpet Fitting 6642 | 1001 | Electrical Fit-out 4532 | 1001 ...more >>

Hello, Is there a way to pass the result set of a cursor to a second stored proc for processing. I got to clean up some really nasty SQL 2000+ lines in one proceedure and would like to break it apart a bit. Thanks ...more >>

The following SQL statement: @ColumnName = 'FR_0' Select * from LabelTbl_0 where @ColumnName like '%'+@searchkey+'%' is giving problems. I am not able to assign the parameter @ColumnName a value. In the above query, if I replace @ColumnName by FR_0 , the query gives the result. Otherwise it...more >>

Hi there, I'm stuck in here, could anyone help me? I have to build a result Query order by Points, from 3 distinct tables, and I'm using UNION to agregate all this 3 distincts selects, but the end result is one line per each select, and I wanted that SQL sum all the "pontos" fields ...more >>