Saturday, May 25, 2013

[SQL Server 2008 issues] Unanswered unable to work on SSIS project in BIDS 2008

[SQL Server 2008 issues] Unanswered unable to work on SSIS project in BIDS 2008

Unanswered unable to work on SSIS project in BIDS 2008

Posted: 24 May 2013 06:31 PM PDT

i am getting this error when i try to open any SSIS project and work on that "an error preventing the view from loading" and also "Could not load file or assembly 'msddsp, Version=9.0.0.0, Culture=neutral, PublicKeyToken=**' or one of its dependencies. The system cannot find the file specified. (Microsoft.DataTransformationServices.Design)" adding new project is working fine in it but when i try to work on this project like opening data flow, adding tasks it gives this error need help

unable to work on SSIS project in BIDS 2008

Posted: 24 May 2013 06:28 PM PDT

i am getting this error when i try to open any SSIS project and work on that "an error preventing the view from loading" and also "Could not load file or assembly 'msddsp, Version=9.0.0.0, Culture=neutral, PublicKeyToken=**' or one of its dependencies. The system cannot find the file specified. (Microsoft.DataTransformationServices.Design)" adding new project is working fine in it but when i try to work on this project like opening data flow, adding tasks it gives this error need help

Internal error. Buffer provided to read column value is too small

Posted: 24 May 2013 02:13 PM PDT

While i am altering a column, getting a message as below.Can anyone please help? It's urgent.alter table test alter column AvgCashAndInvestments numeric(19,0) NULL The statement has been terminated.Msg 682, Level 22, State 153, Line 3Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

Two Identical VMs, When testing processing speed one is significantly slower than other? Advice

Posted: 23 May 2013 10:11 PM PDT

Hello AllI have created two VMs, with exact cloned setup.I am running a simple connection test where from a C# application I connect to the instance declare a variabledisconnectand report how long this took.With VM1 this test takes 2msWith VM2 this test takes 8msWhen i get concurrent threads going to simulate load the difference is even greaterVM1 8msVM2 80msEverything, and I mean everything about these VMs is identical.Can someone explain what troubleshooting can be done to determine the reason for the difference.Both VMsWindows Server 2008 R2Microsoft SQL Server 2008 R1 RTMBoth on same ESX Host.Same disks, memory etc.Same results are replicated locally (so not a network issue as it would use shared memory)What goes on within sql server from listening for the connection, processing the variable declaration that can cause such a significant difference.DECLARE @G UNIQUEIDENTIFIERThat's it.Thanks for your help in advance

Fix VLFs on a VLDB

Posted: 24 May 2013 09:57 AM PDT

What's the best way to go ahead defragging the tlog of a VLDB?Regards

XML, SSIS, and counting Tags

Posted: 24 May 2013 09:27 AM PDT

Right, so. I've got a problem. I need to have a 'tag count' included in the footer of an XML I'm shipping to a system. The problem comes in where there's sublevels in tags that throw off @@ROWCOUNT and running it twice (once for XML and once for count) could give me different results, the system has enough volume and the XML builder travels for 4+ seconds.So, even creating an @@ROWCOUNT solution, SSIS as an OLEDB source doesn't let you do output parameters. You can ship it to an Execute SQL command at the Control Flow level, stuff the recordset into an object variable, and then grab that in a script source in the dataflow, but that is not only overkill, it's moronic... but if it's the only way it's the only way. The problem is that any rowcounts I get will include the sublevels.Errr, I suppose I should explain that.[code="plain"]<Contact name="a"> <Address Type="Home"> <Address Type="Business"></Contact><Contact name="b" /><Contact name="c"> <Address Type="Home"></Contact>[/code]I need to get a count of <Contact without including the count of <Address, but if you @@ROWCOUNT after your FOR XML you get the full count. I need 3, the above would give me 6.Now, usually what I'd do is simply multi-cast in SSIS at this point and strip the little bugger in a script task doing locational checks. Problem... VARCHAR(MAX) (or DT_Text) doesn't allow itself to be shoved into a string type in VB.NET... which I need because these things can get huge.Great.At this point I'm interested in knowing if anyone has even an idea of alternat ways to go. I'm tacking the footer on in another dataflow which is dependent on the XML inclusion, so Pre-processing of some form is an acceptable idea. I may just not know the right VB.Net variable type to use, so if that's the problem please, send me in the right direction. If it's obvious, my google-fu has failed due to keywords. For example, I can easily find things like this: [url]http://blog.boxedbits.com/archives/6[/url], but I can't find a workaround.

Backup large Table

