[SQL Server 2008 issues] Creating a stored procedure inside a stored procedure.

---

• Creating a stored procedure inside a stored procedure.
• Maximum Number of Values in an In in a where clause
• Remembering that it is good to "get back to basics" every now and then
• Requesting for best suitable jdbc driver
• how to Convert rtf format text from database ?
• Pass inter year and month return last day of month
• sp_rename help
• SP to print (see) stored .pdf documents,
• Import from Excel
• Creating Dynamic Columns in SSRS 2008
• Opening .dts package from SQL Server 2000 in Visual Studio 2008
• Tool to search, list out distinct .sql files
• Do I have a lack of memory
• SQL Server Change Data Capture update records but __$operation is 1, 2
• Add SSRS to clustered instance
• Alter Database
• SSIS 2008 Script Component error
• Import Wizard failing to insert with Identity Column
• Attach MDF that wasn't cleanly shutdown
• DROP TABLE in Database Mirroring environment
• Solution sought. Advice needed. SSIS?
• Insert identity dynamically by using insert into
• Change Tracking Cross DB
• Quotations around variable

Creating a stored procedure inside a stored procedure. Posted: 06 Aug 2013 07:03 PM PDT Is this possible?My aim behind it is to create a stored procedure which I save in master. When I deploy a new database on an instance, I will exec this SP passing it the database name as a variable. This SP will then create a new stored proc on the new database which will have the code to - Create folder structure for backups for the database name I passed, delete old backups, create a new full backup.I have tried to do this but ran into issues with quotations.. so im wondering if it is actually possible. If so.. I will continue to tackle the quotations!

Maximum Number of Values in an In in a where clause Posted: 06 Aug 2013 09:18 AM PDT HiCan anyone tell me if there is a limit to how many entries there can be in an in?As in where somevalue in ('Fred','George','Dave'....)The reason I ask is I have a query that was created by SSRS, it creates an 'in' with 20975 entries and it makes SQL sever respond with "The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."SQL Server has over 7 Gb of RAM allocated.Any help would be much appretialed

Remembering that it is good to "get back to basics" every now and then Posted: 06 Aug 2013 06:35 AM PDT I have a new position where I finally have the long sought after Sr. prefix included. Only time will tell if I truly deserve the prefix, but I believe that I can do the job. One of the things that is different about this position by comparison to my most recent projects / positions is a more formalized deployment / implementation process (THANK GOD FOR THIS!!! :-D). Part of this process is a code review with several other developers that are at least as good as I am...and in many cases better.I had my first code review today, and the only real issue anyone had with it (aside from learning naming conventions) involved a bit of code that I have used for years on end:[code="sql"]Select VariousFieldsInto dbo.TestTableFrom dbo.SourceTable(s)----------Set @RecordCount = (Select Count(TestTable_PrimaryKey) From dbo.TestTable)[/code]The code goes on to split the logic path based on @RecordCount = 0 vs. @RecordCount > 0. This is a section of code that I could have said I had down as well as tying my own shoes (there is a Ted Talks reference there, and this entire post lines up with it). The conversation went something like this:[b]Other developer:[/b] Why did you use this code here?[b]Me:[/b] Because I was needing to split the logic path.[b]Other developer:[/b] Yes, I see that. What I mean was why didn't you use:[code="sql"]Select VariousFieldsInto dbo.TestTableFrom dbo.SourceTable(s)----------Set @RecordCount = @@RowCount[/code][b]Me:[/b] Because I had never heard of it, thought of it, or seen it before you just typed it.This post is meant to remind not just me but everyone who reads this to look at those parts of our code that seem all but written in stone that we will use the same approach each time. Is there a better way to do what you're doing?A small advantage in any area of life can sometimes be the difference between adequate results and all star results.

Requesting for best suitable jdbc driver Posted: 06 Aug 2013 05:58 PM PDT Hi All,We have recently planned to migrate from 2005 to 2008, on doing the same on test environment everything seems to be working fine except on some scenarios where batch execution was used with registerOutParameter it started giving "java.sql.BatchUpdateException: The OUT and INOUT parameters are not permitted in a batch ". We have upgraded the jdbc driver with the latest [b]sqljdbc4[/b] & also used [b]jTDS[/b] drivers still didn't work. Please anyone suggest me to resolve this issue.

how to Convert rtf format text from database ? Posted: 06 Aug 2013 01:19 AM PDT Hi Friends, In a table,there is a field(ntext) stored all the text in rich text format. I need to convert it back to normal text format. How can i do this ? I would be so happy if it is possible in script component in SSIS...Any help would be really appreciated.

Pass inter year and month return last day of month Posted: 06 Aug 2013 05:02 AM PDT How to code to pass inter year and month return last day of month?For example, pass 2012, 7 will return 07/31/2012pass 2014, 6 will return 06/30/2014

sp_rename help Posted: 06 Aug 2013 04:27 PM PDT Hi ProfessionalsI am running html with php which passed two variables in based on the column names within the database from a dynamically created table. At the backend I have a stored procedure which renames the two column names passed in.[code]USE [TestData]GO/****** Object: StoredProcedure [dbo].[updatethecolumnsSMPV] Script Date: 08/07/2013 15:19:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[updatethecolumnsSMPV] @column1 nvarchar(1000), /* Needs to be renamed to software manufacturer */ @column2 nvarchar(1000) /* Needs to be renamed to product version */as/******************************************************************************************************* This procedure passes in a combination choice of two variables **** software manufacturer and product version **** we need to rename the columns consecutively *******************************************************************************************************/begindeclare @tablename1 varchar(100), @tablename2 varchar(100);begin begin transaction --SET @tablename1 = '''' + 'newtable.[' + @column1 + ']' + ''''; SET @tablename1 = 'newtable.[' + @column1 + ']'; /* Revised 07-08-2013 */ SET @tablename2 = 'newtable.[' + @column2 + ']'; exec sp_rename @tablename1,'softwaremanufacturer','column' exec sp_rename @tablename2 ,'productversion','column' commit transactionend --Now go on to cleanse the database Software Manufacturers and the Product names to how they should beexec cleanseSMPV; END[/code]The problem is on the front end explorer I am recieving this error, even though everything works fine. Is there a way to not show this error or get rid of it entirely[code]Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 15477 [code] => 15477 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Caution: Changing any part of an object name could break scripts and stored procedures. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Caution: Changing any part of an object name could break scripts and stored procedures. ) ) [/code]thanks

