Hi, How to access the resultset of one sp within another thnks n rgds, anu...more >>

Hello, I need to get a single recordset combining 2 similar tables. Table 1: tblAppointment fields: AppStart (datetime), AppStop (datetime), AppName (varchar) Table 2: tblSchedule fields SchedStart (datetime), SchedStop (datetime), SchedName (varchar) I would like to get a single rec...more >>

I am trying to make a query that returns different strings added to a field, depending on the value of another field. The following pseudo-sql demonstrates what I would LIKE to do. I hope this makes sense. Table has 3 fields: RecID - int, RecCode - int, RecName - varchar. SELECT RecID, [IF...more >>

I am trying to setup a warm standy server. I can find lots of pieces of the puzzle but unable to piece them togehter. These are ther steps I'm looking for. If any one has some of the pieces I need I'd be very grateful I currently perform automated backups nightly and transaction log backups ho...more >>

Hi, please help me! I'm connecting to my database using... Database_ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source="&server.mappath("/db/db127/store.mdb") set con=Server.CreateObject("ADODB.Connection") con.open(Database_ConnectionString) I'm assuming this is corr...more >>

Hi, How to find the triggers associated with a table using sql query analyzer. Thanks in advance for help extended. Regards, Amit...more >>

Hi I want to get the following result from my table in sql server 2000,how is it possible? family (parentcode+childcode=PK) parentcode childcode description ---------- ----------- -------- 100101null 100102 100103 101200 101290 102288 102311 103207 200398 200700...more >>

Hi I'm working with SQL server 2000,and in enterprise manager in following branch I see my database which was locked in 2 different types and some other databases,what's the different between? and when msdb locked(in which activities)? and the last question is why was tempdb locked and w...more >>

Hi All, Suppose I will use Where col1 >= 'value1' and the col1 is a varchar - Indexed column, then how does the >= works on that Case? Does that take the sum of the ascii value of all characters of value1 and find all rows with > value then the sum and display, or how? Thanks Prabhat...more >>

hi everyone, I have been developing software primarily used by English speaking people. I wanted to extend the software to be compatible with other languages, primarily Asian languages, so I need to be able to store unicode data. Here is the challenge: how can I make all "text" columns "...more >>

Hi all, Table Rows: Level Date TimeStart TimeFinish TeacherName 111 1-Oct-04 1PM 2PM Andrew 111 1-Oct-04 1PM 2PM Annie 111 2-Oct-04 2PM 3PM Andrew 111 2-Oct-04 3PM 4PM An...more >>

i have a datetime field that i want to calculate to equal those= values in another field of dates as type integer. I had read= somewhere that this would be an implicit conversion but it won't= go. I've tried both CAST and nested CAST queries using various= intermediate datatypes to get the c...more >>

Dear All I have 2 SQL tables called STOCK and STOCKDESCS. The common field is STOCKID. In the STOCK table I have all of the rows that I have posted, but my STOCKDESCS table is short of the same rows. Do you know of a quick query I could use that basically lists the following (table S...more >>

I got a datefield in mytable, now I need to store the string yyyymm (200411) as a extra field, How can I do that ?? Thanks a lot ...more >>

Hi we posted a question a week ago and received excellent advise on it - thank you! Now, we have another question that I someone can provide us with just as good of an answer. CREATE TRIGGER [spequip_update] ON [dbo].[spequip] INSTEAD OF UPDATE AS /* find out if original values for the ...more >>

Hi, Is there a way to specify a text qualifier in a bulk insert statement? thanks ...more >>

CREATE PROCEDURE st_deneme @userid numeric (18) ,@result numeric (18) output AS select * from users return "10" GO **************************** <!--METADATA TYPE="typelib" NAME="Microsoft ActiveX Data Objects 2.8 Library" UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}" VERSION="2.8"--> ...more >>

Hi there, Thanks in advance for your help. I am looking to create at Stored Procedure that uses a parameter as the name of a the table that creates a dynamic select statement. Something like the following. create procedure spNewLabel @MyTable varchar(20) as Select * from @MyTable ...more >>

how can I update a table and select some rows of it in the same sp? thanks ...more >>

Hi Freinds, I have inherited SQL server which need a lot or clenup work to do. How can I know all users on server who don't have access to any database. long time ago , they created buch of users by mistake on server, and I have to delete those which are not active and don't have any db acce...more >>

Hi all, Sorry if this isn't directly related to SQL Server, although that is my "weapon of choice" as it were... I have a few simple tables, and I'm trying to decide whether my planning and thinking is correct. I want to ensure that I have a relational database, but my previous tables ar...more >>

Here's my problem. I have a stored proc that returns a set of data. I determine the datetime for the where clause in the stored proc. That part works great with no problems. The problem starts when I try to use the variables in the where clause. If I hard code the date filters in take le...more >>

When importing data from Excel into SQL server 2000, some field values change from: 2.9 to 2.89999999999999999 Why does this happen? thanks ...more >>

Hello Experts, I have a table with the following records: TABLE: vehicles PK: booking booking vehicle depot date 431601 1 1 2002-04-01 467021 1 3 2002-10-30 668721 1 4 2004-04-20 685844 1 5 2004-05-27 621911 1 6 2004-04-12 691411 2 7 2004-08-18 688431 3 4 2004-06-...more >>

In my "Customers" table, I have some fields in "nvarchar" type. However, my program always runs into a problem when the field type is "nvarchar". Other fields with "char" or "varchar" type works fine. My code snippet is as follows: rs.Open(CRecordset::forwardOnly, _T(sqlstmt)); if (! rs...more >>

A super challenge, at least for me… I have a form where a user (either a supervisor or an employee) can access his or her Annual and Sick leave report (which shows balances, usage, etc). I've got it set so that if the NT User (the logged on userID) is a supervisor, I can capture their immed...more >>

I would like to use ATL OleDB to run simple SELECT statements in a SQL 2000 DB Server and get the results back as an XML string. I know that I can specify "SELECT * FROM MYTABLE FOR XML AUTO, ELEMENTS" and the result is an XML string; however, I do not know what accessor type to retrieve the s...more >>

HI, could some one help me with this please I have this table "Courses" CurID Chrs_Entry Chrs_Exit 1 10:00 AM 11:00 AM 2 09:00 AM 10:00 AM 3 12:00 PM 1:00 PM how do i get the early time and the latest time in a query min time = 09:00 AM max time ...more >>

I am executing a conversion program written in VB.NET that converts records to a SQL database. The program is using ADO.NET for the inserts. Are there any settings in SQL Server properties/etc. that can be changed to allow maximum performance for inserts? Thanks...more >>

hello, I've a problem with a trigger : this trigger update a file with an index field but it's return an error : UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad parameters for information, this is my trigger : CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS ...more >>

For anyone interested, I have a full pass to the SQL Connections conference in Las Vegas next week which I won in last year's drawing. I cannot go and so I am selling it for the best offer I get. Just reply to this post if you are interested. The full cost of this pass is $1395. Don't miss o...more >>

I have a SP that has to take these kinds of VarChar input: "1000a" "1240-bx" "4000" I need to always put the the numeric (left) part into a table column and optionally put any non-numeric stuff on the right side into another table column. In other programming language...more >>

Hi Freinds, Does anybody tried to quantify the impact of SQL profiler on SQL server performance? Like can I say, Profiler has %10 negative impact while running on sql server performance? We are running a test with profiler on and on client's wants to know how much sql performance will i...more >>

I have a table named "Table1" with 5 fields (f1, f2, f3, f4, f5). Fields "f1" & "f2" consists a primary key(constraint named as PK_Table1) for "Table1". But the question is, I would like to add "f3" as part of the PK_Table1 primary key constraint. I was trying to drop PK_Table1 and then a...more >>

I have the following query now: SUM(case when (Store = '::location::') AND sold_dt > ('::mt::/01/::yr::') AND sold_dt < ('::mt::/31/::yr::') AND nu = 0 then 1 else 0 end) as usedlast, This works fine, but what I want it to do is have a ::yr:: - 1 or something so that it calcula...more >>

Instead of using the trace feature in SQL, is there a way to find out which table in a database that the user has just updated? All the tables in a database has fields called 'updateid' and 'updatedate'....more >>

Fixing the clock for this post.... -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- ...more >>

Hi, I am trying to use the "table" variable in the stored procedure as follows. DECLARE @Order_Numbers_Table TABLE ( [Serial] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [Order] CHAR(11) ) My current database and its objects are in the collation named "COLLATE SQL_Latin1_General_CP1_CI_...more >>

are there any suggestions for recursive queries with 2 or more parents? like the movie office space, i need to model situations where emploees report to more than one manager. "Bob, I have 8 bosses..." AP...more >>

I have a web application with a back end SQL server db. Every time the users login to the web site, they execute a SP. The issue is that the user is getting a time-out error message when they call the SP. If I add the WITH RECOMPILE option to the SP, they don't get the time-out error but on th...more >>

Is there a way to reconstruct a table after somebody has dropped the table? A table has been dropped, and we have lost the data - there is also no backup. What can be done?? ;) ...more >>

I have a table DDL below CREATE TABLE [dbo].[login_audit] ( [username] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [login_date] [datetime] NULL ) ON [PRIMARY] GO This table retains login information as people signon...consider the data below yquazi 11/1/2004 yquazi 1...more >>

I am getting ready to do the third edition of SQL FOR SMARTIES this month. If anyone has an SQL programming technique, trick or tip that they would like to see in the book, drop me an email. You get a virtual beer and your name in the book. Even better, if you have an improvement on someth...more >>

What are the options to protect sqlserver tables, schema and sp from unauthorize viewing or copy? Thanks Alon -- ABM...more >>

Hello there I by mistake have deleted all data from one of my tables on query anlyser what can i do to bring it back? ...more >>

I've tried looking around the web/docs for best practises in using SQLServer 2k and Visual Studio 2k3 for storing large amounts of text but can't find very good articles. I'm finding a lot of confusing things. :< What do you suggest? There are quite a few datatypes that I could use for large...more >>

I now have a sql statement with 8 Left Outer Join Detail Table. There are about 2000 records @ master table. I found that it needs about 15 secs to retrieve the result. Any solution that can be shortten the time of execution like 2-5 sec to retrieve data? thanks ...more >>

I am programming using VB. If I run a stored proceedure & it returns results then when these are read into a dataset the table has columns defined which I can then add to where required. If however no data is selected in the SP no datatable is returned to the VB & I would ahve to add the table & ...more >>

Hi, I am trying to restore a database from a different server to my server and also with a different name. But it gives me an error. ODBC-SQLState 42000 Device Activation Error. Does anyone know what this is ? Any help is appreciated. ...more >>

Hello. I started playing with SQLServer 2K with Visual Studio 2k3 and I found something interesting with SQLServer and I would like to know what the best practises are. I created a specific user for my web app to access their own db and it's tables. But I noticed by default I had to go in an...more >>

Hi, Let's assume I have two databases called A and B Is that possible for me to Backup a database A and Restore it on Database B??? Thanks Ed...more >>

I'm inserting rows into a temp table via a SELECT statement that has an ORDER BY clause because I need for the rows to exist in the temp table in the order specified by the ORDER BY clause. The temp table is just a few varchar and int columns with an IDENTITY property and NO indexes. Someon...more >>

Is there a way to use wildcards in a "IN" select statement; Select firstname,lastname where firstname LIKE IN ('john%', 'james%') I've tried numerous ways "IN Like", etc. Thanks, Darrin...more >>

I've created a DTS package to import data from an excel spreadsheet. When exectuing this package I get the error: "Datasource column 5.. to large for specified buffer size". Column 5 is a text field that I am wanting to load into a text type column in a table in the SQL server 2000 database...more >>

Does anyone have an example on how to remove duplicate entries from a table? ...more >>

Hi Friends, I heard that if we want to use 8Mb ram we should do some kind of configuration on Win Server and SQL server. Does anyone know about that? Thanks in adcance, Pat ...more >>

My largest database has a log current size of 22867.39 and 121.93 is being used. Can i resize shrink this and not destory any critical data? What would be the best recommendation to shrink the log and save disk space?...more >>

I have an image directory where the images are stored in folders by date. I have 2 .Net programs that search these folders throught the day on a daily basis and use the images for various purposes(the folders span years back) As you can imagine there is a serious performace issue on searching...more >>

Why most database programmers use int for there integer datatype when tinyint would be just as sufficient? Example: The integer value will never be over 100. why use value = int datatype instead of value = tinyint? ...more >>

I am trying to do the Chapter 16 practice 'Creating a standby server' in the 70-228 book. When I get to step 28 (setting the network share name for the backup directory), I get this message - Cannot verify that this is a valid share. I can find no mention of this error message in the books o...more >>

We have various users on our database, making use of the same login. Is there a way to get the IP address of a PID? or the machine name? thanks, Justin ...more >>

I got this solution from here: http://www.aspfaq.com/show.asp?id=2501 I was told the "Gods" of SQL are found here (not the link above, but here here) and could help. Anothers input in a dotnet forum said something to the effect the any DBA would be very unhappy with this. I am using a stored p...more >>

Hello I have a situation where I have an XML string that I pass into a Stored Procedure. This string is parsed and it's details validated and tables updated appropriately. The string is then passed to another SP to update a Certain table. This table can be updated by two methods to the SP has ...more >>

Hi, Using SQL Server 2000. When you change a machine name, what is the procedure in SQL Server 2000 for it to use the new name ? @@SERVERNAME still returns the old one... Is there a proc or command of must I edit manually the sysserver table in master ? Thanks! ...more >>

Hi, I want to drop a column but to this I have attached a default value. I know that I can find the sysobjects.name, but I don't know how to drop the default constraint without hardcoding the sysobject.name value. Is it possible to use a command like ALTER TABLE xxxx DROP CONSTRAINT @y and b...more >>

Hi, I have the following SQL statement that run in SQL Query Analyzer USE SFKC DBCC SHRINKFILE (SFKC_LOG,2) BACKUP LOG SFKC WITH TRUNCATE_ONLY Since there is no SQL expert in my office, I want the above statement to be executed everytime the server start. Can I put these statement in th...more >>

Would someone recommend sources to make the transition from Oracle to SQL Server easier? I am very proficent in SQL and PL/SQL with Oracle and we are moving to SQL Server. I am hoping there is something like "SQL Server Programming for Oracle Programmers". I would also like to hear an...more >>

i have a table with varchar entries which were populated from a select query on money fields. the problem is that the table i have inherited has data like; 0.1700 235.0000 50.0000 0.00 is there a simple way to trim the fields down to remove the last 2 zeros, so in effect the val...more >>

I want to select data on certain conditions and alla the conditions must be met. Heres my problem: I have 2 tables [Cars] and [Owners] [Cars] have 2 columns [CarID] and [CarName] [Owners] have columns [OwnerID], [PersonID], [CarID] The data in table Cars is: 1, 'BMW' 2, 'Cadillac' The data...more >>

Hi, I am having this error: ....[ODBC SQL Server driver][SQL Server]Cannot insert the value NULL into column 'version', table 'smartrak.dbo.dtproperties'; column does not allow nulls...... After having created a diagram and when I try to save i got the above error. I have this prob...more >>

Hi all I have SQL Server 2000 (8.00.760) running on a Windows 2003 system and I'm trying to start a distributed transaction with another SQL Server 2000 (presumably 8.00.760) running on a Windows 2003 system and I'm getting the folowing error: Server: Msg 7391, Level 16, State 1, Line 2 ...more >>

Hi While on the subject... Is there a way to link 1 trigger to multiple tables. Requirement, I have a trigger that perform a log function. Who altered the table, was a row added spid etc. This functionality I need for all my user tables. Without duplicating my trigger per table, is t...more >>

Hi All, I'm trying to implement a history log of all objects in a DB . I would like to implement this by creating triggers on the sysobjects table and inserting changed objects into a "History" table. Apparently SQL doesn't support triggers on system tables, if this is true does anyone kno...more >>

I am currently looking into the best way to add a column to the customer table that will Uniquely identify them, and will be alpha numeric in nature. There is already a column that stores a gu_id to make the row unique. So any alphanumeric colmn added would not need to act in this capcity. Wha...more >>