Hi there! Is there any way to convert generated newId() hexa into an int ? for example, the first two char into an int which won't be greater than 255 I've searched for 'convert' or 'cast' but i found nothing... any idea??? thanks a lot Vince....more >>

Hi, Can anyone tell me how to use the result of a stored procedure to be used in another stored procedure? The first sp is a simple select statement with 2 columns, the second one needs those two columns to insert in a temptable. Thnx ...more >>

hello i have a table with 3 columns and 3 rows : id name price 1 name1 453 2 name2 4353 3 name3 32432 and i wanna make select that returns me somethin like that : 1 name1 453 2 name2 4353 3 name3 32432 4 null nu...more >>

I have a database with data in nvarchar columns that was inputted without the N prefix. But the application is being ported to unicode and I need to add the N prefix to store some foriegn language chars. To be safe I'm prefixing all new inserts with N (latin and everything else) but how will ...more >>

I want to select single rows only out of a SELECT based on certain conditions. eg. select * from myTable returns 1 A 500 2 A 500 3 B 500 4 C 500 5 D 500 6 D 500 7 D 500 9 E 500 8 E 500 I want my result atleast as (better if the amount could be roll...more >>

SQL Server documentation (BOL) states that an "instead of" trigger represents code that will execute instead of the action requested by the statement that caused the trigger to fire. For example, in the case of an "instead of update" trigger, the update statment which caused the trigger t...more >>

I have an issue with the "processed" time that shows on the analysis manager. Every time I open Analysis Services and click on my database, one of my cubes shows the current time for being "processed" under metadata. Not all of my cubes shows this time. My database and cube have the same nam...more >>

I am running SQL Server 2000 sp3 with compaibility level=80 and I can't get the UPDATED_COLUMNS() function to work. If I code it like it is represented in BOL: IF (UPDATED_COLUMNS()) & <some bit mask> I get weird and/or unexpected results. i.e. the columns specified in the mask don't yi...more >>

Hello I get incorrect syntax ner '100' on this one :( INSERT INTO 100Test.myuser.Test (Something) SELECT Something FROM TestDB why? TIA /Lasse ...more >>

Greetings All, I was hoping that someone might be able to shed some light on this issue. I am trying to add a FileGroup/Datafile to an existing SQL database. The code below compile and it runs in .NET Studio, however nothing happens? The form closes, no error messages are thrown, there is no ...more >>

Is there any Line Feed and Carriage Return in SQL script? It is something like VBCrLF or chr(10). Thanks....more >>

This morning I spent 3 hours drawing a diagram for 75 tables, had it looking very nice, was saving on a regular basis, but then I couldn't save data structure changes to a table until I closed the diagram out, which also wanted to save one last time. So when I opened it back up, nearly all of...more >>

Hello, I have the following table; __________tblMeasurements_____________ date | value1 | value2 | value3 | .......... |value24| where the date denotes a day (time is not releveant) and the 24 values represent the measurements on a 24 hour period. I decided to completely change the de...more >>

How can I find a table's Identity field name? I have tried to use sp_help, i.e., EXEC sp_help 'myTable' But it get me all bunch of data. Is there any system function to just return me field name? Thanks....more >>

Hi all, SQL Server timestamps are binary numbers that indicate the relative = sequence in which data modifications took place in a database.=20 So, can we retrieve the value of this TimeStamp column ? Reply ASAP. Thanks, Pramod....more >>

Hello. What's the best way to copy a table's schema and create a "table" data type inside of a stored procedure? Thanks in advance, Mike ...more >>

Hi, friends, I am writting a INSTEAD OF INSERT trigger for an insert action on Users table of our SQL Server 2000 DB. I know there is only one record (the new inserted one) in inserted table, but, is this new record also already in Users table? In anther word, if I do a query in the trig...more >>

Hello, I have table with datatime column, i want to create view on that table where all datetime will be converted to only date for example: 2005-03-21 11:15:34.127 2005-03-22 12:12:27.338 2005-03-23 13:18:14.311 2005-03-24 14:15:06.002 in view will be: 2005-03-21 00:00:00.000 2005-03-22 00...more >>

Hi there, I have quite a bit of experience with cursors and stored procedures but this problem is stumping me. I hope someone out there can help. The situation is that I create a cursor to get an id and a status value. I then loop through the cursor and take a number of actions based on t...more >>

Greetings All, I am looking for a reliable method of sending emails to our customers regarding billing information. (Yes - They are real customers.) I expect about 100 emails per day. I have built a system that is using DTS and an email library. It works OK. However, it is not 100% rel...more >>

Hello, I have a table with data like the following: testTable --------- name, cost, eventdate --------------------- a, 2, 2005-03-22 00:00:00.000 a, 3, 2005-03-23 00:00:00.000 a, 2, 2005-03-23 00:00:00.000 b, 2, 2005-03-23 00:00:00.000 I'd really like to be able to get the followin...more >>

I posted this a few months ago, but at the time did not find a simple method... I would like to have a select statement output into a single result... IE select email from a.client rather then return as: user1@user.com user2@user.com user3@user.com I would like: user1@user.comuser...more >>

Is there any PROC or variable that can tell me what the domain login name is of the user asking for data from SQL server? reg. Kjell Brandes ...more >>

A user entered 20 debit memos thru the application. For each db memo, 1 - the data is inserted into two SQL Server 2000 tables, 2- a commit is issued (Begin Trans / Commit Trans), 3 - a Select is used to retrieve data to display on the db memo thru Crystal Reports and is printed. For each of ...more >>

I need to change table's identity column to just be int column. Can someone shed a light? Thanks, DDL: alter table mytable alter column IdentFld int ...more >>

Need answer pls to this problem: Select charge_id,name,amount from tblcharge t inner join tblpayments p on p.id = t.id where p.charge_id not in (select t.charge_id from from tblcharge t inner join tblpayments p on p.id = t.id where t.amount - p.amount = 0) This works if data is: charge_...more >>

Query/Function Question I have a retail issue where I store sizes in a table per item. The table looks like: PO # Item # Color Size Size Equiv What I am trying to do is get a comma delimited list of sizes AND size Equiv per po/item. The sizes AND Size Equiv need to be a unique list...more >>

What's the SQL or is there a stored procedure to replace the DEFAULT value of a COLUMN? Dropping and re-adding the column seems overkill to me. thanks in advance ...more >>

I have a log file containing a datetime field. I need to display the times as periods (ignoring the date) eg. 09:00 to 10:00 is period 1, and so on. I have tried using the CASE function, but can't get the syntax right with the datetime....more >>

Are there any tools available that can export SQL Server Diagrams. We have around 50 complex databases whose structure might change periodically. I want to export those SQL server diagrams to pdfs or image files. If you have any suggestions, let me know. Thanks....more >>

I have an operational parameter in my SQL select statement, @EmployeeName, that will filter timecard data for a particular employee. When I am running the query and it prompts me for @EmployeeName, I would like the option of putting in * or [ALL] or something of that nature to return all the ...more >>

SQL Server 2000 I'm having a problem with an update statement in a stored proc. that I'm trying to create. I can't figure out how to get a column to set to the return of another stored proc. My code is below. Please let me know if this is even possible, and if so, how. Thanks!!!! ...more >>

I have several input parameters in an SP as follows: @sString1 as varchar(1000), @sString2 as varchar(1000), @sString3 as varchar(1000) I want to loop through them and perform the same code on each one Declare @x as int @sStringX as varchar(3000) Set @x = 0 While x < 3 Be...more >>

What's the easiest way to truncate the log file size? After some test operations it was increased to 420 mbytes, keeping in mind that I don't need this log file and the real database size is only 20-30 mbytes it's nonsense. Should I disable a full recovery mode or what? Just D. ...more >>

Hi I have a DTS which uploads a file to Secure FTP server. I'm using ActiveX script to upload the file using scp2 command. I'm using WScript.shell to run the command. When I manually run the DTS through the DTS designer it runs ok but when I schedule the DTS as a job it fails at this point w...more >>

Hello, How can I select the lowest price excluding 0? in this example '4' part price 001 0 001 4 001 5 thx, sh ...more >>

Are there any commands that can be used to dump results to a flat file, or does this need to be controlled by an external app ? Thanks in advance... ...more >>

In VB and VBA there is a function called "EVAL". It evaluates the expression that is the parameter of the function. x = Eval("5 + 3") would return x = 8. Is there something similar in SQL 2000 Thanks, Keith ...more >>

I am trying to write some code to go through all the tables in my database and do a row count. What I have done so far is open a cursor to sysobject and loop round the tables in it. Then I am building a query in a string "SELECT COUNT(*) FROM" + tablename . My next step is to EXEC the created st...more >>

i recently started working for a smaller company. they purchased a new sql server for dev and qa purpose, a developer's box. there would be sql2k on it for .net development. it's raid 10 hardware (very high performance) with 2GB ram. My question is, besides the sql server service, the network ...more >>

In my table I have for example 4 fields : Field1 Filed2 Field3 Field4 I insert each record of this table into different tables. Field1 and Filed2 go to one table ,where Field3 gose to another Table and Field4 as well.Is there a way to combine all these actions and have only one insert s...more >>

I have a table as per below Item1 Item2 AAA BBB BBB AAA BBB BBB AAA BBB CCC BBB I need to find a way to combine the two values alphabeticaly giving the result below Item1 Item2 Result AAA BBB AAA-BBB BBB AAA AAA-BBB BB...more >>

I am trying to use variables in the WHERE statement and I get no records. If I run the same statement in Query Analyzer and replace the variable with the value I get records. Example: INSERT @Output SELECT col1, col2, col3 From Table1 WHERE (col3 BETWEEN @var1 AND @var2) RETURN ...more >>

Hi, I'm considering using a trigger for the first time, the application is as follows: I have a table that contains customer calls, on this table is a bit field that flags if the call is a repeat within the last 90 days, so on the insert of a new record I need to set the flag if it is a repe...more >>

Hi, I have the following table: tblMeasurements Date | value1| value2| ....|value24| I need to find (and display) the maximum per day, i.e. the maximum number among the value1...value24 in the same row (date). I tried MAX(value1,.....,value24) to no avail. The examples I see on the n...more >>

In a normal select into from code, I encountered the intra-query parallelism error. This code is being executed as a stored procedure in another code that executes about 5 different sprocs. My question where does one place a query hint to rectify this error message? Intra-query parallelism ...more >>

I have table in SQL Server 2000 that stores comments in one field of type varchar with a length of 250. A test value entered is 249 characters long. When I run the following: select len(comment) from tblcomments where emplid = '241s' the len returned is 249, as expected. However, once ...more >>

I am using an UPDATE statement (see below) to update a field on one table with the SUM of a field on another related table. When I ran it in query analyzer it took over a minute but it is using a primary key on one table and an indexed key on the other. There are only 872 matching records on...more >>

I have a whole bunch of update statments stored in a .sql file. Is there any way to run it in the program (just pass a filename)? I know I can put the statments in a store procedure and run the store procedure in the code such as sqlcommand.commandtext="execute storeprocedure" Howev...more >>

Hello there, folks. I have 5 tables with exactly same column types, names, and constraints but with differnt names.(e.g. table1, table2, ..., table5) I have a stored procedure(sp_example) that writes some calculated data to "table1". but I would like to modify the stored procedure to wri...more >>

Hi I wish to create a table through sql code which picks up fields from different tables in the database. Lets suppose I have TableA with fields 'W' and 'X'. TableB has fields 'Y' and 'Z'. I want to create a TableC with fields 'W', 'X', 'Y' and 'Z'. Is this possible? Any help is great...more >>

Here is my query, it works fine. Now, I would like to execute this query for every item on the itemprice.item column. I could create another Select query that would retun all items in the item column of the itemprice table, but I don't know how to make the first query (the one below) to g...more >>

I have 2 tables in a MS SQL 2000 DB. Tab_AD contain all the ads with customerid for each AD. A customer can have several Ads Tab_Pictures contain all pictures for all ads. One ad can have several picture but only 1 prefered (prefered=1) Not all ads have a picture either. I am trying to lis...more >>

Hi again, Still one more problem: Does anyone know how to use a query hint on a linked server ? I always end with this known err msg: Server: Msg 7377, Level 16, State 1, Line 7 Cannot specify an index or locking hint for a remote data source. Everybody claims on the web it is impossib...more >>

Hi, I write stored procedure which keep track of every object of database with its defination. But problem is that, SP execution either it is menually or through schedule the job on some fix-time. But i want to execute this SP on when SysComments table update. It is possible or not. And if ...more >>

Hi all, We got an sp which has some trouble when doing: insert into destTable select * from srcTable Our db is in simple recovery mode. I think SQL does the following during the insert: 1) perform the select on srcTable (i.e. a full table scan) 2) once the result set is created from ...more >>

Here's a reproduction for those interested in simple locking issues: -- in one connection (QA window) run this upto the next comment CREATE TABLE dbo.tTestBlocking ( ID int NULL , fname varchar (50) NULL ) GO declare @counter int set @counter = '1' while @counter < 1000 begin in...more >>

Hello All, For my own edification, I was wondering if this query can be written in some other way(such as using Joins ... etc). This query generates the daily cumulative totals. Many Thanks, Gopi Create table DailyOrders (OrderDate Datetime, OrderQty Int) Insert DailyOrders values...more >>