MS SQL Server :: Execute Dts

Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query

Dec 6, 2006

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

View 5 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.

FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

SSIS Execute Package With Execute Out Of Process = True Causes ProductLevelToLow Error

Mar 6, 2008

Hi.

I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.

Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).

The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.

Any help would be greatly appreciated.

Thanks

Geoff.

View 7 Replies View Related

Conditional Execute By Execute SQL Task Return Value?

Jun 25, 2007

I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?

Or is it possible to conditional run other control flow items according the the status of SQL task execution?

View 1 Replies View Related

Execute A SP In The Execute SQL Task

Jan 25, 2007

I am trying to execute a SP in the execute SQL task in SSIS 2005..

but I keep getting an error:

SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load_Gs_Modifier_1
SSIS package "Package.dtsx" finis

I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????

View 3 Replies View Related

Trigger Not Execute Some Data Or Insert Not Execute A Trigger For Some Data

Mar 3, 2008

I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.

If insert data one by one to a table trigger fires success.
Please Help me.

View 1 Replies View Related

Using Execute

Sep 24, 2002

I am trying to return a recordset to an ASP page from a stored procedure.

The stored procedure creates a temporary table and then builds an SQL statement into a string declared with the SP. The string contains an INSERT INTO statement to insert into the temporary table and followed by a select of the rows I wish to insert into the table.

I then use the EXECUTE method to execute the SQL string. After this I have a SELECT which should select from the temporary table and return the rows i am interested in from the stored procedure.

For some reason this does not seem to work. If I execute the SP from query analyzer I get the resultset returned. However when I try to call the stored procedure from my ASP no recordset is returned. I have done this type of thing before and it has worked perfectly. Does anyone have any ideas why this may not be working? Many Thanks.

View 2 Replies View Related

Execute SQL From EM

Oct 28, 2001

Newbie Question: I'm in EM and I want to execute a simple SQL statement (e.g. exec a sproc). How do I do it? Do I have to fire up QA? or is there a simpler way?

View 1 Replies View Related

Execute (hex)

Oct 4, 2004

I don't remember where I saw it, but it looked something like this: ...exec (0x0A738... The result was identical to executing: select getdate() Anybody seen it anywhere?

View 4 Replies View Related

How To Execute SP

Mar 26, 2004

hi all,

i am having stored procedure like the below one:

create procedure pro_emplname
@name varchar
as
select * from employee where ename=@name

i have tried like this to execute:

exec pro_emplname kamal

but i am not getting the output....
could any one tell.....

thanks

View 1 Replies View Related

How Can I Execute *.sgl?

May 29, 2004

HI
i want to rite a sysntax in StoreProcedure that execute sysntaxes in My_file.sql .
How can i do this?
thanks

View 1 Replies View Related

Execute As 'sa'

Apr 15, 2008

I have two instances running on a machine...
The following code will run on the named instance but not on the default instance.

On the default instance I get the following error:
Cannot execute as the user 'SA', because it does not exist or you do not have permission.

However if I specify another account it does work.

use dbReport
go
DROP procedure dbo.clarktest
GO
create procedure dbo.clarktest
WITH EXECUTE AS 'SA' -- 'DomainSqlCmdShellDev'
as
EXEC xp_cmdshell 'dir'
go
grant exec on clarktest to [spexec]
exec clarktest
use master
go

Any assistance is appreciated as the developers code needs to be coded generically i.e. "sa" this is to prevent code manipulation during migrations...
Thank you

You can do anything at www.zombo.com

View 4 Replies View Related

Execute A Job From DOS

Jun 10, 2006

I want to start of run a job from DOS. I have MS SQL 2000 using Enterprise Manager.

Thank You,

Ernie

View 3 Replies View Related

How To Execute

Dec 1, 2006

hi,this is my stored procedure:

CREATE PROCEDURE loop As
declare @fromage bigint

begin

select @fromage=max(age) from emp1
end
begin
insert into loop values(@fromage)
end

how to run thi spc in sqlserver,please tell me

View 1 Replies View Related

How To Use 'execute As '

Feb 27, 2008

Hi Everyone,

I have problem in applying 'execute as'.

Here is an exsample SP
Create procedure test
with execute as 'user1'
as
select * from people.dbo.profile

I have 2 accounts: user1 and user2.
user1 has permission to select data from people.dbo.profile but user2 doesn't. due to some reasons I don't want to grant select permission to user2.

I use user2 to execute this SP and get error message

The server principal "user1" is not able to access the database "people" under the current security context.

How can I solve it? I have been looking for solution for the whole morning.

Thank you in advance.

View 1 Replies View Related

EXECUTE AS

Dec 10, 2007

Hi,
I have some problems with the execute as command.

I have a proc in a database, lets call it 'source'. The proc looks like this:

create proc backup_proc

with execute as 'account2'

as

select * into destination.dbo.loaddata from source.dbo.loaddata

exec dbo.backup_proc

I also have two accounts:
account1 - limited rights
account2 - extended rights

I wan't to be able to run the proc which uses account2(extended rights) to copy a table to another database. I get this error when i try to execute the proc!
The server principal "destination" is not able to access the database "source" under the current security context.

View 1 Replies View Related

Cannot Execute MDX Via RS...

Apr 3, 2007

Hi all,

I have an SQL stored procedure that executes the following MDX query over an OLAP cube:

SELECT * FROM OpenRowset(''MSOLAP.3'',''DATASOURCE=localhost;Initial Catalog=Analysis Services Project1;'','select {[Measures].[Qty]} on columns from [DB ATEST]')

If I call the stored procedure from Management Studio, it executes perfectly well and returns the result I am expecting: the result of the MDX query.

However, when I call this same stored procedure from Reporting Services, as I try to build up a new report, I get the following message:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "(null)".
The OLE DB provider "MSOLAP.3" for linked server "(null)" reported an error. The provider did not give any information about the error.
SELECT * FROM OpenRowset('MSOLAP.3','Initial Catalog=DB ATEST;','select {[Measures].[Qty]} on columns from [DB ATEST]')

------------------------------
ADDITIONAL INFORMATION:

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "(null)".
The OLE DB provider "MSOLAP.3" for linked server "(null)" reported an error. The provider did not give any information about the error.
SELECT * FROM OpenRowset('MSOLAP.3','Initial Catalog=DB ATEST;','select {[Measures].[Qty]} on columns from [DB ATEST]') (Microsoft SQL Native Client)

I've tried it with both an SQL Server and a OLE DB datasource.

I'm connecting locally on my PC (localhost).

Any clues as to why this works via Management Studio but not when called from RS ????

Many thanks to all,

John

View 4 Replies View Related

How To Execute

Mar 8, 2007

I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.

Can someone please give me a basic DTEXEC example?

Thanks in advance !

View 3 Replies View Related

Can't Execute Sp In Vb.net

Mar 19, 2007

Hellou
I'm trying to execute simple sp with 5 input parmeters from vb.net. This sp works great when it is run from SQL Server management studio, but when I want to execute it from vb.net, I allways get this error:
"Syntax error, permission violation, or other nonspecific error"
Here's the code:

Cnn.Open("...")

Comm.ActiveConnection = Cnn

Comm.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

Comm.CommandText = "spSklVnos-Prenos" 'that's thename of the sp

RetParam.Direction = ADODB.ParameterDirectionEnum.adParamOutput

RetParam.Type = ADODB.DataTypeEnum.adInteger

Comm.Parameters.Append(RetParam)

P1.Direction = ADODB.ParameterDirectionEnum.adParamInput

P1.Type = ADODB.DataTypeEnum.adVarChar

P1.Size = 1

P1.Value = LocParA

Comm.Parameters.Append(P1)

P2.Direction = ADODB.ParameterDirectionEnum.adParamInput

P2.Type = ADODB.DataTypeEnum.adInteger

P2.Value = LocPar1

Comm.Parameters.Append(P2)

P3.Direction = ADODB.ParameterDirectionEnum.adParamInput

P3.Type = ADODB.DataTypeEnum.adInteger

P3.Value = LocPar2

Comm.Parameters.Append(P3)

P4.Direction = ADODB.ParameterDirectionEnum.adParamInput

P4.Type = ADODB.DataTypeEnum.adInteger

P4.Value = LocPar3

Comm.Parameters.Append(P4)

P5.Direction = ADODB.ParameterDirectionEnum.adParamInput

P5.Type = ADODB.DataTypeEnum.adInteger

P5.Value = PrevzemIDx

Comm.Parameters.Append(P5)

Comm.Execute()

Am I missing something?
Thanks for any suggestion.
Dido

View 1 Replies View Related

Execute Procedure

Feb 7, 2007

Hello,I have an SQL procedure as follows: ... INSERT dbo.Levels (LevelName) VALUES (@LevelName) ... LevelName is an input parameter of nvarchar type. What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?Thanks,Miguel

View 7 Replies View Related

Execute Scalar ?

Sep 20, 2007

