I have code which generates packages programatically, and script task is a part of the control flow. I've succeded to set source code programatically, but I do not know how to put binary code, because I need to have my script task precompiled.
Just setting PreCompile = true does not solve this problem
Thanks in advance.
Borko
I've been working on adding "copy a script task from one package to another package" functionality to my SSIS Package Manager (PacMan - http://www.codeplex.com/pacman) utility in order to ease some of the pain I'm feeling in my main SSIS dev project these days. The code I have today looks something like this:
Code Block
public void CopyScriptTaskPrototype(PackageUtil sourcePackage)
{
// Basic logic taken from https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=941508&SiteID=1
const string CODE_MONIKER_TEMPLATE = "dts://Scripts/{0}/ScriptMain.vsaitem";
const string PROJ_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.vsaproj";
const string BINN_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.dll";
try
{
// Get the source task
TaskHost sourceScriptTaskHost = sourcePackage.ssisPackage.EventHandlers["OnPreExecute"].Executables[0] as TaskHost;
ScriptTask sourceScriptTask = sourceScriptTaskHost.InnerObject as ScriptTask;
// Get the target container
if (!this.ssisPackage.EventHandlers.Contains("OnPreExecute"))
{
this.ssisPackage.EventHandlers.Add("OnPreExecute");
}
DtsEventHandler targetContainer = this.ssisPackage.EventHandlers["OnPreExecute"];
// Delete the target task, if it exists
if (targetContainer.Executables.Contains(sourceScriptTaskHost.Name))
{
targetContainer.Executables.Remove(sourceScriptTaskHost.Name);
}
TaskHost targetScriptTaskHost = targetContainer.Executables.Add("STOCK:SCRIPTTASK") as TaskHost;
targetScriptTaskHost.Name = sourceScriptTaskHost.Name;
targetScriptTaskHost.Description = sourceScriptTaskHost.Description;
ScriptTask targetScriptTask = targetScriptTaskHost.InnerObject as ScriptTask;
targetScriptTask.SetUniqueVsaProjectName();
string sourceCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
string sourceProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
string sourceBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
targetScriptTask.CodeProvider.PutSourceCode(targetCodeMoniker,
sourceScriptTask.CodeProvider.GetSourceCode(sourceCodeMoniker));
targetScriptTask.CodeProvider.PutSourceCode(targetProjectMoniker,
sourceScriptTask.CodeProvider.GetSourceCode(sourceProjectMoniker));
// We've commented this out due to errors at package runtime
//targetScriptTask.CodeProvider.PutBinaryCode(targetBinaryMoniker,
// sourceScriptTask.CodeProvider.GetBinaryCode(sourceBinaryMoniker));
targetScriptTask.PreCompile = false; // We want to be able to say "true" here
}
catch (Exception ex)
{
throw new ApplicationException("Could not copy script task - oh no!", ex);
}
}
This method exists within a PackageUtil class that has a private member variable named ssisPackage of type Microsoft.SqlServer.Dts.Runtime.Package, and for my current purposes I only need to worry about copying the first task from the OnPreExecute event handler for the package, so some things are hard-coded now that won't be later on.
Now, with that said, here is the question: How, through the .NET API can I force the Script Task code to be compiled so that I can set the PreCompile flag to true and have it work as if I'd done the work through VSA in the designer.
Thanks in advance to anyone who can help out here. This is "icing on the cake" to some extent, as the core functionality I need is currently in place, but it would be excellent to have this 100% complete.
I have a script task that I've created that just displays a MsgBox as listed in Professional SQL Server 2005 Integration Services in chapter 4. The problem is that when I exit the VSA design tool there is a red "X" on the task that says in a popup:
"The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE..."
I go back into the script design, and the code is there, and the PreCompile propterty IS set to True. Attempting to EXECUTE the task only results in a similar error, just more verbose without actually giving any additional insight.
I've read the thread on where the VSA code is deleted on closing.. but my code is still there.. it just isn't seeing the binary code (if it actually exists).
Ideas, comments or snide remarks anyone?
- Mark
Hi All,
I am trying to create Script task programmatically. i am struck in one place where we will add the script code. I have given my code.
My Code:
TaskHost taskHost = package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") as TaskHost;
taskHost.Name = "Create SO";
taskHost.Properties["ReadWriteVariables"].SetValue(taskHost, "User:alesId");
Equvivalent XML code generated by the above code.
Code Block
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
<DTS:Property DTS:Name="ExecutionLocation">0</< FONT>DTS:Property>
<DTS:Property DTS:Name="ObjectName">Create SO</< FONT>DTS:Property>
<DTS:Property DTS:Name="DTSID">{4498C2BE-58B6-433C-8F3E-0268BD267BAE}</< FONT>DTS:Property>
<DTS:Property DTS:Name="Description"></< FONT>DTS:Property>
<DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</< FONT>DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property>
<DTS:ObjectData>
<ScriptProject Name="ScriptTask_681442c33ab344b7bedb4492b01245a8" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId"/>
</< STRONG>DTS:ObjectData>
Generated XML when done thru SSIS Editor. i have given only the DTSObject..thats where i am struck.
Code Block
<DTS:ObjectData>
<ScriptProject Name="ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId">
<ProjectItem Name="dts://Scripts/ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
In the DTSObject, i dont know how to add details thru C# code. Please help me.
Thanks.
Hi,
I have a package in which i have programatically created dataflow task. It used to work fine but now it fails with series of errors out of which one is below
Error 30002: Type 'MainPipe' is not defined. Line 63 Columns 33-40 Line Text: Dim DataFlowTask As MainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)
It seems all the function and classes referd from following dlls is not working:
Microsoft.SqlServer.DTSRuntimeWrap.dll
Microsoft.SqlServer.DTSPipelineWrap.dll
I say so because i get the following error also:
Error 30652: Reference required to assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' containing the type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90'. Add one to your project. Line 86 Columns 33-64 Line Text: DtsConvert.ToConnectionManager90(ChildPackage.Connections(""Source"")) These dlls are in GAC and in C:Program FilesMicrosoft SQL Server90SDKAssemblies with same version but still it gives above error.
Thanks
Mohit
Hi everyone,
I've used "ToLine" property but it seems is not related at all with that field.
Does anyone knows how to do such thing?
My goal is to load a table and by using For Each Loop task send emails...
It seems easy...
Thanks in advance,
Hi,
with a script task I get an error "This task is configured to pre-compile the script but the binary code is not found. Please visit the IDE in Script Task editor by clicking Desing Script button to cause binary code to be generated".
Well, if I do so the error doesn't disappear. The only chance I have is to switch of precopilation, which is quite a performance issue...
I have no idea where this error is comming from... The script is quite easy, just some string and file operations (find out file change date using system.io)...
Any idea?
I am pulling info out of MSDB to report on job schedules. As we have a mixture of 2005 and 2000 servers, I am varying my select statement according to the result of
[Code]
Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)
[/Code]
I have to vary the tables for each condition. Not a problem,
[Code]
if @Version = 9
begin
Select whatever from table1 join table2
end
Else
begin
Select whatever from table1 join table3
end
Problem is that SQL 2005 is trying to verify the existence of the fields in the tables on compile (it knows that the table, for example, sysjobschedules exists in 2005, but the field "Name" doesn't - although it does in 2000) , even after the if statement checking if the version is 8, and SQL 2000 uses 2 tables whilst 2005 needs 3, and the fields I need are on one table in 2000, but in a different table in 2005.
SQL 2000 is fine with it - it just does it.
Syntax checking is fine.
Just to clarify, here is my code:
[Code]
Declare @Version Char(1), @CurrentDate datetime
Set @CurrentDate = GetDate()
Create Table #TempSchedDetails
( Server VarChar(30)
,CurrentDate VarChar(19)
,JobName VarChar(80)
,ScheduleName VarChar(80)
,Freq_Type Char(1)
,Freq_Interval VarChar(2)
,JobEnabled Bit
,Freq_Subday_Type VarChar(25)
,ScheduleEnabled Bit
,StartTime VarChar(25)
,EndTime VarChar(25)
)
Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)
If @Version = '9'
Begin
Insert Into #TempSchedDetails
Select left(@@SERVERNAME,30)
,GetDate()--@CurrentDate
,J.Name
,SS.Name
,SS.Freq_Type
,SS.Freq_Interval
,J.Enabled
,SS.Freq_SubDay_Type
,SS.Enabled
,SS.Active_Start_Time
,SS.Active_End_Time
From msdb..SysJobs J
Left Outer Join msdb..SysJobSchedules JS on J.Job_Id = JS.Job_Id
Join msdb.dbo.SysSchedules SS ON JS.schedule_id = SS.Schedule_Id
Order By J.Name, Active_Start_Time
End
Else
Begin
Insert Into #TempSchedDetails
Select left(@@SERVERNAME,30)
,@CurrentDate
,J.Name
,S.Name
,S.Freq_Type
,S.Freq_Interval
,J.Enabled
,S.Freq_SubDay_Type
,S.Enabled
,S.Active_Start_Time
,S.Active_End_Time
From MSDB..SysJobs J
Left Outer Join MSDB..SysJobSchedules S On J.Job_Id = S.Job_Id
Order by J.name, Active_Start_Time
End
[/Code]
Any ideas as to how to turn the precompile checking off in 2005?
I have installed the Report Execution Sample reports and with it came the RSExecutionLog_Update.dtsx and instructions to enable it in a SQL Agent job. I have followed the instructions, however, where do I set the PreCompile property to false? Is there a way to pull in a dtsx file into BIDS?
View 7 Replies View RelatedHi,
I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried.
An example:
User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be
SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%')
As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures?
Kind regards,
ThaYoung1!
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
Carlos writes "I am trying to create a sp that would jump from one DB to another using the USE command. I built a string and then I tried to run EXECUTE(@SQLString) with no lock. This is the sample code I am using:
declare @DBName varchar(200)
declare @SQlString varchar(300)
set @DBName='model'
set @SQlString = 'use '+@DBName
execute(@SQlString)
Is there any way I can accomplish that?
Thanks
Carlos"
Using ADO.NET Or regular queries on SQL Server, is there a good way ofdetermining if a Stored Proc will be returning a result set or if it isa non-query that does all I/O through parameters?Thanks.
View 1 Replies View Related
Hi
I have a main report and sub report. The subreport has some fields like SYS, LOC, MSG etc.
But the report should display the values filtered basis on system. i.e. I must have a sub report
for SYS1 , SYS2 , SYS3 and so on. I can create a sub report for each system. But I want to create only subreport and
call them from the main report. How do I do this?
Thanks
Sai
Can anyone please direct me to an easy to understand Description, Article or Video tutorial that shows an example on how to programatically connect to an SQL database and retrieve data from a collumn using C# and VISUAL WEB DEVELOPER 2005 EXPRESS EDITION. Also, what namespace should be imported to do this? (From what I have gathered out of the help, it seem that System.Web.UI.WebControls is the one)
I am trying to create a "news letter" web page. I have a basic form and when I hit "send" then the code in the background should access the database with all the recipient info, and retrieve all the E-mail addresses, Names, etc. and include it in each e-mail.
( I don't know if it is just me, but the help included with VISUAL WEB DEVELOPER 2005 EXPRESS EDITION is extremely difficult to understand - from a beginner point of view).
Thanks!
Regards
Jan
Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks.
View 7 Replies View RelatedHi All,
I am having problems getting values out of an sqldatasource.
I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.
the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.
The insert is fine, i just don't know how to get the values out of the first 'select source'
Any pointers or suggestions most appreciated.
Cheers
In VS2003 I have a user defaults table that I would call in the OnLoad - No Postback section to get the user defaults and then used some of the fields from that recordset as Parameters for other recordsets that would also load in that same section and might even issue a different Sql statement based on the field. In VS2005 (VB.NET), I can no longer figure out how to accomplish this. I can use the SQLDatasource Control which seems much improved but I cannot even find the generated code which I would use as a quick way to create my own datasources in the OnLoad event. Where is this code now stored and how can I accomplish my objectives in VB.NET 2005 with the current framework or programatically create data sources as necessary as I did before?
View 3 Replies View Relatedhi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip( id int IDENTITY NOT NULL, ip varchar(15) NOT NULL, hostname varchar(128) NOT NULL, primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code thankssimo stored as a new query
View 1 Replies View RelatedI have a list box populated by a SqlDataSource with a basic statement of Select name where location = @location. Location is bound to a dropdownlist control. I want to have a checkbox for the Location dropdownlist and a checkbox for another dropdownlist (for the alphabet) that will select only those people with a last name of the letter selected. Changing the checkbox would change the sql statement.
OK so there are two questions here:
1. How can I write a sql statement that would select only people whos last name begins with a certain letter. Would it use some sort of convert function to a string with only 1 letter?
2. How can I programatically change the sqlDataSource's 'select' statement. I need to switch between sorting by location and sorting by alphabetical last name. All of my previous experience has had me configuring the datasource in the asp.net code.
Thanks in Advance.
Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.
How can I solve this problem.
Any help is appreciated!
Krish Chandra
OK, so I'm working on a project that requires the dynamic generation of SQL statements. Now in Visual Studio .NET I'm sure many of you have used that wonderful visual database tool that allows you to magically "verify sql syntax" at the click of a button. It takes a query that looks like this:
"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.EMail, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductId, P.BuyerId FROM Affiliates A, Purchases P WHERE (ProductId > 0) ORDER BY 1"
and transforms it into something that looks like this:
"SELECT A.AFFID, A.UserName, A.Pass, A.FirstName, A.LastName, A.Company, A.Street, A.City, A.State, A.ZIP, A.Country, A.phone, P.Email, A.RecieveEMail, A.SSN, A.JoinDate, A.AffType, P.ProductID, P.BuyerID
FROM Affiliates A CROSS JOIN Purchases P
WHERE (P.ProductID > 0)
ORDER BY A.AFFID"
My question is: is there anything in the .NET platform that would allow me to achieve the same kind of result programatically (i.e. transform standard sql to tsql syntax and verify the syntax of the query at run time)?
Any help would be appreciated! Cheers.
I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the following with indexes, RI, ect. :CREATE TABLE [dbo].[Orders] ( [OrderID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL , [RequiredDate] [datetime] NULL , [ShippedDate] [datetime] NULL , [ShipVia] [int] NULL , [Freight] [money] NULL , [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]GO
CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO
CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]GO
CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO
CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]GO
CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]GO
CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]GO
CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]GO
CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY ( [CustomerID] ) REFERENCES [dbo].[Customers] ( [CustomerID] ), CONSTRAINT [FK_Orders_Employees] FOREIGN KEY ( [EmployeeID] ) REFERENCES [dbo].[Employees] ( [EmployeeID] ), CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY ( [ShipVia] ) REFERENCES [dbo].[Shippers] ( [ShipperID] )GO
HiI'm trying to access the properties of my datasource programatically so I can change the stored procedure it connects to depending on the value of a querystringIn my page load I have
Dim myMode = Request.Params("mode")Select Case myMode Case "Category" sqldatasource1.SelectCommand = "productsbycategory" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add querystring parameter Case "Wishlist" sqldatasource1.SelectCommand = "productsbywishlist" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add Session variable parameter Case Else sqldatasource1.SelectCommand = "allProducts" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureEnd SelectThe problem that I have is that I need to pass a parameter to the first 2 procedures above, a querystring to the first and a session variable to the second.How do I add parameters programaticallyMany thanks
Hi ,
Could anybody point me to an NT/DOS script ( e.g a batch file ) that could check if the MSSQL Server and SQL Server Agent Services are running ?
I am trying to write the following script to check for the status. Each of the following commands gives a 1 as output if the service is running and 0 if it is not. The problem I am facing is that I am not able to check the output after the command is executed. I am neither able to assign it to a variable or include the whole command in an IF statement.
net start | find /c /i "MSSQLServer"
net start | find /c /i "SQLServerAgent"
Is there any other way to do this programmatically ? Any help is greatly appreciated.
Thank You,
Srini
What is the best way to programatically stop/start SQL Server? I want to stop it to do backups and then restart it.
I was hoping to do it within SQL Agent but it depends on SQLServer so you can't do a restart.
Any help greatly appreciated.
Hi Guys,
I am trying to figure out a way to restore a database programatically. Here is how I do it now. Right click on the database, select Restore. Browse out to my computer and find the .BAK file. Select the database and go into 'Restore Options' I have to physically change the .MDF and .LOG paths since I am getting this backup is not coming from my computer. I select 'Overwrite the existing database' then click restore.
My problem is that I have to change the paths to the MDF and LOG files. How could I automate this? Thank you!!
use this-is-a-stupid-namereturns an errorFor that matterselect *from this-is-a-stupid-name.dbo.anytablereturns an errorI've tried single quoted, double quotes, escaping. In the SQL editor I canselect the name from the dropdown and I'm OK - but I'm writing a perlscript to connect to the database.I didn't create the database and don't know how it was created.--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedUsing Sql Srv 7I know I can use the system sp's sp_stored_procedures and sp_sproc_columnsto determine all the sps in a db, and what input parms there are for aparticular sp... but... if the sp returns a result set, is there a way tofind out the stru of that in a similar manner???
View 1 Replies View Related
Anybody know how to or if we can programatically insert a pagebreak. in a table. Yes, I know about the ones at group footer and header these arn't doing what I need.
vbwrangler@yahoo.com
Hi All,
I want to programatically deploy the report in VS2005 environment,ie.from an asp.net page if i click deploy report Button ,the report should be deployed into the report server automatically.So that i can view the report in report server.
the input to the asp page would be the.rdl file...
If anyone could help then it would be good.
thankyou.
Need to ask three questions regarding this component I am creating programatically:
1) How do you select "Modify the SQL statement" programatically.
2) How do you change the actual sql.
3) How do you add a parameter, also programatically.
I have manged to select "Enable memory restriction" by using
instance.SetComponentProperty("CacheType", 2);
but can't add a paramater and modify sql statement. Have used
instance.SetComponentProperty("SqlCommand", statement); and
instance.SetComponentProperty("SqlCommandParam", statement);
with no luck. Any ideas, have searched the web but found nothing.
Thanks.
Hi,
I am able to create a rdl file programatically.I want to deploy the RDL file programatically in Report Server.Any help is appreciated.
Thanks,
Prabu