SP to print (see) stored .pdf documents, Posted: 06 Aug 2013 11:33 AM PDT Hi all,I'm thinking how to fulfill customer request to pull from db all info related to given Sample_ID (which could include picx, docs, scans ) and give him ability to print it !!!!I can to pull this info to the table and try to provide hyperlinks to those scans, for it's the only way I can think of, no idea what env I'll need to achieve it.I know that in ssrs design view I can :-Insert Text box- select Property- select action- select go URL and configure it, and it's work after viewing report.I can do this in tablix cell, and probably to add some dinamics into URL name (like rownum)???Or probably it can done with some already availabe features, the same way like I can do link/clik for xml in column???Not sure can I can all this configuration programatically inside my sp.I'm trying it right now. Probably I can configure it to some generic URL, then select .pdf from binary, upload it somewhere in the server and insert that link into wanted column on the SSRS report. I even can hard code URL in field property and if document exist upload it into that URL, it will be different for each cell.Do you think it's doable ? or reasonable -). I'm on SQL 2008 and we can run SSRS server too.Appreciate you input and criticsBestmario

Import from Excel Posted: 06 Aug 2013 02:45 AM PDT I have a spreadsheet with 88 columns and about 5000 rows which I need to import into Sql Server 2008.The import wizard doesn't work - it has a stab at it and imports a few rows and the rest of the data is null.I have tried creating a table with 88 nvarchar(255) columns and pasted a couple of hundred rows from the spreadsheet.But, while most of the data pastes okay, some just seems to be missing.In the first row, 80 odd columns get the data in, but the 81st has data that starts with a pair of inverted commas but the data is not complete. The rest of the row is empty.The next row is okay.Anyone any tips on how do to this please? Anyone got a list of hidden characters in Excel that I can find and remove? (The pair of inverted commas mentioned above do not appear in the spreadsheet)

Creating Dynamic Columns in SSRS 2008 Posted: 02 Mar 2011 05:32 AM PST HiI am trying to create a dynamic report in ssrs 2008,based on the selected parameters i need to populate columns for ex in questionno parameter if i select questions 1,3,5,7 then 4 columns has to be populated in the report ie based on the number of questions selected so many columns has to be added to the reportalso my sp is dynamic so can you please give me some idea to create such report.Thanks in Advance,Sweatha

Opening .dts package from SQL Server 2000 in Visual Studio 2008 Posted: 30 Jul 2013 07:39 AM PDT I can't believe I'm having this much trouble with this, but I am stuck. I have a .dts package that was saved to a SQL Server 2000 database. Unlike what I'm used to, I cannot seem to access this file in Visual Studio 2008.I know that there will be a compatability issue with this, and the main thing I am trying to do is test out how to do this so that I will have a solution ready when I need to make these changes in the near future. Can anyone offer any advice? Can you tell me if what I'm seeing sounds correct (i.e. - I cannot track down an acctual .dts file. Instead I only see the package created on the server. I thought it was in SQL Server 2005 that first allowed that.)?Thank you,Jarid

Tool to search, list out distinct .sql files Posted: 06 Aug 2013 05:31 AM PDT Is there any tool which can scan through each script and spit out list of scripts which are same?

Do I have a lack of memory Posted: 06 Aug 2013 05:44 AM PDT I was looking into whether an existing server could handle added load and I came across some values in RING_BUFFER that look to me like there is memory pressure but it doesn't appear so in perfmon. I am just wondering if I am misinterpreting the results or if i do, in fact, have a memory issue.here is query and excerpt of result from the RING_BUFFER_RESOURCE_MONITOR[code="sql"] ;WITH RingBufferAS (SELECT CAST(dorb.record AS XML) AS xRecord,dorb.timestampFROM sys.dm_os_ring_buffers AS dorbWHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,DATEADD(ms, -1 * (dosi.ms_ticks - rb.timestamp), GETDATE()) AS RmDateTime,xr.value('(MemoryNode/CommittedMemory)[1]','bigint') AS CommitedMemory,xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') AS TotalPhysicalMemory,xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') AS AvailablePhysicalMemory,xr.value('(MemoryRecord/TotalPageFile)[1]','bigint') AS TotalPageFile,xr.value('(MemoryRecord/AvailablePageFile)[1]','bigint') AS AvailablePageFile--,FROM RingBuffer AS rbCROSS APPLY rb.xRecord.nodes('Record') record (xr)CROSS JOIN sys.dm_os_sys_info AS dosiORDER BY RmDateTime DESC;[/code][code="plain"]RmNotification IndicatorsProcess IndicatorsSystem RmDateTime CommitedMemory TotalPhysicalMemory AvailablePhysicalMemory TotalPageFile AvailablePageFileRESOURCE_MEM_STEADY 0 0 2013-08-06 13:28:52.003 4035580 6291000 654948 12580140 6995960RESOURCE_MEMPHYSICAL_HIGH 0 1 2013-08-06 13:28:52.003 4035580 6291000 654948 12580140 6995960RESOURCE_MEMPHYSICAL_LOW 2 0 2013-08-06 13:28:51.890 4046148 6291000 645704 12580140 6985736RESOURCE_MEM_STEADY 0 0 2013-08-06 11:43:20.037 4031484 6291000 613664 12580140 6991752RESOURCE_MEMPHYSICAL_HIGH 0 1 2013-08-06 11:43:20.037 4031484 6291000 613664 12580140 6991752RESOURCE_MEMPHYSICAL_LOW 2 0 2013-08-06 11:43:19.930 4034892 6291000 610772 12580140 6988688[/code]here is query and excerpt of result from the RING_BUFFER_MEMORY_BROKER[code="sql"]with RingBuffer as(SELECT CAST(dorb.record AS XML) AS xRecord,dorb.timestampFROM sys.dm_os_ring_buffers AS dorbWHERE dorb.ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')select DATEADD(ms, -1 * (dosi.ms_ticks - rb.timestamp), GETDATE()) AS RmDateTime,xr.value('(MemoryBroker/DeltaTime)[1]','bigint') as DeltaTime,xr.value('(MemoryBroker/Pool)[1]','int') as [Pool],xr.value('(MemoryBroker/Broker)[1]','varchar(50)') as [Broker],xr.value('(MemoryBroker/Notification)[1]','varchar(50)') as [Notification],xr.value('(MemoryBroker/MemoryRatio)[1]','bigint') as MemoryRatio,xr.value('(MemoryBroker/NewTarget)[1]','bigint') as NewTarget,xr.value('(MemoryBroker/Overall)[1]','bigint') as Overall,xr.value('(MemoryBroker/Rate)[1]','bigint') as Rate,xr.value('(MemoryBroker/CurrentlyPredicted)[1]','bigint') as CurrentlyPredicted,xr.value('(MemoryBroker/CurrentlyAllocated)[1]','bigint') as CurrentlyAllocated,xr.value('(MemoryBroker/PreviouslyAllocated)[1]','bigint') as PreviouslyAllocatedfrom RingBuffer AS rbCROSS APPLY rb.xRecord.nodes('Record') record (xr)CROSS JOIN sys.dm_os_sys_info AS dosiwhere xr.value('(MemoryBroker/Broker)[1]','varchar(50)') ='MEMORYBROKER_FOR_CACHE'ORDER BY RmDateTime DESC;[/code][code="plain"]RmDateTime DeltaTime Pool Broker Notification MemoryRatio NewTarget Overall Rate CurrentlyPredicted CurrentlyAllocated PreviouslyAllocated2013-08-06 13:36:27.803 101 1 MEMORYBROKER_FOR_CACHE GROW 100 285242 367718 -96021 201660 201660 2113632013-08-06 13:36:27.697 1000 1 MEMORYBROKER_FOR_CACHE SHRINK 96 203041 367718 65 211428 211363 2113052013-08-06 13:28:51.980 114 1 MEMORYBROKER_FOR_CACHE GROW 100 283971 367718 -47803 209645 209645 2150982013-08-06 13:28:51.860 1000 1 MEMORYBROKER_FOR_CACHE SHRINK 95 204604 367718 41 215139 215098 2150662013-08-06 11:43:20.003 105 1 MEMORYBROKER_FOR_CACHE GROW 100 285564 367718 -83068 191289 191289 2000142013-08-06 11:43:19.887 1000 1 MEMORYBROKER_FOR_CACHE SHRINK 99 198201 367718 37 200051 200014 199977[/code]The above result look to me that the SQL server is regularly indicating it is low on memory. If I look in perfmon, PLE is hovering around 1500 sec almost no reads /write to physical disk which looks like there is ample memory. I do have some(~600MB) physical memory currently available. Am I correct in thinking that the SQL Server is starved for memory and should increase the max server memory?

