Hello, I'm running a simple query that has a field named "Active". The "Active" field has either a value of "1" or "0" ('active' or 'disabled'). However, instead of returning a "1" or "0" (values stored in the db) I need to return "Active" or "Disabled" based on a simple IF statement. I d...more >>

Hi All I am developing portfolio management application. In this application i am fetching data from database and populate to dataset and that dataset is getting binded to the datagrid... it is multiuse application and one portfolio is getting accessed by more than one user. somehow i wan...more >>

I have a insert/update trigger on MYTABLE. Is it possible to avoid/deactive this trigger under certain conditions? For example, when I run a stored procedure. Any ideas? Maybe setting somekind of temporary variable and unsetting it after the store proc has run? Thanks! ...more >>

Hello, How can I turn the following Access query into a SQL query? Every time I try, I get Cartesian product SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1, dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2, dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.A...more >>

Is there a simple way to do the following? The database table has many records, each record has its own unique RecordID (PK, int), some of the records can have one text field like an intenrifier (SomeID) with the same value. A simplified schema is looking like this: RecordID Som...more >>

Hello, I want to be able to script out the permissions on my stored procedures e.g. the GRANT EXECUTE statement but NOT the stored procedures themselves. I noticed in EM that you can script the objects and INCLUDE the permissions on the stored procedures... but I want to be able to generat...more >>

select 'Customer_Greeting_Section_total'= Case when Customer_Greeting_Section_total is null then 0.00 else left (ROUND(Customer_Greeting_Section_Total , 2, 1),4) end from DTSTEMP1 The col is defined as float When I run this I get Server: Msg 8115, Level 16, State 8, Line 1 Ari...more >>

I need to get the record count for all tables in a DB and get the description of that table (which is stored in another table) i need something like this. for each (exec sp_tables @table_type="'TABLE'") select count(*) as cnt from TABLE my sql isnt that great.. but what i need to do is...more >>

Is there a way to do a union of 2 tables, keep them separate and sort the tables separately? I know you can only have 1 "order by" per union. For example: SELECT id=convert(varchar(11), au_id), last_name = au_lname, table_name = 'authors', state FROM authors WHERE state = 'CA' UNION ...more >>

I have to load around 68 CSV files into one table. I have named the files 1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to load these. I am not very proficient with VBScript ...more >>

I have a query that's joining a messload of tables to populate a single table used later for OLAP reporting. The source tables, and the OLAP table are in different databases. Basic Form: INSERT INTO OLAPDB.dbo.SomeTable SELECT lots_of_columns FROM atables INNER JOIN lots_of_tables.... ...more >>

Hi All I am trying to ouput the results from my query in the form of XML. The query is like this: SELECT a, b, COUNT(S.c ) AS x FROM s GROUP BY a,b ORDER BY a,b FOR XML AUTO, ELEMENTS If run this, i get an error like this: Server: Msg 6821, Level 16, Sta...more >>

Hi, I need to select and later Update a database with information I select from a Access 2000 database. In the following T-SQL, I want to select only one record for each VName not already exists in my database. Since each VName have many records in the table, I'm only looking with the record...more >>

I have a script that I am running from a Query Analyser that I want to put into a SP eventually. In the script, I have 5 Create Views (which I drop when the script exits). But I have to have "GO" after each Create View or I will get an error: 'CREATE VIEW' must be the first statement in a...more >>

I want to be able to return only the SUM of those numbers in an address field. What is the best way to do this? Address Field: 1746 Wellesley P.O. Box 3175 4141 65th Street 773 Florecita Terrace Should return: 1746 3175 4206 773 I have googled this and found http://www.sqlteam.c...more >>

We have an application that has been running fine for several months. Then this morning everyone in the company is getting timeouts when attempting to save image data (tif). It seems to work if the image data is only a few a KB. But anything over 30K seems to hang and timeout after a minu...more >>

Hello all, I am looking for some advice on the design of a database for a particular application I am tasked with. The database is part of an equipment monitoring application that records numerous equipment data readings and stores them to a database. The readings are taken every few seconds an...more >>

i want to get only the first (max sum ) value of each grouped item after a select example of the returned records where: two columns (grouped value company and sum sell price) in the table there are a nr of entrys of each company i calculate the sum for the sell price in each company ...more >>

We have a small application that holds text data for a report. Currently it looks like this, RegNo - ID of the person MostImportant SecondImportant ThirdImportant This has changed and now they only want Important (since it is hard to determine what is most important, second most import...more >>

Hi All, I have been working on the following problem for more than two weeks but without any luck. We have ftp server on windows 2003 exchange server with IIS 6.0. There is a DSN to connect to SQL Server 2000 databases from the FTP site. When a file is ftped, it populates the database wit...more >>

Hi, I'm sure this is real easy, I just want to make sure that I do it the right way, so I'm going to ask the question. I need to put an "8" or "9" in front of an existing number (e.g.: 12010326) in a field in a table. I will be pulling the data from a staging table into a fact table. Th...more >>

Hope this is as easy as I think, but I am struggling to find answer in BOL, etc. I have 3 simple tables and want to link them on the same field, "ProductID". The first table has all productid's on open SalesOrders and the qty sold. The second table has productid's in Inventory for OnHand quanit...more >>

Hi, I have this scenario: Table of Folders: FolderID, FolderName, ParentFolderID I want to be able to *copy* a whole hierarchy of Folders, i.e. my stored procedure gets three parameters: SourceFolderID, ParentFolderID, NewFolderName. It creates a new folder whose name and parent are tak...more >>

I have had the understanding that using TOP speeds up a query. Say like: SELECT TOP 10 Titles.* FROM Titles In the above statement it is not necessary to continue to search all the other records and the search could be stopped after the ten first have been found. Is this what happens or i...more >>

hello, we are using a third party issue management system. On the back end it uses SQL 200 sp3. it has different tables for different types of documents (issue, sir, rick, etc...) and columns for properties on the documents (created by, assigned to, Description, etc...) i see an error ge...more >>

I'm trying to count the number of people that I have in a building at any given hour from a table that basically looks like this.... CREATE TABLE InOutTimes ( In_Date smalldatetime NOT NULL, Out_Date datetime NOT NULL ) go INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 10:00:00'...more >>

I have a stored procedure that feeds a graph in an access ADP, and the numbers are being sent as 0.883838..... can i format the number being returned from the stroed procedure to a percent? (ie 88.38%)? and also, how do i limit the number of decimal places? the columns being used in the ca...more >>

Is there a way to launch QA and tell it to open a stored procedure to edit... I know you can do this from within QA after it opens but... I have an app that I use to stay organized. I associate all related correspondece, contact info and files to the Contact & project. While working with ...more >>

Hello, I need to eliminate duplicates from records containing a text data type. Here is the query I try : --- select NewsGroup.NewsGroupID, (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName -- Line 10 NewsGroup.OnlineFlag from... where... --- And here i...more >>

I have a stored proc called "dbo.SetSettlment" and a used defined function called "dbo.GetSIFByType". The function works great, and so does the sp. Problem is, I can't get them to work together. See below, I have three statement. The first two work fine, the last doesn't. How can I get around ...more >>

I am trying to extract data based on the following: Select * from table where datefield < OneMonthAgo WHen I try the DATEADD, it gives me everything from one month ago to today. I want everything on the other side of one month ago. This is what I currently have select custid, billname,...more >>

Using SQL Server 2000, I am trying to produce a showing the top 3 zipcodes in each of the top 5 counties. I have tried lots of variations and I am really stuck. I get 393 rows in the final resultset where I really want only 15 rows (5 counties times top 3 zipcodes in each county). I am beginning...more >>

Hi, I hope someone can help me. I have a stored procedure that creates some dynamic SQL which is used to populate a payroll system with timesheets information. As part of the SQL that is created a udf is called to calculate the employees base pay for the month. the issue is that if I execu...more >>

How can I find all the varchar columns in a table? I'm looking for an existing empty or null column rather than add a column. There have been quite a few un-used columns that were added years ago for processes that where either never implemented or are no longer needed. In the past I've ju...more >>

Hi, I installed sql server 2000 developer edition (SP2) on Windows NT 5.2. This machine (SQL01) is on the same network. When I use enterprise manager on my desktop and try to add new sql server registration, I dont see SQL01 on the list of available servers. When I try to add it, I get an erro...more >>

Hello - My background is networking and system administration. I know basic programming constructs, and have done .bat, VBA, and *nix shell scripting/programming. I am looking for, at most, two books that can help me in my new role as DBA. Most of my SQL work will be bringing in data that...more >>

i'm performing a massive database transformation, so this sample might seem completely bizarre, but it is only one small part of a 5000 line operation to perform transformations. so try not to read this in the context of "regular" database design, because at this stage the design is mid-transfor...more >>

I am trying to figure out how to do this, and have run into a brick wall. Any help is appreciated. Within a SPROC, I need to retrieve a value from one query, assign it to a variable and use the variable in the next query. When I try to create the SPROC, I get this error: Server: Msg 116, Le...more >>

I sometimes use the ALTER TABLe to add certain fields in my table. I need to do it programatically. I will not get into why eventhough I have access to Enterprise manager and can use that to do it that way. My question is I'd like to know if there is syntax that I can use when I ALTER TABL...more >>

Hi, I need to call a function in a sql query in a stored procedure to calculate time differences between various dates. I have a function that uses a cursor to sum up the totals of these numbers, but it runs very, very slowly. I can accomplish the same results without a cursor by using a tem...more >>

using SQL SERVER 2K 1. using INFORMATION_SCHEMA.PARAMETERS alone, how can I definitively know a user defined function from a stored procedure. I am currently doing this ********* SELECT * FROM INFORMATION_SCHEMA.PARAMETERS P JOIN INFORMATION_SCHEMA.ROUTINES R ON(P.SPECIFIC_NAME = R.SPECIF...more >>

Hello, I am getting the message Implicit conversion from string to date.... Please help Protected Sub btnShowDetails_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnShowDetails.Click Dim startdate As String = txtStartDate.Text Dim enddate As String = txtEnd...more >>

Hello. I'm trying to design an MS Access form that will fire off an email when a combo-box is changed. I need the email to consist of a brief message as well as the results of a query. Since both the view and the email message need to be tailored to the selection, I've written code to dele...more >>

Dev Tool: VB6 Server: SQL Server 2000. Environment: Windows 2000/Windows XP/Windows 2000 Server I have changed a user connectivity from WINDOWS NT trusted connection to SQL Server Authentication. I have granted the same permissions to the new user. The problem is as follows: T...more >>

Hello, I have a strange problem with a cursor that runs nightly. It only happens about once a month. The cursor takes Invoice information from a source table and places the information into a destination table in Document, Detail order; Document, Invoice 1 Detail, Line #1 Detail, Line #2...more >>

Hi all, I'm not quitely sure if it's the right group to write, but it's the best, that came into my mind. I was wondering if there is a way to retrieve the image size (height and width) of the image, stored in column of Image data type in MSSQL. I've googled around, but haven't found simila...more >>

Hi Can I create a User defined function with output parameter? If yes then how? Thank You, Abdul Akbar khan ...more >>

Hi all, is it possible to print the results in comma seprated text. i.e col1 ---- Jhon Henery should be displayed as Jhon,Henery Is possble to display the results in single select statement?. Regards ...more >>

Hello, I'm calculating with 'datetime' fields and have the following question/problem. To get a period I subtract the field <Timefrom> from the field <Timetill>. Both field have the type 'datetime'. 2005-09-22 15:00:00.000 - 2005-09-22 13:30:00.000 = 1900-01-01 01:30:00.000 This is working...more >>

Hello All, I'm learning SQL-DMO programming via VBScript and I'm new to VBScript programming as well. Would anyone know of a News Group exclusively for DMO programming ? Thanks, Gopi ...more >>

Hello, I have just changed the datatypes in a non production table from smallint > tinyint and datetime > smalldatetime. I had anticipated that this would cause the size of database to shrink slightly but instead it has increased from approx 9MB to 12MB. Anyone know why this might be? TI...more >>

Hi all gurus out there ;) I have a cutstomer table, now i want to find a span of customerid's in this table. Lets say the customerid's that are taken are: 1-10, 11-13, 15, 18, 20-33 and so on. Now i want to create a query, that find the non used customerid's and place them in a table, ...more >>

Folks, How can I convert a real value to a decimal value? As long as the real value is not zero I am getting an arithmetic overflow error. I tried both convert and cast. Does anyone have an idea? Cheers Stephanz...more >>

I have around 400 different Select -- Insert queries getting data from 200+ tbles. Data in each table can vary from 1 million to 3 million. All the data is being inserted in the same table. What's the best way to do this? Insert statement obviously wouldn't work. Is there a way I can use al...more >>

Dear All, If I used a SQL command "Delete" to delete for example 1000 records, can I get it back after? It is because I executed the delete command and later found out I need some records back. Thank you for your help. Daryl ...more >>

Hi, I am creating database taking database name as input from the user. In database name I am replacing '[' with '[[' and ']' with ']]' and = passing it to my query. Query has a sort of placeholder for database name where I use this = database name. I am having query in this format: Creat...more >>