Posted: 23 May 2013 08:15 PM PDT

Hi Team,I've a table with 75+ columns and 70000 records, i want to have a backup .sql file.am trying using SSMS > Rt click Database > Tasks > Generate Scripts > ....sql file is created but while opening the same in SSMS getting below error.[b]Error HRESULT E_FAIL has been returned from a call to a COM component[/b]Please help me.....

SSMS query page shifts

Posted: 24 May 2013 03:22 AM PDT

My SSMS 2008 R2 has an irksome quirk. Every 10-15 minutes the query screen shifts down. I will be typing a line of code positioned in the middle of the screen and suddenly it will shift down to the bottom, just off the screen. Any ideas of how to curb this insolent behavior? :crazy:

please help; i have to find the server rebuilt information

Posted: 24 May 2013 04:42 AM PDT

Can some one help me to find the server rebuild information... I am trying to find when the server has been rebuilt. We had some disaster and needed to rebuild the server. I forgot when did we rebuilt the server. Please help

Partition

Posted: 23 May 2013 08:32 PM PDT

[b][/b]"Alter Table XXXX Drop Constraint XXXX_PK With (Move To Part_Schm_Month_Wise(Date_Of_Entry)) "The above query is taking over 24hr to execute. The table has about 65 million rows. Any Suggestion, how can I speed-up the process.

SQL Service Accounts not starting in Configuration Manager after password change

Posted: 24 May 2013 06:17 AM PDT

I have taken over a few sql server instances. Today the Windows account password for the sql service account was changed. When I tried to start the sql and sqlagent service after changing in Configuration Manager, the error message "The request failed or the server did not start in a timely fashion". The password I entered is correct as I can RDP into servers with it. If it is simply a password change, what could be causing the failure?

SFTP with sql server 2008

Posted: 12 Feb 2013 07:38 PM PST

I want to know the syntax of SFTP command that will execute by sql server job and what steps will I have to follow to do public key authentication ? I am using SFTP to transfer files from window server 2008 to another window server 2008.

Need to get list of sprocs for a list of tables

Posted: 24 May 2013 02:33 AM PDT

Hello, I need to create a report with all jobs that run to populate the tables that failed and then also report all sprocs effected by that ETL run Jobid Table_Name Stored ProcedureI can get the list of jobids and table names for the report. But how do i pass these table names which failed to load to get the stored procedure. Any ideas please.

Copy SQL .bak file to other server using FTP/SFTP and restore the backup to a database

Posted: 24 May 2013 01:24 AM PDT

Hi,I am looking for solution for below approach using TSQL or powershell or using SQL agent job.1. Perform backup on prod DB server of one database-- this can be done2. FTP/SFTP the backup file to the staging DB server - i am clearly looking for this line and do not want to use SSIS, this i have to perform using SFTP client/server process.3. Restore the backup to a database Can any one please help how to start working on this process and to automate this process at the end.

Architecture

Posted: 24 May 2013 01:23 AM PDT

can any one send the sql architecture

Microsoft.SqlServer.Replication.ComErrorException; building in "x86"

Posted: 24 May 2013 03:48 AM PDT

Hello,I have a quick question that I have really been struggling with as of late. I'm going to keep it simple. I am developing an application that uses a two databases running different types of Sql Server, one 32-bit and the other 64 bit. Basically, I need to build my application using Configuration properties of "x86" because accessing the 32-bit database has been deemed more important. The problem occurs when accessing the 64-bit database, I receive the exception Microsoft.SqlServer.Replication.ComErrorException, "class not registered". Unfortunately I can't use the most suggested solution of changing the build properties to "Any CPU" because it breaks access to the 32-bit database. Does anyone have an idea about how I would fix this in another manner? I've tried adding references to Microsoft.SqlServer.Replication.dll, Microsoft.SqlServer.Rmo.dll among others and still no luck. Any advice is appreciated and I thank you in advance.

SSIS File Move Anomaly in Scheduled job

Posted: 24 May 2013 01:10 AM PDT

