InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:25:44 GMT20http://www.sqlservercentral.com/Forums/Topic1305092-361-1.aspxHello, (I hope I'm posting this in the right place)We are exporting some tables to text to give to a client. We set query results to file to create .rpt files (SQL Server 2005). When one user did this, the resulting files were huge. Another user on the same tables/database/server got much smaller files. The files do not have any oversized text fields, etc. Can anyone explain this?Examples:(File/Rows/Size of text file for User 1/Size of text file for User 2)File 1: 1,667,469 rows/ User 1 rpt: 1.7GB/ User 2 rpt: 253MBFile 2: 515,120 rows/ User 1 rpt: 379MB/ User 2 rpt: 96MBFile 3: 15,215,975 rows/ User 1 rpt: 24.4GB/ User 2 rpt: 2.5GBThanks for any feedback.Wed, 23 May 2012 09:58:53 GMTlaurence.berghttp://www.sqlservercentral.com/Forums/Topic1268824-361-1.aspxHi,We've SQLServer 2005 logshipping configured on one of production database and the size of this db is around 1 TB. It will take more time to take the backup of the primary database every time. Not possible to take the backup of secondary database because it is in Restoring phase.So, please advice how we can take full backup from secondary database or is there any alternativesolution for this.Thanks and Regards,Ravi.Mon, 19 Mar 2012 01:43:50 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1270481-361-1.aspxHello Friends, I was wondering if you can help me finding a solution....We need to extract data on a daily basis from production database but since this is production we don’t want to use it for this purpose so we thought of setting up Mirroring and creating snapshot and extract it from there but again because of too many transactions going through the database mirroring is not the option since Mirroring is good for low transaction database. We don’t want to use logship since we don’t want to wait for log restore on the secondary server. Now the question is..what are my options to achieve this in SQL? Please helpWed, 21 Mar 2012 13:55:57 GMTsqlquest2575http://www.sqlservercentral.com/Forums/Topic1292050-361-1.aspxHi Allmy qns is i have 70GB space and how to take a full backup and database size is more that 120 gbthanks allSat, 28 Apr 2012 11:06:39 GMTsatvinder.atwalhttp://www.sqlservercentral.com/Forums/Topic1292048-361-1.aspxdear allplz tell mewhich command to use to check the database size in all server that is prod.dev.test in one time RegardsSat, 28 Apr 2012 10:59:14 GMTsatvinder.atwalhttp://www.sqlservercentral.com/Forums/Topic1283259-361-1.aspxHello,We are currently running SQL Server 2005, and i need to archive data from databases in Production server to another server. From the information that i gathered, Which one of the following is the ideal way to do so1) Using SQL Server Import/Export (At database level, i think this is easiest way)2) Write some stored procedure using linked servers to move data from one server to other3) Horizontal partitioning (I think this has to be done for each table in the database, i'm not familer with this)Also we need only data that is three months older in our production database.Pls advice.Fri, 13 Apr 2012 10:22:47 GMTDj463http://www.sqlservercentral.com/Forums/Topic1277688-361-1.aspxI'm trying to prevent a user from updating a value in a table from b to a. Once the value becomes b, it should never revert back to a.To accomplish this, I decided to add a trigger on the table and if they try to update from value b back to value a I don't want to allow this.In my trigger, I used Rollback to prevent the update from happening. This trigger works well in the sense that it does not allow value a into the field however, when I do this, I get a message from SQL saying:Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.Is this message simply informative and not hurting anything or is it causing harm. Should I be doing this differently?Tue, 03 Apr 2012 15:16:26 GMTK Curriehttp://www.sqlservercentral.com/Forums/Topic1275525-361-1.aspxDear All,I have few queries on SSRS.Does SQLServer2008 R2 developer edition Supports the SSRS?Can we update SQLServer 2005 Reporting Services to SQL 2008?It's quite urgent.Please advise me.Thanks and Regards,Ravichandra.Thu, 29 Mar 2012 23:33:38 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1270462-361-1.aspxCan I install Named instance without any default instance?Wed, 21 Mar 2012 13:27:08 GMTjitendra.padhiyarhttp://www.sqlservercentral.com/Forums/Topic1264566-361-1.aspxHello! I have a weekly tab delimited file that is currently being parsed row by row in .net code and then inserted into a new table. The table created has 503 columns with varying row counts from week to week. The current process takes way to long because each row is parsed one by one.I'm looking desperately for a quicker way to accomplish this process. Ingredients:1. Tab delimited text file2. MS SQL Server database3. Program is run .NetHas anyone run across a similar situation or can anyone suggest a time efficient way to get this done?Thanks in advance for any help!Fri, 09 Mar 2012 12:11:51 GMTKirkEBhttp://www.sqlservercentral.com/Forums/Topic1157317-361-1.aspxWe have a situation where our primary accounting software executes a query with a table hint that absolutely kills performance. While the query is still somewhat slow without the HINT its really slow with the Index Hint. Because the T-SQL code that contains the Query Hint is not acesable and therefore can not be altered (i.e. no way to correct this bad design of a query) whats the best way to combat this thing? I've found a piece at MSDN on Forcing Query Plans to try and do this but if anyone has any better suggestions I'm all ears. If I undertsand the pice on Forced Query Hints it does not allow for forcing the over ride to use whatever SQL Server believes to be the best method; in otherowrds how to make the system ignore the query hint to begin with and process the query like as if it never had a hint to begin with.Tue, 09 Aug 2011 15:53:03 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic1257760-361-1.aspxHi all. I've got a following situation:Temporary table #Temptable (msg_id, lang_id, severity, islogged, msg_text) with sample data like50001,1030,16,0,'Just some error text'50001,1033,.......50001,1035,.......50002,1030,.......50002,1033,.......50002,1035,.......and so on.To add the data to the system catalog I am using the sp_addmessage stored procedure (please notice this is a system stored procedure)To achieve that I used the cursor which gets the data from temporary table for each row and passes them to the sp_addmessage procedure.You may already assume that this is a very slow process, for example it takes like 12 seconds to add miserable 200 rows.My questionis if there is any SET based alternative to cursor in this situation?Sat, 25 Feb 2012 02:52:57 GMTDoc_saarhttp://www.sqlservercentral.com/Forums/Topic1261446-361-1.aspxMy data is on a MS sql 2000 server, the table looks like:SignalNr Value Date11104 6781 2011-05-0211104 6883 2011-05-0911104 6963 2011-05-16...11105 5418 2011-05-0211105 5516 2011-05-0911105 5591 2011-05-16...11106 5749 2011-05-0211106 5849 2011-05-0911106 5925 2011-05-16...I would like to define two parameters, Value_Start_Date (for example 2011-05-02) & Value_End_Date (for example 2011-05-09),The results I need is to calculate the diffrences between the same signalnr at above two dates, such as d(11104)=6883-6781=102,d(11105)=98 and d(11106)=100; then the total diff (d11104+d11105+d11106)=102+98+100=300.I want this calculation is done in the crystal reports, so I can specify any start & end date, with any signal nrs. Can someone help me? Thanks in advance!regards davidMon, 05 Mar 2012 01:54:05 GMTdavid.zhenghttp://www.sqlservercentral.com/Forums/Topic1259350-361-1.aspxHi guys,I have a 2005 instance with both a DEV and LIVE database with a stored procedure which references a Linked server.Because the linked server resides at instance level the LS for DEV is different to the LS for LIVE. What is the best process for designing the SQL so that it references the correct LS depending on its parent database.e.g. in Dev it needs to do[code="sql"]SELECT someField from [DevLinkedServer].[myDatabase].[dbo].[someTable][/code]in Live is needs to do[code="sql"]SELECT someField from [LiveLinkedServer].[myDatabase].[dbo].[someTable][/code]Things I have considered - none of which I like:An input parameter to take Dev or Live and construct a Dynamic SQL statement to be executed using exec sp_execute. This means no caching of query plansInterrogate the schema to find out the active database Dev or Live and run a different SQL statement based on the resultCreate two stored Procedures and choose which one to call: I am ultimately going to be calling them from an SSIS package so can dynamically choose which one to use at run-time. This runs the risk that the sps do not get updated in the same way (there is no source code control here either:w00t:)Unfortunately I am not in a position to separate the dev and live systems into different instances - which is the 'proper' way of doing itWed, 29 Feb 2012 02:18:06 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1257753-361-1.aspxHi All,I want to partition a table having more than 30lac data. But my concern is how it will behave in case of concurrent insertion from different source. will the performance slow down for that. will any possibility of dead lock over that table? please help.......thanks and regards,DebanjanFri, 24 Feb 2012 23:36:55 GMTdebanjan.rayhttp://www.sqlservercentral.com/Forums/Topic608264-361-1.aspxI used to use 6.5 a long time ago then I had a career break until recently; although many things have improved in SQL there is one thing that hasn't.I used to be able to schedule procedures to produce files of scripts which generated tables and procedures etc. Although I can still do the same thing from the Management Console manually I don't seem to be able to schedule this sort of thing. I used the 'upgrade' tool in SQL 2000 but this doesn't work in 2005. Does anybody have an idea please? I don't know how to use SMO/DMO so I'm stuck.Thanks in advanceTue, 25 Nov 2008 06:07:59 GMTsharon.smithhttp://www.sqlservercentral.com/Forums/Topic1229815-361-1.aspxHi allI'm looking for some feed back in regards to the below mentioned challenges. In our organisation we have over the last 5 years mainly done scale up, i.e. tried to host as many DB's as possible on large clustered instances (SQL 2005 EE).With a new IT manager this strategy have changed slightly into a combined scale up / scale out approach based on scale up on large VM-ware ESX hosts and scale out via more SQL instances on VM's as we have had before. The strategy is to have the entire datacenter in VM-Ware.So I'm challenged with building the SQL template servers for this new infrastructure, so logically I'm looking into standardization as much as possible.A bit background info:We are a medium sized business with 450 users and around 200 servers in total including all test, acceptance test and production systems. The number of SQL instances we have is around 20, hosting around 150 databases, with the biggest being around 230 GB.The hardware:12 * 3GHz Xeon CPU's100 GB of memoryDisk platform:IBM V7000 SANTier1 and Tier2 storage is identically duplicated at our DR site. Tier 1 storage has 150 spindles (560 GB), but not dedicated to SQL alone, thus shared with file servers and exchange. Tier 2 storage has 60 spindles (1.8 TB), mainly used for non critical production systems as well as acceptance test and test systems.As of what I know now our SQL platform will be hosted on 4 servers of the above mentioed hardware, and have their storage in the tier 1 storage. The LUN presented to the VM-Ware hosts would have a size of 500GB to 1TBUntil now we have split tempdb, data and log files on our large database clusters as per Microsoft recomendations. Even though they were seperate LUN's inside the same SAN storage disk pool. For the disk layout in the new platform I was initially in favor ofa simpler setup with only a single drive, since all logical drives inside the VM is residing inside the same LUN. Or does it still make sense to split tempdb, data and log? I have seen statements on the later arguing that Windows will be able to perform parrallel disk access in case of multiple drive usage at the host level. But is this really the case?I know this is a slightly religious discussion, but I of cause also don't want to create a foundation that are bad from the outset. On the other hand if there is no performance benefit, I would like to avoid the additional management and space loss with the three drive letter allocation per instance.So what's your take on this what would you do?Best regardsSøren Udsen NielsenWed, 04 Jan 2012 05:15:50 GMTSoren Nielsenhttp://www.sqlservercentral.com/Forums/Topic1242300-361-1.aspxHello,I'm wondering if there is any best practice or established convention about dealing with the following:I'm designing a job that cleans up log tables by deleting rows older than a certain date. I am working with multiple log tables, so I would like to log which table was processed. But now that I'm designing the log cleanup log table (I know, this can get a little convoluted), I don't know whether to:1. Find the table's object ID in the SQL Server metadata and store it as an int.2. Create my own table lookup table and use that as a foreign key.3. Store just the name of each table and make a conscious decision to avoid normalization of the log table.#1 seems like the best choice if only by default, given that #2 and #3 seem to violate basic db design principles (#2 seems redundant and #3 subject to update anomalies). But on the other hand I wonder if normalizing is overkill for a log table like this. The job will run once per day so would have only around 1500 rows per year added to it. (4 logging tables in question, so 365*4=1460).Does anyone have ideas or recommendations on the accepted way to handle this type of logging?Thanks in advance for any help or advice anyone can provide.- webrunnerThu, 26 Jan 2012 09:43:30 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic592165-361-1.aspxhas anybody in this SQL forum any experience with the following config:SQL 2005 std.edition 64 bit on Windows Server 2008 std. 64 bit ?Are the any cathes - special considerations to knopw about?is this a safe SQL design?regardsKenMon, 27 Oct 2008 08:48:43 GMTkenneth.knudsenhttp://www.sqlservercentral.com/Forums/Topic1237135-361-1.aspxi m looking a best practice solution, to daily update bulk of records from Transaction Processing System (text files) into SQL Server database. we get large amount of data i.e Customer's static information and their associated trade in 2 text files. these 2 text files are placed on daily basis on FTP Server.we have to read these 2 text files and import it in SQL Server database for reporting and other MIS. One way out is to delete every time the previous imported data from SQL Server and import these 2 files. I want to avoid this method as didnt seems to be workable for large amount of data. -I want to insert only the new reocords which are present in text file -I want to update only the modified records -and delelte only the reocrds which are not present in the text file. this text file can have records from range of 5 lacs to 1.5 million of records.Tue, 17 Jan 2012 05:33:50 GMTsafzalhussainhttp://www.sqlservercentral.com/Forums/Topic1236237-361-1.aspxHi,My rows become increase day by day. FYI, I've 70 million rows saved into 1 table. Looks like it's not healthy. I've plan to apply Table PartitionedI provide some info as following,1. My present database, there was NO filesgroup. It's only PRIMARY2. My tLeaveTrnx table have 70 million row3. I've plan to partition this tLeaveTrnx table within month and year4. Within month and year means all the data between Jan 2002 to Mar 2002 will be into A partition. Apr 2002 to Jul 2002 will be into B partition, Aug 2002 to Dec 2002 will be into another partition, and so on5. I'm using SQL Server 2005 Enterprise Edition (64 bit)Before to do that. I've several question as following,1. Did I required to adjust my database filegroup?2. It's a good idea or not to partition the data within month and year?3. How many filegroup we can create? Up to how many?4. How many partition we can create? Up to how many?5. Did I need to create a NEW table with partition 6. If yes, how to transfer tLeaveTrnx data into new tLeaveTrnx table?7. If no, it's possible to adjust the current table into partition?What's can I say is I'm very junior for Table PartitionedSat, 14 Jan 2012 21:26:42 GMTLittle Nickhttp://www.sqlservercentral.com/Forums/Topic1233780-361-1.aspxHiI want to lock some group of data when some user monitoring this data group on winform. When other user want to monitoring same data i want to throw exception. "This data group open on USER A."What i need to do? How do i ensure this model in MS SQLWed, 11 Jan 2012 01:50:49 GMTumut ozkanhttp://www.sqlservercentral.com/Forums/Topic1232020-361-1.aspxSubject: We are going to put our servers in two places to keep high level redundancy and to avoid bandwidth critics. The two servers are on different LANs but communicate over a WAN network (via Internet). One of the servers is the main server and the other a secondary server. The problem is that the software doesn’t consist just of a database but has also files on the main server that must be transferred to the second server, too. The Server is using SQL SERVER 2005 as its database. The main requirements of our design are the following:1. We are using SQL Server 2005 and are going to upgrade the database to Oracle in future. So we must consider maintenance and evolution issues.2. The files (images, movies etc.) have to be transmitted to the second server, too.3. The relation between the two servers which is around the internet must be completely secure. Security is one of the major points. The only port that is open on the main server is Port 80 for HTTP request which is just read-only and all other ports are closed which proved to be a very secure option. 4. The bandwidth between the two servers is very limited and we don’t want to burden the main server.5. The second server must be writeable but any modification at the second server is not going to be posted back to the main server. So we have a one-directional transaction and don’t want a bi-directional one.1.Solution IData transmission between the SQL Servers: Transactional Replication File transmission between the servers: Cute FTP Security Option: VPN In this solution we are going to use VPN to secure the relation between the two servers. The data between the two SQL SERVERs are being sent via Transactional Replication.2.Solution IIData transmission between the SQL Servers: Backup and Restore File transmission between the servers: Cute FTP Security Option: VPN Here we are going to back up the database every six hours and sent the data with the files through a secure tunnel – VPN – to the second server via FTP. The disadvantage of this solution is that it uses much of the bandwidth and costs much more time than the first solution.3.Solution IIIData transmission between the SQL Servers: Web synchronization with Merge Replication File transmission between the servers: WebDAV over SSL Security Option: - Here we use Merge Replication for our Replication although we are not going to use the bi-directional option of Merge Replication. We are going to use Web Synchronization instead of VPN. To transfer the files to the second server we are going to use WebDAV over SSL to secure to connection. The possible drawback of this option is that I am not sure that the data transmission will be secure and may cause security lacks on the main server. Even for Web synchronization we have to open the 443 port which may also cause security matters especially that we are not using VPN in this solution. 4.Solution IVData transmission between the SQL Servers: Transactional Replication File transmission between the servers: FTP or WebDAV over SSL Security Option: Configuring Proxy Server The proxy server is configured as a multihomed server to prevent unauthorized users on the Internet from accessing the internal server running SQL Server. The proxy server is configured as a multihomed server to prevent unauthorized users on the Internet from accessing the internal server running SQL Server. In this option we have to open ports: 1433 and 21. I am not sure that this causes security lacks especially that we are not using VPN in this solution. Note: You consider that we are not using features like Mirroring or Log Shipping. We cannot use features like Mirroring in SQL Server, because in these cases the standby server is either unavailable or - if using snapshot – just read-only.I prefer to use Solution Nr.1 because of security and performance matter. I’ll appreciate your advice and I am looking forward to get your opinion about my design solutions. If you know another forum that would be better toward this, please inform me. I am awaiting your reply impatiently. Sun, 08 Jan 2012 00:43:05 GMTyakub_andrehttp://www.sqlservercentral.com/Forums/Topic1229065-361-1.aspxFor one of my requirement I want to cache the data in a table. This table will be dynamically formed with data from different tables. Is there any way by which we could cache data in SQL 2005, the way we used to do it in .Net.Mon, 02 Jan 2012 23:48:35 GMTMaruthi Shangarhttp://www.sqlservercentral.com/Forums/Topic1223274-361-1.aspxIf I have a SELECT statement that JOINs with a table in a different database (same server), I can write it like this:[code="sql"]SELECT b.EmployeeName FROM Invoice AS a JOIN PersonnelDB.dbo.Employee AS b ON a.EmployeeID = b.EmployeeID[/code]Or, I can create a view on the same table in the default database and write it like so:[code="sql"]SELECT b.EmployeeName FROM Invoice AS a JOIN dbo.vEmployee AS b ON a.EmployeeID = b.EmployeeID[/code]where dbo.vEmployee is [code="sql"]SELECT EmployeeiD, EmployeeName FROM PersonnelDB.dbo.Employee[/code]Is there a performance difference between the two methods? What if the two tables referenced were on different servers? Thanks for any input.Fri, 16 Dec 2011 11:55:51 GMTDave-148053http://www.sqlservercentral.com/Forums/Topic1223241-361-1.aspxHelloAs the topic says, I did delete the "FOLDER" ( which means the reports also were deleted )Is there a way to recover the reports back ?ThanksFri, 16 Dec 2011 10:54:36 GMTmw112009http://www.sqlservercentral.com/Forums/Topic1222255-361-1.aspxI am database owner for a database around 800 GB. we have auditing tables created for each of the table and triggers populate data in those tables. Currently we are using data purging on daily basis. As the data volume is getting increased day by day, the time of purging job is increasing as well. Is there a way I can do my purging faster ? The columns on the basis of purging is done and which are included in where condition of delete statement are covered by non clustered index. Could you please give me some suggestions on the purging strategy ?Thu, 15 Dec 2011 02:37:54 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1218487-361-1.aspxHello frnds,I am struck in designing a table.pls help me out.I have a fixed database schema and in my UI users can add certain fields dynamically(label,textbox). My question is what is the best approach to capture the values of newly added controls (as table design in fixed..there is no table mapping for those fields)...Let me know how to design such a table?Thanks,Thu, 08 Dec 2011 03:31:31 GMTnavraj22http://www.sqlservercentral.com/Forums/Topic1060951-361-1.aspxHello,I have many issues relating to this subject....but my attempt to write something ended up being over 2 pages long!! so i will start with a specific question to see how it goes!!we support OLTP databases, and at the top end we have 150GB activedb (heavily used), 300GB archivedb (rarely used) and decent tempdb usage. currently the hardware a customer gets depends on how much they spend....but as we all know....expectiations rarely meet actual findings and most of the customers under-spec'd their systems to save money and are now moaning about performance!!the problem is that we hold the data on a massive RAID10 lun on an MD3000i iSCSI SAN which is shared between customers. i have only been here for 6 months so had no input....but need to find a new strategy for any new customers i feel....on some systems we are getting 2000ms logflush wait time and up to 5000ms I/O wait time!!this RAID10 LUN contains all the datafiles, logfiles, tempdb, system db's and full text indexes (*everyone winces!!) i have tried to explain that logical partitions are useless when it comes to db storage but without providing proof the hardware guys dont want to listen....so i am waiting for my lab!what i need to know is how queue depth and filegroup/datafile numbers should be altered when you have limited spindles....i would say that most LUN's presented to the servers are luckly if they manage to get a single spindle to themselvesie: in an ideal world 15 disks in a 14 disk RAID10 gives 7 active spindles split between 2 servers gives 3.5 spindles per server....add the main database, log file writes, tempdb work, multi-threaded parallel queries and 16 filegroups with 1 datafile each split by application module rather than splitting indexes and tables (my boss's idea!!)....most people cannot see the problem here....but i think we have a massive queue problem!!if im correct each datafile can spawn an i/o thread....say half are used regularly gives 8 threads plus the transaction log writes and other I/O....i would guess there are probably 12 I/O requests being thrown at any one time to a LUN that maybe has 1 or 2 spindles to respond!! most people think....oh your SAN has 14 drives so its all good....but most of those spindles are busy with other requestsmy questions are:1. should we reduce the number of filegroups to only 2 or 3 to contain non-clustered indexes and heavily used tables rather than 16 to reduce the number of requests or does it not make much of a difference? (i was told that the space reclaimed by index rebuilds is more efficient with extra filegroups as space can be reclaimed from each one....but i think that was made up to support the existing setup!!)2. do the filegroups still need 1 datafile per CPU core if we do not have enough spindles to handle that number of requests? what number should we use for an 8 core server that only has 1 spindle available?3. should we reduce the queue depth to around 3/4 due to the lower number of spindles?4. should we still create 1 tempdb datafile per CPU when limited by spindles?I know there is a lot here....but i appreciate any thoughts and especially anyone with experience in testing any of these theoriesregards,lilywhitesWed, 09 Feb 2011 03:25:53 GMTlilywhiteshttp://www.sqlservercentral.com/Forums/Topic1213971-361-1.aspxHi,I'm currently at the conception stage of a new project and i'm struggling with how to attack this particular problem.We have a number of third party applications which are all backed by SQL Server databases. Each application stores a separate version of employee data and the database structure of each database is different. This data is sporadically kept in sync by exporting CSV files from one database, importing it into the other and updating the data to match.We have been asked to streamline this process.I was thinking about maintaining a "master" database that holds the definitive employee data and somehow replicating the changes which take place in each individual application database on this master and pushing the changes out to each application database so that the entire batch remain in sync.It seems to be very similar to P2P transactional replication but the fact that i am not dealing with exact copies of a database / database object would suggest i cannot use this technology.That's a bout as far as i have got; any suggestions on how to proceed would be gratefully received.I apologise if this is incoherent, please advise if any further info is needed and i will happily add it.Thanks in advance.Wed, 30 Nov 2011 08:12:40 GMTDBANewbiehttp://www.sqlservercentral.com/Forums/Topic1184178-361-1.aspxHi,I have a general ledger table with approx 50 million records. There is a decision made in the revision meeting to partition this table. This will be partitioned on the basis of Financial Year. What should be the strategy here ? On a high level what should be the steps involved ? Could any one help please..Sun, 02 Oct 2011 08:54:35 GMTsanujsshttp://www.sqlservercentral.com/Forums/Topic1203206-361-1.aspxOkay folks, here's my situation. A .NET process will be shredding an XML document which will have a list of transactions that will need to be passed to a stored procedure which will insert into a table. There can be N number of transactions and there will be 3 values I will be passed. I can have the .NET application loop over the transactions and call the SP for each one or I can have the .NET application pass an "array" that I will parse in SQL. There is logic to determine which transactions will be passed so you can't just pass the XML document. The current production SQL Server is 2005, so I can't do a table-valued parameter and I'm not sure I would even if I could because of some of the limitations. What are your recommendations? Loop and 1 at a time or some kind of "array" that I parse in SQL?Wed, 09 Nov 2011 14:49:43 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1202947-361-1.aspxIs there a way to combine the two columns into one? [code="sql"]R_ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,REPAIR_ID AS 'R' + RIGHT('00000' + CAST(R_ID AS VARCHAR(5)),5),[/code]Wed, 09 Nov 2011 08:25:06 GMTbpowershttp://www.sqlservercentral.com/Forums/Topic1201613-361-1.aspxI really thought I would find a vsd or a diagram that has the following elements on the interwebProduct (product table)Catalog (Catalog associated to Vendors)Page of the catalog referring to the product (Pages Associated to the product)page PDF (image for the app)Then additional information (ie design values) that was associated to the product/pageI looked in adventure works and there was nothing. I have a simple model in my head but was hoping to find something better that would make me think big picture. If you know of anything I would appreciate the help.attached is some code for a general idea.[code="sql"]IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sections]') AND type in (N'U'))CREATE TABLE [dbo].[Sections]( [SectionsId] [uniqueidentifier] NOT NULL, [SectionTitle] [nvarchar](50) NOT NULL, [CatalogId] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_Sections] PRIMARY KEY CLUSTERED ( [SectionsId] ASC)) ON [PRIMARY]GOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Catalog]') AND type in (N'U'))CREATE TABLE [dbo].[Catalog]( [CatalogId] [uniqueidentifier] NOT NULL, [CatalogName] [nvarchar](50) NULL, [ManufacturersID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_Catalog] PRIMARY KEY CLUSTERED ( [CatalogId] ASC)) ON [PRIMARY]GOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CatalogPages]') AND type in (N'U'))CREATE TABLE [dbo].[CatalogPages]( [PagesId] [int] NOT NULL, CONSTRAINT [PK_CatalogPages] PRIMARY KEY CLUSTERED ( [PagesId] ASC)) ON [PRIMARY]GO/****** Object: Table [dbo].[Materials] Script Date: 11/07/2011 16:46:47 ******/IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Materials]') AND type in (N'U'))CREATE TABLE [dbo].[Materials]( [MaterialsId] [uniqueidentifier] NOT NULL, [MaterialName] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Materials_1] PRIMARY KEY CLUSTERED ( [MaterialsId] ASC)) ON [PRIMARY]GOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPages]') AND type in (N'U'))CREATE TABLE [dbo].[ProductPages]( [PagesId] [int] NOT NULL, [MaterialsId] [uniqueidentifier] NOT NULL, [SectionsId] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_ProductPages] PRIMARY KEY CLUSTERED ( [PagesId] ASC, [MaterialsId] ASC, [SectionsId] ASC)) ON [PRIMARY]GOIF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductPages_CatalogPages]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductPages]'))ALTER TABLE [dbo].[ProductPages] WITH CHECK ADD CONSTRAINT [FK_ProductPages_CatalogPages] FOREIGN KEY([PagesId])REFERENCES [dbo].[CatalogPages] ([PagesId])GOIF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductPages_Materials]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductPages]'))ALTER TABLE [dbo].[ProductPages] WITH CHECK ADD CONSTRAINT [FK_ProductPages_Materials] FOREIGN KEY([MaterialsId])REFERENCES [dbo].[Materials] ([MaterialsId])GOIF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductPages_Sections]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductPages]'))ALTER TABLE [dbo].[ProductPages] WITH CHECK ADD CONSTRAINT [FK_ProductPages_Sections] FOREIGN KEY([SectionsId])REFERENCES [dbo].[Sections] ([SectionsId])GOIF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Sections_Catalog]') AND parent_object_id = OBJECT_ID(N'[dbo].[Sections]'))ALTER TABLE [dbo].[Sections] WITH CHECK ADD CONSTRAINT [FK_Sections_Catalog] FOREIGN KEY([CatalogId])REFERENCES [dbo].[Catalog] ([CatalogId])GO[/code]Mon, 07 Nov 2011 11:17:52 GMTJKSQLhttp://www.sqlservercentral.com/Forums/Topic1196676-361-1.aspxhWed, 26 Oct 2011 15:49:09 GMTsrinivas-406082http://www.sqlservercentral.com/Forums/Topic1196526-361-1.aspx[b]QUESTION:[/b] If you have an SP that takes in a number and then returns a value and the contents of the SP does not read from any table but only compares the value passsed in to one of many possibles values (after a formula is applied to the value passed to the SP) then is it more effecient to go with a CASE statement (example #1 below) or an IF THEN (example 2) or is it irrelevant since there's no tables involved?[b]ADDITIONAL INFO: [/b]This is not code of my own making but part of the stoock SP's in a database one of our systems uses and it seems to me like the thing was done by a programmer and not a DB/DBA type since it looks more like VB code then T-SQL. I can't chjange it simply because I want to but if I can make the case that it would be more effecient (to use) if it were changed to use the Case statement (since testing in a Case stops when you hit a TRUE where as the IT-Then versions perfrorms all tests even if the first one is true) then I could get teh vendor to do this.BTW - There are many more levels of testing teh number I just reduced it to 4 levels or tests here to avoid being excessive.Thoughts?[b]Example #1: CASE Example[/b][code="sql"]CREATE PROCEDURE [dbo].[what_is_my_base_value_CASE] (@MyNumber NUMERIC, @MyNumbersBaseValue NUMERIC OUTPUT)ASDECLARE @iBaseType NUMERIC BEGIN SET @iBaseType = floor(IsNull(@MyNumber,0)/10000000000) SELECT @iBaseType = Case WHEN @iBaseType = 33 THEN 3 WHEN @iBaseType = 36 THEN 6 WHEN @iBaseType = 39 THEN 10 WHEN @iBaseType = 48 THEN 7 WHEN @iBaseType NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 33, 36, 39, 48, 18, 19 ) THEN 0 ELSE @iBaseType END SET @MyNumbersBaseValue = @iBaseType END[/code][b]Example #2 - IF-THEN Example[/b][code="sql"]CREATE PROCEDURE [dbo].[what_is_my_base_value_IFTHEN] (@MyNumber NUMERIC, @MyNumbersBaseValue NUMERIC OUTPUT)ASDECLARE @iBaseType NUMERIC BEGIN SET @iBaseType = floor(IsNull(@MyNumber,0)/10000000000) IF @iBaseType = 33 SET @iBaseType = 3 IF @iBaseType = 36 SET @iBaseType = 6 IF @iBaseType = 39 SET @iBaseType = 10 IF @iBaseType = 48 SET @iBaseType = 7 IF NOT @iBaseType IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 33, 36, 39, 48, 18, 19 ) SET @iBaseType = 0 SET @MyNumbersBaseValue = @iBaseType END[/code]Wed, 26 Oct 2011 11:32:28 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic1195898-361-1.aspxI have two columns, in a new table I created, that will store hexadecimal values. One will be 8bit hex and the other 16bit hex. What is the best way to store this type of data? Varbinary, image, etc... Any help will be appreciated.Tue, 25 Oct 2011 12:20:41 GMTbpowershttp://www.sqlservercentral.com/Forums/Topic1194991-361-1.aspxWe're stuck with the perennial problem of linking a 64-bit SQL Server to a 32-bit Pervasive SQL Application on another server. We know all about the 64-bit application and 32-bit driver/UDN/DNS problems. We can connect perfectly to the Sage-Peachtree Pervasive databases in question, but due to the architecture mis-match on bits, we cannot get our applications to match up so we can map the columns in the tables using OLE DB.If I can get my 2005 64-Bit SQL Server to link up to the database in "Linked Servers", I should beat the problem and my application will connect with 'OLE DB for ODBC Drivers'. But, we're hit with the persistent problem, with the 32-bit database/DSN architecture, we can't get the link to connect appropriately in MS SQL 2005 64-bit. If anyone has a cure, we have rewards to trade, first-born children etc...!! ;-)TerrySun, 23 Oct 2011 16:20:03 GMTdocsetchttp://www.sqlservercentral.com/Forums/Topic988637-361-1.aspxI am a .NET developer working with a small team of developers who have to also double as our own DBDs. Recently, the subjects of LINQ to SQL and Entity Framework have been raised by one of our developers. I realize this is a SQL Server forum and developers would be using these via .NET, but I want to know how DBAs feel about these technologies hitting their databases. I've very quickly skimmed:LINQ - http://msdn.microsoft.com/en-us/library/bb308959.aspxEntity Framework: - http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspxAt first glance, this goes against everything I was taught. I was taught "NO INLINE SQL!" yet that's what I'm seeing here. And I even saw a foreach that looks like it is possibly masquerading as a cursor (another big no-no). I rarely have a call to the DB that is a simple "select this from that where this". Most DBAs do not want developers hitting the database like this. Am I completely missing the boat here? Is this a huge paradigm shift that we need to embrace? Does using these technologies mean DBAs roles will change to be more operational DBAs? I can't see any of you wanting that to happen, right?I'm just looking for top of the trees feedback on these concepts. I'm not ready to drink the Kool Aid yet.Thank you for any input -LisaFri, 17 Sep 2010 17:27:31 GMTLSAdvantagehttp://www.sqlservercentral.com/Forums/Topic1185933-361-1.aspxMy current project for performance improvement of a database includes changing from a sequence generated by inserting into a table with an identity column, retrieving the identity value created, deleting the row from the table and then using the generated value in the insert. This takes place in an SP like this:[code="sql"]CREATE PROCEDURE dbo.get_order_id (@order_id INT OUTPUT)AS BEGIN; INSERT INTO dbo.generate_order_id (dummy) VALUES (0) ; SELECT @order_id = SCOPE_IDENTITY() ; DELETE FROM dbo.generate_order_id WHERE id = @order_id ; RETURN ;END ;[/code] This is used like this:[code="sql"]CREATE PROCEDURE dbo.insert_order (@customer_id INT, @order_date SMALLDATETIME)AS BEGIN; DECLARE @order_id INT; EXEC dbo.generate_order_id @order_id OUTPUT; INSERT INTO dbo.orders ( order_id, customer_id, order_date ) VALUES ( @order_id, @customer_id, @order_date ) RETURN ;END ;[/code]As you can imagine this causes deadlocks. The fix is to change the order_id column in the orders table to be an identity column, since that's all the code is doing anyway.You can't just change a column to identity, you have to re-create the table. When using SSMS to generate the script for this you get something like this:[code="sql"]/* Wednesday, October 05, 201110:31:34 AM User: Server: . Database: Play Application: *//* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*//* Start process with Set options Transaction 1 */BEGIN TRANSACTION -- SET options Transaction 1SET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMIT -- SET options Transaction 1/* Drop FK to Customers Transaction 2 */BEGIN TRANSACTION -- GOALTER TABLE dbo.orders DROP CONSTRAINT FK_orders_customersGOALTER TABLE dbo.customers SET (LOCK_ESCALATION = TABLE)GOCOMMIT -- Drop FK to Customers Transaction 2/* create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3 */BEGIN TRANSACTION GOCREATE TABLE dbo.Tmp_orders ( order_id int NOT NULL IDENTITY (1, 1), customer_id int NOT NULL, order_date smalldatetime NOT NULL ) GOALTER TABLE dbo.Tmp_orders SET (LOCK_ESCALATION = TABLE)GOSET IDENTITY_INSERT dbo.Tmp_orders ONGOIF EXISTS(SELECT * FROM dbo.orders) EXEC('INSERT INTO dbo.Tmp_orders (order_id, customer_id, order_date) SELECT order_id, customer_id, order_date FROM dbo.orders WITH (HOLDLOCK TABLOCKX)')GOSET IDENTITY_INSERT dbo.Tmp_orders OFFGOALTER TABLE dbo.order_items DROP CONSTRAINT FK_order_items_order_itemsGODROP TABLE dbo.ordersGOEXECUTE sp_rename N'dbo.Tmp_orders', N'orders', 'OBJECT' GOALTER TABLE dbo.orders ADD CONSTRAINT PK_orders PRIMARY KEY CLUSTERED ( order_id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)GOALTER TABLE dbo.orders ADD CONSTRAINT FK_orders_customers FOREIGN KEY ( customer_id ) REFERENCES dbo.customers ( customer_id ) ON UPDATE NO ACTION ON DELETE NO ACTION GOCOMMIT -- create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3/* add FK to order_items Transaction 4 */BEGIN TRANSACTION -- GOALTER TABLE dbo.order_items ADD CONSTRAINT FK_order_items_order_items FOREIGN KEY ( order_id ) REFERENCES dbo.orders ( order_id ) ON UPDATE NO ACTION ON DELETE NO ACTION GOALTER TABLE dbo.order_items SET (LOCK_ESCALATION = TABLE)GOCOMMIT -- add FK to order_items Transaction 4[/code]AS you can see this sets up 4 separate transactions. I'm wondering if I should modify it to be one transaction so that if there is an error somewhere, the whole thing gets rolled back. This is what I'm thinking:[code="sql"]BEGIN TRYSET XACT_ABORT ON;BEGIN TRANSACTION SET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONALTER TABLE dbo.orders DROP CONSTRAINT FK_orders_customersALTER TABLE dbo.customers SET (LOCK_ESCALATION = TABLE)/* create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3 */CREATE TABLE dbo.Tmp_orders ( order_id int NOT NULL IDENTITY (1, 1), customer_id int NOT NULL, order_date smalldatetime NOT NULL ) ALTER TABLE dbo.Tmp_orders SET (LOCK_ESCALATION = TABLE)SET IDENTITY_INSERT dbo.Tmp_orders ONIF EXISTS(SELECT * FROM dbo.orders) EXEC('INSERT INTO dbo.Tmp_orders (order_id, customer_id, order_date) SELECT order_id, customer_id, order_date FROM dbo.orders WITH (HOLDLOCK TABLOCKX)')SET IDENTITY_INSERT dbo.Tmp_orders OFFALTER TABLE dbo.order_items DROP CONSTRAINT FK_order_items_order_itemsDROP TABLE dbo.ordersEXECUTE sp_rename N'dbo.Tmp_orders', N'orders', 'OBJECT' ALTER TABLE dbo.orders ADD CONSTRAINT PK_orders PRIMARY KEY CLUSTERED ( order_id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)ALTER TABLE dbo.orders ADD CONSTRAINT FK_orders_customers FOREIGN KEY ( customer_id ) REFERENCES dbo.customers ( customer_id ) ON UPDATE NO ACTION ON DELETE NO ACTION /* add FK to order_items */ALTER TABLE dbo.order_items ADD CONSTRAINT FK_order_items_order_items FOREIGN KEY ( order_id ) REFERENCES dbo.orders ( order_id ) ON UPDATE NO ACTION ON DELETE NO ACTION ALTER TABLE dbo.order_items SET (LOCK_ESCALATION = TABLE)IF XACT_STATE() = 0 BEGIN; COMMIT TRANSACTION; END;ELSE BEGIN; ROLLBACK TRANSACTION; END; END TRYBEGIN CATCH SELECT ERROR_NUMBER(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE() IF XACT_STATE <> 0 ROLLBACK TRANSACTION;END CATCH[/code]There will also be SP changes to be made to eliminate the call to the SP that generates the id and to remove the insertion of order_id into the orders table since it is now an identity column.What do you think?Wed, 05 Oct 2011 08:46:47 GMT Jack Corbett