I have a Union Query that works as a View, and is the Source for an MS Access ADP Report. However, I need to pass in some variable parameters but because it uses "as" it won't work. Is there any way to delimit/work around the "As" or some way to pass in the parameters ? I can use UDF in ...more >>

Hi, Im trying to insert some data into a sql server db (well msde) using vbscript however i cant seem to get parameters to work. I get a type mismach trying to add a parameter. The DB fields are both varchar size:2048. Any ideas? See the error message on the bottom of the post. Angelo ...more >>

Hi, I have a table like this: CREATE TABLE [Subs_Tracks] ( [Code] [int] IDENTITY (1, 1) NOT NULL , [TargetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [WireID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Source] [varchar] (50) COLLATE SQL_Latin1_Ge...more >>

Hi, I've got a table whith an index on the CarId column (IdentityIncrement). Now I'd like to write a stored procedure which inserts a new record into this table and returns the CarId of the newly generated record. But I don't know how to do this w/o using a SELECT after the INSERT. Can an...more >>

I've got a typical many-to-many relationship set up with three tables; A, B, and C with table B being the junction table. In some cases, the application creates a one-to-many relation between A and C. When this happens, a column in C will contain a value that indicates this is the case. ...more >>

Thanks for the link to the faq. If my application is only going to be based on the US-English locale will all characters be covered with the non-unicode data type? Some fields may need to store html markup too i.e. tags etc. TIA! "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in mes...more >>

Hi, I have a web site that is choking when we do a search using the query that I built. I want to be able to use the Index Search from SQL Server but I'm not sure how to start. can anyone help me. thanks rafael ...more >>

Okay, I'm pretty decent at working the Enterprise Manager GUI to get what I need, and even adept at using Query Analyzer to create and run queries, but I pretty much suck at being able to write a decent script, create a stored procedure or even run a query to create a table without hefty cut and...more >>

Hello, I received this error message from SQL when I attempt to execute this statement: SELECT CHGID, SUM(chgamount-chgallowed) as "wo" FROM CLCHARGE WHERE CHGID = ( SELECT CHGID FROM CLCHARGE WHERE BILLED = 'b' and status = 'y' and chgamount <> chgallowed and writeoff=0 and ...more >>

sql2k sp3 Howdy. Im trying to return the year and quarter in a string inside a UDF and having data type issues. The desired output would be 2004-1. alter function fnGetQuarterYear (@date datetime) returns char(6) as begin declare @qYr int set @qyr = cast(datepart(YYYY,@date) as Char(...more >>

Anyone used WMI to get stats back on MS SQL Server? Any good articles anywhere? -- Ian (Freebasing On Boredom.......) BEING IN THERAPY And yet, having therapy is very much like making love to a beautiful woman. You... get on the couch, string 'em along with some half-lies and evasions,...more >>

my problem is *******Error converting data type varchar to numeric.************** im using asp and stored.. this is the a birthday of users i save the date like this year&month&day&hour&minute&second for example the date is 31/10/1990 13:30:02 i save this as 19901031133002 ( the ...more >>

Im using CDONTS to send email from an SQL stored procedure... Is there a way to user carriage returns in the body of an email went sending within an SQL Server. below is the code i'm using the send the email: ---------------------------------------------------------------------------- ---...more >>

I've got an Access app that has a module that calls a SQL 2k DTS package. It works just fine on my box of course. When I try to deploy it to my client, it stops on the section of code where I'm defining objects that are part of the "Microsoft DTSPackage Object Library". I tried to copy this si...more >>

Hi all, I have a problem when passing back the custom Error Messages from SQL Server Stored Procedure to VB.Net or VB 6. In my stored procedure , I use while loop to insert records into the table and after the insert statement, I check the @@error and create my own Error Message and retur...more >>

I found a very strange situation when I was troubleshooting why one of = my statements was running so slow for the Alert Service. =20 Take a look at these two statements: =20 SELECT (CASE WHEN MAX(t1.CompletedOn) IS NULL THEN '1/1/1900' ELSE = MAX(t1.CompletedOn) END) FROM Table1 t1=20 LEFT J...more >>

Hi I have an issue on SQL2000 Enterprise edition on a 3 processor machine with 1.5Gb RAM. When I run the query: Select ID, OrgName, Address1, Address2, Address3, Address4, AddressID, County, Postcode, Posttown, Privateaddress, NationalRecord, Level =0 FROM vSearchOrg WHERE recordstatus =...more >>

Hello I need to store small numbers with 2 decimals? 0.22, 0.44, 1.23, at most 2.00 which datatype would suite best? TIA /Lasse ...more >>

im a newbie set @xx=DATEPART(year, GETDATE()) print @xx the value=2004 but i want to make it like this 20040000 i tried set @xx=DATEPART(year, GETDATE())+'0000' but it gave an error. i think it as a conversion problem.... how can i add a extra value to a variable... ...more >>

Thanks for any help. -- Requirement: -- Create check constraint that verifies that newly inserted entries are not overlapping existing -- En entry insert tst1 values (20,3) should not be allowed. How do I create SQL query? I can easily make function out of it if object_id('dbo.tst1') is n...more >>

Is there an easy way to retreive just the integer portion of a number without using the FLOOR or CEILING functions? Thanks for the help. Derek Ruesch...more >>

I have a SQL query question. How would i write a query to get the result as in the result section. I have to get all the columns in table 2 with ID2 = 1 and join with table 1 but i need to get only distinct values for the Code. Table1 Code ID ======== WH1 1 WH1 2 WH2 3 T...more >>

I have a query wich returns data like this: invNr invDate invAmt payDate pydAmt INV001 1.1. 500 5.1. 100 INV001 1.1. 500 10.1. 400 INV002 5.1. 1000 5.1. 1000 INV003 10.1 100 15.1 50 INV003 10.1 100 16.1 30 INV003 10.1 100 17.1 10 This is a list of invoices, their dates, amounts, a...more >>

I am working to break out a column in a database. It is a varchar(7500) that has a ton of junk in it. I have used REPLACE to assign delimiters (,) in each row of the column. I have used SUBSTRING and CHARINDEX with success to grab the first chunk of data but I dont know how to progress through ...more >>

i know, i know. DB2 isn't SQL Server. But i already have a post in the .db2 newgroup, and i'm getting desperate. i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT...more >>

Hello, What is the optimal way to insert multiple rows (around 1000) from a web application into a table? The user enters multiple lines into a text box (up to 10,000). The ASP.NET application breaks that data into a string array. Each line is an item of that array. The user clicks Submi...more >>

Hello Im trying to design a simple database to store results in a golftournament im gonna have with some friends. Im not sure about if i should have the results in one table or in more..... I'll tell how it runs irl..... 20 players play first round, 8 best players (with lowest score) qual...more >>

Hi guys I have created an instead of trigger on a table. Basically when a record is updated in TableA the trigger copies the original record across to pre_update_TableA before updating TableA. Problem: The application that uses the database does not work once the trigger is in place. The tr...more >>

Hi How can I find out what version of MDAC is installed on a Machine. And would there be any problem with me installing both MDAC 2.7 and 2.8 on the same machine Ian ...more >>

Hello, I have a sql-programming-problem. Why does the following not work? INSERT INTO [prefix Person] (Username, [Member since])VALUES ('squakier', DATE()); The table looks like this: CREATE TABLE [prefix Person]( PersonID INT NOT NULL IDENTITY(1, 1) CONSTRAINT[Per...more >>

When I attempt to substitute the @prog_beg_date into the queries, I recieve a syntax error converting Date time string - Here's a few of my attempts, but thus far the problem has eluded me: - b/c variable date time is declared, there is no need for the Convert string (this fails) - Put the Conv...more >>

Hi! I have tried to read up on the issue, but still posting my own little problem. I have developed a webapp on a win2000 server with a sql server 2000 sp3 residing on another server. Now we are setting up a new box with win2003 server. We also have a new box for the sql server 2000 sp3. Th...more >>

I've a table with the following structure:- RoomID (Primary Key) RoomDescription (varchar(100)) MinOccupancy (smallint) MaxOccupancy (smallint) Let's suppose I add a room into the databse with the values... RoomID = "Big Room" RoomDescription = "A big room" MinOccupancy = 3 MaxOccupan...more >>

is there a way when pulling datetime fields in a select statement that I can only pull back the date portion and not the time part of the value. If so, how do you do it? -- J.Daly structure:interactive Ph: 616-364-7423 Fx: 616-364-6941 http://www.structureinteractive.com ...more >>

Hello all. I've got a simple query that returns 6 fields from 3 joined tables, and uses a subquery in the where clause. The query executes but returns less than half of the rows. I know it returns less than half the rows because when I break up the query by first creating a table from t...more >>

I am trying get the total count of rows from 2 tables in one select statement. I found the following online, but it does not like the syntax. SELECT count(*) FROM ( SELECT * FROM SLDeals UNION SELECT * FROM ChDeals ) Any ideas or suggestions. Thanks, John Willia...more >>

Here is the DDL of my database CREATE TABLE [dbo].[impersonation] ( [person_key] [decimal](18, 0) NULL , [proxy_key] [decimal](18, 0) NULL ) ON [PRIMARY] GO I have an array of proxy_key (1,2,3,4) and person_key (5) Is is possible to insert the above data in one insert statement?...more >>

I've been using xp_sendmail to send CSV files to users for their report needs. This process has become difficult to manage due to user mail box limitations. We have decided to create a CSV file on a website and email the user a hyperlink to the file. So, what is the best way to create a CSV ...more >>

As part of a Server Agent Job, I want to run a .bat or .cmd file. Can somebody tell me how to do this as a step in the job.. I thought it would be a s simple as creatinga a new step, selecting Operating System Command, and then typing c:\mybatchfile.bat in the command box.. But that doesn't ...more >>

Hi, I have two queries: SELECT TOP 1 * from employee where grade = 1 SELECT TOP 1 * from employee where grade = 2 I need to get the results from both these queries with a single database call. Is there way to do it using user defined function or stored procedure. I know that we can ...more >>

I am trying to use remote SMTP email forwarding from SQL server. Question for SQL Gurus. What kind of alerts do people put in the SQL Server in prod and devlopment servers?? Any sample code is really appreciated. I will be using CDOSYS route to forward emails to remote SMTP via S PROC. ...more >>

Hello, I Created the following trigger :- CREATE TRIGGER tk_Test ON dbo.Test after INSERT NOT FOR REPLICATION AS EXEC master..xp_sendmail 'TESTPERSON', 'Blob' Hears the odd thing. The first time I ran it the user I was loggin in as didn't have permission to run xp_sendmail so it errored...more >>

I have a stored procedure that takes a table name as an input parameter. By querying the syscloumns and sysobjects table, I am able to retrieve the fields and their datatypes and build a string that generates a 'create table' script that I execute to generate a temp table that mimics the orgi...more >>

Hello, I'm a bit confused as to what's been going on with the math in SQL Server. When numbers of high precision are being divided they can come up completely innacurate from the true (rounded) result. Why is that? For example, in the following, I should receive something along the lines of...more >>

Hi How do i post a message to my MSMQ-queue, from my Sql server??...more >>

I want to know what tables have a column (field) with a known name. Then show the user this tables. There is a MsSql statement they make this a that i can include in a ADO query component ? For example all the table which habe a column "CITY" How can i obtain it ************************...more >>

Hello Everybody; Can you help me how to insert records in different database using trigger? Please give me some code to follow. thanks....more >>

Does anyone knows how to export data from SQL to a text file and excel by using VB6.? I am doing programming in VB6. I really need you help badly. Regards Amar ...more >>

Hello, I have two tables named errorstarttime and errorstoptime. Errorstarttime has columns errorid, starttime and errorstoptime has erroridoff, stoptime. I've made a query to join them. Query goes like this: SELECT errorstarttime.ErrorID, errorstarttime.StartTime, MIN(ErrorStopTime.Sto...more >>

I'm using SQL Server 2000. I've added a Trigger to a particular table that sends an email every time a record gets inserted into the table. The stored procedure which causes the email to be sent is basically just a slight variation on the 'sp_send_cdosysmail' stored procedure. Is there a way...more >>

Hallo, How can I convert autonomous transaction used in Oracle PL/SQL to MS SQL without using flat-files or temporary-tables. I want to write logging to a table where the insert in the log-table is independent of commit/rollback outside my log-procedure. Searching te web was not usefull, also...more >>

I have a data access layer written for ADO.Net which builds queries on the fly based on the the table schema. The data is returned in a structured dataset. In stead of a denormalized dataset which I would have done in classic ADO. Below is a query that is generated by the data access layer. When...more >>