Wednesday, September 11, 2013

[SQL Server 2008 issues] SSIS Package runs as a package but won't run in job scheduler

[SQL Server 2008 issues] SSIS Package runs as a package but won't run in job scheduler

SSIS Package runs as a package but won't run in job scheduler

Posted: 10 Sep 2013 10:07 AM PDT

I have a package that will not complete in the job scheduler, runs fine as a package. Part of the process was to delete a file on our network using the File System Task Editor delete file and when the job runs in the scheduler I get an error saying that the server does not have permissions. It runs fine from the server when I run the package.I changed it to rename the file instead of deleting it and now it says it can not find the file to rename it when I run it in the scheduler.I am at a total loss and am very new to doing all of this. Any help would be greatly appreciated as I am supposed to go on vacation in a week and a half and need for these to be running while I am gone. :-)

Simple Transpose - why can't I do it??

Posted: 10 Sep 2013 01:37 PM PDT

Guri (is that the plural of Guru?)I have a result set that I need to present in a graph:Results: Category PrevWk2 PrevWk1 CurrWkBlueStore 1.5 3 8.25RedStore 4.5 4.5 15.5and I need it to be:Week: BlueStore RedStorePrevWk2 1.5 4.5PrevWk1 3 4.5CurrWk 8.25 15.5 for the SSRS Graph, so that the X axis will be the weeks and the series will be the Red & Blue values...I have looked at the PIVOT and Case examples and I still can't get it to work.Can anyone help a brother out?(Feeling a bit daft!!) Cheers,David

Fillfactor Property not saving

Posted: 10 Sep 2013 11:17 AM PDT

Hi ,Some how SSMS Index Fillfactor Property is not saving Fillfactor settings.We have SQL server 2008 R2 .I checked server property ->Database Settings it is set to 0 or 100%.But when i go to table->index property it shows 70 %.I changed to 100,then went back to index property and it is back to 70.Any idea why SSMS is not saving changes?

unable to run power shell script from SQL server agen

Posted: 10 Sep 2013 06:39 AM PDT

hello,i created this power shell script to retun SQL server configurationsconfiguration, the script works fine when I run it from Powershell ISE, however it fails when i schedule it to run as power shell step in sql agent as ajob, it gives me syntax error, any ideas what I'm doing wrong?, i read some articles about assigning the get-date to a variable, i tried it without any lock Import-Module SQLPS -DisableNameChecking #One way to map New-PSDrive -Name UNCPath -PSProvider FileSystem -Root \\Share\Servers\foreach ($instancename in get-content -path UNCPath:\Servers.txt) { $Date =Get-date -Format "yyyy-m-d_hmmtt" $folder= "UNCPath:\servers" $filename = "$($instanceName)_Configuration_$($Date).csv" $fullpath =Join-Path $folder $filename $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $instanceName$server.Configuration.Properties | Export-Csv -Path $fullpath } remove-psdrive -name UNCPath

New query in project sometimes prompts for connection--other times not

Posted: 10 Sep 2013 01:42 AM PDT

I'm using SSMS 2008 R2 connected to the same version database on two different machines. On both machines, I did this:1. Connected to the server2. No query windows are open3. Created a project4. Right-clicked on the Queries folder in Solution Explorer and selected "New Query."On one machine, I am immediately prompted for a connection. On the other, I am not. How is this controlled? How can I change one machine to behave like the other in this respect?Thanks!

SSIS to download .txt file from URL and import data in SQL Server table

Posted: 10 Sep 2013 04:18 AM PDT

There is a text file placed on a URL lets say http://mysubsite.domain.com/Customers.txtThis text file has \t \Column1 \t \Coilumn2 and so on then to have new line data for column1 and then Column2 with n rowsI have to pull this list of customers in existing customer table. if this customer already exists then do nothing if this customer is not in list then add in Customer table. I need some guidance if this could be done through SSIS, if yes then how? I have no idea how to implement this in SSIS. I was trying to write a win32 service in c# to download text file locally and then run BCP to export data in a temp file then call SP to see if the customer is updated then update else if not found then add in Customer table. I am not sure if this could be done through SSIS and if yes i hope it would be fast and quick to implement, if someone already worked on it. What is the best option to choose, I need to know what is the best solution and how it could be done if possible in SSIS? I need to do this on daily bases to get text file up to date with my Customer table.Shamshad Ali

Index rebuild Maintenance Plan may not be running properly

Posted: 09 Sep 2013 09:44 PM PDT

