Wednesday, July 10, 2013

[SQL Server 2008 issues] Procedure or function not working correctly

[SQL Server 2008 issues] Procedure or function not working correctly

Procedure or function not working correctly

Posted: 09 Jul 2013 04:22 PM PDT

Hi ProfessionalsI have a procedure which calls a function, the function checks to see if the 3 columns exist, if they do then run a query, if they dont then run a different query.The problem I am having is no matter what I change the passed in 3 values too it always runs the first part within my procedure which is the = 'Y' partam i missing somethingcode below and thanks in advance[code]Alter procedure [dbo].[checkcolumnsexists]ASBEGIN if dbo.ColumnExists('SOFTWARE_MANUFACTURER','PRODUCT_NAME','PRODUCT_VERSION') = 'Y' BEGIN select software_manufacturer,product_name,product_version from dbo.newtable; END else select * from dbo.newtable; END[/code][code]ALTER FUNCTION [dbo].[ColumnExists](@SMcol varchar(100),@PNcol varchar(100),@PVcol varchar(100))RETURNS varchar(1) ASBEGINDECLARE @Result varchar(1);IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE UPPER(TABLE_NAME) = 'NEWTABLE' AND UPPER(COLUMN_NAME) in (@SMcol,@PNcol,@PVcol)) BEGIN SET @Result = 'Y' ENDELSE BEGIN SET @Result = 'N' END RETURN @Result;END[/code]

Need a Subquery to delete the previous dates

Posted: 09 Jul 2013 06:16 PM PDT

I have created one table like belowcreate table Verification(id int,date datetime)inserted values likeinsert into Verification values(1,'2013-07-10 03:12:05.397')insert into Verification values(1,'2013-07-09 03:12:05.397')insert into Verification values(1,'2013-07-08 03:12:05.397')insert into Verification values(2,'2013-07-10 03:12:05.397')insert into Verification values(2,'2013-07-09 03:12:05.397')insert into Verification values(2,'2013-07-08 03:12:05.397')insert into Verification values(3,'2013-07-10 03:12:05.397')insert into Verification values(3,'2013-07-09 03:12:05.397')insert into Verification values(3,'2013-07-08 03:12:05.397')I want to delete all the records except for the id who's have the max date.output record should be like this.ID date 1 2013-07-10 03:12:05.397'2 2013-07-10 03:12:05.397'3 2013-07-10 03:12:05.397'

List filegroups in partition scheme

Posted: 09 Jul 2013 05:25 AM PDT

Hi all,I am getting the following error when I try to split a partition function, but I can't find any way to get list of filegroups in the scheme. Anyone have any suggestions or better yet any script? Thanks in advance.The associated partition function MyPartitionFunction generates more partitions than there are file groups mentioned in the scheme 'MyPartitionScheme'

Log Shipping and the very last Tran Log backup

Posted: 09 Jul 2013 07:36 AM PDT

I hope this is not a silly question, I will first give some background...I am heading a project to migrate databases from one data center to another data center that are geographically not close to each other. Instead of copy a large database file across a WAN the day of, I would like to get the work done ahead of time with Log Shipping and on the day of backup my last tran log, restore it to the new server, and then recover the databases. On the primary server, I plan to make sure that no one is connected and there are no running transactions using sp_who2, DBCC opentran, or any other method someone would like to suggest.So my question is, how can I be 100% for sure that I in fact have the last Tran Log backup so that I can restore it and recover the databases on the server?Like I mentioned previously, I hope this is not a silly a question and I do appreciate any and all feedback.Thank You

Hostname is a dot in sp_who2

Posted: 09 Jul 2013 06:52 AM PDT

Hi,Does anyone know what it means when one executes sp_who2, and some of the processes show a dot (.) for the host name but there is a login listed as well as other processing data (database name, command, CPU time, etc.)? I'm trying to figure out where that connection is coming from.Thanks for any help!- webrunner

Error when trying to compress the backup

Posted: 09 Jul 2013 05:16 AM PDT

Hi all,I am getting error when trying to backup with compression:'There is insufficient system memory in resource pool 'internal' to run this query. (Microsoft SQL Server, Error: 701)'.No problem if i backup without compression.SQL server 2008 R2 SP1,STD Edition,total_physical_memory_kb 4193368available_physical_memory_kb 1042748total_page_file_kb 8619144available_page_file_kb 4950160system_memory_state_desc Available physical memory is highAny ideas ?Thank you