I'm having a problem with files not being moved properly to their destination directory.I have an SSIS package that generates comma delineated text files. Once the file is created, there is a File System Task that Copies the file to a directory on another server for archival purposes and then another File System Task that Moves the files to its production destination on the same server that the file was archived to.For example.Created Locally onC:\Out\FileGenerator\Copied to\\AnotherServer\g\AnalyticsProgram\ArchiveSQL\Moved to\\AnotherServer\g\AnalyticsProgram\AnalyticsProgram is a folder that is accessed by an Analytics program that we use. It has a service that runs on that server (named AnotherServer here) where it imports and deletes the files I create, archiving them in a subdirectory of its own.The Source Connection and Destination Connection in both the File System Tasks are variables. The file names are generated by a combination of File Type Designation (e.g 001) numeric StoreID (e.g. 10025) and underscore and the date of the data (e.g. 20130524) followed by the iteration of the file (e.g. 10012) and finally .txtThe Archive files then append (before the .txt) another underscore and the creation date, formatted as above.A sample file name would be00110025_2013052410012.txtWith an archive file of00110025_2013052410012_20130524.txtThe job has two schedulesThe first runs at 45 minutes after the hour every hour between 7 AM and midnight.The second is a cleanup run at 12:30 AM.The job runs every day, seven days a week.The job is also kicked off from another job at 7:30PM, 10:30PM, 1:30AM and 3:30AM every day, seven days a week. This ensures all data is generated before it moved to the data warehouse.The 12:30 run is left over from when we only had one job to move data to the data warehouse at 1 AM instead of the 4 listed above.Our business EOW is Thursday with Friday being the start of a new week.Every Friday morning at 12:30 AM, the files are created properly and are copied to the archive folder, but are not moved to production.In a normal move, the files disappear from the creation directory, so if the move simply wasn't happening, the files should be left in C:\Out\FileGenerator\ but the folder is empty, as it should be if everything is working properly.The files all end up where they're supposed to every other run for the week.This anomaly has happened three weeks in a row now. Always at the same time on the same data and never any other time or date.In an attempt to see what was happening, I turned off the service that imports and deletes the files from \\AnotherServer\g\AnalyticsProgram at 12:15AM and watched the folder from 12:28AM to 12:32AM. Nothing happened. When I checked the archive subfolder, the files were there, they simply never appeared in the main folder.When I coped the files from Archive to Production (10 minutes later since I had to rename the copies I made of the Archive files, removing the generated-date suffix), they appeared properly. When I turned the service back on, they were consumed properly.I've done a search of the destination server and the origin server and the files haven't been moved to a different location, they're just gone.I'm baffled. What is happening here? Anyone have any ideas?

SQL Server 2008 R2 setup has stopped working

Posted: 24 May 2013 03:38 AM PDT

Hi all, I have severals days trying to solve this issue about "Installing SQL Server 2008 R2 (English 64x) over Windows Server 2008 R2 sp1 (English 64x), the thing is installation start normally but after choose the licence "Evaluation" when I presss "Install" button, I get the message "SQL Server 2008 R2 setup has stopped working" I would appreciate any information to solve this problem,Thanks in advance,As additional error information I get:----------------------------------------------------Description: SQL Server 2008 R2 Setup has encountered an error. Problem signature: Problem Event Name: SQL100MSI Problem Signature 01: 10.50.1600.1 Problem Signature 02: Unknown Problem Signature 03: SqlSupport.msi Problem Signature 04: 0x162A16FE Problem Signature 05: 0x3 Problem Signature 06: Install_SqlSupport OS Version: 6.1.7601.2.1.0.272.7 Locale ID: 3082Additional information about the problem: LCID: 1033Read our privacy statement online: http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409If the online privacy statement is not available, please read our privacy statement offline: C:\Windows\system32\en-US\erofflps.txt-----------------------------------------

Remove scientific notation from xml column

Posted: 23 May 2013 09:21 PM PDT

I have a xml column as shown below[quote]<Root> <Row> <Rowid>1</Rowid> <date>2013-05-06</date> <Balance>1.0002E7</Balance> </Row></Root>[/quote]When i query for getting balance i do get it as 1.0002E7 . But i want to get it as 10002000 .How can i do it?I have another question. Can i make any changes so that in xml column it self it store as i required without scientific notation.Thank you

SQL Server 2008 Maintenance Cleanup Task Issue

Posted: 14 Dec 2010 11:25 PM PST

i can't get the Maintenance cleanup task to work in SQL Server 2008 (Enterprise Edition)The step is completing successfully but it is not deleting the files in the folders (and sub folders specified)it is not related to the file extension as i am using bak (not .bak)it is not related to not having the back slash in my directory, i am using (S:\SqlBackups\it is not related to permissions on the folder as i temporarily gave full control to 'everyone' and the backups were still not deleted.i am folllowing the same steps i use in both SQL 2005 and SQL 2008 R2 and there is no issue with either of themAny help apprecicated.

SQL Consulting Help

Posted: 23 May 2013 11:41 PM PDT