SQL Server Change Data Capture update records but __$operation is 1, 2 Posted: 06 Aug 2013 05:41 AM PDT Hi, today I found very strange case in CDC (Change Data Capture) table, we updated one record for several different columns, but in CDC tablethe __$operation is 1 , 2 and pk(pk_placement...) is the same. Normally __$operation should be 3,4, and pk is same. (And 1,2 means delete and insert.) I am not sure what's reason cause CDC using 1 and 2, it never happened before. At that time we have index rebuilt job for that table running, is it possible reason? Thank you

Add SSRS to clustered instance Posted: 06 Aug 2013 12:03 AM PDT I have a clustered environment with 3 nodes and 2 instances on it with 1 node always as a passive one.Customer wants to use one of these instances to configure SSRS now. How would I install SSRS in this scenario. Customer doesn't want to procure a new license though.Also, some blogs say that I can install SSRS on all the nodes of the cluster and can use the clustered instance to put the DB on?In my case, can I install SSRS on all the nodes and map it to 1 DB instance. Oops..Its very confusing

Alter Database Posted: 06 Aug 2013 02:50 AM PDT Hi ,I have database with single user modenow changing to MULTI_USERi used below scriptALTER DATABASE MC_DB SET MULTI_USER;But i am getting below issue(restarted the SQL server also)[b]Msg 1205, Level 13, State 68, Line 1Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE statement failed.[/b]What can i do to change database single_user to multi_user?

SSIS 2008 Script Component error Posted: 12 May 2009 04:21 PM PDT Hi,I'm using Script component in SSIS 2008. This is the error I received when I'm done writing the script. Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent().Any idea what this error means and how can I resolve this error?Thanks

Import Wizard failing to insert with Identity Column Posted: 06 Aug 2013 03:16 AM PDT I am trying to import from one table into another with the import/export wizard...The source table has 17 columns , one of them an identity.I am using a query to insert into the target table.I exclude the id column from my query and enable identity insert but get the 'Cannot insert null into id column'.The select on the source returns 189 rows all of which have a value for the id column...Not sure what I am doing wrong.Thanks

Attach MDF that wasn't cleanly shutdown Posted: 06 Aug 2013 02:13 AM PDT Hi experts, im trying to recreate a disaster recovery scenario and simulate a drive going down that hosts the database log (ldf) file. I want to see / document the steps I'd need to perform to recover the database from the mdf file (if at all possible). The steps i've performed so far are:Create a new database 'Test'mdf file in default SQL locationldf file on a different drive (happens to be a removable pen drive)I then remove the pen drive to simulate a drive going down. So now the Test database is inaccessable right.So what I need to know is: A. Is it possible to recover this database from the remaining mdf file (assuming no full backup exists)B. If so, how?Thanks in advance

DROP TABLE in Database Mirroring environment Posted: 06 Aug 2013 02:17 AM PDT If I am mirroring Database_XYZ from Server_1 to Server_2, can I drop a table in Databse_XYZ on Server_1? And if so, will it be dynamically dropped within Server_2's mirror'd copy of the DB?

Solution sought. Advice needed. SSIS? Posted: 06 Aug 2013 01:03 AM PDT Hello all. I imagine that this is a common scenario and solutions are found all the time. However, before I start experimenting and over complicating the solution, I thought I would ask for advice.Every day, I need to export data from a few derived tables from some internal SQL Server tables and import them into a matching set of SQL Server tables which our web site runs on. Our internal SQL Server is obviously behind our firewall and does not accept external traffic or requests (such as http).We have a web hosting service which hosts our web site and provides us with a SQL Server database. Every day, I connect to both our internal DB and our external (web site) DB using SSMS. I then go through Import/Export Wizard and simply import the data FROM internal tables INTO the matching external (web site) tables.This works OK but it presents a couple problems. 1) Web site data goes down for about 60 seconds. I can reduce that to about 10 seconds but the real problem is....2) Requires a manual process. Someone actually has to do this.So my question is, can I easily automate this? I feel that SSIS services would be the way to.Anyone doing something similar?Are there any good SSIS tutorials online?Thanks!

Insert identity dynamically by using insert into Posted: 06 Aug 2013 12:18 AM PDT Code:SELECT IDENTITY(INT,1,1) AS D_Key,C_Key,D_Num,D_Name,D_AddressINTO D_Table2FROM D_Table1I need to populate a table based on results from a query. If I do: INSERT INTO table2SELECT IDENTITY(INT,1,1) AS D_Key,C_Key,D_Num,D_Name,D_AddressFROM D_Table1It gives me the error: The IDENTITY function can only be used when the SELECT statement has an INTO clause?

Change Tracking Cross DB Posted: 27 Nov 2011 10:46 PM PST I am working on migrating from Rowhash bound SSIS packages to Change Tracking SSIS packages to load my data warehouse. The ETL process is pretty standard, I pull data from our OLTP, stage the data in ETL db then load in EDW. The issue that I am running into is change_tracking_current_version(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'table')) and CHANGETABLE(CHANGES table, @last_csn) must be called from the source db. I really don't want to store my SSIS extract stored procedures on the production system. I have another db on the same box or another box alltogether that I would prefer storing them on. How can I call the CT functions cross database? change_tracking_current_version(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'table')) and CHANGETABLE(CHANGES table, @last_csn) I tried setting up a generice store procedure to call cross db that calls the functions. That worked from change_tracking_current_version(), CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'table')) but would not work for this one CHANGETABLE(CHANGES table, @last_csn) Any ideas or direction would greatly be appreciated. Below is a sample of my extract proc that works perfect on the source db. Just need to get the CT function calls to work correctly cross db. [code="other"]ALTER PROCEDURE [dbo].[SSISStageApptRepExtract] ( @last_csn INT )AS DECLARE @csn INT = change_tracking_current_version() DECLARE @min_csn INT = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'ApptRep')) SET TRANSACTION ISOLATION LEVEL SNAPSHOT ; BEGIN TRANSACTION--Make sure the last_sync_version is valid IF ( @last_csn < @min_csn ) BEGIN SELECT TOP 1 ar.LeadID , ar.ApptDate , ar.SalesRepID , ar.CreditDivisor , BINARY_CHECKSUM(ar.LeadID, ar.ApptDate, ar.SalesRepID, ar.CreditDivisor) AS RowHash , 1 AS AuditKey , ar.ApptRepID , GETDATE() AS LoadDate , 'I' AS CO , 'f' AS LoadType FROM leads.dbo.ApptRep ar WHERE ar.ApptDate >= '20000102' END ELSE BEGIN SELECT ar.LeadID , ar.ApptDate , ar.SalesRepID , ar.CreditDivisor , BINARY_CHECKSUM(ar.LeadID, ar.ApptDate, ar.SalesRepID, ar.CreditDivisor) AS RowHash , @csn AS AuditKey , t.ApptRepID , GETDATE() AS LoadDate , t.CO , 'p' AS LoadType FROM ( SELECT ct.ApptRepID , CT.SYS_CHANGE_VERSION CV , CT.SYS_CHANGE_OPERATION CO , @csn AS CSN FROM CHANGETABLE(CHANGES dbo.ApptRep, @last_csn) AS CT ) t LEFT JOIN leads.dbo.ApptRep ar ON t.ApptRepID = ar.ApptRepID WHERE ar.ApptDate >= '20000102' END[/code]ThanksBryan

Quotations around variable Posted: 05 Aug 2013 07:49 PM PDT Very simple question to somebodyt who knows declare @Databasename varchar(50)select @Databasename = (select DB_NAME() )select @DatabasenameEXECUTE master.dbo.xp_create_subdir N'W:\MSSQL10_50.LIVE802MSSQL\MSSQL\Backups\ ' + @DatabasenameI have tried a ridiculous amount of different variations of the quotations around the variable at the end but can not seem to get it right.Any help would be appreciated before i self distruct

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Export/Import query

---

• Export/Import query
• good book or webite to learn the basic on TSQL and/or SQL server
• Multiple Rows with Multiple Columns into a Single Row

Export/Import query Posted: 06 Aug 2013 05:56 AM PDT Hello all,I have two databases, db1 and db2.I am pulling data from db1 with view which I have to insert in db2 table:- if record in db2 with same field1 and field2 exists - update;- if record in db2 doesn't exist, I need to insert it.Thanks,Brano

good book or webite to learn the basic on TSQL and/or SQL server Posted: 06 Aug 2013 09:35 AM PDT Hi : I have been oracle developer for some years. Recently, I start to support SQL server using TSQL. I need a book or not too expensive online course to learn the TSQL 101. I found some material online to learn the SQL Server as database enginer or SQL server managment stuido as a tool. But I need to go back to TSQL to learn how to write a function, package... how to declare variable....etc Any suggestion?Fushan

Multiple Rows with Multiple Columns into a Single Row Posted: 06 Aug 2013 12:06 AM PDT I have a table structure as follows.id Field1 Field2-- ------ ------4 A A4 S S5 A A5 R R6 A A6 X XNow what I need isid Field1 Field2 Field3 Field4-- ------ ----- ------ ------4 A A S S5 A A R R6 A A X XThe Pivot and Crosstab are generally talking about pivoting on one column into row.Any help for the above? Thanks in advance

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] Different social network user information

---

• Different social network user information
• How does security context propagate between schemas within a database?
• PAGELOCK Conversion Deadlock From Update to Intent Exclusive
• How can lots of ram (128 GB) be used to improve MySQL 5.5 index creation?
• how to make a particular field into seperate columns?
• Fatal NI connect errors
• confused over encoding/locale in postgresql
• Inserting into mysql table with archive engine "duplicate key" error
• How do I generate a SQL script from my diagram in MySQL Workbench?
• In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index
• An error was encountered in the transport layer - SSAS 2012 from Excel 2013
• DB2 Insert and Deadlocks
• hi all i need to sum the M,X,L and C values, how can i do it? help please [on hold]
• DB2 Communication link failure issue
• When is a Dynamic Port "dynamic"?
• fighting filter (nested loops) execution plan from Oracle
• Oracle Database 10g JRE Error
• How to migrate SQL Server to MySQL
• How can I migrate a sybase database to mysql?
• SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server?
• Efficiently Filter Large Set With Disjunctions
• search in mysql with like statement
• GROUP_CONCAT with ORDER BY , but results are not ordered
• SQL Server Decimal(9, 0) vs INT
• Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring
• Practices to keep SQL Server databases easy to port to MySQL?
• How do you migrate a database from SQL Server 2008 to MySQL?
• SQL Server Cannot drop database <dbname> because it is currently in use... but no sessions displayed
• How do I list all databases and tables using psql?

Different social network user information Posted: 06 Aug 2013 07:17 PM PDT I am designing a website with social network login from various sites. The requirement is to allow the easy addition to the list of which social network login methods are supported. If I want to store information about users from the social networks they are logging in from in a relational database such as MySQL, this will be different from social network to social network. My question is: should I set up my relational database as follows: Table Name | Column Names ------------------------------------ users | id, name, dob, username user_social_network | user_id social_network_id social_network | id, social_network_name facebook | user_id, (information about facebook i.e. profile pic, interests) twitter | user_id, (information about twitter) google_plus | user_id, (information about google plus) Although I am quite new to this, this seems to me that I will need to do two DB queries: 1. Find out what social network the user uses 2. Query the desired table for social network information I have read that using joins is much more efficient than sending multiple queries. Is there any other way to lay this out or is this the best way? Any help will be much appreciated! Thanks in advance.

