SQLServerCentral / SQL Server 2005 / SQL Server 2005 General Discussion

General Question

Raghavender — Fri, 20 Nov 2009 04:42:09 GMT

Hi All,Please answer below questions:Which companies uses SQL server dedicatedly ?Which applications uses SQL Server dedicatedly, like SAP uses oracle, which one uses SQL Server ?Thanks and Regards!!

Trace file names

Juanita — Fri, 20 Nov 2009 14:42:20 GMT

HI,I know in sql 2005 the default trace file names are in the form of log_##.trc. I am seeing some trace files in the same directory as the default trace file with different names such as: 291-45486dm5wptrace_216.trc Can someone tell me how this kind of trace file gets created and where from. I'm the only dba that would be doing any manual traces. I tried opening profiler and running a simple trace and stopped it. Went to the directory of the instance of sql server I ran the trace against and no new trace file. So I'm confused how these strange trace files are getting created.Juanitas

Capturing query texts used by an application

EMoscosoCam-602582 — Fri, 20 Nov 2009 04:44:03 GMT

HelloI have an application that uses embedded sql and I don't have the source code. This application runs queries against a certain SQL2005 DB which I have access in order to display reports. I would like to somehow capture the sql text for each of the reports so that I can learn how the query is structured. How can I do that?Thanks a lot.

Reporting on blocking transactions

kellyrmartin-805750 — Fri, 20 Nov 2009 15:09:09 GMT

Is there a way to receieve an email when a transaction stays blocked for more than say 15seconds? Also, is there a way to generate a report to look at this information historically?

Trouble with a GROUP BY

mikesigsworth — Fri, 20 Nov 2009 13:57:01 GMT

EDIT: Nevermind. I figured it out. [url]http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql[/url]Given the following data:X | Y | Z1 | 1 | 152 | 1 | 123 | 1 | 94 | 2 | 35 | 2 | 226 | 2 | 147 | 3 | 998 | 3 | 349 | 3 | 66How can I get X for each group Y where Z = MIN(Z)My results for the example data would be 3, 4, 8My problem seems to be when I group by Y and use the aggregate MIN(Z) I can't select X unless I add it to the group by clause, which ruins the grouping, or use it in an aggregate, which isn't right either.There must be a graceful way to do this.Thanks for your help.

Pulling first word from a text field with SSRS query

iklektic — Fri, 20 Nov 2009 11:22:52 GMT

Hi Everyone, I'm trying to figure out if there is a way to have SSRS pull the first word from a text box. Currently the text box looks something like this: "2/2/2009 Adjustment made..."I'm hoping to be able to extract the date from this text... I tried the CONVERT(VARCHAR(8)... function but that doesn't work since the user will enter the date in whatever fashion they please (eg 02/02/2009 etc.). Any ideas out there on how to achieve this? Thank you in advance.

Using the sp_Send_DBMail SP in msdb Within A Best Practices approach

YSLGuru — Thu, 19 Nov 2009 10:54:40 GMT

[i]NOTE: Because this falls under several Forums sections from Development to Security to Strategy I decided to drop it under the GENERAL section.[/i][i]NOTE 2: I hope I’m not beating a dead horse with this one b/c I know there are numerous posts on the internet on this with a variety of replies but when I search the SQL Server Central Forums I get zero hits so apologies if the answer is already listed here somewhere.[/i]What if any is the Best Practices approach to using the sp_send_dbmail STORED PROC in the MSDB database on SQL Server (2005/2008)?? Currently I have a custom SP that among a few other tasks will call this MSDB Stored Procedure to send an email alert that is raised by a Table Update Trigger. I am of course getting the same PERMISSION DENIED error msg that every other SQL Server users get’s when they have not explicitly done some kind of change to security for this SP so that it can be called. The error of course comes from the fact that you have to be of a certain Role or have specific permissions for this SP in the MSDB database. That makes sense in that it follows the whole security paradigm in SQL Server however this particular SP is in my opinion not the same as any other because of what it does. Now I’ve seen various suggestions (see below List) but what I’d like to know is if any of you have any suggestions to add or thoughts comments on the ones below as well as on any that others add, specifically commenting on what you feel is a BEST PRACTICES approach to this common problem.Thanks to all[u][b]Options for dealing with the Permission Denied error received when using the MSDB Stored procedure sp_Send_DBMail.[/b][/u]1) [b] SQL Email Job [/b]– Create a table to store Email msgs/alerts and have a job check the table for new items and when it finds any, use the job to send these out thru the sp_Send_DBMail SP. By setting up the job and giving the Job owner permissions to the Stored Procedure in the MSDB database you can allow for sending emails via SQL Server.[b]PROS: [/b]It works. While not easy it is also not too terribly hard to implement. Minimizes potential Security issues[b]CONS:[/b] Unless the job runs all the time it’s not live or real time. 2) [b]Run with the EXECUTE AS option[/b] - Setup the SP to run under a security context that has access to execute the SP [b]PROS: [/b]Easy to implement[b]CONS: [/b]May not always work (at least that’s what I read in various posts); Possible Security concerns raised3) [b]Grant Security Access Users[/b] - Explicitly grant EXECUTE priveledges to the sp_Send_DBMail SP to all users/roles that will call the SP[b]PROS:[/b] If you use Roles then so long as any new users are added to the role you need only set this up once for it to run for existing and new users alike.[b]CONS:[/b] Not easy to implement unless you have a few users; Raises serious security concerns depending on your environment.

