Helo, My goal is to print a Crystal Report from a Stored Procedure. I've written a VB.NET console aplication wich succesfully prints a report on the printer, however when this aplication gets called from the Stored Procedure (using xp_cmdshell) the application runs succesfully but it won't pri...more >>

Hi All, I am wondering how to handle the following situation. I have a stored proc that saves my record (either INSERT/UPDATE). Let's say I have Five fields: field_1 field_2 field_3 field_4 field_5 field_4 is conditioned to only allow a user within the group SPECIAL to update this fi...more >>

Hello, when using Query Analyzer to Debug my stored procedure I get the following error when inputting datetime values ERROR: Invalid character value for cast specification How can I assign a date like 1/1/2005 to a Parameter?? without getting the error above I tried '1/1/2005' o...more >>

Hello Folks, how can I find the error message text from the error number that @@error generates when an error occurs. ...more >>

How does the query optimizer interpert a view during runtime Q. Does it replace the view definition with the actual code of the tables/joins at runtime Q. Can there be any performance impact of using a view? Thanks -Paritosh ...more >>

Hello all, In one of my queries i use the following line: If Trim(txtLastName.Text) <> "" Then SearchSQL = SearchSQL & "AND LastName LIKE ' " & Trim(txtLastName.Text) & " ' " It's part of a variable named SearchSQL used in a Visual Basic (6) program It works fine without the * wildcar...more >>

Hi All, I have SQL Server DB in SQL Server 7.0 in a Windows 2000 System and My Client System Has SQL Server 2000 Client and Running A EXE which connects to the SQL Server. I am Able to Retrive the DATA from SQL Server 7.0 But while Updating the DATA in Database I got the folowing ERROR: "Er...more >>