How does security context propagate between schemas within a database? Posted: 06 Aug 2013 06:49 PM PDT We have a SQL Server 2008 database that contains two schema's. The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership. I have created a user ID and have granted it access to only SQL Views created in both of these schema's but not the underlying tables. In the example below I've created two views, one in each schema, that select data from one table in one of the schema's. It works if I execute the View as my new user in the schema that the same table exists in, but not if I try from the other schema. However if I login as the schema owner it work regardless of the schema I execute the view from. How does security propagate between schema's within a single database? Here is a rough example of what I am trying to do, please let me know if I can explain this better, as for Im not remotely close to a dba. USE [DEV_DATABASE] -- Create tables and insert dummy data CREATE TABLE [DEV_DATABASE].[TESTDATA].[UserData] ( COL1 CHAR(10), COL2 CHAR(10), COL3 CHAR(10), COL4 CHAR(10), COL5 CHAR(10) ); INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('1','2','3','4',5'); INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('11','22','33','44',55'); CREATE TABLE [DEV_DATABASE].[TESTCTRL].[ControlData] ( CTRL1 CHAR(10), CTRL2 CHAR(10), CTRL3 CHAR(10), CTRL4 CHAR(10), CTRL5 CHAR(10) ); INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('a','b','c','d',e'); INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('aa','bb','cc','dd',ee'); -- Create views CREATE VIEW [TESTDATA].[DATA_TEST_VIEW] AS SELECT COL1, COL2, COL3 FROM [TESTDATA].[UserData] CREATE VIEW [TESTCTRL].[CTRL_TEST_VIEW] AS SELECT COL1, COL2, COL3 FROM [TESTDATA].[UserData] -- permissions DENY SELECT ON [TESTDATA].[UserData] to JOEUSER; GRANT SELECT ON [TESTDATA].[DATA_TEST_VIEW] to JOEUSER; GRANT SELECT ON [TESTCRL].[CTRL_TEST_VIEW] to JOEUSER; EXECUTE AS USER = 'JOEUSER'; -- tests SELECT * FROM [TESTDATA].[UserData]; -- fails as expected SELECT * FROM [TESTDATA].[DATA_TEST_VIEW]; -- passes as expected SELECT * FROM [TESTCRL].[CTRL_TEST_VIEW]; -- fails, and should pass? REVERT; Error message received: Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.

PAGELOCK Conversion Deadlock From Update to Intent Exclusive Posted: 06 Aug 2013 03:23 PM PDT A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a page, and both try to up-convert the U page lock to an intent exclusive (IX) lock. Sometimes there is only one page involved; sometimes several. We captured a deadlock trace using the trace flag 1222. The SQL Server log shows many, many deadlocks with the following pattern (in bottom-to-top order): waiter id=processe0dc2088 mode=U requestType=convert waiter id=process2f9db2478 mode=U requestType=convert waiter-list owner id=processe0dc2088 mode=IX owner id=process2f9db2478 mode=IX owner-list pagelock fileid=1 pageid=5794 dbid=2 objectname=tempdb.dbo.Item_Package_Site_Costs_Work id=lock1b22de480 mode=IX associatedObjectId=72057594131775488 resource-list Both processes are running the same UPDATE statement to set a flag on this tempdb table. This tempdb table holds information that needs to persist between client calls until the client is done. The table has a fairly long index that starts with a GUID representing a unique process ID. I am having difficulty understanding and simulating this deadlock. I have tried various amounts of records with simulated data. My questions: Why are these processes acquiring U locks and then converting to IX? I would expect the DELETE to acquire IX locks to begin with. How can I prevent the deadlock? The statement causing the deadlock is below. The process has just done a lookup of costs for a list of items at a single store. It is trying to note that there was a cost found. Note that there is a deprecated (NOLOCK) on an UPDATE table. Would this be a contributing factor? UPDATE tempdb..Item_Package_Site_Costs_Work SET ItemPkgSiteCost_VINCostFound = 1, ItemPkgSiteCost_VendCost_Key = SiteCosts_VendCost_Key FROM tempdb..Item_Package_Site_Costs_Work (NOLOCK) INNER JOIN #SiteCosts ON ItemPkgSiteCost_GUID = @ProcGUID AND SiteCosts_Item_Type = 0 -- Standard AND ItemPkgSiteCost_Site_Key = SiteCosts_Input_Site_Key AND ItemPkgSiteCost_Item_Key = SiteCosts_Item_Key AND ItemPkgSiteCost_ItemPkg_Key = SiteCosts_Input_Sel_ItemPkg_Key AND ItemPkgSiteCost_VendItem_Key = SiteCosts_VendItem_Key AND ISNULL(ItemPkgSiteCost_Qty_Recv, 1) = SiteCosts_Input_Qty_Recv The customer's server @@version is: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) So far I have not been able to capture the query plan used at the time of the deadlock, and the normal ways I try to retrieve the query plan are not returning anything(sys.dm_exec_query_plan and sys.dm_exec_text_query_plan both return NULL).

How can lots of ram (128 GB) be used to improve MySQL 5.5 index creation? Posted: 06 Aug 2013 02:11 PM PDT This is similar to another post of mine, but now I have a different table structure, and it's still unclear what MySQL parameters should be changed to take advantage of the extra ram my machine has---so if it seems like a duplicate, let me know how best to rephrase what I'm asking. I have the following table: create table mytable (id1 int, id2 int, score float) engine MyISAM, with 50 billion records. I plan to add an index on the first column (alter table mytable add index myindex (id1)), and I am wondering what MySQL parameters could be changed to take advantage of this extra memory (e.g. buffers?)? Note: the engine type does not need to be MyISAM if that makes a difference.

how to make a particular field into seperate columns? Posted: 06 Aug 2013 03:17 PM PDT Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production. I have this table in the below format: No | User | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want it: No | Port | Vip1 01 | Funds | Systems 02 | Bank | Authority Now the problem is, in this table, the User column has 6 other entries besides Port and Vip1. So I would want 6 columns and their respective values. I also want a query that I can use in other similar tables that have different User column entries. This is what I was trying to do without any success: SELECT No, CASE user WHEN 'Port' THEN Value ELSE NULL END AS Port, CASE user WHEN 'Vip1' THEN Value ELSE NULL END AS Vip1 FROM table1 Please let me know what you think.

Fatal NI connect errors Posted: 06 Aug 2013 01:43 PM PDT I'm wondering if there are any auditing processes I can enable to see what is causing these errors, like sql statements being run, or any other metadata that I could use to track down the source of these errors? I am aware of DBA_AUDIT_TRAIL (which uses SYS.AUD$), but it isn't giving me what I need. Fine Grained Audit Trail may be what I need? Not sure.... Here are my auditing settings for your review, anything I can change here to give me more to work with? SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ----------------------------------- audit_file_dest string /01/daa01/oracle/db/admin/DAA/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB

confused over encoding/locale in postgresql Posted: 06 Aug 2013 01:18 PM PDT I am a little confused over the difference between encoding and locale as it pertains to the postgresql database. When initializing the database, you can specify both an encoding and a locale. Correct me if I am wrong but I assume the encoding defines what the database is actually stored as on the computer. So if I specify UTF8, all the characters in the UTF8 character set would be valid characters. If I specified WIN1252, all those characters would be valid characters. What I dont get is where the locale comes into play. If I specify my encoding as UTF8 and then specify my locale as English_United States.1252, what does that exactly mean? I think the WIN1252 character set is a subset of UTF8 so is the locale just specifying the subset of characters to be used from the UTF8 character set? From what I have read is that UTF8 can be used with ANY locale so what is the point in specifying different encodings if UTF8 is so ubiquitous and the locale is really the one specifying the specific character set to be used? Also, on Linux, the locale can be specified like so: en_us.utf8. So the database encoding is specified in the locale? If the encoding is specified in the locale, why even have a -encoding flag when initializing the database?

Inserting into mysql table with archive engine "duplicate key" error Posted: 06 Aug 2013 02:20 PM PDT I am trying to archive some files using mysql archive engine. I am using this query to insert file contents: insert into test_table (id,arch) values (123,'FILE_CONTENT') After inserting 2 records, I get "duplicate key" error from mysql for a key that doesn't exist in the table. I checked it like 5 times but the record is not there. I selected count(*) for the duplicate id and the result is 0. I checked the same code with an innodb engine and it works fine. Can anyone tell me what the problem is with the archive engine? CREATE TABLE `test_table` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `arch` mediumtext, PRIMARY KEY (`id`) ) ENGINE=ARCHIVE AUTO_INCREMENT=100175977 DEFAULT CHARSET=utf8mb4;

How do I generate a SQL script from my diagram in MySQL Workbench? Posted: 06 Aug 2013 12:36 PM PDT I've created an EER diagram with tables, foreign keys, etc in MySQL Workbench and now I want to generate a ".sql" script that will create this database. How do I do that?

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index Posted: 06 Aug 2013 02:40 PM PDT Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is there any difference(space, performance, memory) between having B-Tree index on data_id and UNIQUE index on it? (besides the case when InnoDB is used as storage engine and UNIQUE index can be used as clustering key so row_id is uneccesary)

An error was encountered in the transport layer - SSAS 2012 from Excel 2013 Posted: 06 Aug 2013 12:25 PM PDT When connecting to an SSAS 2012 tabular model from Excel 2013 (Data... From Other Sources... From Analysis Services... fill in Server Name... click Next), I get the error: An error was encountered in the transport layer. Excel, SQL Server, and SSAS are all running on the same server (Windows Server 2012). Because the server needs to be accessed from the Internet, there is a DNS entry for the server x.mydomain.com but the server's actual Windows computer name is something else servername. In the Excel (on the server's desktop) connection dialog, if I use servername it works without error. If I use x.mydomain.com I get the above error. I have added a hosts file entry so that x.mydomain.com points to "::1". From the server, pinging x.mydomain.com or servername both return the same "::1" for the address. This did not help the problem.

