I have a question regarding the best design for some tables. I have three tables; Orders, BillingInfo and PaymentInfo. Right now, when a customer is placing an order, they select a payment method (from PaymentInfo) such as credit card, purchase order or on account. When they select something othe...more >>

Let's say I have a table Table1 has fields like below ID Date1 Date2 Date3 Date4 1 2/23/03 4/15/03 3/11/03 3/18/03 2 2/24/03 2/10/03 4/12/03 3/15/03 3 1/11/03 3/12/03 2/11/03 3/31/03 I need a SQL statement that will let me find out the larges...more >>

I want to ask a semi-general question, which I think I can do without giving my whole database definition. Books and articles about table design say things like "Columns that are the primary keys or that are unique are most likely to be joined and frequently queried... When no naturally eff...more >>

Hi I'm pretty new to SQL Server Can someone point me in the general direction for information on how I can create a VIEW on tables in a different SQL Server 2000 database on a different server. I have the connection information for this database ie UserID, Password, etc Cheer Tim...more >>

Hi, Sure hope someone can help me out with this... SQL Server 7.0 & 2K I am trying to return a field with parens around it. I can get it to work it query analyzer, but for the life of me I can seem to make it work it the sp... In Query Analyzer this works: '************Start SQl******...more >>

This is my table: LedgerID TransDate TransID TransDesc TransAmount AcctBalance PolicyID ReceiptNo 9 2/27/04 123464 Cash Payment 128 0 0 45653 3 3/27/04 123458 ...more >>

I have a table with SMALLDATETIME values, that are stored from a string. I have a Sql Server 2000 instaled on a Windows 2000 server (Spanish Version). The problem I got is that I receive a string in a file with that Date value, but as it is from another country it has another date format. That's ...more >>

Hi, I am trying to retrieve print statements (inbformation messages) from query analyzer as they happen in a stored procedure. Below is a stored procedure which i have created as a test. All the print outputs are returned at the end of completion of the stored procedure. Is there a way of ret...more >>

Hello All, When I try to truncate the tables in my database I get error message Cannot truncate table 'bt_Bug' because it is being referenced by a FOREIGN KEY constraint. How can I truncate all the tables I tried to do this in many possible order of all tables... but I get this error al...more >>

How can i find out what stored procs have been granted exec permissions to a role ? Say if i have a role 'ABC' and had issued statements such as grant exec on 'sp' to 'ABC' Id like to have the list of all those sps for the role ABC that have execute permissions.. Is that possible ? ...more >>

Hello all, I have an xp written in C++ that needs to take long text as an input parameter (TEXT data is stored in a table). I have no problem defining a parameter of type SRVTEXT however I can't pass it from SQL Server, the limitations on what you can do with TEXT vars are just too great. You ...more >>

Do you reccomend link about sql server? Thanks ...more >>

If I read date in enterprise manager I get value: 1.1.1900 0:01:19 and this is also the value I get in application If I read the same date in sql analyzer I get value: 1.1.1900 0:01:18.907 If I read the date in application and then use it in WHERE condition, I don't get the match, becau...more >>

Hi I have the following SP what I want to do is when "All" is passed to @ledger_year_month I want construct my sql as where ledger_year_month > @ledger_year_month else I want to construct my sql as where ledger_year_month = @ledger_year_month Note the greater than and equal sign... I ...more >>

Can this be done? and if so, where can I see some code? ...more >>

Greetings All I just started playing with MS SQL and was wondering how i could possibly do the following for example I do a 'SELECT source FROM history' and that gives me a list of sources. Now i want to run a SELECT for each source i received from the previous SELECT For further clarity, in pro...more >>

Could someone explain how temporary tables differ from regular tables? = Here's my problem: I have a script that creates a temporary table for = SQL testing and then does things with it. I want that table to be = recreated each time I run the script. I use this script: IF OBJECT_ID ('dbo.#t...more >>

Hi, I want write trans-sql script(s) which compare 2 db's stored procs, user defined functions and triggers. For example; DB1 ----- StoredProcA (different than DB2) StoredProcB StoredProcC TriggerA TriggerB (different than DB2) DB2 ----- StoredProcA (different than DB1) Stored...more >>

I am confused why calling nested stored procedures that return values in parameters does not work. I have debugged using the Query Profiler and found this to be the case, is there a workaround Exampl stored procedure x is running and calls stored procedure y passing it 3 parameter exec y @a, @b...more >>