Cannot scan a remote server with SQL Server BPA 1.0

Posted: 20 Mar 2013 05:34 PM PDT

Hello gentsI am using Microsoft SQL Server 2008 R2 BPA 1.0 wrapped in Microsoft Baseline Configuration Analyzer 2.0 on my workstation and can perform normal scan on my local sql server instance. However, when I tried it to connect to a remote server, it kept reporting the following error:Category: PrerequisiteSource: <servernmae>Issue: 1. User is not a member of Administrators group on remote machine, OR 2. Powershell Remoting is not Enabled on Remote ServerImpact: Analysis cannot be performedResolution: 1. Add user as a member of Administrators group, OR 2. Run Commands Enable-PSRemoting -f through PowerShell command prompt using elevated privileges, AND 3. Run Command winrm set winrm/config/winrs `@`{MaxShellsPerUser=`"10`"`} through PowerShell command prompt using elevated privilegesI've verfied all three pre-requisites (being a local admin for my own AD account, executed the ps commands with elevated privileges) listed above and also turned off Windows Firewall on this target server but still have no luck at all.Do you have any other directions to point out for me?Thanks in anticipation!P.S. The target server is not clustered, just a standalone physical box; both my worksation and the server are in the same domain; my AD account has been explicitly added to local windows admin group and sysadmin role on the server and its hosted sql instance.

install SQL cluster

Posted: 09 Jul 2013 07:19 AM PDT

Hi Folks,i need assistance regarding installing SQL cluster.i need comprehensive step by step guide.

Partitioning a table with primary key

Posted: 09 Jul 2013 05:20 AM PDT

We have a table that we are going to recreate on partition scheme. Its clustered index is a primary key. 1. Can I just drop index without dropping the primary key ?2. In case if the above is impossible, what the best way to overcome the problem with foreign keys created on other tables from our primary key ?Should I drop them and re-create ? I tried to disable, but it does not work.In a short, what is the best practice and sequence of steps in partitioning a table ?Note: we already have partition function, scheme, filegroups. And some other tables are also partitioned.Thanks

Sorting based on user input..

Posted: 09 Jul 2013 03:29 AM PDT

Hi, I have one requirement and need help.I have a table:CREATE TABLE MiscItems(ID INT, Description VARCHAR(50), DisplayOrder SMALLINT)INSERT INTO MiscItems(ID,Description,DisplayOrder)SELECT 100, 'A1',1UNION ALLSELECT 101,'A2',2UNION ALLSELECT 102,'A3',3UNION ALLSELECT 104,'A4',4UNION ALLSELECT 105,'A5', 5UNION ALLSELECT 106,'A6',6SELECT * FROM MiscItemsDROP TABLE MiscItemsI will display in UI: Description & Displayorder and both are editable in UI.My requirement is User can modify DisplayOrder of his interest and clicks on save, then accordingly the display order should be saved in table.User can change multiple items at once and accordingly data should be re arranged and saved in table.Display order will not be a zero & non negative value.1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,62) Now, Change the existing Order of A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 23)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6I have below proc for updatimg display order:CREATE PROCEDURE proc_Disporder @ID INT, @OldDisplayOrder INT, @NewDisplayOrder INTASBEGIN IF(@OldDisplayOrder >= @NewDisplayOrder) OR (@OldDisplayOrder = 0) BEGIN UPDATE miscitem SET DisplayOrder = DisplayOrder + (CASE WHEN @OldDisplayOrder = @NewDisplayOrder THEN 1 ELSE 2 END) FROM MiscItems miscitem WHERE ID <> @ID AND miscitem.DisplayOrder >= @NewDisplayOrder AND miscitem.DisplayOrder > 0 END IF(@OldDisplayOrder < @NewDisplayOrder) AND(@OldDisplayOrder <> 0) BEGIN UPDATE miscitem SET DisplayOrder = (CASE (DisplayOrder - 1) WHEN 0 THEN DisplayOrder ELSE (DisplayOrder - 1) END) FROM MiscItems miscitem WHERE ID <> @ID AND miscitem.DisplayOrder <= @NewDisplayOrder AND miscitem.DisplayOrder >= @NewDisplayOrder - 1 AND miscitem.DisplayOrder > 0 END DECLARE @MiscItems TABLE(DisplayOrder INT IDENTITY(1,1), ID INT) INSERT @MiscItems SELECT ID FROM MiscItems WHERE DisplayOrder > 0 ORDER BY DisplayOrder UPDATE src SET src.DisplayOrder = mi.DisplayOrder FROM MiscItems src INNER JOIN @MiscItems mi ON src.ID = mi.ID END