DB2 Insert and Deadlocks Posted: 06 Aug 2013 06:11 PM PDT My application has 4 different threads doing inserts into the same DB2 table simultaneously. Could this cause a deadlock / lockwait situtation? Theoretically I don't see a problem here; as one insert statement is not going to lock a row required by another insert statement because all are independent insert statements. Also, I commit after each insert so transaction log wouldn't fill up. I'm looking for a high performance model while avoiding any potential lock situtation. Please provide your thoughts.

hi all i need to sum the M,X,L and C values, how can i do it? help please [on hold] Posted: 06 Aug 2013 11:31 AM PDT declare @CodSafra int = 1213 --Safra actual declare @CodVar int = 4 declare @KgsNPK int = 200 --Kilos de fumo produzidos por cada saco de NPK declare @PesHectar int = 18000 --Numero de plantas de fumo por hectare declare @NrSacosHectar int = 6 --Numero de Sacos de NPK Por Hectare (6 sacos estao para 1 hectare) declare @KgsHectar int = @KgsNPK * @NrSacosHectar --Kilos Produzidos por hectar SELECT DISTINCT rp.CodZona ,rp.NomeZona ,rp.CodInstr ,rp.NomeInstr ,rp.CodProd ,rp.NomeProd ,rp.CodVar ,rp.NomeVar ,0 NrSacosNPKDist ,0 NrSacosCANDist ,0 NrSacosUREIADist ,0 NrSacosNPKDev ,0 NrSacosCANDev ,0 NrSacosUREIADev ,0 NrPlantas ,0 KgsSold ,0 KgHa ,0 MediumPrice ,0 OSCredit ,0 BalesSold ,0 Sell ,sum(fc.PesoFardo) X ,sum(fc.PesoFardo) M ,sum(fc.PesoFardo) C ,sum(fc.PesoFardo) L FROM (select distinct z.CodZona,z.NomeZona,i.CodInstr,i.NomeInstr,p.CodProd,p.NomeProd, e.CodVar,e.NrPlantas,vf.NomeVar,pc.CodSafra from FProdutor p, FProdClube pc, FZona z, FZonaClube zc, FZonaInstrutor zi, FInstrutor i, FEstimativa e, FVariedadeFumo vf where p.CodProd = pc.CodProd and p.CodProd = e.CodProd and pc.CodClube = zc.CodClube and zc.CodZona = z.CodZona and z.CodZona = zi.CodZona and e.CodVar = vf.CodVar and e.CodVar = @CodVar and zi.CodInstr = i.CodInstr and pc.CodSafra = @CodSafra and zc.CodSafra = @CodSafra and p.Status = 'A' and p.TipoRegProd = 'P' and e.CodSafra = @CodSafra and zi.CodSafra = @CodSafra) as rp join FFardosCompra fc on rp.CodProd = fc.CodProd and fc.CodSafra = @CodSafra join FClasseCompra cc on fc.CodClasseCompra = cc.CodClasseCompra and cc.NomeClasseCompra in ('FX1FO','FX2FO','FX3FO' ,'FX4FO','FX5FR','FX1F','FX2F','FX3F','FXJ','FXK','FXV','FND','FNDM') GROUP BY rp.CodZona ,rp.NomeZona ,rp.CodInstr ,rp.NomeInstr ,rp.CodProd ,rp.NomeProd ,rp.CodVar ,rp.NomeVar