Hey I have a WIN2K Server with SQL2K Server, when the SQL Server runs a backup, all users get kicked off.. Even my remote client (NETOP) get disconnected.. When the backup is done and it verifyes, the system is OK! I have set CPU_AFFENCITY (only use 1 off 2 cpus) (started as 2 cpus) ...more >>

I have table TEST with ID,VALUE and TIMESTAMP columns. In application I read the values: select timestamp from test where id=1 oldTimestamp=rs.fields.item(0).Value Than I have SP wich cheks if there was no update in time after my reading(if there is the same timestamp when I read it) and...more >>

I have a server, running Win2k & SQL2k Med venlig hilsen Klaus Ladegaard Jensen TECHOTEL E-mail : k.jensen@techotel.dk Homepage : http://www.techotel.com Phone : (+45) 36 19 21 41 Fax : (+45) 36 44 10 75 ...more >>

Hello I need to export and remove all the data from databaseA to DatabaseB and automate this process. WHat is the best way to do so. Thanks ...more >>

Hi, I have a table containing evenly spaced 6-minutely readings and status: Create Table Opacity (Tagkey int identity(1,1) primary key, [Datetime] smalldatetime,Value real, Status bit) insert into Opacity Values('2004-01-01 00:00:00', 12.3, 0) insert into Opacity Values('2004-01-01 00:06:...more >>

Hi All, I have an interesting problem. I need to be able to match rows between the inserted and deleted tables within an update trigger when multiple records have been updated and a primary key may have been changed. I came across the following code on a Sybase web site and it does also wor...more >>

Hi all. I am creating a SQL script that temporarily creates a table in the database called "temp_new_inventory". This is not a temp table but a table I am temporarily creating in the database and that I am dropping at the end of the script. At least that's the plan. Anyway, I am getting a...more >>

When I run the following query the result is always "Jan" instead of "Feb" based on the current month being 03, any thoughts? SELECT left(datename(m,datepart(month,GETDATE()) -1 ),3) as mth from dbo.table1 I am trying to get one month less the current month abbreviated....more >>

Hi, I ran the following query and I got a count for every single table in my database. EXEC sp_msforeachtable 'select count(*) from ?' How can I include the table name for each count in the result set? Thanks for all your help. ...more >>

A coleague of mine and I are in disagreement over what runs faster, a query that has multiple CASE statements or a UNION ALL. I believe that multiple CASE statements will parse faster because you are only using one main query as opposed to running two queries and UNION'ing them together. ...more >>

High, i want to do this in a store proc, if i insert a GO bevore the ALTER TABLE the SyntaxCheck brings errors, if not at the RunTime in the UPDATE i_level is not found and the Table was not ALTERED. Ups, why? if exists (select * from dbo.sysobjects where id = object_id(N'[LTVonZielAuswert]...more >>

We had a production issue where a stored procedure was timing out. This stored procedure is stored in Database1, but insert into history tables in Database2 using a select from database1 where the source tables reside. Now once we forced a recompile on that particular stored proc, everythi...more >>

I have a varchar field that may contain up to 50 characters. It might look like this: 1,12,0,17,15,0,9,12 How can I parse this field, using SQL commands, to get only specific values: the first (up to the first comma), the second (between the first and second commas), and so on? Any help ...more >>

Hi, I am trying tto execute this query and it gives me an error, any leads would be appreciated. Thanks, Shaleen SELECT DISTINCT TblIssueLedger.MagazineId, TblIssueLedger.IssueYear, TblIssueLedger.IssueNo, Sum(TblIssueLedger.nValue) AS [Values], Sum([StockAmount]*-1) AS Copies, Co...more >>

Hi, I have a question regarding the "Timeout Expired" excetpion. We have a database server running SQL 2000, and another computer accessing the database server using ADO.NET (via a CAT 5 cable directly). Occationally, I get a "Timeout Expired" exception tring to access database server. I...more >>

Is there a way, in SQL 7, to grab data from a specific field in the updated record with the same trigger that was triggers by an update to that record. When this table is updated it is by hand so I don't need to worry about multiple records being updated at once but I do need to pass some da...more >>

is it possible to lock a record using select statement something like this, but it didn't work SET ISOLATION LEVEL READ UNCOMMITED GO begin tran gp SELECT *, ImportID AS Expr1, Qualified AS Expr2 FROM tblFleetSites UPDLOCK WHERE siteid = 26743 go Thanks ...more >>

I have been listening to the diatribes on this newsgroup about not using cursors. So I have been going back and converting my cursors to set oriented procedures. Below is a procedure that I am stumped on converting. Background: Before this procedure is executed another procedure has creat...more >>

I have a sql db that collects tons of data. To reduce the amount of data that is stored in the db, I want to create a trigger that will not insert the record when a field value equals a certain value. Any help Thanks...more >>

Lets say I have two table who are related. They have some of the same columns. If the columns in table 1 are NULL, I want to fill them with the values from the columns in table 2 (if a related row exists). Table1 3 rows Column1: TableID - Int - values 1, 2, 3 Column2: SSN - Int - values NUL...more >>

I was thinking... The SQL Server work with big files (like 30Mb or 30Gb) and when i use "UPDATE..." or "INSERT..." it updates the MDF instantly. I think it just find the correct place in the file and update the record. But it doesn't create another file (or create?) after it performs the up...more >>

Hello All, I need to shutdown SQL for my Back-ups using Backup Exec. I used to have the Tape drive and Backup exec along with SQL on one server but since we upgraded to Win2000 I had to install the Backup and tape drive on one server while SQL is on another. My question is.....How can I shu...more >>

I have a query like select dbo.function(column) as newcol from tablename where newcol=0 everything works fine without the where clause but when I add it, I get an error that newcol is an invalid column name I am sure that I have done this before (quite a while ago) but can not remember ...more >>

Hi, Can you pass a recordset/table to a SRPOC, and how would you do it if you can? TIA, Eric...more >>

Hi, I need to programatically export a table into a fixed length text file, for reporting back to a legacy system. I'm using BULK INSERT to import the file in the first place utilising a format file to map it into the database, is there any way to use the same fomat file to get the data back ...more >>

Hi All, Following is a stored procedure that clone a Item with new name from a Item with orginal Name. The stored procedure work fine in Query Analyzer, but when I called the stored procedure from VB program, It cannot insert a record into the Items table. Does anyone know the problem? CR...more >>

hi i'm writing a code on SQL. ...... BEGIN TRAN IF CAST(@ver as datetime)=(SELECT version FROM channel WHERE id=@id) BEGIN IF @strum1 IS NOT NULL BEGIN UPDATE channel SET end_time=@adesso WHERE channel.id=@id SELECT @canale=id_cha FROM channel ...more >>

Hello Im trying to install sp2 on a win2k machine and i get: Error running script: sp_vupgrade_replication(1) all the time when its trying to finish the installation... sigh how to fix that? /Lasse ...more >>

I have ar query I would like to write which is a bit complicated (being a newbie). What I want to do is select from one table (tOrders) the Driver ID for a Pickup, Driver ID for Dropoff, the Commission Sum for that Pickup DirverID, the Commission Sum for the Drop off Driver ID. For example (t...more >>

I know this question has been asked a lot but I'm going to try again... We have a batch process that loads data to SQL Server 2000. It runs every night and loads approximately 20000 rows per night to a database of around 2.5 million rows. We perform a commit after each 100 updates to the dat...more >>

I have a use-defined function that takes a date and returns the date with zero for the time piece. Here is the code for the function. create function ZeroTime(@Date datetime) returns datetime as begin return convert(char(10),@Date,112) end GO Now I have a table with this definit...more >>

Hi, Here's my problem: Take this simple SQL statement I use in a simple stored procedure: SELECT FirstName, LastName, Department From tblSomeTable WHERE DepartmentID = @MyParameter My question is sometimes I'm looking for employees from accounting department. Sometimes, I want employees ...more >>

I will be using MS Access to insert rows into a SQL server database. I know how to do this using recordsets / ADO. I also understand how to insert the rows one at a time using a stored procedure. Is there a way to pass many rows at once using a stored procedure ? Thanks ! ...more >>

I have written the following Stored Procedure, for SQL Server 2000 I am wondering if there is a better way to accomplish my goal The Stored Procedure works okay, but I am an SQL Amateur, so maybe this code can be improved The Procedure uses 3 input parameters, and 1 output parameter I need to re...more >>

Hi all. We're getting an error "Invalid object name 'dbo.udfGetPositions'." when calling this function. The function was created with no errors. Here's how we're calling it and its definition. Thanks. select dbo.udfGetPositions (170011) ------------------------------------------------------- ...more >>

Currently I am running IIS on the same server as our SQL server 2000 standard edition. IIS only runs the company's intranet site. The main purpose of the intranet site if for employees to access reports based on the SQL server through Crystal 9.0. I want to break off IIS from SQL serv...more >>

I am trying to create a query that will generate a list of appointment slots. I have a table as follows to help generate the time series (hopefully it is Joe-Friendly enough not to get criticized to sharply): CREATE TABLE dbo.CalendarTime ( TimeOfDay datetime NOT NULL, HourOfDay tinyin...more >>

Hi All, Does anyone know how to get the starting date for a given weeknumber?I am using Datename(ww,inputdate) and get the weeknumber, however, since the data is tied to GUI, i would like to also get the start date of the week....like week number 1 start date is 1/1.... Thanks in advance ...more >>

Is it possible turnoff the IDENTITY property from an existing table/field? If so, what is the SQL Statement to do that (I need to do it from a SQL Statement, not the Enterprise Manager). Thanks. Jeff...more >>

I manage a large complex payroll system. Processing occurs employee by employee with extensive queries, use of temp table and insert to a variety of table. Each employee is wrapped in a transaction block. Performance is excellent when running small batches of employee (approx 100 or so) with t...more >>

I have to read a string wich has numbers, but sometimes it may be empty, but not null... So I have to Use Select CAST( ISNULL( Substring(@STRING,44,4) ,0) AS NUMERIC) as NUMBERS So It Works if it has numbers or NULL but is not working if if is ' ' empty, because cast will say that "There...more >>

We have a couple of tables with identity columns used in order to generate unique keys. As we move the set up information from a QA environment to a Production environment, we would like to keep the initial information. However, our DBAs don't like to do a lot of data script handling. This is farmed...more >>

Hey Group - I've got my new PC and I want to load Yukon instead of 2000 but I still need to administer 2000 on some servers. Has anyone successfully gotten Yukon and SQL 2000 SP3a to coexist on a machine? I'd like to run XP, but I can run any server OS if I have to. Thank you, Ter...more >>

Is there an easy way to script ALL the stored procs in a DB out to individual files? We have over 400 sps that I need to search for individual strings. I would like to make a separate file of each one to review and consolidate. I can do one-by-one via QA, but may not live long enough to...more >>

Hi all, I am using Bulk Insert which works fine but the problem is some rows have null values for some numeric columns and then it gives me an error. How can I insert nullvalues using bulk Insert. I am not using any Format and I cannot change before bulk insert as file is very big. Pleas...more >>

I have a job that runs everynight with 20 steps in it. Each step runs a Stored Procedure on 20 different servers in 20 different locations. I set each step to go to the next step even if it fails, so all of my locations get updated. I have a net send setup for when the job fails, but I on...more >>

Hi all, I have very email body content stored in database field whose data type is text. I need to retrieve this value in side the stored procedure , replace few parameters in the body with actual values and then send the emails using CDONTS from within the stored procedure. I'm able to r...more >>

Joe Celko wrote on 2004-03-01 3:01PM: By starting with a sound data model, then implementing a schema in 5NF that is appropriate for that model. What is: schema in 5NF ? Thanks in Advance. ...more >>

I require to access the domain name in SQL can anyone suggest how to do this I am working in Nt Domain *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi all, I need to perform a select on two table. Here is what I want to know Table 1 Number ID 11000 8 11001 15 11002 58 Table 2 (can have multiple same number, not primary key) Number Page Name 11000 1 Franck 11000 2 David 11000 3 John 11001 1 Dave 11001 2 ...more >>

Hi all, I need to perform a select on two table. Here is what I want to know Table 1 Number ID 11000 8 11001 15 11002 58 Table 2 (can have multiple same number, not primary key) Number Page Name 11000 1 Franck 11000 2 David 11000 3 John 11001 1 Dave 11001 2 ...more >>

Hello I need to export the data automatic from stored procedure. Meaning every time that the stored procedure will be call, it will make the select statement and the result will be export to excel. Thanks again... Hello. How can I export data from table in sqlserver DB to Excel. I don't...more >>

Hi All, I created a column as datetime type and use a default getdate() method to insert a current DateTime to this field. Why SqlServer inserts time only in seconds, although I'm using the full size datetime? Is it a limitation in the SqlServer? How can I set/get the parts of seconds? Dmit...more >>

lets say table ABC consists of following numID char(8) NOT NUL IDSet smallint NOT NUL unfortunately table ABC has no PK and so I plan to creat one by adding a calculated third column a example of the data would be table AB numID IDSet A 0 A 1 C 0 C 1 C ...more >>

Hello. How can I export data from table in sqlserver DB to Excel. I don't want to export all the data that in the table, but only a few rows that I get in my "select" statement. Thank you very much...more >>

I need to write an SQL SELECT statement that when I pass two parameters, it should bring in all records (like there is no filter) or only the filtered record. If there is a better way to do this I am very open. This is working somewhat, but I think if some of those values are NULL, they are...more >>