Friendly Way To Look At Sqlagent Ssis Messages Without Exporting Rest Of Log
Apr 13, 2008
I just started playing with sqlagent and am finding that viewing a specific message from an ssis step is cumbersome when other entries are also in the log. Exporting seems only able to export all history entries. Viewing right in the log viewer leads to a series of boundary expansions and right scrolls that dont seem convenient for quick identification of a problem in a medium sized ssis message. Right clicking on the message in the viewer seems like it is preparing the message for possible copy but it seems to end there. I'm already familiar with the different levels of verbosity available in the command line. Dont see anything useful on the web.
Is there a user friendly way to copy the contents of a specific message for perhaps pasting to notepad , so that problem identification is facilitated? Is there a not so user friendly way available, one taht doest involve 1st exporting the entire log?
View 7 Replies
ADVERTISEMENT
May 13, 2008
This is a fun one. I have a job that runs a SSIS package. It has some variables that I set a runtime. How can I programmatically change the values for these variables using SMO? I can get to the step in the job using VB but I can't find a way to programmatically change the value of the parameters of the job. The code loops through every property of the SSIS step. There's a command string but that's an ugly beast to code against. Any help is appreciated.
With oStep
Console.Write("Name: " + .Name + vbTab)
Console.Write("Proxy Account: " + .ProxyName + vbTab)
Console.Write("Type: " + .SubSystem.ToString + vbTab)
Console.WriteLine("Urn to point to job: " + .Urn.ToString)
iProp = .Properties.GetEnumerator
While iProp.MoveNext
Console.WriteLine("Property Name/Value: " + Convert.ToString(iProp.Current))
End While
End With
View 3 Replies
View Related
Apr 15, 2008
do sqlagent service account proxies need more than just permissions on the app databases being read from and written to in the executing ssis package?
it looks like there are some prep steps when a pkg is going to be run. In my case, the pkg comes from msdb which has it's own security roles. So will my proxies need "datareader" permission on msdb...in addition to datareader and datawriter permissions on the other databases the pkg reads/writes from/to?
are there other permissions/roles normally important to proxies used in getting ssis pkgs to run? Where are they set?
View 7 Replies
View Related
Jun 2, 2006
Hello-
I've an SSIS packeage that genrates offline cube (.cub) files via an MDX query run against an Analysis Services cube. When I run the package through the Management Studio all runs fine and the cube files are generated and can be used by excel.
However when I run this package through a SQL Agent job the files are generated and the job completes successfully , but when I try to open them in Excel they appear to be corrupted and I the Data Link properties screen comes up. In looking at the files the ones generated by the SQL Agent are also a different size than those generated from running the package directly
Has anyone else seen this kind of behavior? As an FYI I'm adding the files to the SQL Agent step from the SSIS Package Store.
Thanks,
Tristan
View 3 Replies
View Related
Apr 28, 2006
Hi
Using SQL Server 2005 with SP1, I have successfully managed to schedule jobs to run SSIS packages. They connect to another SQLServer 2000 box, using SQLOLEDB connection manager, to extract data and import it into SQL 2005. The protection level for the packages is Server storage so that the job is run under the SQL Agent account. This is a specific domain account so that it can access other servers.
However, using the same setup for a scheduled job to to run an SSIS package which connects to another SQL Server 2000 box with connection manager SQL OLEDB, I get the following error message:
The AcquireConnection method call to the connection manager "xxx" failed with error code 0xC0202009.
As the both the successful and failed jobs seem to have been set up in the same way with the same protection levels and are both run under a domain sql agent account, is there anything else I should be checking that I don't know about?
Any help is much appreciated!
View 3 Replies
View Related
Nov 28, 2007
Hi,
I am running SSIS package using command line. However I want to suppress the messages that come and I want to display my own messages. How can I do that?
For eg: I am using File System task to transfer a file from source to destination. How will i tell this to the user who is seeing this executed at the command line?
thanks.
View 1 Replies
View Related
Aug 29, 2007
Hi,
I am designing a simple SSIS package that tests database connectivity. To use the same connection for each database, I am changing the "InitialCatalog" property of the Connection Manager object to test each database in a server.
When the connection fails, I can capture a generic error message from the exception in a Try...Catch block, but it doesn't tell me why a connection attempt failed. I have tried Package Logging as well and that was actually less helpful.
I would have stopped there except the Output Window and Progress/Execution Results tabs BOTH show that the reason for the error was a login failure. I am trying to capture the message that appears in either of these windows as they are the most helpful.
Has anyone been able to programmatically capture these messages?
Thanks!!!
View 5 Replies
View Related
Jul 20, 2007
Do you guys have any good links or suggestions relative to this?
View 1 Replies
View Related
Aug 22, 2007
First of all, please let me prefix this by saying AAARRRRRGGGGGHH!!!
Troubleshooting an SSIS package is not even close to easy. The error messages are incredibly cryptic and are anything but useful. It is absurd that for most of the errors that I get, I find myself putting the error codes into Google to find out what they mean and how to address them. Since there is indeed an known exception, why not include a "descriptive" description of the error?
Okay, enough with the venting. Sorry about that...
What I really came here to ask is this, what in the world does this error message REALLY mean? I have found nothing useful on the Internet so far. The resulting forums and blogs with the error code do not apply to what I am doing.
Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
View 20 Replies
View Related
Apr 28, 2008
hi all,
I am developing an application in C# which creates SSIS package programatically.These packages perform various activities like transfering data from one server to another,extract data from table,transfering data from sql to msaccess etc.Here I am using the following code to get the description of the SSIS errorcode.
Code Snippet
Public static String GetErrMesg(IDTSComponentMetaData90 MD,Exception Err)
{
try
{
COMException comEx=Err as COMException;
return MD.GetErrorDescription(comEx.ErrorCode);
}
Catch
{
return Err.Message;
}
For example if the error code is 0xC0204018,the application shows its equivalent description as
"The "%1!s!" has a precision that is not valid. The precision must be between %2!ld! and %3!ld! "
But the actual error message is "The Output column "fund" has a precision that is not valid. The precision must be between 1 and 18"
My question is how to show more descriptive error message..like how to replace "%1!s!" to the actual field.
Please give ur suggestions...
View 1 Replies
View Related
May 18, 2006
This package which is a child package has been running successfully for quite some time now. All of a sudden we are getting these intermittant error messages. Does anyone have any ideas what to do or check for?
thanks
===========================
Error portion
Error: 0xC0047012 at CF-DFT Oracle Sales Fact, DTS.Pipeline: A buffer failed while allocating 100483760 bytes.
Error: 0xC02020C4 at CF-DFT Oracle Sales Fact, order line id [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
Error: 0xC0047011 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The system reports 30 percent memory load. There are 8587960320 bytes of physical memory with 5972680704 bytes free. There are 2147352576 bytes of virtual memory with 1324290048 bytes free. The paging file has 12673945600 bytes with 10005012480 bytes free.
Error: 0xC0047038 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The PrimeOutput method on component "order line id" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047056 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The Data Flow task failed to create a buffer to call PrimeOutput for output "Union All" (13359) on component "Union All Output 1" (13361). This error usually occurs due to an out-of-memory condition.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "SourceThread1" has exited with error code 0xC0047038.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0x8007000E.
Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" has exited with error code 0xC0047039.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
====================================================
Complete child package log
Executing ExecutePackageTask: D:ssissrwpackagesSRW_ORACLE_SALES_FTBL.dtsx
Information: 0x40016041 at SRW_ORACLE_SALES_FTBL: The package is attempting to configure from the XML file "D:SSISconfigurationCONFIG-STAGE1.dtsConfig".
Information: 0x40016040 at SRW_ORACLE_SALES_FTBL: The package is attempting to configure from SQL Server using the configuration string ""MSSQL-CONFIG";"[dbo].[SSIS_Configurations]";"System Configuration Settings";".
Information: 0x40016040 at SRW_ORACLE_SALES_FTBL: The package is attempting to configure from SQL Server using the configuration string ""MSSQL-CONFIG";"[dbo].[SRW_SSIS_Configurations]";"SRW Main Configurations";".
Information: 0x4004300A at CF-DFT Oracle Sales Fact, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: Truncation may occur due to inserting data from data flow column "IC_ORDER" with a length of 240 to database column "IC_ORDER" with a length of 1.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "SERIAL_NUMBER" (2680) on output "Sort Output" (2453) and component "Sort 1" (2451) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "ORG_ID" (13377) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "CUST_TRX_TYPE_ID" (13428) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Data Conversion 1.Copy of CUST_TRX_TYPE_ID" (13443) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "GL_ID_REV" (13449) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Copy of GL_ID_REV" (13458) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x4004300A at CF-DFT Oracle Sales Fact, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: Truncation may occur due to inserting data from data flow column "IC_ORDER" with a length of 240 to database column "IC_ORDER" with a length of 1.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "SERIAL_NUMBER" (2680) on output "Sort Output" (2453) and component "Sort 1" (2451) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "ORG_ID" (13377) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "CUST_TRX_TYPE_ID" (13428) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Data Conversion 1.Copy of CUST_TRX_TYPE_ID" (13443) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "GL_ID_REV" (13449) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Copy of GL_ID_REV" (13458) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x4004300A at CF-DFT Oracle Sales Fact, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: Truncation may occur due to inserting data from data flow column "IC_ORDER" with a length of 240 to database column "IC_ORDER" with a length of 1.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "SERIAL_NUMBER" (2680) on output "Sort Output" (2453) and component "Sort 1" (2451) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "ORG_ID" (13377) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "CUST_TRX_TYPE_ID" (13428) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Data Conversion 1.Copy of CUST_TRX_TYPE_ID" (13443) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "GL_ID_REV" (13449) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The output column "Copy of GL_ID_REV" (13458) on output "Union All Output 1" (13361) and component "Union All" (13359) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x400490F4 at CF-DFT Oracle Sales Fact, REV GL SEGS [307]: component "REV GL SEGS" (307) has cached 780 rows.
Information: 0x400490F4 at CF-DFT Oracle Sales Fact, get oper unit [813]: component "get oper unit" (813) has cached 12 rows.
Warning: 0x802090E4 at CF-DFT Oracle Sales Fact, get oper unit [813]: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.
Information: 0x400490F4 at CF-DFT Oracle Sales Fact, get header txn type for IC flag [13685]: component "get header txn type for IC flag" (13685) has cached 768 rows.
Information: 0x4004300C at CF-DFT Oracle Sales Fact, DTS.Pipeline: Execute phase is beginning.
Information: 0x4004800D at CF-DFT Oracle Sales Fact, DTS.Pipeline: The buffer manager failed a memory allocation call for 100484768 bytes, but was unable to swap out any buffers to relieve memory pressure. 83 buffers were considered and 83 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
Error: 0xC0047012 at CF-DFT Oracle Sales Fact, DTS.Pipeline: A buffer failed while allocating 100484768 bytes.
Error: 0xC0047011 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The system reports 31 percent memory load. There are 8587960320 bytes of physical memory with 5869387776 bytes free. There are 2147352576 bytes of virtual memory with 1223802880 bytes free. The paging file has 12673945600 bytes with 9901600768 bytes free.
Information: 0x4004800D at CF-DFT Oracle Sales Fact, DTS.Pipeline: The buffer manager failed a memory allocation call for 100483760 bytes, but was unable to swap out any buffers to relieve memory pressure. 162 buffers were considered and 162 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
Error: 0xC0047012 at CF-DFT Oracle Sales Fact, DTS.Pipeline: A buffer failed while allocating 100483760 bytes.
Error: 0xC02020C4 at CF-DFT Oracle Sales Fact, order line id [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
Error: 0xC0047011 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The system reports 30 percent memory load. There are 8587960320 bytes of physical memory with 5972680704 bytes free. There are 2147352576 bytes of virtual memory with 1324290048 bytes free. The paging file has 12673945600 bytes with 10005012480 bytes free.
Error: 0xC0047038 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The PrimeOutput method on component "order line id" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047056 at CF-DFT Oracle Sales Fact, DTS.Pipeline: The Data Flow task failed to create a buffer to call PrimeOutput for output "Union All" (13359) on component "Union All Output 1" (13361). This error usually occurs due to an out-of-memory condition.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "SourceThread1" has exited with error code 0xC0047038.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread2" has exited with error code 0x8007000E.
Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread3" has exited with error code 0xC0047039.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC0047039 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: The final commit for the data insertion has started.
Information: 0x402090E0 at CF-DFT Oracle Sales Fact, TEMP OUTPUT [998]: The final commit for the data insertion has ended.
Information: 0x40043009 at CF-DFT Oracle Sales Fact, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at CF-DFT Oracle Sales Fact, DTS.Pipeline: "component "TEMP OUTPUT" (998)" wrote 0 rows.
Task failed: CF-DFT Oracle Sales Fact
Warning: 0x80019002 at SRW_ORACLE_SALES_FTBL: The Execution method succeeded, but the number of errors raised (15) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: CF-EPGT SRW_ORACLE_SALES_FTBL
Warning: 0x80019002 at CF-SQC Facts: The Execution method succeeded, but the number of errors raised (15) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at SRW_MAIN: The Execution method succeeded, but the number of errors raised (15) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "SRW_Main.dtsx" finished: Failure.
View 9 Replies
View Related
Jul 20, 2005
I am new to SQL server and I am looking for the best practiceregarding the following:The GUI for the database I am creating has a number of questions like:A. Where are you currently living?1) In my own home2) In the home of a family member3) In a shelter4) On the streetHowever, for speed of data entry, the GUI (it's a web front-end,ASP/VBScript) accepts the number only. Futhermore the database onlystores the number entered.So for example for the above question, when the user is doing the dataentry, they would just enter a 1, 2, 3, or 4.However, when viewing the data, the end user wants to see the textthat corresponds to the number. So for example, if the user entered"3" for the above question, when they are viewing the data in the GUI(not to make edits, just viewing), they want to see "(3) In ashelter".My question is how best to display these friendly values?Should I do all the conversions in a stored procedure? If so, whatwould be the best way to do that? I tried something like this:SELECT Current_living =CASE Current_livingWHEN 1 THEN '(1) In my own home with my partner'WHEN 2 THEN '(2) In the home of a family member'ELSE NULLENDFROM tbl_DemographicInfo WHERE ClientID = @ClientIDbut I can't figure out how to do this for multiple fields.ALSO, I'm not sure if burying this stuff in a stored proc is the wayto go, because if the client wants a slight change to the friendlyname I have to get into the SP and change it. Seems like there mustbe a better way.I can also do this in the ASP code, but that also doesn't seem likethe best practice.I can have a lookup table, but again, where do I do the conversion?In an SP? With a view?Again, I'm looking for the best practice here. I know some ways Icould do it (like in the ASP), but I want to do it efficiently andcorrectly.Any help/comments are greatly appreciated.thanks in advance,Jon LaRosajlarosa at alumni dot brown dot edu
View 2 Replies
View Related
May 25, 2006
I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.
I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.
I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)
Thanks,
Josh
View 2 Replies
View Related
May 14, 2007
The notorious situation: a duplicate key exception. Users register themselves using fancy nicknames.This often leads to conflicts. In addition, as I point out in error reporting -- to except or not expect or return, the conflicts can not be prevented in a distributed environment (one ensures that there in no such name and still can get a concurrent conflict). For these reasons, the apps should be prepared to the conflicts. The cases to which apps are prepared, are not exceptional.
Meantime, if the engine insert process fails things naturally, we get an exception with a message like "Violation of UNIQUE KEY constraint 'col_name'. Cannot insert duplicate key in object 'table_name'. At first, it is not quite informative. Customers prefer to see something like: "a user with such a name already exists". Additionally, as explained in the referred topic, exception processing is extremely CPU consuming and should be not normally used as a function "feedback" tool.
The option to prevent the exception raising by preceding the INSERT op by a table-locking SELECT is not attractive either because of 1) locking and 2) double checking. Obviously, we get the conflict exception on INSERT because the DB engine precedes the insertion by a select. How it would check the existence of the same key in the table otherwise? I want to say that the only thing we should do is to override the default DB error handler.
How do I know that the error was caused by some specified constraint violation letting other issues to fail naturally? Should I parse the exception message looking for key_name?
View 5 Replies
View Related
Apr 1, 2008
I have been able to export to Excel with success. However, here is a big issue
Destination spreadsheets that contain colums. This seems to work OK but what is strange (by design???) is that appearently it reads the first seven or several rows and then determines the data types (numeric or string). Why doesn't it just use the data types from the source fields? If the last row is column headings (strings, such as Jan, Feb, etc) it treats EVERY row data element in the column as string or character data type. Why??? For my testing, I had to put a full row of 0's in my monthly buckets, so the data would be imported as numbers in the spreadsheet, otherwise it was string or charater data type.
Please tell me there is some logical reasoning behind this Excel export "feature". Or is there someting I am missing? I really want the destination Excel spreadsheet to be a template and have formated Title, Headings (different fonts), columns etc. My thinking is that it would just drop the data at the end of the spreadsheet (in this case the first row following the column headings) with the same data types as the fields in the source. I hope I am wrong and there is away to accomplish exporting to Excel as described above. A very simple approach, Microsoft could have possibly taken is have the ability to specify the starting row, column as a propert(ies) in the Excel Destination object / control.
Thanks in advance for your help!
SkySeek
View 4 Replies
View Related
Oct 5, 2007
Hi experts,
What I am trying to do is to open an existing report in a Print Friendly format .
What is the simplest way to do this using Visual Studio?
Here's the sort of thing I hope is feasible:
I can open my Report on a web page , then on that page can be a button / link for the user in case they want to open
that report in a separate window.
So I'd like to jump to that page by jumping to an URL and sending all parameters
something like :
http://Dev_Server/Reports/Pages/Report.aspx?ItemPath=%2fMy+Reports%2fTest+Report&rsaramOne=Parameters!paramOne.value + paramTwo=Parameters!paramTwo.value + etc all other parameters , I have on that report
Any suggestions would be really appreciated .
Regards,TIA
View 3 Replies
View Related
Apr 23, 2007
I currently have a export that takes data from my SQL Server 2005 DB and exports it into Excel. This process works correctly. My excel template has the first row headers and the data is dumped in the row after the header. I would like to know if it is possible for me to add borders around my data without doing it within the template? I don't know how much data is going to be exported so I can't put borders within the template. I put borders around the headers to see if it will copy the formatting down to the data and it didn't. Thank You for any help.
View 6 Replies
View Related
Nov 22, 2006
I am using Office 2007 beta. I have a SSIS package that exports the records from sql server to excel file, when number of records is less than 24000 then it exports well, but if number of records is greater than 24000 than it does not export anything to excel file.
But when I give administrative privilages to the service account under which the SSIS package is running, it export even more than 24000.
On prod server giving administrative privilages to service account is not a good option. I don't know what are the minimum permissions it needs while exporting more data into excel 2007 file.
I thought this is the problem in office 2007 beta, but same behaviour is with RTM also.
Thanks in advance.
Atul
View 2 Replies
View Related
Oct 23, 2007
Hi, the message below is generated when I try to generate an SSIS configuraiton file. The link does not provide any help. Do you have any suggestion what could cause this error?
Thanks, Piet
==========
TITLE: Microsoft Visual Studio
------------------------------
Could not complete wizard actions.
------------------------------
ADDITIONAL INFORMATION:
Could not generate the configuration file. (Microsoft.DataTransformationServices.Wizards)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=CouldNotGenerateConfigurationFile&LinkId=20476
------------------------------
Failure exporting configuration file.
(QAYTriggerNotify)
View 9 Replies
View Related
Oct 20, 2007
Hi,I am using sql server 2005 and i am having table with a column which has unique key constraint.So if the user tries to enter the duplicate value for that particular column and try to save it ,it errors out in runtime..how do i raise the user friendly error message in this DB error, when user tries to save it.Thanks
View 9 Replies
View Related
Dec 9, 2006
I have problems when exporting data into Excel file from SSIS. It all works fine with numeric columns but an apostrophe is attached at the beginning of each text cell. I tried using derived columns and data conversions but it didn't work. It seems to me that problem is in 'excel destination' task... I saw many people had this kind of problems too... Is there any solution possible?
Thanks.
View 3 Replies
View Related
Oct 23, 2007
I am exporting data to excel with ssis package.In my system it is working fine., but in server it is giving this error.All the connection string for database and excel file path are coming correct in run time with the help of package configeration.
one more thing is if we execute directly the package by double clicking in server it is working, but through my application it is giving this error.
An OLE DB error has occurred. Error code: 0x80040E09.
The ProcessInput method on component "Excel Destination" (22) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Thread "WorkThread0" has exited with error code 0xC0202009.
someone help me.. thanks in advance.
View 4 Replies
View Related
Apr 24, 2015
Is there a way to display a friendly message if a sub-report does not have content ex "No additional products available"...
View 2 Replies
View Related
May 19, 2015
I writing the data from sql table to flat file destination. I want to insert the record count in the first line of the destination file.
Record count must preceed 00.EX . Writing 4500 records from database should show 004500 in the first line of flat file.
I have an execute task to store the count in a variable now.
View 0 Replies
View Related
Apr 6, 2006
Hello,I would like to query the top 5 best companies' sales (total sales),then total the rest, what is the quickest and effective SQL to queryit?Thanks in advance
View 8 Replies
View Related
Nov 17, 2015
I need to create a dynamic top count of customers, plus the rest. I've seen many posts on this, however the difference I want is that the rest needs to be expandable to show what customers actually make up the rest category. For example:
Top
Cust1
Cust5
Cust2
Rest
Cust3
Cust4
I was thinking potentially the top/rest could be a shell dimension and then scope on these members to show the top and rest, however I haven't been able to achieve this.
View 5 Replies
View Related
Dec 17, 2007
I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?
View 17 Replies
View Related
Mar 20, 2008
HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it?
View 6 Replies
View Related
Feb 10, 2015
I am looking for an elegant way to return top n and sum up the rest, in my case CTE/temp table/variables are not allowed.
View 9 Replies
View Related
Apr 29, 2008
Hello, I am new here and to sql so please bare with me.
I am trying to create 2 select statements from the same field. The field is a job_description field that sometimes contains PO#'s. One select statement needs to extract the PO# if it has been written in the field and the other needs to extract all data that is not the PO#
Here is some example of the data:
New shirts for 2007
Magazine cover for may edition
Way to go postcards PO# 45687
PO#879
For the first select statement I need to capture just the PO#. the result for the above examples would be:
NULL
NULL
45687
879
For the second select statement I need everything but the PO#. The result would be:
New shirts for 2007
Magazine cover for may edition
Way to go postcards
NULL
So far I have put together a basic idea for the PO# part:
(SELECT SUBSTRING (job_description , (SELECT patindex('%PO#%', job_description)+3),10)WHERE job_description LIKE '%po#%')
If i could figure out a way to only return numeric data from the above i think it will work.
As for the second select statement i am at a loss on how to select everything but the PO#.
Any help at all is very much appreciated.
Thanks
View 2 Replies
View Related
Feb 21, 2008
I am using the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have created the 2 datasources, ordering each on a field commmon to both.
I have created the two derived columns headers and am now moving on to the merge.
It is failing with the following error:
"the input is not sorted"
And whilst I definitely have an order by on the query, when I look at the metadata between the datasource and the derived column, the Sort Key Position items displays "0" for all my fields, I was expecting the sort field to have a "1" in this column. What am I missing?
Any help would be most appreciated!
View 7 Replies
View Related
Dec 8, 1998
Hi, I am wrote the following code in one store procedure called p_bcp_all.
and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table?
thanks for your adivce
regards Ali
create procedure p_bcp_all as
Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tbltwo in d:bltwo.txt /fd:formatfilebltwo.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tblthree in d:blone.txt /fd:formatfileblthree .fmt /servername /Usa /password/b250000 /a8000"
GO
View 2 Replies
View Related
Oct 25, 2006
I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion
I am getting this error:
Invalid length parameter passed to the RIGHT function.
View 6 Replies
View Related