DB2 Communication link failure issue Posted: 06 Aug 2013 12:15 PM PDT I would like to get your valuable suggestions for the below problem. My Perl application connects to a DB2 database and keeps inserting data into a table. The application successfully inserts data for a while and after sometime I start seeing the below Error 1. Error 1: [IBM][CLI Driver] CLI0108E Communication link failure. SQLSTATE=40003 Error 2: [IBM][CLI Driver] CLI0106E Connection is closed. SQLSTATE=08003 Error 1 occurs only once, after that Error 2 occurs repeatedly till I restart the application and establish new connection to the database. It's been confirmed that the database is running healthy and able to accept connections and even the network connections are healthy. Background on Error 1: Error "CLI0108E" Suggests that "The connection between the driver and the data source failed during execution of this function." And "SQLSTATE=40003" suggests that "The statement completion is unknown." Is it possible that something going wrong in an insert statement actually closes the connection? Or leaves the connection stateless? This actually breaks my Perl application. What are the best options to handle this situation?

When is a Dynamic Port "dynamic"? Posted: 06 Aug 2013 12:01 PM PDT I was having a discussion today about Dynamic Ports with one of my co-workers and could use some help clarifying how they work. First question: If the IPALL TCP Dynmaic Ports setting is a specific number (say 1971) does that signify that you have a static port of 1971 or a dynamic port that is currently 1971 and may change at some point in the future. Second question: This is the one I'm most curious about. We have an instance that has had the same port (the value in the IPALL TCP Dynmaic Ports setting) for several years through dozens of instance restarts. What actually causes the dynamic port to actually change after an instance restart?

fighting filter (nested loops) execution plan from Oracle Posted: 06 Aug 2013 02:30 PM PDT My query is: SELECT category, count(*) FROM large_data_table t WHERE (t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 1)) OR (t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 2)) GROUP BY GROUPING SETS ((category), ()); The resulting execution plan is like this: SELECT STATEMENT SORT (GROUP BY ROLLUP) FILTER large_data_table TABLE ACCESS (FULL) SMALL_TABLE TABLE ACCESS (BY INDEX ROWID) SMALL_TABLE INDEX (RANGE SCAN) SMALL_TABLE TABLE ACCESS (BY INDEX ROWID) SMALL_TABLE INDEX (RANGE SCAN) Where FILTER is, essentially, NESTED LOOP JOIN which never finishes as NESTED LOOPS often do on large tables. (the two subqueries on the small table return, say, 100 rows each). I am looking for a way to tell Oracle to NOT use the stupid FILTER here. I've tried sticking USE_HASH hint everywhere including some places I'd rather not talk about now.... GROUPING SETS seem to play a role in this, but getting rid of them is very difficult here. The one reasonable thing that works is to replace OR with a UNION, but it doesn't cover all cases either.

Oracle Database 10g JRE Error Posted: 06 Aug 2013 12:03 PM PDT I am trying to install Oracle Database 10.2 on OEL5U6(Linux System). I followed all the instructions from HERE. When I am executing installer this return: Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-08-06_12-57-33AM. Please wait ...The Java RunTime Environment was not found at /tmp/OraInstall2013-08-06_12-57-33AM/jre/bin/java. Hence, the Oracle Universal Installer cannot be run. Please visit http://www.javasoft.com and install JRE version 1.3.1 or higher and try again. : No such file or directory. But Java is already installed on my system.

How to migrate SQL Server to MySQL Posted: 06 Aug 2013 01:49 PM PDT I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried: MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables themselves, and so I can't get past the "Schemata Selection" screen. sqlcmd and bcp -- both fail because they can't export csv properly. They don't quote strings with commas in them. Plus, they don't create table schemas in MySQL. SQLyog -- just fails. Creates a MySQL table from a MS SQL view ok, but doesn't import the data. Also takes a really long time on small tables, and I have to import millions of rows. Anyone have suggestions?

How can I migrate a sybase database to mysql? Posted: 06 Aug 2013 01:38 PM PDT I've been tasked with migrating a fairly complex legacy Sybase database into a MySQL database and I have no idea where to start. I can't use any off the shelf software, so I'm going to have to write some sort of script that takes all the info out of the Sybase database and puts it into MySQL. Does anyone have any resources or thoughts on how to do this quickly, as I have very little experience with Sybase or MySQL.

SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server? Posted: 06 Aug 2013 01:54 PM PDT I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help. -------Edit - Answered------- This issue has now been resolved. I used Remus Rusanu's suggestion for finding the rows with these surrogate pair characters using CHARINDEX and have decided to use SUBSTRING to exclude the troublesome characters like so: UPDATE test SET a = SUBSTRING(a, 1, (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 - 1) -- string before the unwanted character + SUBSTRING(a, (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 +1, LEN(a) ) -- string after the unwanted character WHERE CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000))) % 2 = 1 -- only odd numbered charindexes (to signify match at beginning of byte pair character) ----------------------------- SQL Server source database has latin1 collation (so has ISO 8859-1 character set right?) but doesn't have any char/varchar fields (any string field is nvarchar/nchar) so all this data should be using the UCS-2 character set. MySQL target database wants the character set UTF-8. I decided to use the database migration toolkit in the latest version of the MySQL workbench. at first it worked fine and migrated everything as expected. But I have been totally tripped up upon encountering UCS-2 surrogate pair characters in the SQL Server database. The migration toolkit copytable program did not provide a very useful error message: Error during charset conversion of wstring: No error. It also did not provide any field/row information on the problem-causing data and would fail within chunks of 100 rows. So after searching through the 100 rows after the last successful insert I found that the issue seemed to be caused by two UCS-2 characters in one of the nvarchar fields. They are listed as surrogate pairs in the UCS-2 character set. They were specifically the characters DBC0 and DC83 (I got this by looking at the binary data for the field and comparing byte pairs (little endian) with data that was being migrated successfully). When this surrogate pair was removed from the SQL Server database the row was migrated successfully to MySQL. Here is the problem: I have tried to search for these characters in a test SQL Server table (this chartest table is just various test strings an nvarchar field) to prepare a replacement script and keep getting strange results... I must be doing something incorrectly. Searching for SELECT * FROM chartest WHERE text LIKE NCHAR(0xdc83) Will return any surrogate pair character (whether or not it uses DC83), but obviously, only if it is the only character (or part of the pair) in that field. This isn't a big deal since I would like to remove any instance of these anyway (I dont like to remove data like this but I think we can afford it). Searching for SELECT * FROM chartest WHERE text LIKE '%' + NCHAR(0xdc83) + '%' Will return every row! Regardless of whether it even has a unicode character present in the field let alone the DC83 character. Is there a better way to find and replace these characters? Or something else I should try? I have also tried setting the target databse, table, and field character set to UCS-2 but it seems as though it does not make a difference. I should also mention that this migration is using live data (~50GB database!) while one of the sites that feeds it is taken offline so any solutions to this need to have a quick running time... I would appreciate any suggestions very much! Please let me know if there is any information I have left out.

Efficiently Filter Large Set With Disjunctions Posted: 06 Aug 2013 08:21 PM PDT Let's say I have a single table CREATE TABLE Ticket ( TicketId int NOT NULL, InsertDateTime datetime NOT NULL, SiteId int NOT NULL, StatusId tinyint NOT NULL, AssignedId int NULL, ReportedById int NOT NULL, CategoryId int NULL ); In this example TicketId is the Primary Key. I want users to be able to create "partially ad-hoc" queries against this table. I say partially because a few parts of the query will always fixed: The query will always perform a range filter on an InsertDateTime The query will always ORDER BY InsertDateTime DESC The query will page results The user can optionally filter on any of the other columns. They can filter on none, one, or many. And for each column the user may select from a set of values which will be applied as a disjunction. For example: SELECT TicketId FROM ( SELECT TicketId, ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum FROM Ticket WHERE InsertDateTime >= '2013-01-01' AND InsertDateTime < '2013-02-01' AND StatusId IN (1,2,3) AND (CategoryId IN (10,11) OR CategoryId IS NULL) ) _ WHERE RowNum BETWEEN 1 AND 100; Now assume the table has 100,000,000 rows. The best I can come up with is a covering index that includes each of the "optional" columns: CREATE NONCLUSTERED INDEX IX_Ticket_Covering ON Ticket ( InsertDateTime DESC ) INCLUDE ( SiteId, StatusId, AssignedId, ReportedById, CategoryId ); This gives me a query plan as follows: SELECT Filter Top Sequence Project (Compute Scalar) Segment Index Seek It seems pretty good. About 80%-90% of the cost comes from the Index Seek operation, which is ideal. Are there better strategies for implementing this kind of searching? I don't necessarily want to offload the optional filtering to the client because in some cases the result set from the "fixed" part could be 100s or 1000s. The client would then also be responsible for sorting and paging which might too much work for the client.

search in mysql with like statement Posted: 06 Aug 2013 12:17 PM PDT Imagine I have this table named test_table in the test_db in MySQL: field1 field2 google http://google.com apple http://apple.com python http://python.org Now if I search the database using this: SELECT * FROM test_table WHERE field1 = "google"; It will return : http://google.com Now I want to search the database with field1 and return field2 value where field1 like to search keyword. For example if I give it googles it should return : http://google.com How can I do this?

GROUP_CONCAT with ORDER BY , but results are not ordered Posted: 06 Aug 2013 01:05 PM PDT The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT 0,5 but the images column are not ordered as priority. I am unable to understand why it is happening Structure for table project CREATE TABLE `projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `catID` int(11) NOT NULL, `title` varchar(255) NOT NULL, `description` varchar(400) NOT NULL, `url` varchar(255) DEFAULT NULL, `tags` varchar(255) DEFAULT NULL, `featured` varchar(3) NOT NULL DEFAULT 'No', `featured_url` varchar(255) DEFAULT NULL, `order` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `catID` (`catID`), CONSTRAINT `FK_catID` FOREIGN KEY (`catID`) REFERENCES `category` (`catID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1; Structure for table project_photos CREATE TABLE `project_photos` ( `id` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(250) DEFAULT NULL, `project_id` int(11) DEFAULT NULL, `priority` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;

SQL Server Decimal(9, 0) vs INT Posted: 06 Aug 2013 08:52 PM PDT One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to DECIMAL(5,0) to regain some storage. According to the MSDN library, the storage space of the DECIMAL(5,0) datatype is, just like the DECIMAL(9,0) datatype, 5 bytes. INT is 1 byte smaller, but can store everything in the range of -2^31 to 2^31 instead of the -99,999 to 99,999 which DECIMAL(5,0) can store. Even the largest DECIMAL which fits into 5 bytes (DECIMAL(9,0)) can store only integers in the range -999,999,999 to 999,999,999 (which is less than half of the range INT offers in 4 bytes). I can think of two "benefits" of using DECIMAL over INT: The ability to add scale afterwards, without using more storage space The ability to scale the precision up to 38 digits, without altering data type but these aren't real benefits in my opinion: Adding scale to integers does only make sense in very few cases (in most cases where scale does make a difference, it could also be added beforehand) SQL Server sees every precision / scale combination as a different data type, so the datatype isn't left alone when increasing the precision or scale. This makes me wonder: what is the added benefit of a DECIMAL(5,0) datatype for integers?

Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring Posted: 06 Aug 2013 01:44 PM PDT I have gigabytes of tabular data stored in a SQL Server database. I've been trying to migrate it to MySQL using the Migration Toolkit from Oracle. I didn't have any mapping errors and fixed all the syntax mismatches, so now the rows are being transferred. But when I go to PHPMyAdmin and try to browse, it keeps saying MySQL returned an empty result set (i.e. zero rows). ( Query took 17.0116 sec ) although it says there are 44,546,089 rows in that table, and I can see the hard drive space filling up too. I just can't access any of the data. I researched a bit and increased the buffer size from 16M to 160M in my.conf. But I can't fix this problem. Some things I noticed (red marked in the status page): Innodb_buffer_pool_pages_dirty: 376 Innodb_buffer_pool_reads = 92 k Handler_read_rnd = 41 Handler_read_rnd_next = 59 k Any advice? This database will be read-only. Also the server specs: dedicated, 8GB RAM, Debian 6, 500GB HDD, ~2.8GHz Quadcore Intel

Practices to keep SQL Server databases easy to port to MySQL? Posted: 06 Aug 2013 01:46 PM PDT We've developing an ASP.NET Web application that is to be designed for MS SQL Server 2008. I'd like to minimize my future dB admin's headaches should there be a need to migrating over to MySQL. With that goal, are there any specific design patterns or features we should avoid on MS SQL? Issues that trip over the MS SQL -> MySQL migration tools?

How do you migrate a database from SQL Server 2008 to MySQL? Posted: 06 Aug 2013 01:41 PM PDT I'm just getting into MySQL, so forgive me if this is a basic question. For my application I need to migrate my database from SQL Server 2008 R2 to MySQL. Is there any mechanism or technique or suggestion or tips or magic tricks for migration?

SQL Server Cannot drop database <dbname> because it is currently in use... but no sessions displayed Posted: 06 Aug 2013 12:08 PM PDT When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. I've also set the database to single_user mode with rollback immediate. Why is this happening?

How do I list all databases and tables using psql? Posted: 06 Aug 2013 12:14 PM PDT I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610