I want to create, alter and delete tables (not records) from ASP code. Does anybody know of a class (preferably ASP or DLL) that hides the complexity of the Data Definition Language? Cedric ...more >>

It has been a couple of years but I remember being able to format numbers, dates and text in SQL Server statements, can't find much in online books , Where can I relearn this, thanks Jim ...more >>

I am trying to loop through the databases on a server (SQL 2000) and dynamically run sp_helpfile against each database on the server. Of course that means I need to store the name of the database as a variable or parameter. When I use the following code I am told "a USE database statement is ...more >>

Hi, I am wondering if there is any sql syntax that do the switch case like If there is 'X' return X Else if there is Y return Y else return null Thank you in advance, Sunny ...more >>

i have a view that simply performs a query to a linked server: CREATE VIEW Sales AS SELECT * FROM REMOTE.pubs.dbo.Sales If i perform a query against the view: SELECT * FROM Sales WHERE qty = 15 i get the following execution plan StmtText |--Filter(WHERE:([REMOTE]...more >>

Hi, I'm working on some "daemon" able to restore whatever come into a given folder to a "stand-by" SQL Server (sort of log shipping, but also to restore full backup and so on). I'm now faing a problem : I can't put the logical name of the database from the backup file to a variable. Here...more >>

I was asked by a client how I would normalize some data that they had. An example follows: XIBMH002602382,XIBMH005,D:\005\00002382.TIF, XIBMH002602383,XIBMH005,D:\005\00002383.TIF, XIBMH002602384,XIBMH005,D:\005\00002384.TIF, XIBMH002602385,XIBMH005,D:\005\00002385.TIF, XIBMH002700...more >>

Both of these show up in the sql server log what does it mean? in what cases can these occure? 2005-01-28 14:28:24.50 spid90 SqlDumpExceptionHandler: Process 90 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. Exception Address = 1467...more >>

please run: select 50000000*80/10000.00/366*31 select 500000000*80/10000.00/366*31 select 5000000000*80/10000.00/366*31 and kindly tell me the reason...more >>

I've created a job in Enterprise Manager under Management | Jobs. I double click the particular job, select the Steps tab, then double click the step name. In the command window are four stored procedures that I execute: exec sp1 exec sp2 exec sp3 exec sp4 This works fine. If I comme...more >>

Hi, I get an error when I try to delete multiple rows from a table. Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an express...more >>

Hi, I have a very strange problem and just wanted to run something with you guys to see if this scanario is possible. Is it possible that data inserted into a table using an insert query within a stored procedure and called from an ASP page could be selected by a diferent ASP page calling ...more >>

I have a client that is wanting to know the advantages of SQL Server over Oracle - to be used by in-house developers as the database for custom applications. Can anyone provide a few high-level points or links to articles that compare the products? Thanks! ...more >>

How do I do this sort of thing: I want to call a stored procedure, but only return a limited number of rows. I want to pass that number in to the stored procedure. Like This: EXEC spFindNews 25 Then, in the Stored Procedure: CREATE PROCEDURE spFindNews @NumLimit int AS SELECT TOP @Nu...more >>

I'm in the process of designing a new table with a comment column that could be either varchar(7000) or text. I wanted to get an idea of positives or negatives of going with the varchar(7000). The other columns in the table add upto 200 bytes so the row size would not exceed 8K in size. Thanks...more >>

Hi, I am trying to create a select statement with a where clause that will get all records when a date1 > date2 and where dat1 - date2 is > 1 day. How can I do this second part of the where clause? Thanks ...more >>

We have a system with the following table: CREATE TABLE [dbo].[CategoryDocuments] ( [SystemId] [tinyint] NOT NULL , [CategoryId] [int] NOT NULL , [DocumentId] [int] NOT NULL , [IsSaved] [bit] NOT NULL , ) ON [PRIMARY] GO CREATE UNIQUE CLUSTERED INDEX [PX_SystemId_CategoryId_Docum...more >>

I have a Stored Procedure that returns a result set. I want to call that stored procedure, and SELECT the output of the result set INTO a table. I don't want to hard code that into the stored procedure because it shouldn't do that all the time. I know how to SELECT * INTO MyNewTable FROM ...more >>

What is the minimal permission for users to view the Diagram in Enterprise Manager? Thanks Ed...more >>

Hello Everyone, I have a procedure which returns profits made by sales persons. for this I need to access 2 data bases. to calculate the profit I have written a function on one of the databases but I get an error asking me to set ANSI_NULLS and ANSI_WARNINGS. when I set these I get syntax err...more >>

I am getting this error when executing an insert procedure Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E57) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated....more >>

Hi, why the first statement returns nothing, but the second one returns the result I want? select soldto.soldtonumber from soldto where soldto.soldtonumber not in (Select soldto from fsosoldto) SELECT dbo.SoldTo.SoldToNumber, dbo.FSOsoldto.Soldto FROM dbo.SoldTo Left JOIN...more >>

Here is my query Select Prospect.XTeleLink as exp1, TeleScrp.XteleLink as exp2 from Prospect, XTeleLink Where exp2 <> exp1 and TeleScrp.XResultCode = 9 What I want is to find XTeleLink numbers where the XResultCode = 9 in the TeleScrp table, but there is NOT at record in my Prospect table ...more >>

Is there a way to get line numbers into QA to determine exactly where an error occurs? How else can this be done besides counting lines? Thanks, Brett ...more >>

How do I know the service pack# installed on SQL Query analyzer client machines? Thanks, Ram Kumar...more >>

I want to grant updated privileges to a column only, but I can't get the syntax right. grant update columnname on tablename to username gives me syntax error. I seem to have a problem in naming my column. Can anyone give me the correct syntax?...more >>

hi guys when yukon is going to be introduced? Pat ...more >>

Created my first trigger a day or two ago that inserts the current date into a smalldatetime field WHEN another field is updated. My question: why is it that one cannot update that field a second time using my website front end? It's as if you enter in that field's info once and then you're stuc...more >>

During trigger execution there are logical tables 'deleted' and 'inserted'. Because of difficult and repetitive queries I want to call a stored procedure (from within the trigger) that reads from these special tables. However, an error is generated, it says that 'deleted'/'inserted' are inva...more >>

Hi, I'm importing contact information into SQL Server from Excel using OPENROWSET. The issue I'm having is with how the phone numbers get stored. The phone numbers have no extra characters like dashes, so they appear like 9495551212, which is equivelant to 949-555-1212. The phone number is ...more >>

Test this script. Can anybody explain why Microsoft designed it lika that? drop table dbo.TestTable go create table dbo.TestTable ( id integer not null ,c1 varchar(40) null ) go insert into testtable (id, c1 ) values(1, null) insert into testtable (id, c1 ) values(2, replicate('a' ...more >>

Hello, I am having trouble adding a UNIQUE CONSTRAINT to an existing column with duplicate key using WITH NOCHECK in SQL Server 2000. Here is my SQL syntax: ALTER TABLE user_email WITH NOCHECK ADD CONSTRAINT unq_user_email_email UNIQUE (email) Query Analyzer keeps giving me the err...more >>

Hi i have a view structured something like this : id catname productname 1 aa prod1 1 bb prod2 1 aa prod1 1 bb prod2 2 cc prod3 2 dd prod4 2 cc prod3 2 dd prod4 i need to insert to another table so the...more >>

I am importing data in the table which has primary key as identity. Data file doesn't has the data for the column. I created view which has the column except the identity column trying to import but getting error Server: Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into colum...more >>

I have a colleague who mysteriously lost his record in our Employee table. The "employee ID" field serves as the primary key on the table. How do I manually insert his record, including the old primary key value, back into the table? That is, how do I bypass the primary-key constraint? Tha...more >>

Hi All I know that I am supposed to submit a schema of my table, but it is a very basic one so I'll crack straight on with my query. In essence I have a 3 column table called STOCKTRANS, which consists of STOCKCODE, TRANSACTIONDATE and TRANSACTIONTYPE. Each time a user orders a stock it...more >>

Hello, I want to be able to give a couple of my junior dbas the ability to do a DBCC INPUTBUFFER. However, they do not belong to the sysadmin role. Is there a way that I can give them the ability to execute the DBCC INPUTBUFFER without making them members of the sysadmin role? If there i...more >>

Please Everyone Help I have worked with a MS Developer, but they could figure out what's wrong with the Code to SQL systems. "Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information." I have looked at th...more >>

I have a host of server logins, NT and SQL access level on my server. I'd like to iterate through the databases and find users who have no logins to non-system databases. Using master.dbo.sysdatabases, I can retrieve a list of all databases on the server, but not the type (System or User), u...more >>

Hello, is it possible to have a column UNIQUE but without regarding NULLs, that is allowing multiple NULLs? Thank You Joachim...more >>

Hi all, I need to create a web application. I will probably need to web pages in this application. On the first page, I would like to query a Sql table and list the rows returned by the query in a List. When user will click on any of the listed rows and click on Edit, I would l...more >>

I'd like to find the max value of columns in one record. Here is my table and data: CREATE TABLE [usability_summary] ( [summary_id] [int] NOT NULL DEFAULT (0), [message_id] [int] NULL CONSTRAINT [DF_usability_summary_message_id] DEFAULT (0), [links] [smallint] NULL CONSTRAINT [DF_usabi...more >>

How do I do this: Table_1 has ID | Col1 |... ---------------- 1 | hello 2 | hello again .... Table_2 ID | Col2 |... ---------------- 1 | World 2 | SQL World .... I need this as output Table1 with Col1 (Col1 = Col1(Table_1) + Col2(Table_2)) i.e. ID | Col1 |... ...more >>

When first connecting using query analyzer, you are prompted for login/password and the server you want to connect to. As you add new servers to the list, they then NEVER go away, even if you uninstall and reinstall SQL Server client tools. Are these server names being stored in a registry set...more >>

Given the syntax: DELETE [ FROM ] { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } [ FROM { < table_source > } [ ,...n ] ] [ WHERE { < search_condition > | { [ CURR...more >>

SQL Server 2000 Greetings, I have a table as follows: SysID LotNumber Extension ------ ------------ ---------- 1 1 2 1 A 3 1 B 4 3 5 2 6 2 A I n...more >>

Hi all, is it possible to get the transaction id inside a trigger as a context variable like HOST_NAME(), ... ? What I would like to do is storing the transaction id along with other audit trail data so that I can investigate at a later point which statements did run in one transaction. ...more >>

Hi, I am kind of new to MSSQL Server and I have a few things to clarify: 1. I want to be able to create some code which runs on the MSSQL server side. In this code I want to encapsulate all the logic for manipulating some tables. Suppose I have an insert which does some checks, a...more >>

Dear All, I have 3 databases in sql , one db have a master data and I want to synchronize to other 2 databases. is any sync. sw is available for that. or is any thing in SQL also. databases may be in same place or remortly also please help thanking you Regards K R Lal ...more >>

background: sql2k I have a quick question about sql sp3a. when i apply the sp3a, is that just for the sql or for analysis service as well? if i have ananlysis services installed, do you need to apply the sp3 for analysis service seperately (in MSOLAP|install|setup on the sp3a CD) or sql sp3a...more >>

I would like to convert a string into datetime data type. String contains date in the format 1:00:00 AM, 1/28/2005 select cast('1:00:00 AM, 1/28/2005' as datetime) Thanks Vijay ...more >>

I have a need to get data from a Linked Server from an Access application. My ODBC connection name is 'GMSUpsize', and it points to a database on my SQL Server (2000) named 'GMS'. I have a linked server on the same SQL box named PCCW. When I run the query: SELECT * FROM PCCW...Trans on...more >>

[sorry but my first post got a "non-answer" so I am reposting] I have followed examples in a couple of books but am still unable to step into Stored Procedures in VS.NET. I enable sql debugging in the project properties, open the sp in server explorer and highlight a line where I want to start...more >>

is there a possibility to transfer the IRR function (internal rate of interest) of VBA into a T-SQL udf or stored procedure ? Did anybody ever try this or has anybody a T-SQL example ? Thanks Juliane...more >>

I need to insert an email addrees, but only if the email address is not in the table -- David ...more >>

I have tested with the following select in a SPROC: ....... DECLARE @Column varchar(10) /* Get correct column*/ SET @Column=(SELECT Field FROM Test_view WHERE (LID = '49')) SELECT LDate, LTime, LDepth, + @Column FROM TimeL_tbl ORDER BY LTime That does not work and i am wondering why?...more >>

Will yukon be implementing anything like these 2 UDFS? Select * from numbers (7, 9) ----------------------------- number 7 8 9 Select * from dates ('3-Mar-2001', '7-Mar-2001') where date <> '6-Mar-2001' order by date desc ---------------------------- date 7-Mar-2001 00:00:00.0 5-Mar...more >>

This should be an easy one for someone(Other than me!) Thanks for the help! I am trying to accomplish the following: Update AM_Hist set Am_Hist.CM_ID= Xref_import_Comp.CM_ID where AM_Hist.CM_ID=Xref_Import_Comp.Imp_Comp_ID How do I need to reference the Xref_Import_Comp table? I kn...more >>

I'm having a brain cramp. I'm writing a stored procedure that will do an insert into two tables in a parent-child relationship. I want either both inserts to succeed or both to fail. Obviously, a transaction is required. If an error occurrs any time during the SP execution, I want to return t...more >>

I need to to modify a stored procedure so that it can determine if it is already running. I could create a table to add/opdate a status record, but I would prefer to read a system table looking for the proc running on a connection if possible. If anyone has done something like this, I would ...more >>

I have a simple table with only two columns: name score ------ ------ john 90 john 85 mary 100 john took 2 classes, so he has 2 scores. I need a SQL to get only the highest score for each student. In other words, I need the following: name score ------ ------ jo...more >>

We are wondering which storage type to choose. The number of images will be about 30 million and the size of one image will be about 30kb. What are the advantages and disadvantages of the two storage areas? Is there anyone who can help me with his expertise in this area? Please no answers lik...more >>

Hello, I’m currently working with SQL server and I’m SQL query generator that work real fine for my proposes. In the last days im working on using my SQL query generator with Access Database. The problem is that some group of queries takes really long time to process in Access (7-9 minut...more >>

I'm specifying a db where a physical site (parent record) can have between one and ten measuring devices (child records) on it each of which measures flow in cubic meters per second and records this every 15 minutes. In a given day each measuring device will provide 96 flow readings, and there...more >>

Hi Does anyone know of a tool to troubleshoot data access to the database eg a small program which continuously read/write to a database?...more >>

Hi all, I am trying to access the database roles of a database made in Sql Server 2005 (Yukon). I am trying to create a new role for a databse. Here is the code public void AddRole(string role) { Server server = new Server(); string strConnection = ""; ...more >>

I'm having problems inserting times into my SQL backend through my VB app I have a access front end that I am wanting to recreate in VB but when i execute my SQL insert statement instead of 09:00:00 showing in the field, 01/01/1900 09:00:00 or similar shows, i can insert only a date into the ...more >>