I'm trying to do something like the code below, but it's saying "specified cast is not valid"
If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.protected float getProjectHours(string project)
{string selectCmd = "SELECT SUM(hours) FROM tasks WHERE project=@project";
string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;SqlConnection myConnection = new SqlConnection(strConnection);
SqlCommand myCommand = new SqlCommand(selectCmd, myConnection);myCommand.Parameters.Add(new SqlParameter("@project", SqlDbType.VarChar));myCommand.Parameters["@project"].Value = project;
myConnection.Open();float total = (float)myCommand.ExecuteScalar();
myConnection.Close();
return total;
}

View 6 Replies View Related

How Do You Execute Udf Functions From ADO .net

Mar 25, 2008

I hope this is a right form for ADO .net type of question.
My question is, can you call SQL function the way you call stored procedure from ADO .net. I coded it this way and does not seems to be getting result set back. The DataReader is seems to be coming back with nothing. Can someone post an example. I know you can write "SELECT udf_function()" but I really mean the way the stored procedure is called. Thanks.

View 2 Replies View Related

Can't Execute Sql In Code-behind

Apr 18, 2008

I found a example of using a button inside of a gridview at http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.buttonfield.aspx.I modified the code-behind and added: // Insert the producer into the database SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource; sds.Insert(); but the page throws an error.Can someone look at my code-behind and show me how to execute the line 56 in the code-behind? Thanks. Object reference not set to an instance of an object.

Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 55: // Insert the producer into the databaseLine 56: SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource;Line 57: sds.Insert();Line 58:Line 59: } Below is my code: ASPX PAGE: <asp:Label ID="Message" ForeColor="Red" runat="server" AssociatedControlID="CustomersGridView" />  <asp:GridView ID="CustomersGridView" DataSourceID="CustomersSqlDataSource" DataKeyNames="ItemID" AutoGenerateColumns="False" OnRowCommand="CustomersGridView_RowCommand" runat="server" AllowPaging="True" AllowSorting="True" PageSize="50"> <Columns> <asp:BoundField DataField="ProducerName" HeaderText="Producer" SortExpression="ProducerName" /> <asp:BoundField DataField="ItemName" HeaderText="Item" SortExpression="ItemName" /> <asp:BoundField DataField="Year" HeaderText="Year" SortExpression="Year" /> <asp:BoundField DataField="RegionMasterName" HeaderText="Region" SortExpression="RegionMasterName" /> <asp:BoundField DataField="CountryName" HeaderText="Country" SortExpression="CountryName" /> <asp:BoundField DataField="StateName" HeaderText="State" SortExpression="StateName" /> <asp:TemplateField HeaderText="ItemID" InsertVisible="False" Visible="false" SortExpression="ItemID"> <ItemTemplate> <asp:Label ID="ItemID" runat="server" Text='<%# Bind("ItemID") %>' Visible="false"></asp:Label> <asp:SqlDataSource ID="sqlItemInsertIntoPart" runat="server" ConnectionString="<%$ ConnectionStrings:VBJimboConn %>" InsertCommand="INSERT INTO [ZCPart] ([PartUserId], [PartItemID]) VALUES (@PartUserId, @PartItemID)" OnInserting="sqlItemInsertIntoPart_Inserting" > <InsertParameters> <asp:Parameter Name="PartUserId" /> <asp:ControlParameter Name="PartItemID" ControlID="ItemID" PropertyName="Text" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> </ItemTemplate> </asp:TemplateField> <asp:ButtonField ButtonType="Button" CommandName="Select" Text="Add to Part" Visible="True" /> <asp:HyperLinkField DataNavigateUrlFields="ItemID" DataNavigateUrlFormatString="ItemDetails.aspx?ItemID={0}" Text="Details" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="CustomersSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CONNSTG %>"....> </asp:SqlDataSource> CODE-BEHIND:using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;partial class Inventory_InventoryList : System.Web.UI.Page{ protected void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) { if (!User.Identity.IsAuthenticated) { CustomersGridView.Columns[CustomersGridView.Columns.Count - 2].Visible = false; } } } protected void sqlItemInsertIntoPart_Inserting(object sender, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@PartUserId"].Value = Membership.GetUser().ProviderUserKey; }// Source for gvInventoryList to display message: http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.buttonfield.aspx protected void CustomersGridView_RowCommand(Object sender, GridViewCommandEventArgs e) { // If multiple ButtonField column fields are used, use the // CommandName property to determine which button was clicked. if(e.CommandName=="Select") { // Convert the row index stored in the CommandArgument // property to an Integer. int index = Convert.ToInt32(e.CommandArgument); // Get the last name of the selected author from the appropriate // cell in the GridView control. GridViewRow selectedRow = CustomersGridView.Rows[index]; TableCell contactName = selectedRow.Cells[1]; string contact = contactName.Text; // Display the selected author. Message.Text = "You selected " + contact + "."; // Insert the producer into the database SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource; sds.Insert(); } }}

