Hi, I'm trying to make a bulk insert from a client computer that is connected to a SQL Server. However, I have noticed that the files that I want to import need to be on the server, instead of the client. You may not have windows access to the server machine, so I don't see the point of c...more >>

Dear Group, I have this sp: create procedure UpdateUser ( @id char(10), @name char(10), @pwd char(10) ) as update User set name = @name , pwd = @pwd where id=@id return I have a row insert into User values('test','peter','pwd') Question : How can I NOT update field pwd if the @pwd is n...more >>

Hi All, I'm having problems with a query, I have three tables: - IMPORTCordlessPhoneProducts: ParentCategory, SKU (and other product related columns not related to this query) - Categories: idCategory, CategoryDescription - Products: idProduct, SKU (and other product related columns not re...more >>

How to lock a record so that only one user can update at a time.I try to test my database using two user accessing and editing same data at the same time.The last user who click the save button will be the one who can get the updated data. How can I make the update record available into one us...more >>

Hi all, Please help me to write a stored procedure that can delete data from 3 different tables. I can do it with one delete statement per stored procedure but there must be a way of doing it at once. Thanks. ...more >>

Hello, I got a strange issue. The query with table join is faster than without table join. Here are the two queries. From common sense, it shouldn't be??? --query 1: select * from cashcard_txn_log where cc_tid ='80099314' and CONVERT(char(8), cc_settlementdate, 3) = '05/07/04' order...more >>

When running a Query Analyzer query I want to save the data to a text file. Whast is the text to do this? I also need to run this in a schedule. I can't get the job to run properly in DTS but I can get it to run in Query Analyzer - so how do I get the job to run nightly in Query Analyzer?...more >>

Hi All, I am new to SQL Server Stored procedures and could any one explains what is mean by N in this SQL server? What is it represents? set @col = N'au_fname' set @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) Thanks....more >>

Hi, I need to write a sp to decide if the parameter passed in is 15th of the month or last day of the month, how can I get the last day of the month? Thanks...more >>

Hi I try to create SP from ADO. Open connection and excecute queries. con.Execute "SET QUOTED_IDENTIFIER OFF " con.Execute "SET ansinull ON" con.Execute TextProc TextProc content " create procedure BISSGetDllName ( @ID_MESS numeric(10) = 0, @SwiftOperType int = 0, @IsAc...more >>

Hi I am trying to write some queries that run on SQL Server 1, but reference tables on SQL Server 2 using a linked server. I have no problem doing this. My problem is that I will be deploying these databases to more than one client, and the Servername of SQL Server 2 will be different at each ...more >>

Greets I have sucessfully created a sql database and vb.net program locally connecting to my local server. (I used visual studio 2003) I would like to send the database along with the compiled vb.net code to the user. The problem is the users machine and msde server are going to have differe...more >>

My version of SQL Server: Microsoft SQL Server 2000 - 8.00.878 (Intel X86) Short DDL explaining for what I talk: ------------------ drop table t1 GO create table t1(id int) GO insert into t1(id) values(1) GO print 'no cast' SELECT * from t1 Where id=1 print 'implicit cast. works' SEL...more >>

Hi, We're trying to select a variable number of records from a table and pass them as HTML to send them via CDONTS. This is the stored procedure we're trying to use to get this, but it only brings back the last record in HTML (in query analyser it brings back the lot). Can anyone help plea...more >>

Hi all, My .NET application needs to know if the currently logged on user has rights enough to create global tables, indexes, stored procedures, views, etc in the current database. The program runs through routines that create all these tables and we have to make sure there is sufficient...more >>

I have written an application to convert a FoxPro database to SQL Server 2000. Without going into detail, there is a lot of cleaning and reformatting that has to be done to this data as I move it across which is why this is being done with an application as opposed to using DTS. Below is on...more >>

Hi All, I have an application which has concurrent users. This application calls an SP whose job is get back an output value after some computation --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- Didn't worked out well Computation Algo: DECLARE @MYID INT,@NEWID INT SELECT @MYID=MAX(ID) FROM...more >>

Hi Freinds, I am creating some cubes which are based on heavy views of db I am getting timeout when building the cube. SQL server timeout is 0 (unlimited) Where else I can increase timeout for OLAP? Thanks in advance, Pat ...more >>

I have the following T_SQL working (kind of). What it's doing now is completely ignoring the insert statement, which means the @@cursor_rows never = 0. I have 5 records in TableA A - ALPHA B - BRAVO C - ZETA D - DELTA G - GOLF and 6 records in TableB A - ALPHA B - BRAVO C - CHARLIE...more >>

I have empolyee payment table by employee number, payment_date, period_end_date and payment. Some time for some employees by each payment_date, there are two or three or four period_end_date. I need write a query to only return last two (period_end_date)s for each payment_date for all emplo...more >>