HiI have 2 jobs from a maintenance plan and 1 backup job from sqlbackup (redgate). The job are scheduled as follows:Sunday 23:00Hrs - The Rebuild indexs on all user databasesSunday 23:15Hrs - The system databases Integrity checkSunday 23:15Hrs - The sqlbackup job of all system db'sI have noticed that the 1st job to rebuild all user database indexes is running in less than 1 minute and I am thinking this should probably take longer. The job succeeds but when I look in the log file for the job history I see something like this:[quote]Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ABC_XXX...".: 8% complete End Progress Progress: 2013-09-08 23:00:14.91 Source: Rebuild Index Task Executing query "USE [ABC] ".: 8% complete End Progress Progress: 2013-09-08 23:00:14.92 Source: Rebuild Index -[/quote]Because I am not seeing 100% and only 8% complete does this mean the alter index statement is not completing the index rebuild task fully ? I was thinking this may be the case as either the the backup job somehow interferes with the 23:00hr job ?Any ideas on as to why the index rebuild task runs so quickly ?

Logshipping - SQL server 2008 R2

Posted: 10 Sep 2013 01:37 AM PDT

Recently logshipping is configured on one of the sql instances in our environment between two different sql isntances. Everthing looks good other than primary server Lsalert job. It keeps failing on the primary instance. When I verify the log shipping tables on the primary sql instance the Secondary name was updated wrongly. that means seconday server name is udpated as primary server. it' causing this problem. both sql instance are newly built.Does anybody had/heard this issue before?Thanks.

Error DTExec: The package execution returned DTSER_FAILURE (1)

Posted: 09 Sep 2013 09:45 PM PDT

Hi,A Maintenance Plan Job for UpdateStatistics is scheduled from SQL Server 2008 and it is a weekly job (Sunday). Last Sunday it failed with the below error. Can some body help me to fix the issue.Error:Executed as user: ABC\XXX. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 6:00:02 AM Progress: 2013-09-08 06:00:11.52 Source: {9B20A854-9645-4762-B4C7-8B5DA1A19210} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2013-09-08 06:22:09.05 Code: 0xC0024104 Source: Update Statistics Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:00:02 AM Finished: 6:22:09 AM Elapsed: 1327.41 seconds. The package execution failed. The step failed.Thanks in advanceTapas

Table name by a value

Posted: 09 Sep 2013 08:56 PM PDT

How to know a Table name by a value?plz help me

How to insert Parent node text if child node is avilable?

Posted: 09 Sep 2013 08:07 PM PDT

Hi,I have an XML that I need to output somewhat differently, for reference: <?xml version="1.0"encoding="UTF-8"standalone="true"?> <Computer > <OS Name="Microsoft Windows 7 Ultimate" ServicePack="1"/>OS - Category, Namem, Service Pack - Property, <HardDisks> HardDisks - Category, Value - Save as Name in the Property table <HardDisk Value="C:\ (NTFS)"/> </HardDisks> <LocalGroups> <Group Name="Administrators"> LocalGroups - Category, Name - Property <Member Name="Administrator"/> Member - Category, Administrator, whargrove - Property, Administrators - Parent <Member Name="whargrove"/> </Group> <Group Name="Backup Operators"> </Group> <Group Name="Guests"> <Member Name="Guest"/> </Group> <Group Name="IIS_IUSRS"> LocalGroups - Category, IIS_USERS - Property <Member Name="IUSR"/> LocalGroups - Member, IUSER - Property, IIS_USERS - Parent </Group> <Group Name="boinc_projects"> </Group> </LocalGroups> </Computer>Need to insert data like below into my table from above xml. Here is a one special case i.e If the node contains child node need to insert parent node as parent in the table Parent column if child not exist need to insert NULL. [code="sql"] Category Property Value Parent OS Name WH& NULL HardDisks Null Null Null HardDisk Value C:\ (NTFS) HardDisks LocalGroups Null Null Null Group Name Administrators LocalGroups Member Aministrator Null LocalGroups Member whargrove Null LocalGroups[/code]My query is below:[code="sql"]insert into AssetProperty (Category, Property, Value, Parent) select T.C.value('local-name(.)', 'nvarchar(max)') as Category, A.C.value('local-name(.)', 'nvarchar(max)') as Property, A.C.value('.', 'nvarchar(max)') as Value, T.C.value('local-name(.)', 'varchar(max)') as Parent from @XMLdata.nodes('Computer//*') as T(C) outer apply T.C.nodes('./@*') as A(C)[/code]Any solution for this?

SQL failover cluster - prestaging objects in AD

Posted: 09 Sep 2013 07:51 PM PDT

Hi all,What I'm looking for is a succint summary of what needs to be created & what permissions granted in AD to facilitate a failover cluster install in a low permission environment (ie. where SQL can't create these objects itself). I have an idea of what needs to be created and granted, but want to make sure that it covers everything without granting too much :-Dnb. I am a SQL person not an AD person - I have had a look at the MS articles on granting cluster permissions ... and am a bit lost in all the AD terminology :hehe:So here's what I have so far:Create computer object for each physical node in AD & the Cluster name (I understand this is done by the Windows clustering)Create MSDTC object in AD (not sure where this gets done?)Create Virtual SQL Name object in AD Grant full control of this to the following: Cluster computer object Each physical node's computer object
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog