I want to export a SQL table to new MS Access file or to a file already existing in a known location. The SQL table is a temporary table (#). If not for # table, any idea to export a SQL table will be useful. The exporting logic should be coded in stored procedure. * I don't want to use th...more >>

Hello all! I'm trying to create a function that returns a random progressive / regressive series of values (e.g. 11000, 11300, 11900, 12000, 12250) taking a start value, an end value and (an increment value). Can you please provide help? Until now I've managed creating the function returning ...more >>

I am in the process of changing from SQL server A to SQL server B but the databases are huge. Can I mirror A with B then just turn off A when its completed? What steps would be involved here? Thanks! Jon ...more >>

Can someone give me the command to properly backup an entire database including all security and what-not so I can move it to a different SQL server? The restore command too would help! Thanks! Jon ...more >>

Hello. I'd appreciate any help from you sql gurus on this problem: I have tables Foo, Bar, and FooBarJoin, which is your typical join table for a m-n relationship. I need to find the unique rows in Foo, where the uniqueness consideration includes the relationships with Bar. E.g., |Foo| ...more >>

Hi I want to write a SP that has 2 params: @begin, @end. This SP returns records from the order "@begin" to "@end". For example: set @begin = 5 set @end = 10 I want to take records from 5 to 10. Thank for any reply GhostNguyen ...more >>

Is there a check for numerics? In my conversion from another system to ours, they have an alphanumeric field that puts an "A" in front of their check numbers in some cases, has no Alphas in some and all Alphas in others. I could always do a case statement looking for the A and then doing a...more >>

I have a need for a criteria to be = 32 or 33 and wondered if BETWEEN 32 AND 33 would be faster than IN(32,33). This is an indexed field. Thanks. David ...more >>

Hi All I am trying to use a left outer join but am not getting the correct results. Here it is SELECT glpd_no FROM glperiod LEFT OUTER JOIN invnmove ON (glpd_no=nvtmper_glpdno) AND (glpd_company=nvtm_company) where (glpd_no>='200501' AND glpd_no<='200505') AND nvtm_type='in' AND...more >>

Hi! I want to copy a row in a table, and insert it into the same table but with the difference i want to modify four fields. Why you might ask. I want to be able to copy a order and specify a new date and ordernumber. Is it possible?, How? Examples, links, directions. Anything would be mos...more >>

Hi All, I have few questions for Transaction Log of the SQL 2000 DB. I use the below command to truncate the Transaction Log datafile. backup log databasename with truncate_only dbcc shrinkfile (databasename_log, 100) But how do I do this using the Enterprise manager? I can see that th...more >>

Are these two statements equivalent? If not, how can I rephrase the 2nd one? UPDATE Contact SET DoNotCallTypeKey = 20 WHERE EXISTS ( SELECT * FROM SSA INNER JOIN SSP ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey WHERE ssp.ContactKey = Con...more >>

Wonder if somebody know if it exist som tools that makes it possible to get a graphical overview of relationships between C# source code components and their's interaction with the database tables/elemts (E.g SQL server database) A typecial rewengineering tool to get a grasp of a system wit...more >>

Hi, How do i insert a value into varbinary column using ASP Here i dont want to use "INSERT INTO" Tx. DNK ...more >>

Hi, How do i insert a value into varbinary column using ASP Here i dont want to use "INSERT INTO" Tx. DNK ...more >>

When running the following query against MS SQL Server 7.0: SELECT documentitems.manufacturerpartnumber,documentitems.customnumber01,documenthe aders.customnumber01,documentitems.description,documentheaders.projectno FROM DocumentHeaders INNER JOIN DocumentItems ON DocumentHeaders.ID = Docu...more >>

I'm kinda new to SQL (Been working with access) and since links are not permitted in SQL (except as a view with SELECT * FROM DATABASE.dbo.TABLENAME) it's a static connection where I need dynamic selection. What I would like to do is build a view to pull the DATABASE.dbo.TABLENAME from a refe...more >>

Hi All I have a trigger which is copying data from a tabel in database A into another table in Database B on the same SQL server - but for some reason fails, without an error (as far as I can see). As soon as I change the trigger to copy in to a tabel in the same database A as the source...more >>

I'm stumped, Not sure if this is the right place, sorry if it isn't, but just point me to the right spot if not. My problem, another developer has stored some text, html, images, etc into a table field that is a blob. The problem is, in an asp.net page, I need to get the HTMl back out o...more >>

I need to push some data from SQL Server to AD - phone number, office, etc. I can get the data into SQL Server using a SELECT and the ADSI setup documented on the MS KB, but updates on that same object fail with a "provider can't handle that type of transaction" error. Any ideas on how to b...more >>

I have a date that I need to get into a mmddyyyy format. There is no standard format to do this and I need to do this in Sql as there is no client program here. SELECT DateBeg,Convert(varChar(2),DatePart(mm,DateBeg)) + Convert(varChar(2),DatePart(dd,DateBeg)) + Convert(varChar(4),DatePar...more >>

Any help would be appreciated. What's wrong with the following syntax? CREATE TRIGGER tr_CMR_Client_Status_Confirm ON [CMR Client Numbers] FOR INSERT, UPDATE AS IF UPDATE [Current Status] CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR Client Numb...more >>

Is there any difference between defining a primary key this way: personID int not null PRIMARY KEY CLUSTERED(personID) and this way: CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID) ?? ...more >>

A little background first. I import a csv file into SQL Server into a single table in a junk database. I then stage data for import into a Sql database. The file has an (prospect)id, flag field and a comments field. The refer column is used as a flag to notify the sales team of a hot prosp...more >>

Simple question ... From within an MS 2000 SQL database stored procedure, I need to execute a DOS command that runs an application AND pass a parameter from the stroed procedure to this command line application. I also need to receive back a parameter (result) from the DOS application into...more >>

Below is my query so far. There is a field in Winpayment call transaction_amount that I would like to add up for use count statement if possible. So what I am asking is there a way to for each count case add the transaction amount and have a transaction total show for each count case state...more >>

Not to keep beating a dead horse, oh why not. DDL: DDL A: Survey( InternalId int IDENTITY PRIMARY KEY, RespNo int, RespKey nvarchar(16), Qtr char(4), CntyCod smallint, ImportDate datetime not null default getDate()) SurveyAnswers( InternalId int FK, QuestionId int FK to Q...more >>

Hi group. I have this query: SELECT FTDLN.NID FROM FTDLN INNER JOIN FTDOC ON FTDLN.NTIPO = FTDOC.NTIPO AND FTDLN.NNUMDOC = FTDOC.NNUMDOC AND FTDLN.CSERIE = FTDOC.CSERIE This query in a particular database dont'n work, i.e., 0 rows return. But this query: SELECT FTDLN.NID FROM FTD...more >>

Hi, When I execute a query in OSQL using Query Analyser the output is not well formatted. Sample Query is given below. Can someone suggest me a way to get good readable format in QA. Thanks in advance. -Kumar. ---***--- SELECT TOP 3 * FROM pubs..authors ---Using Command Prompt: --osql -...more >>

*All other things being equal*... just wondering if there is any performance difference [really, if SQL Server has to do less work] between: SELECT * and SELECT col1, col2, col3.... I'm wondering because I have a SELECT statement in a sp that returns all but one of the columns from a sin...more >>

how can I import memo field from Foxpro table?? Many Thanks in Advance!!...more >>

is there a way to add a remote server name to sysservers in the master DB. i am not able to use linked server. ...more >>

We've just created the "sp_send_cdosysmail" stored procedure on our sql server box. When running the test call from Query Analyzer to see if it works, we get back the message: "-2147220978". We've searched and searched, and we can't find this error listed anywhere. Does anyone know what this ...more >>

I have a a Case statement which sometimes fails when converting a Varchar column which contains numeric values to Money. I can understand why it fails when "1.05E+07" is passed in, but other values appear to fail also. I have not located the other offending values yet (500,000 rows to sift ...more >>

We have a sqlserver 2000 database with stored procedures, I am trying to call the stored procedure from my desktop using java. The stored procedure will execute some selects to get some values it needs before doing an insert. The insert fails because a field cannot be null. The tables for the ...more >>

Hi, I need to represent a data structure in sql server which I think is called a "heterogeneous tree", and I don't know how to efficiently do this in sql. I have a Container table : ContainerName C1 C2 C3 and each Container can have any amount of compartments in an ContainerCompart...more >>

Dear All, Please can you tell me why the following query takes 4-5 mins: SELECT * FROM db1.dbo.table1 WHERE column1 IN (SELECT column1 FROM db2.dbo.table1 WHERE column2 = 'x') The IN () query SELECT column1 FROM db2.dbo.table1 WHERE column2 = 'x' gives: field a1 a2 a3 a4 Whereas th...more >>

Hi I'm tring to write a statement to analyse what orders were open on the first day of each month from a system and return one data set with the months listed and all the orders open during that month. eg. Mon Order_No Jan 001 Jan 002 Jan 003 Feb 002 Feb 003 Feb 004 ...more >>

Is there function in SQL to return just the filename. For eg. if the filename is 'C:\test\job.txt' , i want to extract job from the filename. Is is possible? Thanks in advance!...more >>

Yes!! It works. But just a point. In the [Index] column I have both strings and numbers. When I query [Index]=AA there is an error: Invalid column name 'AA' Any more suggestion? Thank you Helen "John Bell" wrote: > Hi > > Maybe > > CREATE TABLE foo ( [index] int not null identi...more >>

I have a big (4 milj rows) aggregation table based on month, prod, customer, country etc columns with month Qty totals, always queried by month with really good performace ! I also have a small correction transaction table (< 5000 rows) that is not aggregated. I want to query the union of thos...more >>

Hai, Who can help me ?? I'm making a query from a table. In that table there is a column with the name NR_ The rows of that column give a result as ~2000252 ~2003 ~26578 What i want as result, the most right character from the ~character and as result in the same column 200252 2003 26...more >>

I have a table with 3 fields. In the first field named a there are values i.e. 5 In the second field named b there are values i.e. 9 In the third field named c there are expressions i.e. a+@q+3*b where a,b supposed to be the contents of the previous fields, different in each row and @q is a v...more >>

Is it possible in an SQL Update Trigger to get values that where for the record before the update statement was run and then insert these pre-update values and new values in to a different table? ...more >>

can i do something like this: SELECT BidID FROM (EXEC BidAccessRetrieve @BidID = 30, @LevelID = 6) where BidAccessRetrieve is an SP which returns a resultset? I just want to reuse the said SP to obtain the list of BidIDs which I will use in an IN clause of another SP, I hope I am making sen...more >>

Hi, I have a SQL Job step that uses xp_cmdshell to run a 3rd party application which in turn executes jobs on a remote server. To achieve this, I have had to set the job as a TSQL Job type(i need to validate a parameter before executing remaining SQL). Due to this the only way i can get th...more >>

Hi I need to run a masive script on multiple databases (about 20) I only want to execute the script once to then loop and run on all databases one by one. Is there a clean way of doing so? Thanks...more >>

Here are the server's specs: HP Proliant DL580G2 4 x 3.0ghz Processors 3.6gb RAM 2 x 146gb Local Hard Disks 4 x 250gb 2gbps FiberChannel SAN Disks 2 x Power Supplies 2 x 1000mbs Network Interfaces Here's the SQL: BEGIN TRAN CREATE TABLE #tmpClmIds ( EncKeyC INT NOT NULL PRIMAR...more >>

I am using VS 2005 and trying to execute a stored procedure in SQL 2005. This SP takes a parameter @var1 of type varchar(8000). When I debug the SP via VS 2005 debugger and step through the SP code, the value of @var1 is truncated containing only the first 255 characters. I tried replacing the s...more >>

Hi, I would like to know why SQL Profiler return the output shown below and is it possible to tune and avoid this problem? declare @P1 int set @P1=7 exec sp_prepexec @P1 output, N'@P1 varchar(255)', N'SELECT Tbl1019."Name" Col1075,Tbl1019."PhoneNumber" Col1080,Tbl1019."Pager" Col1081,T...more >>

When I need to have more info about a process, I run this kind of query: SELECT PROGRAM_NAME FROM master..sysprocesses WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %' The result might be something like this: SQLAgent - TSQL JobStep (Job 0x9BD0927CE9086241B582AAAAD7D3B86D : Step 1...more >>