what is the preferred technique for writing a stored procedure whose signature includes parameters for all cols in a table, but for those params not passed it does not alter the col values presently in the table ? i can see how to do it via a technique of assigning default values (such as null...more >>

"If a temporary table has its data changed more than six times, a recompile will occur. Consider using the KEEP PLAN option on SQL statements causing the recompilation. This allows SQL Server to use an existing query plan instead of creating a new one. " what does "temporary table has its data...more >>

I want create view referenced more than 256 tables, and i recive the message 'The query and the view or functions in it excees the limit of 256 tables' Is there anybody who know can't i encrease the limit ? ...more >>

Hi, I have a text file with data in report format. my task is to bring this format into SQL Server. I used to do this in Visual FoxPro, but the current environment don't have FoxPro. Does anyone have experience of pulling this kind of dat into SQL Server. 0000162 PLATINUM AT,TEST33 ...more >>

hi, i wish to know how to check file existence with sql... like in vb, i use dir(path&name) to check whether the file exist in the particular location... may i know how to do that with sql?? Regards, Jacky ...more >>

Hi, I'm finding methods to facilitate support to remote customer and the = latter have no knowledge of SQL server. Is there anyway store procedure can be update via a vb exe program? The = store procedure is written and keep in a txt/sql file. When the exe is run, it'll replace existing sto...more >>

Hi, I hope this is the right place to ask this; if not, I apologize. I am using a stored procedure in a web application to collect user information and insert it into a SQL table if the user's email address is not already in the table. Everything has been working fine (I thought) until I receive...more >>

Hello, Im developing an application (Visual Basic 6) in which it is a requirement that users be able to define their own SQL statements. To aid them in this, Id like to be able to pop up a query builder similar to the one found throughout Microsofts SQL Server products (e.g. click on tabl...more >>

I would like to add a clause to a view which tests a time value against the current time, and if the time value is too old, then it causes the query to return no rows. Here's a simplified query which works but, to my surprise, re-tests the condition for every row: SELECT LinkID FROM Links,...more >>

I have replications between SQL Server and SQL Server CE on iPaq devices. I need to catch event on the SQL Server, when the merge replication has been done. I need run some tasks each time after merge replication. How to do it? Any trigger in sys tables? Thanks Mike ...more >>

If I have a table, NOTES, that looks like this: TRANID ID DESCRIP 555 1 This is 555 2 a test 555 3 of the 555 4 Query 555 5 Analyzer How can I do a SELECT that would concatenate all those into one character string? Basically I have ...more >>

I have a pretty complex stored procedure that needs to insert records to numerous tables in the database. It also has some conditional logic to update certain records etc. How would I put this in a transaction? My stored procedure looks like this right now: BEGIN TRANSACTION /* A ...more >>

I am trying to check a VarChar field to see if it's contents are numeric and am hitting some problems. It almost looks like the system thinks it's reading hexidecimal or something. ISNUMERIC('2E0034') results in 1 (true). The same results for the values 2D0034 or 1E0034 but not 2A..., 2B..., 2C....more >>

Hi, How do I make a distinct selection from a table with two IDs ? Her is what I want: MyTabel ActionID 3 3 3 4 4 ActionTypeID 2 2 4 3 2 My wanted output would be: Select distinct (ActionID together with ActiontypeID) from MyTabel ActionID ...more >>

I wrote a function to return the current user without the domain name. This is a function that would seem appropriate to place in the master database. Our DBA is nervious about making changes to the master and has said that we should replicate the function to each of our individual databases...more >>

I don't know if any anyone can answer my question via a newsgroup, but here it goes.. How do u pronounce 'coalesce' ? ...more >>

Hi, Can a SQL server's name be different from that of the machine on which it is installed. I mean suppose a machine name is 'MachineA' can I install a SQL server on it with the name different than 'MachineA' ? (Not considering the named instance) TIA, Ratnadeep ...more >>

Hi A little help needed if possible I have a stored proc as below ----- Create proc TEST @Criteria as Select * From Table1 Where Field1 = 'Hello' ----- What I need to happen is the variable @Criteria to hold a string expression ie. "And Field2 = 'Bye'" so that I can append it to the...more >>

This is a bit OT. My Win XP task bar is 3 levels high (whereas the default is 1) This is of course so I can see the names of my 20-30 open applications:-) (right now only 13) . Using Query Analyzer, when viewing the execution plan for some larger queries, when I put my mouse over the bottom m...more >>

Because payments are not a one to one with bills, and the exact relationship of a payment to a bill cannot be determined (for instance, suppose 10 bills are created in one day for a customer all are the same amount except for two of them, and the customers remits payment for 70% of the bills, how...more >>

Hi! I have a view which does something like select datediff(d,x,y) as dd from table and a view which does something like select dayrange=(case when dd < 100 then ... when dd> 100 then ... end) I just had an error message "overflow for tinyint" in the second view, because one value ...more >>

Hi all, Is there any way to check what SQL Server edition I am running? I know one thing that it is not Developer, Standard or Enterprise edition and I need to change it to one of these 3 editions(So that I can install Reporting Service). How can I do this? Please help. Thanks... -Nikh...more >>

I bring in a text file through DTS. I have the columns in the file set to the columns in the table through the transformations I would like to add a "line number" to each row. Any idea how to add create a counter that is enumerated every time a row is added and then 'transformed' into a column i...more >>

Recently moved to sqlserver where can I find documentation on listing all the sp_* scripts, and why can't I edit them ? ...more >>

I am writing a few stored procs that insert records into the same table. However, the columns do not accept null values, yet I don't want to have to address each column in the table that I am not using in the proc just so it won't have a null. Is there a shortcut to setting a colum to 'blank'....more >>

When I open some of my existing procedures in Query Analyzer by right clicking on the procedure name in the object browser and selecting "Script Object to New Window As" --> "Alter", the procedures opens up but... instead of saying ALTER Procedure <name> it says CREATE Procedure Also, i...more >>

I need to pull data by: Total department sale and Total for each Employee. Basically 2 levels of grouping: Dept Empl_ID SaleAmount 1 a 3.00 1 a 5.00 1 b 2.00 3 d 1.00 3 e 5.00 Desired Result: Dept Empl_ID Tot...more >>

I am running V5R2 on client and am trying to link a 400 from SQL Server 2000. Can this be done using IBMDA400? I have gotten to a point with the IBM DB2 UDB that I can connect without error. But now I can't see my tables. This is what I am using for my provider string: User ID=USERNAME;...more >>

BlankHi Friends, I want to read from a text file, and use that string to insert into the database. My problem is that I don't know how to read from a text file through SQL Procedure. Please help me out. Thanx in advance, Amit ...more >>

Hello, I'm having a tough time figuring out if a certain select is possible. Have solved it by writing an interpreted web page but not happy with the solution. I'm stuck thinking in loops - not in sets of data. Would like to run a select from a table like: sqlID fKeyedID value 1 1 first...more >>

I have a table like the following: EmployeeID SupervisorID 1 22 2 7 3 22 4 5 5 22 6 5 etc. Given any supervisor I need to find the set of all employees underneath = that...more >>

I want to copy data from one table into another. I'm getting an error on this code about the object, @sbak , one of the table names, already existing in database. @sbak is the table I'm copying data into. What is wrong with this code ?? ========= code ============ CREATE PROCEDURE [db...more >>

I have basic sales data with a contract, sell price, cost, and order date like Contract SalePrice Cost DateOrdere A 343 333 12/1/200 A 255 222 1/1/200 B 234 1123 1/1/200 . . I want to average out the profit (Sum(...more >>

should nullable columns be indexed ? if you can't make not null should you not index then? AP...more >>

Hi all, I have a table called SalesDatesInfo with these columns: Id - Identity, auto increment, seed = 1, increment by = 1 SalesId - long SalesDate - datetime <other columns omitted for brevity> I am using this INSERT to get data into it: <begin transaction> INSERT INTO SalesDates...more >>

Hello all. We have a function as listed below, which is supposed to strip the following characters out of any string: !/\@#$%^&*+=-_[]{}<> However, it doesn't work because some of the characters are meta characters (i.e., "% and []"), and PATINDEX doesn't allow for escape characters. Is there a w...more >>

Hi all I have a database which has had quite a lot of the tables and columns renamed and deleted. I'm beginning to experience problems when deleting rows. The error message seems to indicate that there are still references somewhere to the old column names: Server: Msg 208, Level 16, Sta...more >>

Hi group, I'm new to sql statements. I have a table (news) with these fields: newsid date title content I want to make a select statemtent to see this output ordered by date: date 1 title 1 title 2 date 2 title 1 title 2 title 3 date 3 title 1 date 4 ti...more >>

Hi - I am trying to set up a trigger on each table such that when the application does an update on any record the following fields get inserted into the Audit table. EMPloyee_ID, key to the table Hire_date, key to the table table name fieldname old value new value username (the ...more >>

Hi I just wanted to know which of the following sets of codes in a stored proc will run faster, or take less of memory to process, or in short... more efficient. Different SELECT statements are UNIONed together. I have a choice to put the WHERE clause in the individual SELECT statements or...more >>

Hi , I have asked the similar kind of questions and got the solution by Anith Sen (I always thank all of you for such nice helps. The earlier help link is : http://tinyurl.com/2pab4). DDLs: CREATE TABLE [dbo].[Table1] ( [SARNO] [char] (6) NOT NULL , [CAUSALPT] [char] (17) NULL , ...more >>

Hello. This has been annoying me for a while now, and I'm not getting very far (and a deadline looms!). I have a table which contains time spans, so it has two columns of relevance here: Start (datetime) and Finish (datetime), I need a query that consolidates contiguous (adjacent) time span...more >>

Hello, Any links on erd for invoicing and ar? Question is now, how is normalized the ivoice to payment relationship Thanks ...more >>

Inside a table I would like to shift the data from two columns to their appropriat columns. So right now I have these columns: Person_Designation_ID, Designation_Code, Name, Description I would like to shift the data from Name into Designation Code and data from Description into Nam...more >>

sql2k sp3 I want to get a sum of a value on a row by row basis. My example produces exactly the results I want. I was just wondering if anyone has a better/ faster way? create table t1 (c1 int identity(1,1),c2 int) insert into t1(c2) values(1) insert into t1(c2) values(2) insert into...more >>

Hi, I would like to get this result (13.563) from this query SQL: select 13563/1000 result: 13.00 How can I set the result set to be 13.563 instead of 13.00? Thanks for the help... ...more >>

Hello ALL, I create some stored procedures and will send to my client who will run in his database. I don´t know what is the my client database name who store my procedures. So, I need to get the database name at "runtime" to execute the procedures correctly. How can get the name database na...more >>

Hello I need to create a stored procedure that can make a copy of a record in a table, rename the value in the primary key field, and then past this record back into the same table Is there a way to do this without retrieving all 70 column of data into variables, changing the key field variable,...more >>

Hi all, On my development machine, I have SQL Server 2000 installed. It is named instance. So I have to access it using machinename/instancename. But now I need to change the name so that I can access the server by using just the machinename. How can I do that? Thanks... -Nikhil ...more >>

Is there any way to hide system objects in Enterprise Manager, so that it will show only my tables and other objects Thomas...more >>

I have a DLL that we use to generate an excel spreadsheet for a particular report. This DLL calls several stored procedures to retrieve the data it needs for the report. I am attempting to optimize the stored procedures to make the report run faster One of the stored procedure ultimately gets it...more >>

I'm using ASP.NET and have a textbox (or a datagrid with a textbox in edit mode) that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but ...more >>

Using SQL2K. I've got med student/patient encounter records where the student can perform up to 4 procedures per patient and log these procedures 4 per patient record. I have the following union query and want to return counts per student of each procedure they've done. But I also want it to ...more >>

Hi! I have a table called interest which contains three fields (guidowner, idfld and idcod all varchar) where all three together is the primary key. I have a VB6 application (SQL-server 2000) which uses ADO to update this table. I do this by calling two stored procedures (one for delete and ...more >>

Im trying to create a #temp table with a number range in from 1 to a given number I know you can do; Select TOP 100 tNumbers=Identity(int,0,1) Into #TempTable from syscolumns how can i make the 100 a variable as declare @myNumber int Set @mynumber = 100 Select TOP @myNumb...more >>

Do you know how to Detect Implicit data type conversions? I just started as a DBA and I found a few performance problems with our t-sql code. One of the problems I found was a where clause doing an Implicit conversion while search a 2 million row table. Is there anyway or tool I can use to detect...more >>

Hello MVPs, 1)Why this query is wrong? (See the second one too, to understand my issue) Delete from Wages from Wages As W Inner Join Movements As M Inner Join MovementsEmployees As ME On W.ForeignKeyName1 <> ME.ForeignKeyName1 And W.MyYear = 2004 On ME.MEKey = M.Forei...more >>

Assume that a record is being updated in an transaction. The current value of a particular field in the record as currently stored in the database is 5. The transaction in place has changed it to 10 but hasn't committed it yet. If I do a Select against that field, without using NOLOCK, the Sel...more >>

All, I have to create a SP that calculates a percentile rank within a main class and within a sub class for an entity based on a raw score. Posted below is the DDL and some samples to populate the table. Using the data below, I need a fast way to calculate the percentile rank of each e...more >>

Hello. I test some query on sql server 2000 (sp2 on OS windows 2000) and i want to know why a simple query like this : select * from Table Where Column like '%value' is more slow on 2000 than on sql 7. And this case arrive only if the % character is in the begin. If you test this : select...more >>

Hi, Is there a quick way to make a number negative. I don't want to do this: (TheNumber * 2) - TheNumber I tried Casting, but I get an error. I did something like this: CAST(CAST('-' + TheNumber AS VARCHAR(20)) AS MONEY) So..... HELP!!! TIA, Eric...more >>

Hi there, I have a table with a set of foreign keys in them (id). The problem is I the ids have been inserted incorrectly and need to be replaced with the correct ids. Does anyone know an update statement that will replace the 1's with 2's and 2's with 4's (etc.) in the id column. id ...more >>

i wrote this user function CREATE FUNCTION GetXmlField (@xmlDoc ntext, @xmlField nvarchar(50) RETURNS nvarchar (128 AS BEGIN DECLARE @idoc in Declare @retVal nvarchar(128 EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDo @RetVal = Select Value From SELECT * FROM OPENXML ...more >>

Hi I'm using MSDE 7.0. When I execute the stored procedure (in Query Aalyser as well as in application sp_tables @type=TABL it gives out an error as 'type' parameter not found. But, the syntax specifies the 'type' parameter. Not only, 'type' but other parameters for the same stored procedure ar...more >>

when i pass a table data type as parameter to the Stored procedure, i am encountering syntax error Could any body tell me the syntax for passing table data type as parameter to Stored procedur i have used like thi create procedure proc1 (@temp table)...more >>

I am trying to find out 1. how meny days in the current month from curDate 2. which day of the week it is from curdate ...more >>

Hell From SQL server I have opened a rowset to insert data into an Access table which works fine INSERT OPENROWSET ('Microsoft.Jet.OLEDB.4.0','\\servername\sharename\databasename.mdb';'admin';' ',data (LEVEL SELECT TID FROM XY Is it possible to rename this table called data to data2 Stev ...more >>

Hi I had declared a variable called @sql The value of this variable is 'select * from departments How could i execute this variable... Thanks to al Josema....more >>

I have a string stored in database using HTML encode (Ex. "How%20was%20the%20Evaluation%2C%20Created%20%3F%2C"). In the above string the %20, %3F stands for white space and question mark (?). Now my concern is how do I decode the string at database level. Are there any functions available for the sa...more >>