Hello! Is it possible to update records being shown in a grid when using an advanced SP, or do I have to create another SP to update the record? Using ADO to update, but that shouldn't be a problem I hope. - lars...more >>

I'm having problems getting a particular query to work. It works ok against JET / Access, but gives several syntax errors in the Query Analyzer. It's supposed to return results from two subqueries (Sub1 and Sub2) linked by an outer join. SELECT Sub1.* FROM (SELECT tblFeeTypes.FeeTypeID, t...more >>

Hello, I have an application running on my server that is scheduled to run every morning. this program does one thing... It gets a recordset of all the items in my paymentschedule table where the day of the month is equivelant to the variable "dayToProcess" Everything that is returned in that ...more >>

Can anyone tell me how to create a table in which I have information of all relationships between serveral tables within 1 database. I want to create a table with the next fields: Tablename 1 | Fieldname 1 | Name Relationship | Type relationship (1:1 or 1:n) | Tablename 2 | Fieldname 2 I ne...more >>

Hello, I wrote sp. And I use @@Error to get error Number inside of my sp (no problem). I can not get exect error message I need it to write ActionLog data in the table. I can get error message with placeholders from master.sysmessages table. But how I can replace placeholders? I would...more >>

Hi there, I am having difficulty in getting the temp table data in ASP pages. Let me explain my scanerio more clearly. There is a stored procedure which creates a temp table #tempTable and this table is filled in with data in the Stored Procedure. Execution of the Stored Procedure is don...more >>

Hi all, I have 2 tables names "order_header" and "order_detail" as follows(I have simplified the struture): Create table order_header ( order_header_id Numeric(10, 0) IDENTITY(1,1) PRIMARY KEY CLUSTERED, order_date datetime, created_by varchar(10), created_...more >>

hello, I have a problem when trying to call Set RowCount 4 from a user defined function It Generate the following error message. Invalide use of UNKNOWN TOKEN in the function Please Help. Thanx alot...more >>

I have a search page where users can search on 6 different fields. Of course, they can elect to use any one or all of the fields. I want to switch from dynamic ASP to a stored procedure. I'm having grief in passing a wildcard to the stored procedure. I found a little help searching the groups. ...more >>

From Visual Studio.NETs Server Explorer, you're supposed to be able to right-click on Tables (or Views, or Stored Procedures) and select "New ..." from the context menu. My context menu only has "Refresh" and "Properties". How do you create a new Table, View or Stored Procedure?? Brad ...more >>

Is there a way to retrieve the primary keys for a table via stored procedure? Thanks in advance for your assistance!!!!!!...more >>

I have a text file I need to move from one folder to another in a stored proc. How do I do this? Thanks!...more >>

i have got 2 views View:Donor_Age Donor_ID Age 5 43 9 40 8 28 1 null 2 null 3 null View:Age_Range ID Start_Val End_Val Sequence 33 1 15 1 34 16 20 2 35 21 25 3 36 26 30 4 37 31 35 5 38 36 40 6 39 41 50 7 40 51 60 8 41 61 70 9 42 71 null 10 I ...more >>

The scenario is: I have an entity called requests. Every request have a common set of data fields. There are 4 different types of requests (general, then 3 other special types). The 3 special types need to record specific data fields per that type. If I model this requests entity as a single tab...more >>

i want to insert a record at a parent and child table at a stored procedure with transaction that either one got error, rollback the whole transaction. after inserting the parent table, the pk, JobID should then be passed to the child table's column JobID i know how to insert individual table ...more >>

I have a table with 2 fields. PDATE is characters with 8 places. CDATE is datetime. I need to convert and copy the PDATE to the CDATE retaining the selected PDATE value PDATE CDATE 20031117 11/17/2003 20030723 7/23/2003 The table contains appr...more >>

I need to design a table to store the day and time zone permission for each employee. Basically I need to store which day and what period of time the employee can go to work, so that it can determine for door access or not. For each, employee1 may go to work on Monday through Friday and from 8-...more >>

Consider the following schema: CREATE TABLE A(i int, j int) CREATE TABLE B(i int, j int) INSERT A VALUES(1,1) INSERT A VALUES(1,2) INSERT A VALUES(2,1) INSERT A VALUES(2,2) INSERT A VALUES(2,3) INSERT B VALUES(3,1) INSERT B VALUES(3,2) INSERT B VALUES(1,1) INSERT B VALUES(1,2) INSE...more >>

I am not succeeding at what seems a simple task: Example: ------------ DECLARE @myTable TABLE ( aKeyValue int, someValue int) UPDATE @myTable SET somevalue = A.someOtherValue FROM aRealTable A WHERE @myTable.aKeyValue = A.aKeyValue It is a forum documented feature that the table var...more >>

SQL 6.5 restore wrong sort order The data base you are attempting to LOAD was DUMPed under a differen sort order ID (53) than the one currently running on this server (52) I am trying to restore a database from one SQL 6.5 server to a second and I am getting this error. Is there some ...more >>

Dear All, I'm looking for a function that runs sql-scripts to add new SQL-SERVER-OBJECTS to the current DB. The code should run from the access.adp-interface. So a form having a textbox that hold the script (path+name+extention) and a cmd to to run the script. Thanks, Filip ...more >>

Hi, Can someone explain why a left join on the same table - a self join requires a hanging join clause to work correctly? please see DDL below. Thanks HS ---------------------------------DDL------------------- USE PUBS if exists(select table_name from information_schema.tables where table...more >>

I'd like to look only at the date part of a datetime field. What's the best way to do this? For example, I have a calendar table which I want to obtain the DFWk reference for the current date. I use: select c.[DFwk] from [calendar] c where cast(getdate() as int) = cast(c.[date] as int)...more >>

i have an existing database (i didn't design) and i'm trying to create a query but am not getting the results i expect. any thoughts/help appreciated. i can use a similar query on other tables and get what i want. the only clear difference that i see is one of these tables (security) has 2 prima...more >>

We're going throught the whole 'SPROC -- good or evil' debate right now, and are trying to get correct and up-to-date information about the performance issue. My current state of knowledge is that with SQL 2000 it is no longer the case that only SPROCs can access the execution plan cache. Now ...more >>

Consider this table: if exists (select * from dbo.sysobjects where id = object_id(N'[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyTable] GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)...more >>

I am trying to write a stored procedure to copy a table from one database to another. The table changes quite a lot, and it is a lot simpler if an sp can be made to do it. Problem is, I don't know how to work out what the primary key is on the table, assuming there is one. I can get it from th...more >>

Hi All, I have a problem. That is:- I have one table "Employee" in my SQL Server. How Do I Write a Stored Procedure which will Create a NEW .dbf File under "C:\" with name "emp.dbf" the structure will be (EmpID Number, EmpName Char) AND Will Insert Some Rows from the Employee Table of SQ...more >>

All, How would I set the drillthrough property to be turned on when using OWC 10. Thanks, Chandra....more >>

We're designing an e-commerce app. using SQL 7. We want to allow for unlimited billing/shipping locations. There will also need to be "levels" of users, i.e. Master Admin - administers all information, including company info., etc. for all locations. Location admin - only administers info. for a...more >>

hi all: I want to use bcp Utilities in my program.And i read help it needs three document: Odbcss.h,Odbcbcp.lib,Odbcbcp.It saids that these three files is distributed with the SQL Server ODBC driver.I use SQl 2000 now. But i can't find these files.How can i use bcp Utilities in my program...more >>

Can I execute a SP (Stored Procedure) while transfering data from a .CSV file to a table ? To explain this in detail: 1. Say there is a .CSV file which contains the following Fields, Bank_Code Address1 Address2 City Country 2. In a table named "Bank_Master", I am h...more >>

Hi all. I am trying to run an update command from inside the INSTEAD OF UPDATE trigger. However for reasons difficult to explain I need to do this with "exec" command instead of simple UPDATE. If I run this with normal update everything works. If I try to update inside the "exec" comma...more >>

Hi All, I want to show a value of '0' instead of seeing a value of Null. example: SELECT (SUM(Field1) + SUM(Field2)) As [SumOfFields] From Table1 I may get a result similar to [SumOfFields] --------------- 3 Null 5 2 Null ...more >>

Sql Server gurus: A particular Stored procedure has increased its runtime 10x. No one will admit to any changes in 1. input file size, 2. Server hardware configuration, 3. Server software configuration. The job just started taking a LOOOOOONG time. Database is large 22.5 gb, 3.6 gb ...more >>

Hi, I have developed a script to iterate through all of the table in my db using a cursor and call DBCC to re-seed it. The trouble is some of the tables do not have identity columns so dbcc CHECKIDENT gives an error. Is it possible to limit the query to only tables that contain autonumber ...more >>

I want to create a temp table as the result of a query, and want to establish an identity column in the temp table so that I can reference that value in my ASP code. The combination of other values in the fields that I am querying may not be unique, so I want to add this unique value column. ...more >>

Hi, What is the best way to Delete the Duplicate values in a column Ex: ID Name age 01 aa 12 02 bb 14 01 ab 13 say I want to Delete the Duplicate values in above Table and result table will be ID Name age 01 aa 1...more >>

Thanks Pavel, That has removed the problem with zero's, but it is returning the results as integers rather than an exact value (eg 2 instead of 2.3333). Also, can you explain why you have used MAX instead of SUM in the cross tab code (it works the same but i don't understand why :( ) Thanks...more >>

Hi All, Does anyone know if it is possible to pass two parameters, provided by user input, in Excel to a linked Stored Procedure? John. ...more >>

Hi I have the following column in my table emp_full_name last_name first_name uid I want to update the table with first letter of first_name and the last_name and update the uid column with the new value Example emp_full_name: Joe Bruin last_name: Bruin first_name: Joe uid:jb...more >>

I have tables with comments. I am trying to retreive a nice grid that looks like the following: TABLE_NAME TABLE_COMMENTS (these are my comments in the desgin table\ then clicking Table and Index Properties button) FIELD_NAME FIELD_DESCRIPTION (these are my comments for the column field i...more >>

Hi, CREATE TABLE dbo.ImportErrorType ( importErrorTypeId integer IDENTITY NOT NULL, importErrorCode char(15) NOT NULL, importErrorDescription varchar(100) NOT NULL, CONSTRAINT pk$errorType$id PRIMARY KEY (importErrorTyp...more >>

If I grab the code (ANSI) from EM, the query executes and returns the = row I expect, but if I don't use ANSI it gives an error message. Can = anyone shed some light on this for me? I fail to see the difference. = HELP! WHERE=20 a.gl_cmp_key =3D b.gl_cmp_key ...more >>

I can't resolve the error: "Error converting data type nvarchar to int." Giac (code below) --For testing make the table if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestEncrypt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestEncrypt] GO ...more >>

I just completed a SQL programming class where Table Variables were described, and I'm sure that's what I need to use, but I'm having trouble with applying the concepts. Right now, I'm doing a Select... Into #tmptable then Select... Into permtable JOIN #tmptable Into newtable Any suggestions...more >>

I am having issues with the text field of a record on an update trigger. I update a few fields in table1 which inturn inserts the record into table2 for historical purposes. The issue is when the text field is one of the fields updated only 64000 bytes are inserted into the historical table ...more >>

Hi Have a look at the following.. select ltrim(reverse(handset)) from #handsets where handset like 'SEC-SGHP400%' 004PHGS-CES 004PHGS-CES As you can see, there seems to be some trailing blanks at the end of the column handset in the first row which cannot be removed by ltrim!! s...more >>

Hello, We have a working and production server, both running MSSQL 2000 Standard Edition. As we enhance the system, we develop on the working server, and then deploy to the production server. Some times, these deployments can be quite large, and consequently we script each of the databases, ...more >>

This is a pattern I frequenly use. I can find tens of times where I have used it so there must be something really trivial missing in this statement that I cannot see. UPDATE [LD_LMS].[ldlms].[DRUser] SET [managerID] = MANAGER.[userID] FROM [LD_LMS].[ldlms].[DRUser] TARGET JOIN [LD_LM...more >>

I am trying to use xp_sendmail on SQL Server7 running on a Win2000 Server. I have outlook installed on the same box as the SQL Server7 Database. I have an internet mail profile set up to connect with our Exchange server. I added the profile under Support Services in Enterprise manager ...more >>

When I get this error message, are we talking about the Process ID for = the OS process or is it the process id in SQL Server (or are they the = same)? <?MSSQLError HResult=3D"0x80004005" Source=3D"Microsoft OLE DB Provider = for SQL Server" Description=3D"Transaction (Process ID 109) was = d...more >>

Hi, How can I add data to a text data type? I need to add this data at end of text field contents. I tried to make the following, but fails: create table #Temp ( DummyText text ) insert into #Temp (DummyText) values ('teste') update #Temp set catalog = catalog + 'ABCDEFGHIJ...more >>

I have a problem with bcp and format files. We changed our databases from varchar to nvarchar to support unicode. No problems so fare with that. It is working fine. But now I need a format file for the customer table and and it is not working. It is working fine with the old DB with varcha...more >>

Hi Do any of you have - or know where to find - a script that for each column in a table caluculates the Selectivity Ratio (Sr) of a key value If the number of rows, which are uniquely identified by the key, is Rk, and the total number of rows on the table is Rt, then Sr = 100*(Rk/...more >>

I have a table with following data. 1100JOHN800020-02 2IRVINECA78951 2BOSTONMA78951 1200MIKE600020-02 2DALLASTX45225 if first character is 1 then its user info if 2 then its address The table is well sorted by users. how can i split it into following tables ? please find table s...more >>

Here is my problem: I have two tables with the exact same fields on a database. I like to copy one row from one Table to the other one. I use a store proc like that: DECLARE @ColumnName int; -- And all other columns SELECT @ColumnName = ColumnName, ... FROM Table1 WHERE 'some filter on ...more >>

Anybody know if there is a way to keep a table in memory? ...more >>

We are having an issue on one of our SQL 2000 (sp3) servers where if we create a new stored procedure using Enterprise Manager it saves it as a system sp for some reason instead of user. Has anyone else run into this problem?...more >>

I am building a VB client app with SQL server backend (about 10 client will be installed). This client app will get a recordset and display on the screen and users will make some modification of this recordset. Then users move on to process new record. I want to know what is best way to i...more >>

Hi An ActiveX script has 42318 characters in a vbs file. We are trying to use this .vbs file as a job step. We get this error message -------------------------- Edit Job Step - 192.168.1.75\TrialJo -------------------------- The specified file ('mailscripts\send_auto_con.vbs'), is 42318 chara...more >>

Hello! How can I set up a DTS to import access files with PK and IX?...more >>

Hello, Can anyone tell me the function or sp that will return the name of the database you are currently in ? Thanks J...more >>

Hi all, I have a query that uses a SUM function, and I want to convert it to an AVG instead. However, replacing the SUM with an AVG just gives me a whole bunch of zero's :( Can anyone advise where I am going wrong please. --------------------------------------------------------------- ...more >>

Hi, MVP Jacco Schalkwijk was kind enough to solve my UNION problem yesterday, but I've encountered another problem. I'd like to be able to see other fields from both UNIONed tables. For instance : Positions table: Book SecurityNumber Description A 001 Fred B...more >>

Hi all why can i run a sp.... in Win2k and Server2k (update to date) -------------------------------------- dbo.spdev_test '>String-Parameter with 129 Characters<' - it work: parameter value with quotes in Win2k and Server2k (update to date) -------------------------------------- d...more >>

Hi, Can we create views in Stored Procedures. Is yes how do we go about this? Thanks, Vamsi...more >>