Hi,
I have a package that uses a variable string (in date format) to execute a package.
I want to modify that variable in such a way that if no value is set for that variable then use system date else use date in varaiable.
Can someone help me out in this.. I know it has something to do with the expression builder but donn know how to do it.
Cheers
Hi All,
I am creating the report and setting the datasource(dataset) dynamically to the report. I want to set the parameters also dynamically. am using Report Viewer to process the report.
When i set the parameters locally, i am getting the below error:
An error occurred during local report processing
Here is my code:
da = new SqlDataAdapter(strqry, conn);
ds = new DataSet();
da.Fill(ds);
ReportDataSource ReportDataSourceX = new ReportDataSource();
ReportDataSourceX.Value = ds.Tables[0];
ReportParameter[] parm = new ReportParameter[2];
parm[0] = new ReportParameter("Business_Function", "SQMO");
parm[1] = new ReportParameter("Application", "ETMRS");
parm[0] = new ReportParameter("Owner", "Subbu");
//parm[0] = new ReportParameter("Business_Function", ds.Tables[0].Columns["Business_Function"].ToString());
//parm[1] = new ReportParameter("Application", ds.Tables[0].Columns["Application"].ToString());
//parm[0] = new ReportParameter("Owner", ds.Tables[0].Columns["Owner"].ToString());
RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
RptViewer.LocalReport.ReportPath = "C:\ETMRS Reporting\ETMRS Reports\ETMRS Reports\PlannedTestCasesSummary.rdl";
//RptViewer.LocalReport.SetParameters(parm);
RptViewer.LocalReport.DataSources.Add(ReportDataSourceX);
RptViewer.LocalReport.SetParameters(parm);
RptViewer.LocalReport.Refresh();
Can anyone please help me in resloving this issue.
Thanks in advance,
SR.
Hi:
I have posted concerning this-I don't mean to double post but I have
isolated the problem and have new information. (and I'm desperate!!) Please direct me to a DTS forum if this is not the place for this, I didn't see one.
I am trying set the data connection object (ODBC to Sybase) twice in
the same package but the Data Pump Tasks that use this connection don't
seem to be using the new setting in the connection after the second
time it is set, although at the end of the package I can see that the
connection has been set. The pumps just use the original connection a
second time instead of using the 2nd connection.
Here is the progress:
1. SQL Task1: Gets server information from a SQL Server table, and sets
it to a recordset global variable in the DTS.
2. ActiveX Task1: Begins loop, and sets the connection to server2 and
sets sql of datapump tasks
3. Several data pumps run.
4. ActiveX Task2: just loops around to ActiveX Task1
5. ActiveX Task1: Sets the connection object to server2 (msgbox tells
me this happens successfully)
6. The datapumps continue to use server2(??)
7. Package finishes succesfully
8. I right click on first datapump and do a "execute task". It executes
using server2, which for some reason it did not do during execution of
the entire package.
The loops works fine (as I have gotten it from you Allan), it clearly
goes through twice when I have specified two servers (I can see the
data in the resulting table is doubled from the source server, and I
can see it in the progress window going through the data pump steps
twice). After, if I run the data pump tasks by right clicking and doing
an "execute task" then it works using server2 was set.
I put a msgbox in the ActiveX Task1 to confirm the server is being set
correctly.
Any ideas here? Is there some other property I need to set? Is it a
setting with the Data Pump task? This all seems very weird-I feel like
I must be missing something obvious.
Here is the part of the code of ActiveX Task 1 (only part) that sets
the connection (let me know if you want to see the package):
Dim pkg
Dim SCCScon
Dim objRS
Dim SQLStatement
set pkg = DTSGlobalVariables.Parent
set SCCScon = pkg.Connections("Connection_Name")
set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
Set objRS = DTSGlobalVariables("gvDataSources").Value
stpFinished.DisableStep = true
stpEnterLoop.DisableStep = false
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
msgbox "DSN is now: " & SCCScon.DataSource
SCCScon.DataSource = objRS.Fields(1)
SCCScon.UserID = objRS.Fields(2)
SCCScon.Password = objRS.Fields(3)
msgbox "Now I've set the DSN and it is now: " & SCCScon.DataSource
_____________________________________________
Thanks as always,
Kayda
I am trying to dynamically set the name of a checkpoint file and I have used expressions in the package property and evaluated the following expression correctly
Expression: "C:\CheckPoints\" + "CheckPointFile_ "+ @[User:BName]" + ".xml"
Evaluates as: C:CheckpointsCheckPointFile_@[User:BName].xml
when I try to save the package, it comes up with an error saying
Error at Caller Package: the filename is not valid. the filename is a device or contains invalid characters.
Anyone know why?
Can you dynamically set the name of the table in the SelectCommand section of the SqlDataSource? (If it is relevant, I code in C#)
For example,
<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test-MySQL %>" ProviderName="<%$ ConnectionStrings:Test-MySQL.ProviderName %>" SelectCommand="SELECT * FROM TestTable">
I would like to replace 'TestTable' with the name of a table that is extracted from a string array in the code-behind.
Appreciatively,Peter
Hi all,
Does sombody have experience on dynamically set or change the default value of a report parameter?
Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube
What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.
What I did is:
Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");
ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });
what I got when reveiw the report:
P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.
Any idea and suggestion will be pre-appreciated!
Jone
Hi,
I am trying to execute a DTS on sql server 2000 using the "Execute DTS 2000 Package Task".
I can see the inner variables and when I set the values everything works fine.
Now I want to pass the values dynamically. How can I achieve this?
Many thanks
Hi all
Recently i was asked to design and implement an On-line WebSite for a Shop Store.I decided to define some Category for this Shop's Products such as MP3-Players,Mobiles,Televisions,Monitors and so on....and then Assign each product to a certain Category.Something like Amazon.Com , So i designed my Category and Product Table as follow:
Category Table----------------CatID int Primary-KeyCatDesc varchar(50)
Product Table-------------ProductIDProductDescCatID
After that i noticed that each category has its own Fields and Property and sometimes somecategories have common Fields for example , Price ,Color are two Common Property or Fieldsfor all Categories that i mentioned earlier But , Size is a special property for only[Monitor] or [Television] Categories and isn't useable for [Mice] or [KeyBoard] categories for example. So i Added a table for Keeping each category's Fields as follow:
Category Fields Table---------------------FieldID intCatID intFieldDesc varchar(50)FieldType tinyint --> 0=Numeric 1=String 2=Boolean...
After that i should save each Field's Value somewhere for each product so i added thefollowing Table(Product-Field-Value Table) for this purpose:
Product-Field-Value Table-------------------------ProductIDFieldIDValue
So far every thing is ok But i've faced with a new issue!!!.As you have realized so far , each productbelongs to certain category and each category has its own Fields List , in the other hand one category maybe has only 3 to 6 Fields but another Category might has 10 or more fields,So my problem here is when user wants to Add new product to Databse ,how should i get the Field's value for each product in an ASPX page ?in the other hand i'm dealing with a dynamic structure as you can see, so how should i Render the appropriate User Entries for getting Values of each product dynamically and then Saving these values in the Database? what Asp.Net Components should i use for this purpose? Could anyone help me?
Thanks in advance.Kind Regards.
Hello all,
Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>
I need to make my site aware of which server_name it is loading from so it uses a different connection string. (have dev + prod servers for web/sql)Currently my connection string is in web.config as follows: <connectionStrings> <!-- Development and Staging connection string --> <add name="myconnection" connectionString="server=myserver; user id=mysuer; password=mypassword; database=mydatabase" /> </connectionStrings> I need to make sure the 'name' is the same for both connection strings since that is how the rest of my site looks for it. However, I'm not sure how to get both in here with some sort of 'if/then' statement to determine which one to use.I've heard it could be done in global.asax with something similar to the code below, but I dont know how to assign a 'name' to a connection string for that type of setup. Sub
Session_OnStart ServerName =
UCase(Request.ServerVariables("SERVER_NAME")) IF ServerName = "prod.server.com" THEN ...Set Prd string... ELSE ...Set Dev string... END IF End
Sub
Hi all
I hope someone can help me on this issue:
I am designing dynamical reports, that are abel to run in four different languages - so far everything works just fine for me (I select my headers and labels from a database). Only problem is the prompt text for report parameters. How can I set this text dynamically?
E.g. I have a parameter where the user selects a storage - this prompt should be "Storage: " in the English report and "Lager: " in the Danish report. I have absolutely no idea how to fix this.
I am greatful for any help.
Hi all,
I have been playing with integration services for a few days and at the moment, its up there with my list of software that I find ......painful.
What I am trying to do is read different tables from my one SQL database, then populate my Access database with its data.
I have put a foreach loop which goes through a collection SQL statements that I have entered into it. It first assigns it to a string variable called tablenameVar which contain statements such as "Select * from Terminals". Then the datareader is supposed to execute it based on the connectstring which never changes, and the SQLCommand value which I passed using the variable @[User::tableName]. However when I try to run it, I'm getting this error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [DTS.Pipeline]: "component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Does anyone have an idea of what I should do? or maybe a better way to do it? I appreciate you guys for taking a time to look at this.
Thanks,
Joseph
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
View 14 Replies View RelatedOn MS-SQL 2k I have a created user. Security for the SQL instance isset to mixed.When I attempt to connect to SQL using Enterprise client on anothermachine on the local network using Windows Auth I get the error: DBName-Login Failed for user ('null')- Reason: Not associated with atrusted SQL Server Connection.What am I over looking?TIA
View 1 Replies View Relatedok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View 1 Replies View RelatedI'm attempting to modify some Crosstab generating code, and I need someadvice/examples.Currently, the code uses a single string variable to store thedynamically generated query (www.johnmacintyre.ca). The problem is thatI am trying to pivot biological taxonomy information, and may end upwith a table containing over 200 columns. This takes the dynamic stringwell over the 8000char limit for variables.[color=blue]>From my understanding, the EXEC() command does not have the 8000char[/color]limit if the execution string is broken into chunks, and concatenatede.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need isto:1) start a counter at the beginining of the dynamic generation2) append the counter value to the end of a string variable name3) DECLARE the new variable and attach that loop cycle of text to it,or attach each chunk of characters < 80004) build the EXEC() string by concatenating each dynamic varibleCan this be done? Should it be done? Is there a better way to addressthis type of problem?Thanks for any ideas or insightsTim Pascoe
View 5 Replies View RelatedI have a SSIS pkg with several task. I need to run a global variable dynamically.
the global variable's value is basically a bat file with two arguments (\.......bat \.......xls \........xls)
bat file arg1 arg2
This global variable gets set from its previous task at run time. I did put in this global variable as an argument in expression and for executable i am calling the cmd.exe file. But for some reason it does not work. the command prompt comes up wait for an action from user which is wrong. Please help
Thanks
Hey all.
I need to call another database in the same server dynamically; creating a varible off it or somehow. Example:
-- In database DBaseA
declare @dbname as varchar (30)
set @dbname='[DBaseB].[dbo]'
select * from @dbname.[table2]
I know this block of code doesnt work. And i need a way of making the database DBaseB dynamic.
DBaseA and DBaseB are in the same server. I am using Sql Server 2000.
How do i solve this? Solutions ?
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).
To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.
When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.
Any ideas why this isn€™t working?
sry im an idiotbut how do u take like "SELECTbla FROM blar WHERE blam=blfda" and make the thing it "selects" into a variable thnx
View 5 Replies View RelatedHi, I have this query basically im trying to set the @db to the current database depending on the quarter. This will be used in a package to change the database that the package will use.
DECLARE @db nVarchar(4000)
SET @db = N'TESTDB' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10')
THEN 'Q4' END END END END
EXECUTE sp_executesql @db, N'@level tinyint', @level = 35
I get the error Incorrect Syntax near 'TESTDB04_Q3'.
hi !
I am trying to set the variables value through Execute SQL task and create directory through the FileSystemTask by setting the user variable as source. The User variable has the Directory path to create the directory, but when i set the source as the user variable it complains that its value is empty.
Execute SQL TAsk happens to work correctly when i check the watch window the user variable is set correctly . but value is not setting in the SSIS/Variable window Value.
What is wrong an what method should i use to set the variables value to create directory.
Thanks,
aaks
Hi, folks. As part of a larger bunch of code, I am trying to validate some user form input against a database...I've been picking apart code samples and have the following (currently not yet working) code to show for it. I find tons of complex examples out there RE the data controls, and I have those working. But I can't find any examples of just pulling a single record from a DB, sticking it in a variable, and then checking against that variable to get a boolean response. Anyway, here is the code. I am trying to check that a password field filled by the user matches the value located in the DB. I am not married to this code; if someone suggests a better way to do it rather than using a custom validator control, that's fine too:BEGIN CODE SNIPvoid Page_Load(object sender, EventArgs e)
{
Page.Validate();
SqlDataSource1.SelectCommand = "SELECT Password FROM aspnet_Membership WHERE aspnet_Membership.Email='" + emailID.Text + "'";
}
private void CustomValidator1_ServerValidate(object source, System.Web.UI.WebControls.ServerValidateEventArgs args)
{
//between this and the next comment is where I have to get the DB Password field value for the current record
SqlCommand command = new SqlCommand("SELECT Password FROM aspnet_Membership WHERE aspnet_Membership.Email='" + emailID.Text + "'");
command.CommandType = CommandType.StoredProcedure;
sqlConnection.Open();
reader=command.ExecuteReader();
string currentPasswordDb = reader.IsDBNull(reader.GetString("Password"))? null: reader["Password"].ToString();
args.IsValid = false; // Assume False
{
// Compare db entry against user's entry
if (currentPasswordDb == currentPassword.Text)
{
args.IsValid = true;
}
}
} END CODE SNIP The code above give me the following at runtime:Line 14: SqlCommand command = new SqlCommand("SELECT Password FROM aspnet_Membership WHERE aspnet_Membership.Email='" + emailID.Text + "'");Line 15: command.CommandType = CommandType.StoredProcedure;Line 16: sqlConnection.Open();Line 17: reader=command.ExecuteReader();Line 18: string currentPasswordDb = reader.IsDBNull(reader.GetString("Password"))? null: reader["Password"].ToString();Although I'm sure it won't be the last. I'm a former ASP classic developer, moving to c# and .Net. Any ideas?
Hi,
I want to execute a BAT file using Execute Process task, where I want to select the file path (directory) dynamically using a variable whose value is set at runtime.
In simple terms I want to send a value to the "Executable" property dynamically
Can some one help me on this
The For Loop will execute a stored procedure that passes a variable each time it loops. I need to set this variable, @FiscalWeek, equal to the variable @Counter. So the first time it loops, the counter will be 1, and the Fiscal Year would be set to 1. The next time it would be two, and so forth. Can I do this in the Expressions section of the For Loop? If so,
what would the property be?
InitExpression @Counter =0
EvalExpression @Counter == @CurFiscalWeek
AssignExpression @Counter = @Counter + 1
Hi,
below is the sql statements for my web service using C#.
Code Block
string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC"
SqlCommand comm = new SqlCommand(sql, conn);
Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?
I want to be able to have a single select statment:
SELECT TOP 1 Call.JobNum, Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID
but with this select statment I also want to set a variable:
declare @variable int
SELECT TOP 1 Call.JobNum, @variable = Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID
Now SQL Server does not like this, can not set a variable in a multiple select statment. I NEED to do this all in one step if possible. Any suggestions?
pat
Why does this not work?
declare @ret varchar(50)
DECLARE @X SQL_VARIANT
set @X=10
SET @ret=select SQL_VARIANT_PROPERTY(@X,'BaseType')
Just trying to assign the SQL_VARIANT_PROPERTY return value to @ret and it issues an error: Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.
But I don't want to convert I just want to assign the result to the variable.
what's confusding is that you can do this:
if(select SQL_VARIANT_PROPERTY(@X,'BaseType'))='int'
So I assume I should b able to do the above.
--PhB
I am having the wrost trouble with this today for some dumb reason...
Please don't suggest any alternates; this is just a quick example, full code is more elaberate.
Today is 07/19/2007
Declare @StartDate DateTime
@StartDate = CONVERT(VARCHAR(10), Month(GetDate()) & "/22/" & Year(GetDate()), 101)
So I want @StartDate = '07/22/2007'
What I need is CDate
Hi,I am having problems setting the value of a variable in a SQL Stringthat I have to create dynamically in my procedure. The code that Icurrently have is as follows:set @sqlStatement='Set @compare_string=' + '(Select ' +@group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' whereIdentity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'exec(@sqlStatement)The error message that I get is as follows:Must declare the variable '@compare_string'.Here @compare_string has already been declared in the procedure and Idon't have a problem using the variable anywhere else but this SQLStatement (when called using the EXEC function).I am not sure why SQL Server can't see the variable declared when usedin a string in conjunction with EXEC. Is this a syntax issue? Any helpon this issue would be greatly appreciated!Thanks in advance.
View 5 Replies View RelatedHi,
If this is the syntax to set a property in a package using dtexec:
/set PackageMyForEachLoopMyDataFlow.Disable;False
What is the syntax for setting a global variable in the package?
Thanks
I'm in the process of creating a series of packages to do the ETL for a datawarehouse. As such, I've got quite a few DataFlow tasks scattered through them.
The problem I'm coming across is where the disk that the temp files (created during the processing) are put on is short of space (when compared to some of the data sets I'm copying around and noting that it's the System disk, not the "Data Drive").
To get around this, I found number of article scattered around that suggested using the BufferTempStoragePath property of the DataFlow tasks to redirect the temp files to somewhere else. This works fine for the tasks that I hard-code the new directory.
Noting the number of these tasks that I want to redirect, and also that the Dev environment will be different to the Production environment (and I have no control over the drive letters, paths, etc, in Prod), it was suggested that a package variable be created, assigned to the property and then said variable could be exposed via the Package Configuration file.
That's great as far as it goes, but I just cannot, for some reason, make it work.
The xml in the config file is:
<Configuration ConfiguredType="Property" Path="Package.Variables[::BufferTempStoragePathValue].Properties[Value]" ValueType="String">
<ConfiguredValue>D:Temp</ConfiguredValue>
</Configuration>
The property in the DataFlow task is then set to: @BufferTempStoragePathValue.
When the package is executed, it logs the error message "The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission."
My question for the group is two-fold: 1) what am I doing wrong in the setup of the property/variable, and 2) what are the security permissions that are required for the (new) folder.
It's got to be something obvious, I just can't see what!
Many thanks for your assistance.
Cheers,
Mike