My company is in need of a SQL Server consultant. We need someone to come in and do an evaluation of our SQL server configuration, look at our databases, look at our SSRS configuration, look at our indexes (and lack of), etc., etc., etc. And when all said and done, we would like for them to leave us with a plan on how to monitor performance and action plan for maintaining performance.I am asking for recommendations as I do not know where to look for this type of work. I'm sure there are thousands of companies I could Google, but I'm hoping someone here can help me cut through the chees. We'd want to call around to talk to references to vouch for this company/individual as well.Does anyone have any recommendations?Thank you.Joel

Confusion on creating a schema and adding users permissions to it

Posted: 24 May 2013 01:21 AM PDT

Business case: Require an application to access data in another application's databaseProcedure: Create new application login, new schema in existing database for objects, grant new login permissions to execute the new schema procs only. Also need a login/user that can create objects in the new schema that can read objects in the existing dbo schema.So, creating the schema... Not sure who to make the owner. Also, when I try to grant permissions on the schema to the new user, it seems it is letting the user create objects in dbo. Here is what I tried:[code="sql"]USE existingDatabaseNameCREATE LOGIN [domain\appServiceAccount] FROM WINDOWS;CREATE LOGIN [domain\developerGroup] FROM WINDOWS;GOCREATE SCHEMA schemaName AUTHORIZATION dboGRANT EXECUTE ON SCHEMA::schemaName TO [domain\appServiceAccount]GRANT CONTROL ON SCHEMA::schemaName TO [domain\developerGroup];GO [/code] I have never used schemas before and really have a poor understanding of some of these permissions; i.e. CONTROL. I feel that BOL explains these permissions poorly and I can't make sense of them.

Snapshot Replication Problems...

Posted: 22 May 2013 01:37 AM PDT

Hello all,I have a couple of issues while trying to implement snapshot replication. Basically, I just want a nightly copy of a production database on server 1 to a reporting database on server 2. Seems simple enough, right?First, every time I go into the article properties, the "[b]Action if name is in use[/b]" option is always set to "[b]Drop existing object and create a new one[/b]." If I change it, hit "OK" and exit out, then come back in, it will still show as "Drop existing object and create a new one." Does it just show that as the first option, regardless of what is actually being used? (I've tried setting it to "Delete data. If article has a row filter, delete only data that matches the filter."Second, the scheduled replication processes [i]seems [/i]to be working fine. The snapshot gets generated fine (without any errors), and then (later) the distribution job runs fine (without any errors).However, the data doesn't change on the reporting database.Can someone point me to some common reasons that this might be happening? Perhaps some options or settings that I have overlooked?Thanks.

Load Balancing SSRS with NLB

Posted: 23 May 2013 09:15 PM PDT

Hi allWe have a 4 node NLB at present but as we only have 2 SQL licenses we are creating a scale out deployment of 2 servers.Can NLB be configured such that when the virtual URL is accessed it knows to direct the requests to the servers with SSRS installed, or will it try to re-direct the requests to the nodes without it to?Thanks

Editing SQL Operator Alert Emails

Posted: 23 May 2013 10:56 PM PDT

Hi all,Could anyone tell me if it's possible to edit the default job notification emails sent out to operators? I have an alert set up to email me every time a backup job completes, although I would preferably like it to tell me whether it has Succeeded or Failed in the subject line, rather than having to open up the email to view the status line.Any idea if that's possible?I have so many servers sending notification emails and invariably end up viewing them on my phone, so it would just save some time not having to open them all to check if anything has failed!Cheers

Convert data to HL7 message

Posted: 21 May 2013 05:37 AM PDT

Hello everyone. I have be trying to figure out how to convert patient records into HL7 format for about a week now with no success. Is this possible within SQL Server? Is it even worth doing or should I look to a third party solution. If this can be done in SQL Server, can someone please refer me to any resources or point me in the right direction.Thanks.

Databas Shrink

Posted: 23 May 2013 08:56 PM PDT

Hello there,I have a database where we have archived and purged 95% of the data, leaving a 230GB file where only 7GB of it is in use by the data. As part of the purge script I have written, we need to conduct a shrink on the data file down to 50GB, leaving room for growth.The collection of dbcc shrinkfile commands on my workstation, a quad core, with 12GB, takes around 5 minutes to complete. On our test kit, which has it's own dedicated SAN with SSDs, oodles of memory and 80+ cores takes over an hour.I also tried creating a new filegroup, moving all the data across to the new filegroup, and shrinking the now empty primary file, and it still takes over an hour.I know that database shrinking is bad, but under these circumstances it is warranted.I'm at a loss as to why this may be. The SQL log show's nothing out of the ordinary. Does anyone have any suggestions ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog