Wednesday, August 7, 2013

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

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

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
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog