Hi, I am interested in knowing how we can query the Active Directories. Is it possible to query the sql server to find the user in the domain. For example i am having domain,user and password can i validate that user from SQL Server. Is it possible to do. Can anyone provide me the ...more >>

I have written the following T-SQL code in a stored procedure to send email using SMTP. The e-mail is going but the attachment is not going. If I try the same in VB, the attachment is also going. SET @strAttach = 'd:\test.txt' EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT EXEC @hr ...more >>

sql2k sp3 Is there an alternative to the REPLACE function that can be use for ntext data type? Trying to do lots of UPDATES. TIA, ChrisR ...more >>

Given two fields from a table, SELECT field1, field2 FROM table1 is it possible (I am sure it is) to do something like SELECT field1, field2,MAX(field1,field2) FROM table1 as a 3rd column? So effectively it is computing the 3rd column from the other 2 Thanks Stephen Howe ...more >>

can anyone point me in the right direction of how to loop through sysdatabases, take each dbname and the use thatname.sysusers to display all my databases and which users they have. I'm pretty new to cursors and am having difficulty with looping through the fist cursor, of dbnames. Any h...more >>

Hey All, I am new to DTS so please bear with me. I have a DTS that generates a txt file output. All the queries, export and that jazz work fine. What I need now is to encrypt that exported file with a public key. Right now I let the DTS generate the file then manually encrypt the file. A...more >>

am executing a sql stored procedure in which i have written a cursor to loop and insert from a table into another table. when i execute it it says 'Internal SQL server error.'. pls see the code below that i am exexuting. I am also using linked servers. it gives the error at line 'inser...more >>

How I can get only the numeric part of a string? Example: ZUA123456789, I wan only 123456789 Thanks ...more >>

Hi all, I would like to create a SP that would perform the following Table1: Field1 varchar(10): Contains "Dave" Field2 varchar(10): Contains "Dave2" Field3 varchar(10): Contains "Dave3" Now I want to call a SP where I would pass "Field1", "Field2" or "Field3". It should then return a...more >>

I have the strangest situation with a UDF and data conversions of non-displayable characters. Take this situation: select @return_val = dbo.myudf(parameters) -- @return_val is varchar(32) Myudf ends up with this: .... return @result -- @result also declared as varchar(32) en...more >>

for example, this table has one column COLUMN1 only. SELECT COLUMN1 from TABLE1 result: COLUMN1 ------------ DATA1 DATA2 DATA3 ...... I want to convert it to a list so the result will be one row. ------------------------- DATA1, DATA2, DATA3 , ...... is that pretty ...more >>

Friends, Hi ... I am trying to learn Transact SQL for performing all of the tasks that I can otherwise do through Enterprise Manager, including DTS Services. Am I missing something within SQL Server itself - is there a feaure that will allow me to see the code that SQL Server generates...more >>

SELECT * FROM salesview1 WHERE (Store = '::location::') AND sold_dt >= ('::mt::/01/::yr::') AND sold_dt <= ('::mt::/30/::yr::') ORDER BY sold_dt ASC As you can see, the month day and year. What happens is errors when the month changes to a month that only has 29 days :( I was hop...more >>

I know the next release of SQL Server allows .Net stored procedures, but I urgently need to be able to call a COM object from an existing 2000 stored procedure. I really want to create the COM object using .Net. Does anyone have a good reference for explaining both what is required to crea...more >>

I am trying to create a query that pulls out the following order information: orderid, total value of order, and a name of the product with the greatest value on that order. I can easily get most of the data, but i can't seem to get the product name. Below is a sample that works with the N...more >>

I used MSSQL 7 and I was able to distribute encrypted SQL code to our customers. Is there any way to do it in MSSQL 2000? Thanks for your advice Martin ...more >>

Here's a query to run against Northwind. select employeeid, customerid, orderdate from orders o1 where orderdate = (select max(orderdate) from orders o2 where o1.employeeid = o2.employeeid) order by employeeid This returns ten rows, with employeeid "2" twice. This is because...more >>

Dear Sirs, When I select data from a view reading from a database on a linked server using server.database.dbo.table, the process takes longer time than selecting the same table locally, how may I speed up the retrieval process. Regards, Yahya ...more >>

what is the best way to insert one day of data rows (1500,000) from one table to another using TSql with less transaction log . the table has 100 columns and one day data is 1500,000 indexed on datetime Any sample code will be help full Thanks, yalaman...more >>

For some reason I cannot get the subject to print on my email. All other arguments work fine. I have tried: @subject = 'Subject Name' @subject = @SubjString (tried both VARCHAR and nVARCHAR) What am I not doing?? Thanks in advance GH...more >>

hi I have 2 identical dbs with almost the same numbers of records.. they are both on the same drive ..one is a restore of the other's backup. I have a view that give differnt query plan on each db..one finishes in 2 Min.. the other 2 hours. can anybody explain that?? thanks ...more >>

Hi, I need to store resumes in a database. What datatype should I use? 900 words max. Thanks...more >>

I have the below code which inserts new rows into a table. This code errors out on the VALUES clause because it will not accept the @TEST parameter that is being passed to it. @TEST is not the primary key of the table. The table's primary key is an autonumber field that is not being touched...more >>

I need to write a query that would make the following: (CertExpiry View) Org Cert Date Active Loaded Microsoft 2 2006-09-28 true true Microsoft 1 2004-12-31 false true Cisco 2 2006-09-28 true true ...more >>

I suspect that this is not possible, but if you have any suggestions,... I have two tables forming a hierarchy. 1) At the leaf level we have the Drone table, with schema: D_ID Int D_Name Char Mgr_ID Int 2) Each Drone entry points to an entry in the Manager table, with schema: Mgr_I...more >>

Hi All, I have a quick question, I have a field that is a varchar(5000), but I only want to select the first 2500 characters from this field, and then I want to limit this further to only select all the characters up to the very last full stop. That is the first bit, once this is done I need ...more >>

CINT is vbScript Is there a CINT equivalent in SQL Transact? I have a varchar field for account numbers, but want it to sort as though it were a number. thanks ...more >>

Hi All, I have a quick question, I have a field that is a varchar(5000), but I only want to select the first 2500 characters from this field, and then I want to limit this further to only select all the characters up to the very last full stop. That is the first bit, once this is done I ne...more >>

Hi All, I have a quick question, I have a field that is a varchar(5000), but I only want to select the first 2500 characters from this field, and then I want to limit this further to only select all the characters up to the very last full stop. That is the first bit, once this is done I ne...more >>

Can I compare 2 values in a query before I sum? Here is my query: SELECT Count(Results.StopType) AS ProCount, Sum(Results.HookCount) AS HookCount, Sum(Results.DropCount) AS DropCount, Sum(Results.Cubeweight) AS CubeWeight, Sum(Results.WEIGHT) AS Weight, Max(Results.StopType) AS MaxOfStopTyp...more >>

Hi All, I have a quick question, I have a field that is a varchar(5000), but I only want to select the first 2500 characters from this field, and then I want to limit this further to only select all the characters up to the very last full stop. That is the first bit, once this is done I ne...more >>

Hi, i'm trying to insert a text file that was created by a linux system. The issue is the the carriage return at the end of the line. SQL does not seem to recognize the end of line character. I have to put the file into Excel and save it as a CSV or TXT file. then it works, but not something...more >>

Hi... I would like UPDATE or DELETE per partes. Like per 10% of total numer of records. Or per 1000 rows. I have a very large table and log could be blocked. Is there any idea ? ...more >>

I need to update a "total" record (RecordType = Z) with the last balance (highest Period value) for a number of fields from the same table, but grouped by two other fields, EntityId and ScenarioId. The ending period is an INT datatype (200411) and this max period varies for the different entiti...more >>

