Hi,
is it possible to to modify/add to QA (using options ,macro ..)to execute line bellow on run of each script opened
from *.sql file
select
'script= put you file name'+ char(13)+
'server = ' + cast(@@servername as char(20))+ char(13)+
'dbname= ' + cast(DB_NAME()as char(20)) + char(13) +
'execution date= '+ cast(getdate()as char(24))+ char(13)+
'executed by = '+ system_user
thanks
Alex
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.
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" /> <!-- Populate the Columns collection declaratively. --> <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(); } }}
How can I execute am .sql script that was generated from SQL Server?
View 4 Replies View RelatedHi all,
I have some problem to need you all help. My problem is i have a Excel Worksheet with VBA coding, in VBA code it have modules,function and sub procedures. after that, i change the VBA coding in add-in file and save as .xla to make the user friendly.
But now, i need to use DTS to automatic to execute the excel file that contain VBA code every week. This is because the VBA in excel cannot automation excute by every week. So, i need use DTS to make it automation. The problem is i don't know how to use the DTS function to call the addin excel to execute? Which the connection and task compenent should i use? Or how do i paste the VBA code in DTS?
DTS is very new for me, are any help can provide me the solution? thanks
Regards,
Andy :confused:
i have a piece of code in an adp that is currently behind a form but i could put it in a module and make it public if i need to. how can I use DTS to run this piece of code? which connection and task could i use. can this even be done? i have little experiance with DTS.
thanks.
justin
I am creating a stored procedure to generate the sp_addrolemember syntax, since SQL 2005 does not script role members. The stored procedure is named sp_ so it can be executed from any database. However, any reference to a view beginning with sys. only gets executed in the master database and not the current database. Why?
Try this code for a test.
Use Master
GO
Create Procedure sp_test as
Begin
Select g.name, u.name
From sys.database_principals u,
sys.database_principals g,
sys.database_role_members m
Where g.principal_id = m.role_principal_id
And u.principal_id = m.member_principal_id
And u.name <> 'dbo'
Order by g.name, u.name
End
-- Run sp_test from a database containing fixed database role members. The select only runs against master and not the current DB.
Hi I have a console application from which I want to execute a package.
How can I do it. Can you also tell me what references I need to include to make the code compile.
Please I need to do it ASAP. Can any one help me with this.
Thanks
Sai
is this possible? if so, how?
thanks
Hi all,
I am excuting DTS package from code behind and the table is created in the SQL database. However, the data in my excel sheet is not transferred over and Im not sure where the problem lies. Below is my code:
pkgLocation = @"C:/Program Files/test.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation,null);
pkgResults = pkg.Execute(); Any advice is much appreciated.
Hi All
I just have a excel macro code, It is working in excel itself.
But I want to execute within SSIL package.
When I am trying through Active X, It is returning error.
Can any one help me please....
MichaelRaj Arokiyasamy
Hi,
Is it possible to connect to a database and fetch data from it from within custom code inside a report?
Appreciate any help.
Regards,
Asim.
hi all
I have to execute the stored procedure from code file
string constr = ConfigurationSettings.AppSettings["ConnectionString"];SqlConnection con = new SqlConnection(constr);con.Open();SqlCommand cmd = new SqlCommand("GetTax",con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter paramFrom = new SqlParameter("@from", SqlDbType.VarChar, 50);paramFrom.Value = "JFK";SqlParameter paramTo = new SqlParameter("@To", SqlDbType.VarChar,50);paramTo.Value = "HOU";SqlParameter paramAirline = new SqlParameter("@Airline", SqlDbType.VarChar,50);paramAirline.Value = "US";SqlParameter rpTax = new SqlParameter("@Tax",SqlDbType.Int);rpTax.Direction = ParameterDirection.Output;cmd.Parameters.Add(rpTax);
insted of this way can i execute stored procedure any other way
like
exec MystoredProc "param1"."param2","param3"
i appreciate u r help
Hi!
Is it possible to execute a SSIS package using Java? How?
Thank you!
Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
hi, I need to run a batch file in specific time of the week. can anyone show me the code to run a batch file with both ways: window nt schedualer and ms sql server task manger.... I do appreciate your help
Ali
Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.
View 4 Replies View RelatedIn Executing "E:EmailDelivery.exe" "EP12A 4" at "", The process exit code was "-532459699" while the expected was "0".
View 4 Replies View RelatedHi all,
I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE ChemAveRpd
GO
WITH PivotedLabTests AS
(
SELECT LT.AnalyteName, LT.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)
FROM LabTests LT
JOIN Samples S ON LT.SampleID = S.SampleID
GROUP BY LT.AnalyteName, LT.Unit
)
SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,
avg2 = (abs(Prim + QA)) / 2,
avg3 = (abs(Dupl + QA)) / 2,
RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,
RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,
RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2
FROM PivotedLabTests
GO
===========================================
How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?
Please help and respond.
Thanks in advance,
Scott Chang
I have a requirement in which i have to create a custom .net class library for Ex:-I retrieve password(s) from a thrid party component. Below is what i am doing.
(1) Created a custom class library which reads a custom .xml file from a drive Ex:- "D:MyAppMYAppCofig.xml" and sets to my properties of my custom class library and inside it i created an instance of third party component's class and passed these values. Since i need to use this .net custom class library both in web and ssis/database side i am using this custom .xml file.
(2) After validating passed data (properties set in custom .net class library) the thrid party component instance object created in my custom .net class libraty returs a password to me own custom .net class libray.
(3) This password I use in my web app for connecting to database. This code is working fine.
(4) My question is how to execute a custom .net class library code through ssis and to use the my same custom .net class library and pass the password to my SSIS component / taks so that that code block also uses the returned password to connect and do any needed tasks? In other words how to use custom .net class library from SSIS.
My Environment is as follows:-
SQL Server is : 2008 R2
VS.NET 2013
I'm having a hard time locating a listing of potential error codes and the meanings for the Execute Process Task component...can someone assist?
Thanks
Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server
What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%
However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".
There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log
Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
thanks in advance
Dave
How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.
Thanks,
Monisha
I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.
For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.
I am using the Execute Process Task and the process parameters I am providing are:
WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat
The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"
the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.
The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :
[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]
Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.
Can anyone help ?
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Hi
I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use.
Is there a Possibility to make a "force detach" or something?
Thanks for Help
Greets
Id like to know how I force the transaction log to shrink in v2000?
I have sp_force_shrink_log but this only works in v7.
... Also, how do I set the log to truncate on checkpoint?
Many thanks,
Lauryn
How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc.
Pretty much how to force it being on number I would like to begin.
Hi All
Forgive my ignorance. I have been told you cannot run a force restore without SQL Agent running? Is that so?
Also, is it possible to execute a force restore from a command line. If so, how would you go about do this.
I ask these questions because recently our database and MSBD files became corrupted and couldn't restore them in the normal way. Had to manually rebuild MSDB file.
Thanks
I have a script or SP that takes a very long time to performmultiple tasks, and after each one there is a PRINT statementthat shows the time and what was just accomplished, to help memonitor what's happening and estimate how long it will take.In a script, I can put a GO after each PRINT to cause the outputto appear immediately, but that's not possible inside an SP.Instead, it seems the output goes to a buffer, and the bufferis only output when a PRINT causes the buffer to become full.Sometimes there is a long delay before the buffer fills. Isthere a way other than GO to cause immediate printing? (Iguess I could just use longer messages, and fill up thebuffer with every one, but that's not very elegant.)Thanks,Jim GCountrywide Home Loans
View 2 Replies View RelatedI've a complex stored procedure, that makes a lot of insert, update,delete and so on.I would like to make some commits durint this sp, but of course theyare not "real" commit because who call the sp could decide for arollback.But I know that this commit has to be real. In fact, the transactionlog grows really too much during the execution.Is there a way to force a commit durint a sp ?thank you very much!
View 3 Replies View RelatedI set up odbc to link to sql express from access 2003, using sql authentication to force a login, and did not click 'save password'. when i open up access again, and open table, it goes right in without a login. How can I set so it does a login, at least each time Access opens?
View 1 Replies View Related