According to SQL Server Books Online, "Trailing blanks are ignored in comparisons in non-Unicode data", that is, the expression "abc" is considered equivalent to "abc ". Because this statement is not explicit about how Unicode data is handled, this led me to perform a test with expressions...more >>

I created an access project .adp front-end for my users to maintain my sql 2k database. I have set up two usergroups/Roles - group1 and group2. I have a "Product" table and a view "V_Product". The Access front end is tied to "V_Product". I would like to have group1 to be able to update/inse...more >>

Hi - I am trying to create an inner join on 2 DataTables in a DataSet. I am also using a parameter to use in the Where clause. Since a DataSet is an in memory, I was hoping this was possible but so far have not had any luck. Is it possible? Thanks....more >>

Hello, I am trying to drop a trigger using Query Analyzer or Enterprise Manager but it takes forever (ran for 1hr) but still didn't drop the trigger. I had to cancel the query. It is simple update trigger. Any ideas or any help will be greatly appreciated. Divyesh...more >>

Hi gurus, is there any command, which exports stored procedure from MSSQL ? I need export stored procedure into txt file, so some program could looks like: exportsp [needed db, connect definitions] name_of_sp > myfile.txt is something like this possible, or I have to clicking ? -- In...more >>

Is there a way, short of a global change to the registry, to allow a specific database login account to have the power to run ad hoc queries? I have several scripts that query Excel and Fixed Length delimited files for import purposes. I have to be logged in as a system admin in order to run ...more >>

Hello, I have a small program in which I'm using a variable in the WHERE clause. Are you allowed to do this, or is there a trick to it? This code retrieves no records: Declare @mod_field varchar(32) set @mod_field = 'myField' SELECT id FR...more >>

Hi, Where can I find the formula that tells me the exact page footprint of a table's columns? Books Online has a bunch of stuff on how to estimate the amount of disk space a table will take based on general factors about its column structure. However, I have not found the exact formula th...more >>