The code: Declare Startcalltime cursor for select Cast(Convert(Char(10),entdte,101)+' '+ Convert(Char(10),enttme,108)as datetime) from callscore for update Declare @Tcallentered datetime open Startcalltime while @@fetch_status =0 begin fetch Startcalltime into @tcallentered updat...more >>

I have a table which contains the status of a person. The table contains the ID of the person, and the date their status changed. Eg: ID Status Date 1 Blue 2001-01-01 1 Bronze 2001-03-01 1 Gold 2001-05-01 Is there a way to display the missing months between the status dates...more >>

Hi Freinds, How can grand access for a user to a readnly DB? Thanks in advance, Pat ...more >>

I have a file with about 6,100 picture I need to add to a database, because I cannot figure out how to get SQL Reporting Services to Hard Code an image's source to the file containing the pictures, ie: C:\Folder\2004 Pictures\6.100 from Oct Load\*.jpeg Can anyone tell me either how to get t...more >>

hi guys: I want select one space row from anyone table. ...more >>

I have a table with TaxRate column FLOAT type. It contains data like 1.0865 First question: Is this type correct for this kind of data. The second question: Although in the table I see 1.0865, Select statement returns 1.0869999999999. Why? I'm using SQL Server 2000 Thank you Al ...more >>

Table "Station": stationID supervisorID Now I need to generate a view vw_stationWeek that contain stationID supervisorID MyDate where MyDate is all dates between currentDate and current Date - 6. In short, stationid & supervisorID coulumns will repeat 7 times with different date for ...more >>

I am joining a main table with sub tables like so: select distinct #dataset.company_name, #dataset.partner_type, #dataset.region, #ccsp.CCSP, #cca.CCA, #ccea.CCEA, #ccia.CCIA into #comply from #dataset left join #ccsp on #dataset.company_name = #ccsp.company_name left join #cca on #datas...more >>

Thanks in advance for any leads... This is a "nuts & bolts" question, rather than a general concept question. I've found ('groups, BOL, Microsoft's KB, conversations) that SQL Server has a datatype (image) that can hold Word documents (or PDFs, or ...). I'm looking for a way to add zero-to-...more >>

in my store procedure, select 'INV' as docid, companycode, ..... from myInvoiceTable. I use SQL anayzler to run it , however, i only get the result companycode..... etc without docid, How can i get the docid column in my result ?? [of course, docid is not the datafield in the table] Thanks ...more >>

hi, I am working on sql 2000 server. Jobs kept randomly failing on one of my servers with the following error, and the next runs will be successful randomly, any thought on this? 'SqlDumpExceptionHandler: Process 72 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Se...more >>

Hi I'm using SQL Server 2000, Can anybody introduce me some related tools or softwares to manage clients connected to the server and control the current activities,and send some messages in suitable time ,or some tools to make more security for critical informatin or ...? Thanks very much. ...more >>

Hi I'm using SQL Server2000, how can I see the activities happend before in my database (for example what information inserted or deleted yesterday to or from Employee table)? I know I can set the parameter to save the result of SQL Profiler to a special table and then I can view what happe...more >>

My problem is to screen out duplicates in an efficient manner. Say I have 3 columns in a table: Junior, Midlevel and Senior referring to the names of people who worked on the job in three separate capacities. However, there may be cross over. Joe, being senior, may sometimes act as a junior an...more >>

Hi, I wrote a procedure which have 2 update statement, Is this the best practice? I would like both table been updated together in one transaction. Thanks CREATE PROCEDURE updateOrderStatus @OrderIds as varchar(4500) AS Declare @sql varchar(5000) SET @sql = 'update order set fla...more >>

I need a statement that will give me the indexes on specific tables. Example I need the indexes of this table called Teller in Query analyzer...more >>

Our Company wants to have a seperate SQL server for reporting only. It should be real time data from production server. I am proposing Transactional Replication. Do you agree? Thanks, Pat ...more >>

Hi I have strings of rows such as below Quazi, Yaheya Kerry, John .... What I want to do is grab the last name - which is the string followed by a comma. How can I do that?...more >>

Hi, I'm not sure if I'm in the the right area for this question. sorry. Can SQL server / Access handle attaching word and pdf files. I also want to search the database and the attached files for keywords. any help is welcome! Thanks Paddy...more >>

Hi all, Below is the result of sp_spaceused on one of my tables. name: Coverage rows: 976560 reserved: 1041680 KB data: 520840 KB index_size: 16 KB unused: 520824 KB Why is the table consuming so much "unused" space? It is the same amount as the "data". Is there a way to...more >>

I'm reviewing SQL code written by another programmer. I have a table with a datetime field that is in an index. Here is a SQL statement: Select * from table where (datetimefield + 2) between @StartDate and @EndDate; I think this is problemmatic, but I'm not sure. My gut feeling is th...more >>

Hi, I want to run an .exe or console application from sql server 2000. Can i execute an executable from sql server, if so how can we do that. Please help me. ...more >>

I want to add/remove email addresses from an active directory distribution list from a sql 2000 stored procedure. Is this possible and how? thanks....more >>

I am trying to write my first UDF to return a table. I have a working Stored Procedure that I need to convert. Here is the stored procedure: CREATE PROCEDURE dbo.PROC_getLog AS SET NOCOUNT ON DECLARE @tableName VARCHAR(100) DECLARE @SELECT VARCHAR(1000) SET @SELECT = '' DECLARE @m...more >>

I've written a function that rounds a decimal value up or down & returns an integer, based on the fractional value of the passed parameter & the variable @RoundUpLevel. Here is the code to the function, this is exactly what I want it to do....just seems a bit inefficient....any ways to opt...more >>

Is there a command to list all the tables and their associated filegroups? Thanks in advance...more >>

How do I change or alter a user defined data type? Thanks in advance...more >>

If I forgot/lost the sa password. What should I do to get it? Thanks in advance...more >>

How do you restore single table from backup in SQL Server 7.0/2000? In SQL Server 6.5? Thanks in advance...more >>

Hi, Can anyone help me to use RaiseError with @@ERROR as the first parameter. Code in my stored procedure is, RAISERROR (@@ERROR,15,-1, @err_msg) WITH SETERROR On SQL Server 2000 i am getting the compilation error saying "Incorrect syntax near '@@ERROR'" though this works on Sql serve...more >>

Hi I'm going to upgrade a SQL Server 2000 database table with a new column. But how do I check if the column already exist? Here is my script checking if the table exist but I don't know to check if the column already exist: if exists (select * from dbo.sysobjects where id = object_id(N'[E...more >>

Hello Experts, I want to view the dates that result by adding days, I have as start date '1/1/2004'. I give you a very starting point of my code: Select cast('1/1/2004' as smalldatetime) + [day] 'date' From (Select 10 [day] union select 12 union select 15 union select 6) days The r...more >>

How do you find out all the IDENTITY columns of all the tables in a given database? Thanks in advance...more >>

Is there a way to find out when a stored procedure was last updated? Thanks in advance...more >>

Will my MCDBA certification still be valid when everything change to SQL 2005 or will the be additional subjects that you could take for an upgrade ? What's the future of MCDBA will there be additional modules for sql 2005, will I have to do a new certificate (there's alot of changes in sql2005...more >>

I have written the following T-SQL code in a stored procedure to send email using SMTP. The e-mail is going but the attachment is not going. If I try the same in VB, the attachment is also going. Please help me in this. SET @strAttach = 'd:\test.txt' EXEC @hr = sp_OACreate 'CDONTS.NewMa...more >>

Hi Tested on Microsoft SQL Server 2000 - 8.00.194 I create a table with different field types. The table is only accessed from a stored proedure. When I created the table the first time I did not care in which order I added the different fields in the table. Trying to save data to the ta...more >>

How do you persist objects, permissions in tempdb? Thanks in advance....more >>

Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or How do you generate output with row number in SQL Server? Thanks in advance...more >>