Hi can not execute a package!
If I run the package from Visual Studio, it works;
If I deploy it and execute it, it works;
If I log in the server an execute it, it works;
If I execute it from a Task using the SQL Server Agent it works fine until I have a DataReader Source in the Package!
Is there a way to display messages? To see more details why it is not running?
Thanks!
Since I had a bad error in replication, my replication monitor won't show the tab "Error details or messages of the select session".Â
I am not able to activate or to show this "tab", to get all information about my error.
I added two screeshots, one with the 3 tab, and the other, were the information about my error are missing. How i can my default view back?
Replication Monitor OK:
My Replication Monitor with missing tab:
I had to know, which ID is causing a primary key violation. Instead of Replication Monitor, I would use t-sql.
Hi,
I have reports in reporting services. I want to add an introductory page to the report that shows a logo, report name , date and a few other details. However, I want to show that page only when user prints the report. Is this possible and if yes how?
Thanks
Hi,
I want to display details ection for each group in a single table using one dataset.
For ex:
Group 1 Row --> Name Age
Details 1 Row--> XXX 30
Details 2 Row--> YYY 20
Group 2 Row --> City Country
Details 1 Row--> CCC ZZZZ
Details 2 Row--> BBB AAAA
I have a report that is grouped based on a field. There is no way to pre-determine the number of detail lines each group will have. Is there a way to set the print condition such that if any group details do not fit onto one page then the whole group should be printed on the next page. Something like this:
Group1
DetailLine1 -------------
DetailLine2-------------
DetailLine3------------
-
-
DetailLinen-------------
Group2
DetailLine1 -------------
DetailLine2-------------
DetailLine3------------
-
-
DetailLinen-------------
Group3
DetailLine1 -------------
DetailLine2-------------
DetailLine3------------
-
-
DetailLinen-------------
If ALL Group2 details do not fit onto the same page print it on a new page. (I don't want each group on a separate page, I just want the group detail to start on a new page IF THEY DO NOT ALL fit on one page)
Hi,
I am executing a package using command line. I want to display messages on the console while the package execute. Which component will I use?
thanks.
In my requirement,My production server running more then 5 jobs . I want details in running schedule in minutes. For example
1) First Job
I am configured every one hour.
2) Second job
Configured in every 30 min
3) Third job
Configured every 60 min
4) Fourth Job
Configured every 24 hrs
I want the result
SNOJOBNameRunning Schedule(in Min)
1First JOB 60min
2Second JOB 30 min
3Third JOB 60min
4Fourth JOB 1440min
Hi,
After running BCP to export data from table to text file, we are getting some system generated messages in the log file as below :
output
------------------------------------------------------------------------------NULL
Starting copy...
NULL
81 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 30 Avg 0 (2700.00 rows per sec.)
(6 row(s) affected)
==========================
We want to suppress these messages while generating log file. Is there any option for this ?
Saritha.
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.
Scenario: (1) Initiator/Target are running on different machines; (2) Target is on SQL Standard Edition with service pack 1; (3) Initiator ends the conversation.
If Initiator is running on SQL Express Edition with service pack 1 or 2, €œSend message€? and €œEnd conversion€? will introduce two messages in initiator€™s sys.transmission_queue. One is empty message with correct message type; the other is empty message with no message type. Both of them are from the Target to the Initiator.
If Initiator is running on SQL Standard Edition with service pack 1, with same stored procedure (attached below), there is no message left in initiator€™s sys.transmission_queue.
We like to find the way to make Initiator on SQL Express Edition acting same as it on SQL Standard Edition, namely not left messages in the initiator€™s sys.transmission_queue
CREATE PROCEDURE [dbo].[CreateMessageForQueue]
@message varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [TCP://Initiator:4321]
TO SERVICE N'TCP://Target:4567'
ON CONTRACT LogContract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @handle
MESSAGE TYPE LogMessage(@message);
END CONVERSATION @handle
END
Thanks,
Hi,
I searched in the previous posting but can't find any or miss them. We have a few databases that users can run reports from, i.e. production, training & development. Is there any way, say put it in either in the header or footer, to display from which database that the report are running from? Sometimes they got confused whether the data is current or like few days old.
Any input is very much appreciated. Thanks in advance ..
hi all,
i have a package; thats executing another 4 packages, i can able to execute these packages in ASP.net
I want to display the status notification of the executing packages in the webpage( while packages are being executed, i want to display which package is executing, and what is the status of the package! whether it is executing or not? or it is raising any errors?) every thing i want to give in the front end--> its not my requirement, my client ask me to do that!
so please help me! how to display these status information of the running package in asp.net (c#)
(i'll give you one good chocolate if you give me good solution!)
post your mails to vivekanandaraj.natarajan@honeywell.com Thanks you a lot!
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View RelatedHello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
I would like to display a portion of report where there is data or no data
There is data subreport  display  Â
   Product Name Latex Gloves Â
   Product ID   Â
xxxx5678
 There NO data in the subReport
Â
  Product Name             Â
  Product ID  Â
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
View 6 Replies View RelatedHi,
Is there any way to capture the log details such as row count of the data flow,number of success rows and failed rows in a separate table?
Well I made a bussiness object for registering users as well as logging them in. I dont know if my bussiness obect is screwed up or if its the database, or the SQL syntax or what. Please take a look at this. I am not getting any error messages, but nothing is being added to the server. I am using MSDE for the SQL Server.
Here's my bussiness object:
Imports System
Imports System.Data
imports System.Data.oledb
NameSpace LoveShare
Public Class UserDetails
public UserID AS Integer
public FirstName AS String
public LastName AS String
public UserName AS string
public Password AS String
public Address AS String
public City AS String
public State AS String
public Zip As Integer
public Email AS String
End Class
Public Class User
Private objConn As New oledbConnection("Provider=sqloledb;Data Source=GARAGESALELOVESHARE;Initial Catalog=LoveShare1;User Id=sa;Password=notonthispost;")
Public function Login(strUsername AS String, strPassword As String) As Integer
dim intID as integer
dim objparam AS new oledbparameter
dim objcmd AS oledbCommand
objCmd = new oledbCommand("dbo.SPLoginUser", objconn)
objcmd.commandtype = commandtype.Storedprocedure
objparam = New oledbparameter("@UserName", oledbtype.Char)
objParam.Value = strUserName
objCmd.Parameters.Add(objParam)
objParam = New oledbParameter("@Password", OleDbType.Char)
objParam.Value = strPassword
objCmd.Parameters.Add(ObjParam)
Try
objConn.Open
intID = CType(objCmd.ExecuteScalar, Integer)
objConn.Close
Catch e As Exception
Throw e
End Try
If intID.toString = "" Then
return 0
End if
Return intID
end function
Public Sub AddUser(objUser As UserDetails)
Dim intId as integer
Dim objReader As oledbdataReader
Dim objCmdID As New oledbCommand("SELECT MAX(userID) FROM tblUsers", objconn)
Dim objcmd AS New oleDBCommand("spAddUser", objConn)
Dim objparam AS OleDbParameter
objCmd.CommandType = CommandType.StoredProcedure
objParam = New oleDbParameter("@FirstName", oledbtype.Char)
objParam.value = objuser.FirstName
objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@LastName", oledbtype.Char)
objParam.value = objuser.LastName
objCmd.Parameters.add(objParam)
objParam = new oleDbParameter("@UserName", oledbtype.Char)
objParam.Value = objUser.UserName
objCmd.Parameters.Add(objParam)
objParam = New oleDbParameter("@Password", oledbtype.Char)
objParam.value = objuser.Password
objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@Email", oledbtype.Char)
objParam.value = objuser.Email
objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@Address", oledbtype.Char)
objParam.value = objuser.Address
objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@City", oledbtype.Char)
objParam.value = objuser.City
objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@State", oledbtype.Char)
objParam.value = objuser.State
objCmd.Parameters.add(objParam)
objParam = New oleDbParameter("@Zip", oledbtype.Integer)
objParam.value = objuser.Zip
objCmd.Parameters.add(objParam)
Try
objConn.Open
ObjCmd.ExecuteNonQuery
objUser.UserID = CType(objCmdID.ExecuteScalar, Integer)
objConn.Close
Catch e AS Exception
Throw e
End try
If objUser.UserID.ToString = "" then
objuser.UserID = 25
End if
End Sub
End Class
End NameSpace
Here's is my stored procedure for the registration:
CREATE PROCEDURE [dbo].[SPAddUser]
@FirstName VarChar(255),
@LastName VarChar(255),
@UserName VarChar(255),
@Password VarChar(255),
@Email VarChar(255),
@Address VarChar(255) ,
@City VarChar(255),
@State VarChar(255) ,
@Zip [INT]
AS
INSERT INTO tblUsers (FirstName, LastName, UserName, Password, Email, Address, City, State, Zip)
VALUES (@FirstName, @LAstName, @UserName, @Password, @Email, @Address, @City, @State, @Zip)
GO
And here is the actual registration page
<%@ Page Language="VB" %>
<%@ Register tagPrefix="LoveShare" TagName="Header" src="head.ascx" %>
<%@ Register tagPrefix="LoveShare" TagName="Menu" src="Men.ascx" %>
<%@ Register tagPrefix="LoveShare" TagName="Stats" src="Stats.ascx" %>
<%@ Register tagPrefix="LoveShare" TagName="Footer" src="foot.ascx" %>
<%@ Import Namespace="system" %>
<%@ Import NameSpace="System.Data" %>
<%@ Import NameSpace="LoveShare" %>
<script runat="server">
Public sub Submit(sender as object, e as eventargs)
If Page.IsValid then
dim objuserdetails as New LoveShare.UserDetails
dim objuser As New LoveShare.User
objuserDetails.FirstName=tbFirstName.Text
objuserDetails.LastName=tbLastName.Text
objUserDetails.Username=tbUserName.Text
objuserDetails.Password=tbPassword.Text
objUserDetails.Email=tbEmail.Text
objUserDetails.Address=tbAddress.Text
objUserDetails.City=tbCity.Text
objUserDetails.State=tbState.Text
objuserDetails.Zip=tbState.Text
ObjUser.AddUser(objUserDetails)
Session("UserID") = objUserDetails.UserID
FormsAuthentication.SetAuthCookie(objUserDetails.UserID, false)
Response.Redirect("index.aspx")
else
lblMessage.text="Information entered incorrectly"
End If
End Sub
</Script>
<html>
<body BGCOLOR="00ccFF">
<Table Width="800">
<tr>
<td>
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td>
<LoveShare:Header runat="server" />
</td>
</tr>
<tr>
<td>
<Table cellpadding="0" cellspacing="15" Width="800">
<tr>
<td>
</td>
<td>
<td>
<form runat="server">
<table cellpadding="3" cellspacing="0" width="100%">
<tr>
<td colspan="2" Align="center">
<Font size="6">Sign Up Today!<BR><BR></Font>
</td>
</tr>
<tr>
<td Align="Center">
<Font color="red">
<asp:label id="lblMessage" runat="server" />
</font>
</td>
</tr>
<tr>
<td Align="right">First Name:</td>
<td Align="Left"><asp:textbox id="tbFirstName" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbFirstName" ErrorMessage="First Name Required" Text="Forgot First Name" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Last Name:</Td>
<td Align="Left"><asp:textbox id="tbLastName" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbLastName" ErrorMessage="Last Name Required" Text="Forgot Last Name" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Desired UserName:</td>
<td Align="Left"><asp:textbox id="tbUserName" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbUserName" ErrorMessage="UserName required" Text="Forgot UserName" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Password:</td>
<td Align="Left"><asp:textbox id="tbPassword" Textmode="Password" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbPassword" ErrorMessage="Password Required" Text="Forgot Password" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Verify Password:</td>
<td Align="Left"><asp:textbox id="tbVPassword" Textmode="password" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbVPassword" ErrorMessage="Verify Password" Text="Forgot to verify Password" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Email Address:</td>
<td Align="Left"><asp:textbox id="tbEmail" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbEmail" ErrorMessage="Email address required" Text="Forgot Email address" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Verify Email Address:</td>
<td Align="Left"><asp:textbox id="tbVEmail" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbVEmail" ErrorMessage="Must verify Email address" Text="Verify Email address" Display="Dynamic"/>
</td>
</tr>
<td Align="right">Street Address</td>
<td Align="Left"><asp:textbox id="tbAddress" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbAddress" ErrorMessage="Street Address Required" Text="Forgot Address" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">City:</td>
<td Align="Left"><asp:textbox id="tbCity" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbCity" ErrorMessage="City Required" Text="Forgot City" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">State:</td>
<td Align="Left"><asp:textbox id="tbState" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbState" ErrorMessage="State Required" Text="Forgot State" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right">Zip:</td>
<td Align="Left"><asp:textbox id="tbZip" runat="server" />
<asp:requiredfieldValidator runat="server" Controltovalidate="tbZip" ErrorMessage="Zip Code Required" Text="Forgot Zip" Display="Dynamic"/>
</td>
</tr>
<tr>
<td Align="right"> </td>
<td Align="Left"><asp:button id="btsignup" text="Sign Up" onclink="submit" runat="server" />
</tr>
</table>
</form>
</td>
<td>
</td>
</tr>
</table>
</td>
</tr>
</tr>
<td>
</td>
</tr>
<tr>
<td>
<LoveShare:Footer runat="server" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>
Please help, I cannot figure out what is wrong
Hi,
I need to get the index_name,colum_name and table_name in entire database.
Coule you let me know how I can get that details.
Thanks,
Hi,
I used my DB and some tables for replication earlier, now i removed my all replication subscription and publication and my DB is out of replication.
Now i am altering my table for PK then its giving err as follows:
Cannot alter the table 'mstparty' because it is being published for replication.
From where i can find details, i mean in which sys table all replication obj are stored.
There is no Replication now on DB then why still table 'mstparty' has a replication err when i alter table.
Please advice as i am not able to alter my table or tables which earlier i used in replication and now i removed replication.
I have a database which has contact column eg. Mr Peter Smith
I am writing a new database which is to have three seperate columns.. saluation, first name and surname. What would be the best way to split the column up?? I was thinking on concentrating on the spaces??
Note: some conacts may not have saluation inc in the contact column, and in this case the saluation column should be blank...
Thanks
Hello
Is there any script or procedure which can give the object name , type ,status and owner in a database.
Thanks
Can somebody please go in depth detail what exactly is done in OLAP? i know it deals with the data warehouse end. but what does the SQL DBA do in the OLAP end? please elaborate. thanks.
View 5 Replies View RelatedHow can i get the details of the error in sql server 2000 such where itoccurs in which line. I need something like i get in sql query analyzer.--Message posted via http://www.sqlmonster.com
View 4 Replies View RelatedI Have so many user create trigger's in my database
like create trigger trig_name
Now i want to see what does what and delete the unnecessary ones.
How can i See the logic in the trigger.
I can get the trigger name but not the logic using this query .
SELECT S2.[name] TableName, S1.[name] TriggerName,
CASE
WHEN S2.deltrig = s1.id THEN 'Delete'
WHEN S2.instrig = s1.id THEN 'Insert'
WHEN S2.updtrig = s1.id THEN 'Update'
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id]
How to see the logic.
Where is, (or even does it exists) the best place to look for some details on when package execution fails if running as a scheduled job. Obviously when you run from the command line or in VS, there is plenty of output detail on progress and on the source of errors, but when you run it as a scheduled job, it just says step 1 failed in the sql server log, and package foo failed in the NT application log . Is there anywhere to find this info or do we need to build error traps into the package to write stuff out somewhere?
THX
Dave
First, soapbox questions for someone who might have more direct pull with Microsoft (or at least knowledge of how I should work with what they've given us), then a more specific question:
Scenario: You install SQL Server Express (or any other version, it doesn't matter) and the feedback with all the little green checkmarks tells you it has installed successfully. "Oh goody," say people (like me) who are not super experienced with SQL Server, "it installed successfully; now I can jump in and start using it!" But it aint so--there are so many other hoops to jump through before it really becomes operational.
After successful installation, why can't some link become visible indicating what you have to do after that? I mean, it's fine to wade through all the questions in this forum and get answers from all you nice and informed guys, AFTER the problems start coming; but I would rather not have to associate with you, to be honest, in terms of time spent that could be going into my projects.
Why isn't there a comprehensive guide right off the bat? That is, something referenced immediately that tells you such things as: how to register a database with emphasis on the fact that just creating a database won't do the trick for your application; how to set permissions and rights; that (what was I reading in the advice of one post?) you have to register both the database and the user, blah blah; all about instances, the web.config file, etc. etc. I mean, doesn't it seem logical that by virtue of a person installing the database, come on, that this is a pretty good indication that he/she in all likelihood is new at it? As it is now, all they tell you on the SSE site is that you have to have the .NET Framework installed; and the green checkmarks indicating that you've installed it correctly (sorry for mentioning that twice).
"OK, self," I say, "I have the Framework installed and SSE installed correctly, so let's get to work." Then the trouble begins. Unless Microsoft's purpose is to keep you tech guys and book writers in business, I don't understand why they don't give more up-front guidance. Any thoughts on why they work it this way? Bueller, Bueller? Anyone, anyone?
Now, to the question that's pressing me at the moment: I've installed SSE and I'm going through a tutorial ("How Do I: Create Data-Driven Web Sites?" on http://www.asp.net/getstarted/default.aspx?tabid=61). Yeah, the guy makes it look really easy. He creates a database then shuts down the connection. I try to do the same things and I'm informed that I don't even have a valid connection! How SQL-Server-Express fun and easy is that? It's a real AdventureWorks, if you ask me.
OK, that's my rant; and since MS didn't think in advance and implement my idea of a link to jump off from the installation menu, I have to face the reality of learning this by putting all the pieces together myself. So, my question is, before I try to go through the video and get more frustrated, WHERE DO I GO FOR THE MOST BASIC OF ALL THE STEPS I NEED TO DO, ONE BY ONE? This is like the second "beginner" video I've tried to go through but even these don't start at the very beginning with information on how to register a new database, making the user a part of aspnetWHATEVER, what permissions to dole out, etc.
Guidance is solicited and will be most appreciated. Thanks, Bryan
Master table (tlbProduct) having productID as Primary key and which acts as reference key for table (tlbCategory).Fields for tlbProduct are productID,productNameFields for tlbCategort are productID,CategoryID(primary key),CategoryName,Prizeboth productID,CategoryID are autoincrementing.but when i write two inserts simultaneously as follows insert into tlbProduct(productName)values(@productName)insert into tlbCategory(CategoryName,Prize) using sqldatasourceI get the error that ProductID value is null which is not providedBut (productId in both is autoincremented) and relationship is there in both tables How to resolve this problem without adding ProductID in second Insert?SWati
View 1 Replies View RelatedHI, I'm using Visual Web Developer and SQL Server 2005 Express Editions. I have a database with two tables, say AccountsTable and PersonsTable, with a one to many relationship respectively.
Given a particular selected record in PersonsTable, I need to look up the corresponding master record in AccountsTable. Here's what I've done:
SqlDataSource1 has a "SELECT * FROM PersonsTable" and a GridView1 which uses this as its datasource and the 'enable Selection' checkbox is On. This works fine.
Then, I create a second SqlDataSource2. When I click on the WHERE button in the Configure Data Source page, I have chosen the following parameters:
Column:PrimaryKeyField; Operator:=; Source=Control; ControlID=GridView1; Default Value='I left this blank' but the Value field shows GridView1.SelectedValue when I click the ADD button.
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:bizConnectionString %>"
SelectCommand="SELECT * FROM [AccountsTable] WHERE ([AccountNumber] = @AccountNumber)">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="AccountNumber" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
I have bound DetailsView1 to SqlDataSource2. When I run the page, the GridView1 is shown with Persons records OK. Then I click the 'Select' next to a record in the Gridview expecting the corresponding lookup to the master record (Account) to be shown in the DetailsView, but it does not show anything.
What am I doing wrong? Is there a better way of doing this?
Your help is much appreciated. Thank you.
PS. I could find Tutorials showing Master/Details retrieving records in a top down fashion (1:n), but I could not find the reverse (n:1)
Does anyone know if the enhanced data types (character length up to 8,000 bytes for some types) and the increase in the number of tables used in joins are available when using the 65 backward compatibility mode?
Many thanks in advance...