SqlServer 2008 Processor Affinity

Posted: 02 Jul 2013 12:51 AM PDT

Hello All ... I am looking for some advice on CPU affinity settings in SqlServer 2008. I have a 2 node 3 instance cluster with 16 cpu's and 104 GB's of ram. The OS is 2008 Enterprise.The current setup is:Instance 1 Processor: 0-7IO : 8-15Instance 2 Processor: 8-15IO : 0-7Instance 3Processor: 0-3,8-11IO : 4-7,12-15Currently Instance 1 and Instance 3 are sharing a cluster node. I do see some spikes in CPU activity but those are generally focused during maintenance tasks, so that can be expected. Does anyone know of any best practices or any other recommendations?

CDC Cleanup Process Question....

Posted: 09 Jul 2013 03:14 AM PDT

I recently inherited a database that has CDC enabled. There is a custom cleanup process that was written to only remove the records where the operation = 1 or 2, since it appears they wanted to only keep the before and after update statements for reporting.Here is one of the many problems I face with this....If I go ahead and turn on the cleanup agent (sys.sp_MScdc_cleanup_job) with a 10 year retention, it removes records from within this timespan.This table starts out with 5 million records dating back to Nov 2011, then this cleanup job removed 2 million of them which included Dec 2011 and various times for 2012.I did find out that the database was placed into simple mode on various occasions to shrink the transaction log since there was no DBA in house at the time. :w00t:Could this be a matter of something being out of sorts within the database when it tries to go back and determine the high and low watermark for the records it thinks it needs to remove? I guess I don't have a clear understanding of how it determines what records to purge from these tables, if I have a 10 year retention set, I would have assumed that the records would have remained untouched. But I know what happens when you assume something... :-)

Minimum rights required to add users to a DB

Posted: 09 Jul 2013 02:22 AM PDT

Hi AllWe run both SQL 2008/2012 but this question stands for both versions.I would like to delegate permissions to our helpdesk so that they can add new (SQL authenticated) user accounts to a particular DB then assign the db_datareader & db_datawriter roles to the account.So, in Management Studio I have created a SQL login for the Helpdesk AD group and assigned the securityadmin server role, then gone to the relevant DB and assigned the db_securityadmin and db_access admin roles for this group.Problem is that when the Helpdesk try to create a new user for this DB they can create a server login but get the following error when assigning DB roles to that user:[i]TITLE: Microsoft SQL Server Management Studio------------------------------Add member failed for DatabaseRole 'db_datareader'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------[/i]I cancel out of the message only to find that the server login has been created for the new user but not the DB assignments.The only way I have found to get around this is to assign the Helpdesk group to DBO DB role but this is just too permissive for them. Surely there is a way to do this without assign DBO for the Helpdesk??Thanks in advance

Query sql server agent jobs

Posted: 09 Jul 2013 02:22 AM PDT

Hi, I am dropping some legacy stored procedures.There are many jobs scheduled on production server. I dont know whether the stored procedures I delete are still used by any jobs or not.How can I query whether these procedures are used by any of the jobs oratleast I have SSIS packages which uses the dropped procedures. Can I query jobs to know whether these packages are called or not.I can't manually open each job and check. Sometimes the packages are called from master package.How can I search?

Cannot open New SSIS Project in SQL Server 2008 R2

Posted: 02 Nov 2012 12:03 PM PDT

I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer use the SSIS to create New Projects. I cleaned out the registry with a Registry cleaner from Wise. I deleted the SQL Server and Visual Studio folders as well.When I create a New SSIS Project, I immediately get an error as follows:"Microsoft Visual Studio""An error prevented the view from loading.""Additional information:""Specified Module cannot be Found. (Exception from HRESULT: 0x8007007E)""(System.Windows.Forms)"How do I resolve this problem? What this means is that I cannot create any new SSIS Projects.Cecil

PushD commands from MS

Posted: 08 Jul 2013 11:27 PM PDT