Create SQL job in SQL SERVER 2005

Tushar Bedekar — Fri, 20 Nov 2009 01:34:02 GMT

I am new to SQL Server 2005 and I want to schedule one .exe file with scheduled job of SQL Server. Can anybody help me out in this.Thanks in advance.....

New Code not running

gregory.anderson — Thu, 19 Nov 2009 09:25:26 GMT

I have updated a procedure to change from MAXDOP 0 to MAXDOP 2. It has been in production for over 1 week now and according to my sql server monitoring software, it is still running the old version that has MAXDOP 0.I have tried sp_recompile, exec [procname] with recompile, and our dba has tried freeproccache.What else can we do without recycling the sql server in order for these changes to take affect?Please help! Production running very poorly right now!

Is there a T-SQL command that will report drive fullness without having to have access to xp_cmdshell or the server console?

shew — Fri, 20 Nov 2009 07:04:21 GMT

We use SQL Server 2005 in a locked down shop, and xp_cmdshell is turned off by design. The DBAs do not have server console access, which means that we currently have to chase down a server administrator when we want to check to see how full a drive is. Unfortunately, the server monitoring of drive space fullness does not work effectively. :(Is there a T-SQL command that will report drive fullness without having to have access to xp_cmdshell or the server console?Or, maybe there is some SQL Server Management Studio feature that I am missing. (I am relatively new to SQL 2005, but I worked with prior versions of SQL Server extensively at a former shop.)

Replication Monitor

Alberto.Omini — Fri, 20 Nov 2009 04:03:33 GMT

Hi people.I have this problem.During my test i create some pubblication in my sql server.Now, some of this are working, and someother are deleted.My problem is...When i open replication monitor, i see in my list one deleted pubblication.Someone know how can i delete from this list this item ???i try using sp_dropmergesubscription and sql answer me "The subscription does not exist".thank you in advance.byeAlberto.

script copying tables From to server on other machine.

mazenob — Fri, 20 Nov 2009 02:25:48 GMT

Is it possible to script operation of copying table from a database on a Sql Server to other Sql Server in other machine ?thanks

SQL Server 2005 SP2 Developer Vista-Win7 upgrade

Dom Horton — Fri, 20 Nov 2009 07:05:31 GMT

Hello,I'm currently running SQL Server 2005 SP2 Developer x64 on a Windows Vista Ultimate x64 machine and I'm looking at upgrading the OS to Windows 7 Ultimate.Has anyone successfully attempted this yet and can offer advice and things I should be aware of?Thanks,Dom

sp_send_dbmail question

William Plourde — Thu, 19 Nov 2009 12:17:53 GMT

I have a question regarding sp_send_dbmail. I've just used it for the first time and everything works well but after putting my solution into production I realized that one of the emails created was sent to an invalid address due to a configuration error.I have created a view to join my email tracking tables to msdb.dbo.sysmail_mailitems, and I am using the sent_status column to determine the mail status. I have translated the codes as 0=unsent, 1=sent, 3-retrying. I have determined these code values by reading some of the system views so they seemed correct at the time.My problem is that when an email was sent to an invalid email address the code value of 1/sent is returned, even though it never arrived at the destination. Does anybody know of a way to determine if there are delivery errors for sql generated emails ?Thanks,Bill, Charlotte NC

Streams VS Sparse File

Subhash-63067 — Thu, 19 Nov 2009 03:28:38 GMT

Can somebody help me to understand, [b]is there any difference between "SPARSE FILE" and "Streams", are the same?[/b]"SQL Server 2005 uses sparse files for snapshot database files and online DBCC operations against files stored on NTFS volumes.""SQL Server 2005 online DBCC checks are based on the database snapshot technology by using transient, secondary file streams to store point-in-time images of data pages when the DBCC is in progress"

How to hide owner name in object explorer ?

vcapone — Wed, 18 Nov 2009 10:03:33 GMT

SQL 2005 Management studio, Object Explorer tree is showing owner name in front of the objects.Is there a way to hide the owner name?Thank you !

Reading HTML file stored as VARBINARY(MAX)

sabyasm — Thu, 09 Apr 2009 10:15:19 GMT

Hi,We have a table that stores html email templates in varbinary(max) column. We are inserting the html file as follows:INSERT INTO .. SELECT emailtemplate.BulkColumn,.. FROM OPENROWSET(BULK 'C:\Email\generic_email_template.txt', Single_Blob) AS emailtemplate, ...Is there anyway - we can 'see' the content once it's loaded through executing a query/stored procedure?Thanks in advance.Sabya

Help merging data from 4 tables into one query

tpwalker1980 — Thu, 19 Nov 2009 10:59:44 GMT

Hoping someone here can help me out with a problem I am having.I am trying to create a query that merges data from 4 tables.Table 1 Contains the CompanyID, and other Identifying information. This table is used as the primary for grabbing the companies to display.Table 2 Contains all original Repair Ticket Data this is linked by the CompanyIDTable 3 Contains Modified Repair Ticket Data by Reporting Group A which can be compared to Table 2 or 4 by the Ticket NumberTable 4 Contains Modified Repair Ticket Data by Reporting Group B which can be compared to Table 2 or 3 by the Ticket Number.The way I envision the query working is like this......Query Table 1 and get list of CompIDs that are marked as active.Get all Tickets from Table 2 that match a CompID from Table 1 and have been closed in month X.Query Table 3 and overwrite HEALTH_TOTAL in the above results if TicketID = TIcketIDQuery Table 4 and overwrite HEALTH_TOTAL in above results if TicketID = TicketIDGet Sum of HEALTH_TOTAL grouped by CompIDNow getting the HEALTH_TOTALS from just Table 2 was easy enough.[code="sql"]SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC FROM TRT_parent_accounts ALEFT JOIN (SELECT CompID, SUM(HEALTH_TOTAL) As HEALTH_TOTAL, SUM(HEALTH_MTTR) AS HEALTH_MTTR, SUM(HEALTH_OTR) AS HEALTH_OTR, SUM(HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(HEALTH_CHRONIC) AS HEALTH_CHRONIC FROM TRT_remedy_tickets WHERE (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-10' GROUP BY CompID) BON A.CompID = B.CompIDWHERE A.Status = 'active' AND (B.HEALTH_TOTAL > -1 OR B.HEALTH_TOTAL IS NULL)[/code]I was even able to return only Table 2 results that were not found in either Table 3 or Table 4.[code="sql"]SELECT A.CompID, A.Company, A.Region, B.HEALTH_TOTAL, B.HEALTH_MTTR, B.HEALTH_OTR, B.HEALTH_REPEAT, B.HEALTH_CHRONIC FROM TRT_parent_accounts ALEFT JOIN (SELECT CompID, SUM(HEALTH_TOTAL) As HEALTH_TOTAL, SUM(HEALTH_MTTR) AS HEALTH_MTTR, SUM(HEALTH_OTR) AS HEALTH_OTR, SUM(HEALTH_REPEAT) AS HEALTH_REPEAT, SUM(HEALTH_CHRONIC) AS HEALTH_CHRONIC FROM TRT_remedy_tickets WHERE (CAST(CAST(YEAR([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(4)) + '-' + Right('00' + CAST(MONTH([DATE_RESOLVED_FOR_CLOSURE]) AS VARCHAR(2)), 2) AS VARCHAR(7))) = '2009-10' AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_hip_reviews C WHERE C.MAIN_TICKET_ID = TRT_remedy_tickets.MAIN_TICKET_ID) AND NOT EXISTS (SELECT MAIN_TICKET_ID FROM TRT_pm_reviews D WHERE D.MAIN_TICKET_ID = TRT_remedy_tickets.MAIN_TICKET_ID) GROUP BY CompID) BON A.CompID = B.CompIDWHERE A.Status = 'active' AND (B.HEALTH_TOTAL > -1 OR B.HEALTH_TOTAL IS NULL)[/code]I just don't know how to pull off what I first listed... Is there any Join that will use data from the join to overwrite data from the top query?

Importing .DBF and .FPT into SQL Server 2005

Adam.Massey — Mon, 16 Nov 2009 10:13:04 GMT

Hello all: Here is the situation:I was just assigned to this new project...12 centers will FTP their data in a Zipped Folder that has .DBF and .FPT files in it...They then want all the data consolidated into one database...However when I went to create the db, I needed to see how the file was structured so I would know how to set up my table and when I tried opening one of the .DBF files, the window popped up saying it didnt know what to open it with - so I tried Notepad, Wordpad, MS Word, MS Access, and SQL Server...It would open up but it was in such a format that I couldnt tell what it was so I dont know how to proceed...Can anyone help me? I tried googling and some the things I read said it could be opened in MS Access but when I tried to do it said that the External Table was not in format...I need to know how those files are set up so I can be able to get started with this project! Any help is appreciated...if I have not given sufficient info, just ask and I will go into more detail!Thanks

What kind or error am I looking for?!

FreeHansje — Thu, 19 Nov 2009 05:10:52 GMT

I am implementing SP2 and hotfix KB934459 on a SQL2005 instance in a cluster and receive the following error:"Update 'Hotfix 3159 for SQL Server Database Services 2005 (64-bit) ENU (KB934459)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB934459_sqlrun_sql.msp.log."When I look into the mentioened log I see an enormous amount of text and have no idea what to look for. When I search for 'error' I find 100s maybe 1000s of hits, which do not indicate a real error but are descriptive of nature, some standard text. When I find some meaningfull text on error, it is basically the reference to this logfile, that is: itself!When opening SSMS I can see the correct version number. When calling SELECT @@Version I receive the correct version as well. Everything seems to be working fine. Before I go i9nto extensive testing I would like to know what this error message is telling me, and I would like some pointers how to search an errorlog like this for clues on what is actually wrong.Any hints?

I can not query Active Directory using linked server

mtseu — Thu, 19 Nov 2009 09:18:56 GMT

I have run the following SQL statement to setup my linked server on my SQL Server:EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'I then try and run the following SQL statement:SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=myserver,DC=com'' WHERE objectCategory=''user'' ') - where myserver is I put in our domain name.and I get the following error:Msg 7321, Level 16, State 2, Line 2An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=myserver,DC=com' WHERE objectCategory='user' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". I have checked the providers and ADSDSOObject is there.I'm not sure if there is something else I should be looking for. I appreciate the help and I honestly tried to serach for a solution in here.Marc

Installing Additional Components After Installation

MattieMich7 — Thu, 19 Nov 2009 08:40:49 GMT

When I originally installed SQL Server 2005 Standard on my PC, I could not add the Reporting Services component because I had the Vista Home Premium OS (Vista Ultimate or higher is required to add the RS component). I have since upgraded to Windows 7 and would like to add the Reporting Services component without having to do a complete re-install of SQL Server 2005 Standard. Does anyone know if it is possible to go back and add individual components?Thanks!

replication in SQL 2005

murgocimark — Wed, 18 Nov 2009 04:58:06 GMT

HelloI need some suggestions with this situation: I have a c# client application and a MSSQL 2005 database.My application needs to run on 2 different locations (let's say location A and location B), each with its own SQL database.The ideea is that all the data changes in location B must be reflected as well in location A, ie database from location B contains its own data, but the database from location A must contain the data operated locally as well as the data operated in the other location.the tables from database have primary key id with autoincrement (1,1)My approach is:start database is called DBase-copy DBase to location A server and change increment 2 and seed the next available ID odd-copy DBase to location B server and change increment 2 and seed the next available ID even.... (that's to prevent conflict of primary keys)- set database from location B as publisher- set database from location A as subsriber- create a transactional replication that runs conntinouslyMy first question is that this approach is correct, than:- i know than in a replication, its recommanded than dailly to perform a snapshot replication also, for that the integrity and consistency of data- in case that is performed an insert in location B, and, for whateverreason this new addly record itsn't performed in location A, what is the appropriate action? i have to use replication monitor and see that? how can i be secure then the data from location B always get to location A?Thanks, and i wait for any suggestions...

Can anyone define a value of 65536 in master.dbo.sysdatabases.status?

shew — Wed, 18 Nov 2009 14:57:04 GMT

When I do the bit math to decode master.dbo.sysdatabases.status, I am seeing 65536 for some of our databases, [b]but not all[/b] of them. A google search lead me to believe that 65536 means "online." However, if that is the case, wouldn't all of the databases (which are all online) have that bit set? (They don't.)I have pasted the Books Online documentation below, but it does not define bit 65536. Can anyone point me to additional documentation?Thanks in advance.----------------------------------status int Status bits, some of which can be set by the user with ALTER DATABASE (read only, offline, single user, and so on): 1 = autoclose; set with ALTER DATABASE. 4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.16 = torn page detection, set with ALTER DATABASE.32 = loading.64 = pre recovery.128 = recovering.256 = not recovered.512 = offline; set with ALTER DATABASE.1024 = read only; set with ALTER DATABASE.2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.4096 = single user; set with ALTER DATABASE.32768 = emergency mode.4194304 = autoshrink , set with ALTER DATABASE.1073741824 = cleanly shutdown.Multiple bits can be on at the same time.

MSDASQL Driver

Kevin-130791 — Thu, 19 Nov 2009 06:47:31 GMT

What is wrong (if any) with this: Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Pathname.mdb;:w00t:

How to find the Rename filed

parthi-1705 — Wed, 18 Nov 2009 22:58:58 GMT

I am having XXXXX010109.Tran file it has been kept in C drive,some one have renamed it or deleted how to find whether it is deleted or renameHow to find the Rename filed?ThanksParthi

How to find the Rename filed

parthi-1705 — Wed, 18 Nov 2009 22:54:29 GMT

I am having XXXXX010109.Tran file it has been kept in C drive,some one have renamed it or deleted how to find whether it is deleted or renameHow to find the Rename filed?ThanksParthi

Help creating search query

santosh.shrestha — Wed, 18 Nov 2009 17:18:24 GMT

Hi,I have a small problem and it is related to searching a column with one or more stringsI have a table as follow-------------------------------------------------------------------------------CREATE TABLE Movies1(Mname VARCHAR(25),Descript TEXT)-------------------------------------------------------------------------------INSERT INTO Movies1 VALUES ('AAA','This movie is full of action')INSERT INTO Movies1 VALUES ('BBB','This movie is full of drama')INSERT INTO Movies1 VALUES ('CCC','Worst movie ever')INSERT INTO Movies1 VALUES ('DDD','Sci-Fi fan will love this film')INSERT INTO Movies1 VALUES ('EEE','Must watch')INSERT INTO Movies1 VALUES ('FFF','Don't watch this film')--------------------------------------------------------------------------------Now if I need to search the descript column using LIKE operator, the following stored procedure will work. But the problem is it will only take 1 search string, and if I need to search using 2 or more search string then I will have to make similar stored procedure with more variables.---------------------------------------------------------------------------------USE [MovieDatabase]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[SearchMovies]@Query VARCHAR(50)ASSELECT Mname, Descript FROM Movies1 WHERE Descript LIKE '%'+@Query+'%' ----------------------------------------------------------------------------------Since it is impossible to know how many search string will be there, so we can't make infinte number of stored procedure.So can any one help me create a single stored procedure which can take 1 to maximum (say infinite) number of variables to get the result.Since I am using this in ASP form to search the database, so it will be important to know how to pass the search strings to the given procedure too.So please can any one help.Thank you.

How to protect a table

renuukamutyala — Wed, 18 Nov 2009 20:32:20 GMT

I've a table in my database. Many people use that table for insertion and updation.How do I protect that table from getting dropped ?

SQL Mirroring - comparable OS

Eric-1016455 — Wed, 18 Nov 2009 08:07:08 GMT

According to Microsoft, for setting up SQL Server 2005 Mirroring they recommend "The partner servers should run on comparable operating systems that can handle identical workloads". They don't go into specifics, but would the prinicipal server (running on Windows SE 2008, 64bit) and the Mirror (Windows Server 2003, SE 32bit) be ok to setup mirroring? The DBs and Logs for both parties will be located on LUNS that have enough disk space. Both servers will have instances of SQL Server 2005 Standar Edition. Both servers will be able to handle the same workloads...I'm just not 100% sure if there will be an issues with the different operating systems. Thanks

How do I extract numbers from a text field?

ayfour — Thu, 12 Nov 2009 22:43:34 GMT

Hi AllI have a text field in a table that allows free form text. The users will often put in 8 digit account codes in amongst the text in that field. Each record may contain 0, 1, 2 or 3 account codes. There may also be other numeric codes that do not conform to the account code standard.I also know that all account codes start with a "6".Please, can anyone help me with some code to extract these account codes?Many thanksK

export a table into database to a excel file

faravani — Mon, 16 Nov 2009 04:46:22 GMT

Hi i want export a table into database to a excel filei search s command in Sql Server That help me Export a Table in Excel File without create file manullyplease guide me

SQL 2005 Cluster Windows 2008

pat-261672 — Wed, 18 Nov 2009 07:30:01 GMT

Hi all,I am confuse about the system here.I want to install SQL Cluster 2005 64 bits on Window 2008 server. Here are the questions:1) Should I create Windows 2008 fail over cluster2) Or I can use role based on Window 2008 which can create cluster on role based.Please suggest!! and clear my mind?ThanksPat

Conversion failed because the data value overflowed the data type used by the provider.

Alex Kirwan — Thu, 10 Aug 2006 05:13:00 GMT

Hi all,

This is a follow up to a previous post from aprox 2 years ago.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=145803

I have stored procedures set up to import tables from a PROGRESS DB to SQL 2005 via linked server.The import process works fine Id say 50% of the time. Usually works fine after business hours when all users are logged out of system. However I do get this error message and it fails about half the time I run the script. I noticed this problem was raised in the link above so any help adive would be greatly apreciated!I presumed it was some kind of dat type conversion issue??.... PROGRESS stores date as a datatype DATE - dd/mm/year I have the comolmn in my SQL set up as DATETIME. Could this be a problem?? I dont think this is the problem, as I said the script does work 50% of the time. Also if I import the date column alone then it seems to work no problem. My thinking is that it is to do with the data on the PROGRESS side is forever updating (as thats our LIVE DB) so when the data is copying over from PROGRESS to SQL it can fail due to an increase in data from PROGRESS?? - could that be possible reason to fail??Here is the message in full:OLE DB provider "MSDASQL" for linked server "PRO_LIVE_SQL" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 7341, Level 16, State 2, Procedure ImportButlersVAT_LIVE, Line 4Cannot get the current row value of column "[MSDASQL].fdate" from OLE DB provider "MSDASQL" for linked server "PRO_LIVE_SQL". Conversion failed because the data value overflowed the data type used by the provider. As always - Thanks in advance!

Sql server search query

sevdej — Wed, 18 Nov 2009 00:13:53 GMT

Hi friends,I am developer .I am making a project .I need phone brand model using useragent. I have a table for this. My Input is useragent like this. 'Mozilla/5.0 (SymbianOS/9.2; U; Series60/3.1 NokiaN76' my table columns are brand, model,useragent. I am using this arguments and return brand and model . How am I write a query ? Thank you.

Information requested on system views in 2K5

kim.cantrell — Tue, 17 Nov 2009 08:21:58 GMT

Greetings all. I am looking for information on the many system oriented views and or xp procs that may be available. Specifically for this question I am looking to determine how many rows are in the tables in my database.Thanks!!

What is the difference between database mirroring & log shipping

RKGoriparthi — Tue, 17 Nov 2009 01:36:14 GMT

Hi,Good dayWhat is the difference between database mirroring & log shipping?which one is more secured and preferable..Hvae a nice dayThanks & regardsRk Goriparthi

Profiler Trace Question - How to trace Start And End in 1 line

YSLGuru — Thu, 05 Nov 2009 15:41:41 GMT

Profiler version 2008 running against a 2005 SQL Server box.I'm not sure on how best to word this so apolagies if this seems verbose. Currently in Profiler whgen capturing amy SELECT/INSERT/UPDATE/DELETE command I get the sam T-SQL command at least 4 times. Stmnt:Starting/Stmnt:Completed and then the Batch equiivelants. I can narrow this down by dropping the Batch items and while I could further reduce this by keeping just the STmnt:Completed event, I like to include the Stmnt:Starting event because if there is some long running execution, I can see the actual T-SQL ebing executed where as with Stmnt:Completed you see the T-SQl code only after it's completion.My thinking is there shoudl be a way so that when tracing the STmnt event, you get just 1 line that shows as soon as the command starts and when the command is done, instead of getting another event line, some value in the event line you alrady have simply changes to indicate the status has changed. Make sense>So if I am tracing the activity and a long running SELECT starts I see that SELECT in my trace and it shows with a status of Stmn:Starting and then once it's done the status is chnaged to Stmnt:Completed and no additional copy or event for that same T-SQL is shown. It juist takes a long time dig thru what often feels like a lot of excess capturing.Make ssense? Anyone have any tiops on how to do this or at least to minimize the logging while still maintining the status of the event?Thanks

how to create nested schema?

tarunsoodrnd — Tue, 17 Nov 2009 02:16:20 GMT

I have created one schema (Schm1) into database and trying to run the script in order to create tables under schema (Schm1). but problem is that, script has another schema inside it and when i run that script its giving me error like:”User does not have permission to perform this action.Msg 2759, Level 16, State 0, Line 2CREATE SCHEMA failed due to previous errors.”E.gI have schema say Schm1 under abc database. now I want to add some table under Schm1Here is script:– Create schema for common tablescreate schema Schm2;– Create common tablescreate table Schm2.Table1(ID nvarchar(20) not null,TYPE nvarchar(50) not null,primary key(ID));Can we add Schm2 schema under Schm1 schema? If yes then please suggest me how we can do that? what Kind of permissions we need?Waiting for your reply!Thanks in advance

Simple Insert Stored Proc and Performance

ease2002a — Sat, 14 Nov 2009 21:54:43 GMT

Hi,I have a simple insert stored proc in sql 2005 (unfortunately I'm at home and can't generate the SQL but trust me the SP is simple). The insert has about 6 parameters that are either varchar (no more than 40), integer, or numeric(18,4 or 18,9). The SP is literally only Create SPName (Parameters) as Begin Insert Into TableName (6 fields), (6 Values) End. Select No Count is referenced. The table has no more than 5000 records and no indexes (staging table). So a very basic Insert SP. The db is on a lab server, and I am doing my development work on it for now, so no one is hitting the db or the table with "requests".The SP is called via vb ADO and the front end app can only call the SP 252 times during a single "button push" to insert 252 records. If I don't call (.Execute in the code below) the SP in vb, the looping mechanism is equal to or less than .1 seconds. When I call the SP it takes 8 seconds. I know the vb ADO code is written efficiently, e.g., [Code="plain"]Sub LoadDataSet ConnOb = Server.Createobject("ADODB.Connection") ConnOb.Open Session("ConnectionString") Set CmdOb = Server.Createobject("ADODB.Command") With CmdOb .CommandText = "TheStoredProc" .CommandType = adCmdStoredProc .Parameters.Append 'something' .Parameters.Append 'something else' .ActiveConnection = ConnOb End With **Loop** With CmbOb .Parameters("something") = value1 .Parameters("something else") = value2 .Execute, , adExecuteNoRecords **tried without adExecuteNoRecords = No difference in execution time, still 8 secondsEnd With ** End Loop ** **Clean Up (Close Connection)End Sub[/code]I'm quite certain the performance is on the server side. Any suggestions on how I can improve the performance b.c I want to use the template above to add on to the front end app to call the insert about 1000 times for another process, and I don't want the user to have to wait 35 seconds to load 1000 records.I can't use SSIS as end users will have to be able to load data and then refresh a screen so the process has to be real time. I am wary of linked servers and loading bulk as I like the integrity check of parameterized stored procs.Any help or ideas on how I can speed up the Insert SP would be greatly appreciated.Thanks