I have a function which returns a table. ========== CREATE FUNCTION fnExample() RETURNS @ExampleTable TABLE ( f1 char(8) not null , f2 datetime not null , f3 varchar(100) PRIMARY KEY(f1, f2) ) AS ''' Fill up the temporary table... RETURN GO ========== I have set fields "f1", "f...more >>

I am designing a report to use as a data dictionary for my database. I am able to retrieve all of the information I need on the database tables and columns except the column formula. How do I query SQL server to retreive formulas enterd for columns?...more >>

Hi All, Is it possible to create a conditional Where Clause similar to the idea below? Create Procedure dbo.usf_Get_Stuff @vtype Bit As Select field1, field2 FROM Table1 Where field1 = '123' Case When @vtype = 1 Then 'And (DateCompleted Is Null)' Go Any help would be great!, Joh...more >>

All, Is it possible to use IF or CASE / ELSE inside the query? The problem is following. The application saves the log on the database so that the SessionStart and SessionFinish are DateTime. If the session started then the SessionStart=GETDATE(), but the column SessionFinish should be NULL un...more >>

I am trying to select values from a table where the date has passed. the problem is the date is stored as an integer. query ddl and comments below. drop table #Temp create table #Temp (pk int identity, dateasint int) insert into #Temp (dateasint) values (20040501) insert into #T...more >>

All, Is it possible to use IF or CASE / ELSE inside the query? The problem is following. The application saves the log on the database so that the SessionStart and SessionFinish are DateTime. If the session started then the SessionStart=GETDATE(), but the column SessionFinish should be NULL...more >>

Does anyone know why this code doesn't work? When I check the syntax, it shows errors on lines 23 and 25 (the END statements) Thx for the help ---------------------------------------------------------------------------- declare @code as varchar(10), @description as varchar(50) declare...more >>

I'm having trouble getting query results that I want. Suppose the table looks like this: mewr_id, rpt_published, country_id --------------------------------- id1, 1/2/2004, ab id2, 1/5/2004, ab id3, 1/8/2004, bc id4, 1/9/2004, bc the joined table is: cy_id, abbr ---------------- ab,...more >>

Suddenly when I try to generate scripts in Enterprise Manager an error occurs that reads as follows: [SQL-DMO]CreateFile error on 'MyBox.MyDatabase.PRC' The script file is still generated (preview does not show). The script file however does not contain any scripts for my stored procedures. A...more >>

I have two tables, I am linking The first is a transaction table with medical procedures (primary). The second contains cost data for the medical procedures (secondary). I have had selection criteria on the transaction table only. Now I am modifying the medical procedures table to have a y...more >>

Please help me with this one. I wrote a function that returns a string that contains a lot of commaseparated values, this is returned from the function as varchar(1000). When using this function in querys like this SELECT....... FROM..... WHERE 555 IN(dbo.functionName(ARG)) where dbo.fu...more >>

Hi All, I have a stored Proc that I run that pulls information from last month. running this manually is fine by changing the dates and passing them thru. Since I Always want the entire month of the previous month I am running this, is there a way to get the first day of last month and the la...more >>

I'm trying to use "INSERT INTO Columns... SELECT Columns..." and its not inserting the rows where one of the columns in the select statement is null. Does anyone know why this might be happening? I have been trying to use a case statement to change the null value to a string but this also do...more >>

A question concerning Access Project with SQL Server: I use a stored procedure that is calling several other stored procedure which update or append values in several tables. All of them are stored procedures with input parameters by which they filter rows to be updated or inserted into other ...more >>

Greetings, how to get just the time value from a dateTime field? For example: declare @d1 datetime set @d1 = '15:17:21' select @d1 gives --->>>1900-01-01 15:17:21.000 select convert(char(20), @d1, 1) gives --->>>01/01/00 How can I get just 15:17:21:000? or bette...more >>

All, How come I am getting this error Executed as user: CLC_DOMAIN\SQLAdmin. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed. when I run this t-sql as a job but runs ok if I run...more >>

I would like to use WMI to get information on what Services are currently running on a range of computers, but do not want to use VBScript. Can I use SQL instead to do something like the following ?? Set oWin32_Services = _ GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _ ...more >>

my SP like that, and I got the error near 'like' , Could someone be kind to help me take a look ? thanks in advance CREATE PROCEDURE dbo.companyinfo_tax_list @tcotype varchar(2), @searchcode varchar(11), @searchname varchar(40) as select cinfo.CoCode,cinfo.CoName,cinfo.CoAdd1,cinfo.CoAdd2...more >>

Hi everyone, I have an ntext field that needs a section replaced. I've been reading about the "updatetext" function (the replace function doesn't work on ntext fields, I've learned) and experimenting with it...unsuccessfully, I'm afraid to say. All the updatetext examples I've seen...more >>

I'm curious if anyone has done this. It seems so simple. Anyone have any suggestions? I am dynamically creating variables: @input1 and @input2. I want to print their values, but can't figure out how to expose them to be evaluated instead of interpreted as a string. If anyone has used tcl ...more >>

Hi, I faced to the following problem. I would like to express the next query such that condition1 appears only 1 time in the query SELECT * FROM mytable WHERE mytable.time = (SELECT MIN(time) FROM mytable WHERE condition1) AND condition1 condition1 may be complex. Actually, what...more >>

Hello everyone: Under heavy load, our calls to sp_xml_preparedocument are failing for what appears to be a lack of available memory. I'm assuming one of our SP's has a leak and is not removing the xml document in proper fashion, perhaps when an error occurs. I'd love to take the advice of ...more >>

Hi, I'm wondering about what will be the impact of encrypting all sps in application. would it slower down performance or no impact at all? thanks in advance. CNP....more >>

Hi, I loaded in a DB the list of (collections/ objects) and their (methods and properties) as described in msdn. I need to add the arguments and data types for the methods and properties. These are listed in VB Object Browser in the Microsoft Development Environment). Is there a way to easi...more >>

Hi I would like to create a stored proc which will get a specified servers event logs export them to text files and clear the log. I already have a cursor which will read the server details from a table but don't know how to export the logs or even if thats possible. Thanks Phill...more >>

Hi all, I have a column, which I’ve set as Datetime and I want to just enter the year portion. For instance Column A is written 08/11/2004 (UK) and I want to enter this into column B (the one I’ve set as date) and just return 2004. So far I’ve not achieved this as a date data type. I...more >>

Hi All Apologies for this posting coming in THIS group, but there didn't appear to be any group that was appropriate!? Anyway, my question is "How do i delete backups from a backup set?". I have a single file to which all the backups for the database i work with go to ... them being a Wee...more >>

Hi guys I have an interesting problem with an SQL statement, hopefully you can help! Basically, I've got a Contacts database, with names, phone numbers etc. Just one table. What I want to do is list the contacts in a certain order. I want to be able to show contacts alphabetically, based...more >>

I have a stored procedure in SQL server 2000 that uses table variables. When I execute this from query analyser, it takes roughly 2 minutes. I tried calling this SP from a VB stub using ADO and it seems to execute at the same 2 min. But when this is called from Web interface (ASP) using AD...more >>

Hi, Is it possible to have two tables where the primary keys are unique across both of them, so that if an automatically generated ID corrisponds to a row in one table that same ID will not be used for a row in the other table, and vice versa? Describing the background to this in terms o...more >>