I have a dos commande to delete backup files older than 2 days:[quote]PushD "\\myuncloc\BackupDumps\" &&( forfiles -s -m *.bak -d -2 -c "cmd /c del /q @path" ) & PopD[/quote]The code in a cmd prompt workt fine. How can i make this work in management studio?I tried the below code. I'm not getting an error, only output with value NULL. But when i check the backup location (\\myuncloc\BackupDumps\), the files are not deleted.[quote]declare @CMDSQL as varchar(5000)select @CMDSQL = 'PushD "\\myuncloc\BackupDumps\" &&( forfiles -s -m *.bak -d -2 -c "cmd /c del /q @path" ) & PopD'print @CMDSQLEXEC master..xp_CMDShell @CMDSQL[/quote]

FOR XML PATH with Namespace issues

Posted: 09 Jul 2013 12:55 AM PDT

Good DayI have a SQL query which has to produce an XML PATH output for a financial interface between systems.My query fetches data from two views and produces the correct XML output structure as is needed by the Financial system importer.The query is as follows:[code="sql"]WITH XMLNAMESPACES('eExact-Schema.xsd' AS noNamespaceSchemaLocation, 'http://www.w3.org/2001/XMLSchema-instance' AS xsi) SELECT (SELECT TOP (1) 'E'AS "@status", DIVISION AS "Division/@code", DESCRIPTION "Description", INVOICEDATE AS "Date", REPORTDATE AS "DocumentDate", JOURNAL AS "Journal/@code", 'I' AS "Journal/@type", CC_HEADER AS "Costcenter/@code", 'TZS' AS "Amount/Currency/@code", CASE WHEN CURRENCY <> 'TZS' THEN INV_TOTAL / INV_EXCH ELSE INV_TOTAL END AS "Amount/Value", CURRENCY AS "ForeignAmount/Currency/@code", INV_TOTAL AS "ForeignAmount/Value", (SELECT CASE WHEN ROWNUMBER IS NULL THEN 1 WHEN ROWNUMBER = 0 THEN 1 WHEN ROWNUMBER > 0 THEN ROWNUMBER+1 END AS "@number", TRSTYPE AS "@type", TRSSUBTYPE AS "@subtype", REPORTDATE AS "Date", GL AS "GLAccount/@code", DESCRIPTION AS "Description", CC AS "Costcenter/@code", CREDITOR AS "Creditor/@code", RESOURCE AS "Resource/@number", QUANTITY AS "Quantity", 'TZS' AS "Amount/Currency/@code", CASE WHEN CURRENCY <> 'TZS' THEN DEBET / INV_EXCH ELSE DEBET END AS "Amount/Debit", 0 AS "Amount/Credit", VAT AS "Amount/VAT/@code", VAT AS "VATTransaction/@code", CASE WHEN CURRENCY <> 'TZS' THEN DEBETTAX / INV_EXCH ELSE DEBETTAX END AS "VATTransaction/VATAmount", CASE WHEN CURRENCY <> 'TZS' THEN DEBET / INV_EXCH ELSE DEBET END AS "VATTransaction/VATBaseAmount", 'B' AS "Payment/PaymentMethod/@code", '30' AS "Payment/PaymentCondition/@code", YOURREF AS "Payment/Reference", REPORTDATE AS "Delivery/Date", TRSTYPE AS "FinReferences/@TransactionOrigin", YOURREF AS "FinReferences/YourRef" FROM CY_EXACTEXPORTINV_FINENTRYLINES WHERE CY_EXACTEXPORTINV_HEADER.YOURREF = CY_EXACTEXPORTINV_FINENTRYLINES.YOURREF FOR XML PATH('FinEntryLine'), TYPE ) FOR XML PATH ('GLEntry'), TYPE) --AS GLEntryFROM CY_EXACTEXPORTINV_HEADER ORDER BY CY_EXACTEXPORTINV_HEADER.YOURREF ASCFOR XML PATH ('GLEntries'), ROOT('eExact')[/code]A snippet of the Results of the query is as follows:[code="xml"]<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:noNamespaceSchemaLocation="eExact-Schema.xsd"> <GLEntries> <GLEntry xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:noNamespaceSchemaLocation="eExact-Schema.xsd" status="E"> <Division code="002" /> <Description>DETAILLED TOPOGRAPHICAL SURVEY OF THE QUARRY</Description> <Date>18/12/2012</Date> <DocumentDate>18/12/2012</DocumentDate> <Journal code="675" type="I" /> <Costcenter code="311" /> <Amount> <Currency code="TZS" /> <Value>18086096.00000000</Value> </Amount> <ForeignAmount> <Currency code="TZS" /> <Value>18086096.000000</Value> </ForeignAmount> <FinEntryLine xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:noNamespaceSchemaLocation="eExact-Schema.xsd" number="1" type="N" subtype="T"> <Date>18/12/2012</Date> <GLAccount code="513003" /> <Description>DETAILLED TOPOGRAPHICAL SURVEY OF THE QUARRY</Description> <Costcenter code="311" /> <Creditor code="30850" /> <Resource number="9244" /> <Quantity>0</Quantity> <Amount> <Currency code="TZS" /> <Debit>15327200.000000</Debit> <Credit>0</Credit> <VAT code="18" /> </Amount> <VATTransaction code="18"> <VATAmount>2758896.000000</VATAmount> <VATBaseAmount>15327200.000000</VATBaseAmount> </VATTransaction> <Payment> <PaymentMethod code="B" /> <PaymentCondition code="30" /> <Reference>000003</Reference> </Payment> <Delivery> <Date>18/12/2012</Date> </Delivery> <FinReferences TransactionOrigin="N"> <YourRef>000003</YourRef> </FinReferences> </FinEntryLine> </GLEntry> </GLEntries> <GLEntries> <GLEntry xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:noNamespaceSchemaLocation="eExact-Schema.xsd" status="E"> <Division code="002" /> <Description>GYPSUM EX KILWA - HIGH TECHNOLOGY INSTITUTE</Description> <Date>14/12/2012</Date> <DocumentDate>29/12/2012</DocumentDate> <Journal code="675" type="I" /> <Amount> <Currency code="TZS" /> <Value>5712144.00000000</Value> </Amount> <ForeignAmount> <Currency code="TZS" /> <Value>5712144.000000</Value> </ForeignAmount> </GLEntry> </GLEntries>[/code]The big issue I have is that the Namespace allocation at ROOT level is fine, but I cannot find a way to control it so that it is not repeated in its entirety on the NODE level (ie: at the GLEntry and FinEntry level).Is this at all possible ?I'm using SQL Server 2008 R2.Thanks in advance