View 2 Replies View Related

How To Write And Execute?

Jun 18, 2008

Hi all,
      i want to write a stored procedure to get the user details from userprofile table based on the time schedule from another table called reminders.
   the structure of reminder table is called
       jobtype - type of job that is sending mails to user or sending mails to employee like that
       frequency - how frequenctly sending mails that is daily or weekly or monthly
       weekday    - if the above option is weekly then we have to take which day on weekly to send mail
       dayofmonth - if the frequency option is monthly then we have to take this field for which day on month
      jobtime - At what time to send mails it may be daily or weekly or monthly
so i want to write the stored procedue based on above values to execute some task( that is getting userdetails and sending mails).
please help me.
Thanks!

View 6 Replies View Related

How To Execute A SQL (.sql) Script From Within VB?

Jul 13, 2004

Hello,

I am trying to figure out the best way to make VB6 execute an auto-generated .SQL file from SQL Server 2000.

SQL Server 2000 has created a script (about 1000 lines long), creating views and SPs, and I need to execute said script from a VB client application. Similar to the effect of pasting it into the Query Analyser, only automated.

Anyone have a suggestion (besides reformatting evey like and hard-coding it?)

Thanks a ton!

View 2 Replies View Related

Execute .sql From Code?

Mar 31, 2005

How can I execute am .sql script that was generated from SQL Server?

View 4 Replies View Related

I Can't Execute My Asp.net Application With IIS 5.0

Apr 6, 2005

Hi,
please i have a problem, i can't execute my asp.net application with IIS 5.0?
i have a message indicating that MYSERVERASPNET can't connect, and that arrive when trying connect to the database under SQL Server 2000.
you have to know that my application work very well with webserver of WebMatrix.
Web Matrix Project : version 0.6
.Net Framework 1.1
Thank you.

View 1 Replies View Related

Execute DTS Package From ASP.NET

Aug 19, 2005

I am trying to execute a DTS package in my asp.net code, but the dts package has both a owner and user password. I want to be able to use the user password to execute the package within the code, but whenever I try that I get the following error: Access to package properties requires entry of package owner password.

Line 101: oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "DTSPassword", , , "CopySourceTest1")
Line 102:
Line 103: For Each oStep In oPKG.Steps
Line 104: oStep.ExecuteInMainThread = True
Line 105: NextIt works fine if I execute with the owner password, it just will not work with the user password. Any ideas will be appreciatedThanks

View 8 Replies View Related

Execute DTS Package From Asp.net

Aug 27, 2005

Hi All,How we can call and Execute DTS Scripts From Asp.net.Can any one pls give me some help.RegardsVAsu

View 1 Replies View Related

SELECT And Execute

Apr 7, 2006

Hi:
I have to loop through some records in a table, then for each row i want to execute a procedure.
I know this can be done through Cursors, is there a way to do so just with SELECT Statement?
Thank you.

View 2 Replies View Related

Use C# Through ADO Execute StoredProcedure

Apr 16, 2006

Hi
public static void ExecuteStoredProcedure(string SPName, ref ArrayList Parameters)        {            object result=null;            ADODB.Connection Connection = new ADODB.Connection();            ADODB.Command Command = new ADODB.Command();            Command.ActiveConnection = Connection;            Command.CommandText = SPName;            Command.CommandType = CommandTypeEnum.adCmdStoredProc;
            if (Parameters != null)            {                for (int i = 0; i < Parameters.Count; i++)                {                    Command.Parameters.Append(Parameters[i]);                }            }
            try            {                Connection.Open(ConnectionString, "", "", 0);                Command.Execute(out object RecordAffected, ref object parameters, int options ) ;//the second parameter what mean? how set it?            }            catch (Exception ex)            {                throw ex;            }            finally            {                Connection.Close();            }
        }
Thanks

View 2 Replies View Related

Best Way To Execute A SQL Script ??

Nov 14, 2001

I am a software engineer about to release the next version of my software
which runs on SQL 2k. Since the last release I have altered, added, and
dropped many tbls, sps, views, triggers, etc. I have a fairly huge t-sql
script that when run will update an exisiting db to the new structure. My
question is, what is the best way to execute this script. I won't have
access to the client installations physically or through MMC. I think I will
write a VB app that the user will run on the SQL server and it will in turn
execute the script. But how do I do it?
Do I shell out to isql ?
Can I use DMO ?

What is the best, safest way to have an install/patch program execute a huge
t-sql script ??

Thanks
Josh

View 1 Replies View Related