Say one stored procedure returns information in a table (like "select * from MyTable"). How would a second procedure, which calls the first one, access this information? Example: ------------------------------- create procedure sp1 as select * from myTable go ----------------------...more >>

Consider the following DDLs: CREATE TABLE Facility (FacilityID int,FacilityName varchar(50)) INSERT INTO Facility VALUES (1,'Entire Org') INSERT INTO Facility VALUES (101,'Central Lab') INSERT INTO Facility VALUES (102,'District Complex') INSERT INTO Facility VALUES (103,'District Junior Coll...more >>

Hello, Thanks to OJ for previous answer in message: Ext. Procs. Web Services, C#, C++, PLEASE HELP ME! I'll continue my Extended procedure in C++ calling a web service... I need help on how to replace the DLL on the server. I used two commands to free my DLL and still: -sp_drop...more >>

I would like to know all the tables that have an identity column and the name of the column(s) Using SQL 2000 Thanks ...more >>

Hi, I am trying to select a list of column mappings (table name, field name) from a sql server 7.0 view using the system tables. However when I do this I get the columns in the "From" clause returned as well as those from the "Select". I wish to be able to return the columns in the "se...more >>

G'day, I'm wondering if there's anyway I can catch alter drop and create table events. I thought it would be as simple as adding a trigger on SysObjects - but it won't let me - says permission denied. Any ideas? thanx! Darren ...more >>

Hi, I have a calendar table with series of data, and I would like to perform some simple SQL (7.0) to calculate and update an accumulated column value. The table: CREATE TABLE Table1 (MyDate datetime, MyID integer, Number integer, AccumulatedValue integer) Some data: INSERT INTO Table...more >>

I've created a new SP in which I am getting an error I do not understand. The error I get is: ================================================== SELECT UnitName, UnitClass, Rank, NetScore FROM SCScores2003 WHERE (((ShowType)='SC') AND ((CircuitID)='5') AND ((ShowDate)='20030125')) ORDER BY Un...more >>

I can insert records into an existing table from the resultset of a stored proc like this: INSERT temptable EXEC sp_GetOrders But it appears that the resultset generated from the stored proc must match exactly with the fields in the table. What if temptable had an custid field an...more >>

I am trying to arrange a query so that i get a result set back ready to drop into an excel spreadsheet that will be then used to populate a chart - histogram like. Look at my query (is a cut down version - full query is MASSIVE) SELECT '>0' = SUM(CASE WHEN col1 < 26 THEN 1 ELSE 0) , '>26' = S...more >>

is there a way that i can count the number of records inserted into a table by hour? i have a created_date field with a datatype of datetime. i would like to see break down of rows insert by hour for a given day. please advise... thanks, Brett ...more >>

My question has to do with inserting data into partioned views, which I understand is a feature of Enterprise Edition. I currently have three databases on the default instance of sql server with one table called tblCall in each database. The table was created as follows in all three ...more >>

Hi All, Can someone please suggest the best way of: 1. iterating through a table of information 2. based on field value choose a Stored Proc to insert data into another table Sudo example: <Table1> is my source <TableA> Through <TableG> are my potential Destinations SELECT * FROM <Ta...more >>

Hi All, I am trying to use an IF statement within a WHERE clause; or at least understand how to write the following correctly. Below is a Stored Proc that works quite well: -------------------------------------------------------------------- CREATE PROCEDURE dbo.usp_GET_PROV_CRITICAL_DATES...more >>

Hi, I am looking at options for data management for our application. This application sends and gets XML messages from all over the world, so keeping the XML seems to make sense to me, but then how can I search on the data as I figure I would have to put the XML string into a text field to ...more >>

Want to grab the first 3 records for each state ordered by zip desc. This almost works..starts off good, it will finish..but the joins aren't working correctly. The second subquery ends up being null most of the time. We can't figure out a pattern and why some join and some don't. Use Pubs ...more >>

Hi Friends, SQL 2000 WIN 2000 I have a job which when running , at the end it hangs and says PERFORMING JOB COMPLETION it is remaining like that for the rest of time untill I restart SQL agent. What is that about and why not FAIL or SUCCEED. Thanks in advance, Pat ...more >>

I have the following query. Select Tenant, TenantID,password from Rentor where tenantid in ('123', '145', '543', '589') I would like to update password with the following passwords ffuishd , sadasd24, asdasd34, 435r34rd. Doesnt matter who gets what. They all just need to be differe...more >>

I created an application on my windows xp machine using access 2000 It has been tested at my clients on both their windows 2000 and windows xp (with accessxp) with no problems. I built the security system and took it in yesterday to do the final conversion of their paradox files. I also ...more >>

My question has to do with inserting data into partitioned views, which I understand is a feature of Enterprise Edition. Our need is to distribute data across many different databases and at the same time having a identifier created for each record inserted. For example DB1.Table1...more >>

Can I recover a database if I only have the "mdf" file? I have a SQL2000 database that the LDF file had gotten really large so I stopped SQL and deleted the ldf file. Nothing is working... I thought SQL would automatically create a new LDF file, I guess I was wrong. How can I get this "MDF" file...more >>

Hi, I need some help in getting a backup of the data base from one server (S1) to another server (S2). The database name of S1 must be DB1 and db name for S2 is DB2. Is it possible to be done directly from the enterprise manager., without having to create a backup in S1 and copy the backu...more >>

I believe that I have correctly setup SQL Mail. When I execute xp_sendmail, it says "Mail sent", but no email is ever sent. I have checked the logs on my mail server, and do not see any connection being made to send an email. When I execute xp_stopmail, my mail server logs indicate that a pro...more >>

Hi, I need help on how to change within a stored procedure the name of the file that a bulk insert must take to insert int a db table. The example goes like this: declare @filename varchar(30) set @filename = 'c:\publisher.dat' --this is the file that´s gonna be sent as an sp parameter ...more >>

I have a SP that does DBCC DBReindex for tables in a database. When I call the SP via Query Analyzer, I can see info about the execution (SELECT output, plus DBCC status info). When I run the stored procedure via a SQL Scheduled Job, I see neither. I need to be able to view messages in ...more >>

Hi, Working on sql server 2000. I have a job that pulls data from one server into this server's database. It failed last time claiming the primary file of the db was full while there were over 24 gig room to grow and the job will only pull in less than 2 gig data. Then, I run it ag...more >>

how can i create a script for recompiling procedures and reindexing tables of a database. ...more >>

I have table: CREATE TABLE (i int, vchFoo varchar(20) ) I have data: i vchFoo - ------ 1 abc 2 def 3 ghi 4 jklmno 5 foobar N xyz123 How construct I, a SELECT statement to get: "abc, def, ghi, jklmno, foobar, xyz123" All in one column in one record? T...more >>

Is there a way to generate a script for a Full-Text Catalog. I have one working perfectly on the test server and want to move it over to production. Thanks in advance! -- Posted via http://dbforums.com...more >>

I need to add zeros to the beginning of a value with in a field. It will not always be the same amount of zero's. For instance 10 would need to become 00010 and 3000 would become 03000. The final length of the value would always be the same (in this case 5 characters long). I do not need th...more >>

I have this query, which returns in 11 sec DECLARE @dtStartDate AS datetime DECLARE @RefNo1 AS varchar(15) SET @dtStartDate = '09/1/2000' SELECT eh.ClinNo, eh.CrDate AS ContactDate, cm.Status, cm.CloseType, cm.RefNo1, cm.LocNo FROM tblEventHist eh INNER JOIN tblClinMast cm ...more >>

Hello, Does anybody know how to disable implicit distributed transactions on a linked server? Here's my scenario... This works fine: EXEC SomeRemoteServer.OtherDatabse.DBO.Proc_SomeProcedure But this doesn't: INSERT INTO #Whatever EXEC OtherServer.OtherDatabse.DBO.Proc_SomeProcedu...more >>

I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the new month starts I create a new table with the same fields and move the data of previous month into that table and delete it from calls. So 'calls' holds the data of only the current month. for example at the start of nove...more >>

Hello, I need to find differences in a varchar column that might have the folloing values 'a' and 'A', so i can execute SELECT * FROM Table1 WHERE Col1='A'. Is there an option for this? If so, how can i enable it? Best Regards Hugo Sousa ...more >>

Hi all, Will someone PLEASE help with a few simple questions I have, I'm falling at every hurdle in trying to get XML out of my SQL Server. Firstly, when I run a query, I'm getting the results in one huge truncated string. Most of the examples show true XML formatting... <line1>This is a...more >>

I'm designing a database for persisting data about people. I want to design my tables for optimum performance. Some columns hold data that do not allow null values (lastName, firstName, gender)—let's call these columns GroupA. GroupB columns have null data rarely, but null values occur someti...more >>

Hi, I am wondering if I have a table contains millions of records, and there is one column in the table named "gendor". Then in the following query, Select * from myTable where gendor = 'F' I think with index on the "gendor" column will let the SQL Server go through only about half of ...more >>

Hello, Please excuse my english... I need to call a Web Service from a Stored Procedure in my SQL Server 2000. I know very little in SQLServer. I've heard of Extended Stored Procedure (xp_). I'd like to create it in C# but need to know what to include... Else, I could do it in VB6, ...more >>

Hello! I am trying to use an open concept for my database where I store = different values for different userfields in one table (I call it vitual = columns). I have one table called UserFields where I can add rows with UFID and = UFName Eg: 1,'CompanyName' ; 2,'CompanyAddress' ; 3,'CompanyN...more >>

I am querying Active Directory via SQL Server. SQL Server (Standard Edition, SP3, 8.00.818). I get the error below when query is executed --------------Error Message--------------------------------------------------------------------- ---------------- An error occurred while preparing a que...more >>

I am trying to figure out to work the Debugger. When I right click on a stored procedure and select the debugger option, I get a message box displayed which says the following: "SP debugging may not work properly if you log on as a local system account while SQL server is configured to ru...more >>

I'm having a heck of a time inserting rows into my SQL DB from VB Code. If you can offer some suggestions, I would be appericative. Thanks... Below is the connection strings... Public Function ConnectToDailyXTsSQL() On Error GoTo ErrorHandler Set DailyXTsWS = DBEngine.Workspac...more >>

We have an application that uses ADO to access a SQL Server 2000 database. SQL Server is running with SP3a and the development environment is using MDAC 2.7a. We are using smart pointers as our ADO implementation method. The problem is that whenver error is returned a 7-10MB memory leak is...more >>

Just recently I ran into an issue where a stored procedure changed it's execution plan based on whether I use a date constant in a stored procedure vs using date variables. I get results back from the stored procedure in a few seconds running the stored procedure the following way. ...more >>

MyFirstTrigger (shown below), created via Enterprise Manager, hopelessly hoses up the target database. Any attempt after the trigger is created to use it (or even the executable it wants to run) just hangs Enterprise Manager. Before creating the trigger, the executable (which adds a record to ...more >>

Hi, I'm trying to create a view summarizing BUNDLE_COUNT pr CREATE_DATE. The date field I have in mu source table is a datetime, and I want my view to give only date, and the count of records as BUNDLE_COUNT. I got a suggestion from the group to convert the date like this: SELECT CONV...more >>

Hi, I wonder why I need the dbo-prefix when I call a user defined function. In SQL-Server-Online-Documentation I found a sample where I don't need it: ----------------- CREATE FUNCTION fn_CustomerNamesInRegion ( @RegionParameter nvarchar(30) ) RETURNS table AS RETURN ( ...more >>

We have a manufacturing system (supplied by a 3rd party) that uses SQL Server as the back-end DB. Some colleagues have been claiming that some of their data is being changed or deleted by unknown persons! The system is thought to be very stable, so we are assuming for the time being that th...more >>

I get the following message when I'm inserting a row into a table. The statement is very simple, but there is a large amount of data going into one column. Internal Query Processor Error: The query processor ran out of stack space during query optimization All the KB articles I've found so...more >>

Folks, Can you help me out? I am working with an increasing number of SQL Tables, stored procedures and reports from Crystal. What I would like to be able to do is run a tool that will let me know from a high level point of view what has and has not be changed in the database and list all syst...more >>

Hi, I have a simple update statement like this: Update Tbl_A Set Col_A = B.Col_A From Tbl_A A, Tbl_B B Where A.Col_B = B.Col_B Col_B is an integer colum. In Tbl_A, we have about 2.5 million records. In Tbl_B we have about 0.4 million records. Each record in Tbl_B will have one or m...more >>

Does anyone have a great way of calculating how much space a single record is consuming? I'm trying to estimate how much space I'll get back if I delete a large number of records, from a large number of tables. Thanks, Andre ...more >>

Is this possible? select Column1, Column2, Column3 from Table1 where @param in (select Value from dbo.udfTest(Column1)) The userdefined function udfTest returns a string table, @param is also a string. What i want to do is to check wether @param is in the commaseparated list in Column1. ...more >>

I am trying to run a VB 6.0 app against Access, SQL Server and PostgreSQL using ADO. Access and PostgreSQL have a boolean data type, but SQL Server does not. Everything works so far with all 3, except a SQL statement: SELECT * FROM Table WHERE FindValues or SELECT * FROM Table WHERE ...more >>

Occasionally we see the following in our ERRORLOG: 2003-11-03 05:07:32.31 spid4 Victim Resource Owner: 2003-11-03 05:07:32.32 spid4 ResType:ExchangeId Stype:'AND' SPID:73 ECID:82 Ec:(0xAFB54098) Value:0xac19e454 Cost:(0/2710) This is not a traditional deadlock error where SQL server...more >>

I have no experience with these types of tables and the SQL required to run against them effectively. Does someone know of a good source for newbies on this type of design and subsequent queries? Thanks, Robert Gibson ...more >>

Hi all, i can imagine that it is a very common scenario, but somehow i can't get a very optimized way to do it, Iam trying to search through a number of columns in a table for any keyword, the application was working earlier by dynamically creating queries , and sending it to server to proces...more >>

2 Tables One called Agent1 and the other called Agent2 The same columns are in the both tables (Agency and Password). I would like to update Agent1 with the passwd of Agent2. Agency is the PK in both tables....more >>

Dear All, I was wondering if you could help me with a problem. I would like to create an audit trail using triggers. So say if I had a table called audit, I would like to insert the before values of an update, then immediatly the after values. For testing purposes I have two triggers:...more >>

I want to add SQL to test for a constraint if the constraint exist then drop it. Here is my code to create the contstraing ALTER TABLE [dbo].[MapPortfolio] ADD CONSTRAINT [IX_MapPortfolio] UNIQUE NONCLUSTERED ( [EntityID] ) ON [PRIMARY] GO ...more >>

I have a table with the following structure and contents: ============== TableA ============= CREATE TABLE [TableA] ( [Event] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [State] [char] (10) COLLATE SQL_Latin1_Gener...more >>

Does anyone know if there is a way to programmatically (in VB 6.0) assign a field/column data type of Float? Closest I can come is adSingle which shows up in the database as Real-4 when what I need is Float-8. Program is currently using PROVIDER=sqloledb (with Microsoft.Jet.OLEDB.4.0 presently ...more >>

using SS 7.0. I am having an issue that I hope someone can help me with. I want to convert a datetime value such as "2003-10-31 13:15:45" to just show "1:00" and still be sortable in order of logical time i.e. 1:00 comes after 12:00 and so on. The issue I am having is when I try to do the...more >>

Hi I have written a function in SQL Server 2000. One of the parameters are of type numeric. When I pass a value eg. 12345.47 the value that the actual function can see is 12345. It seems to cut off the fractional part when I pass it to the function. is there a way I can fix / get aroun...more >>

Hi! I don't do any criminal. I don't use bind sessions also. But with growing my SQL2000 server&#8217;s workload I've started to receive this errors. What does it mean? How should I process it? Thanks ...more >>

Hi, I'm trying to rewrite this stored procedure into a set-based solution. But uptil now I have no succes doing so. Can anybody please help me with this? Many thanks in advance, Marc, :) -------------------------------------------------------------------------------- Stored Procedure...more >>

Hi I want to implement an audit trail on a few of the tables in our database. Due to the disparate nature of the applications that access the database i want to do this using a trigger on the affected tables. Our DBA does not like this idea so i wanted to know if microsoft has any recom...more >>

The SQL Server Books Online says: If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables. But the "exec sp2" in the following code gives an error "Invali...more >>

Hello -- I'm curious to know how most folks write VB code to connect to their database using ADO without hardcoding the values in the application. I frequently see books that allow the user to enter a username/password within a login form and pass this information to the Conneciton object Open...more >>