NULL Columns QUery

Posted: 08 Jul 2013 10:00 PM PDT

Hi,I have a column which contains XML data. If I were to set the values to NULL, would this use less space than the XML data? (No NC index exists on this column. Only a clustered index on the PK).Thanks

Mapping a Login to a Database at the Instance level

Posted: 08 Jul 2013 09:05 PM PDT

Friends,I am trying to map a Login to one of the Databases in the Server Instance. However, I am unable to do so through SSMS. I have login xxxxxxxx at the Instance Level. I am trying to map this Login to one of the DBs in this Instance. I going to the properties page of the login through Security folder and going to the User Mapping options. I am clicking the check box of the DB that i want say A and clicking on the OK button. The window shows as if it is executing. However, when I check the properties again, this DB remains unchecked. Is there a script using which we can map a DB to a Login at the Instance level.thanksMurali

Failing agent job

Posted: 27 Jun 2013 03:26 AM PDT

Hi,Strange one bugging me have almost identical code running in another database on the server without issue.I have an agent job which runs[code="sql"]DELETE FROM [biostratweb].[dbo].[xx_FreeTextSearch_ABS][/code]However I always get an error:Date 27/06/2013 17:23:50Log Job History (BiblioWeb Merge and FreeTextSearch Update)Step ID 2Server SQLVMJob Name BiblioWeb Merge and FreeTextSearch UpdateStep Name Free Text updateDuration 00:00:00Sql Severity 16Sql Message ID 208Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: NEFTEX\spsqlagent. Invalid object name 'biostratweb.dbo.xx_FreeTextSearch_ABS'. [SQLSTATE 42S02] (Error 208). The step failed.When I run it directly it works fine.Any ideas, I am running SQL 2008.Many Thanks as always,Oliver

datatype convertion

Posted: 08 Jul 2013 08:21 PM PDT

i have a table with colum having string data in it. i want to convert it into integer. how can i do this..example.. region south northi want to convert above into integer format like region 2 3
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog