Reading And Writing Same Variable To A Script
Apr 27, 2006
It looks like its not possible to both read and write the same variable from a script using the conventional Me.Variables.<variable> syntax.
I can only assign a variable as Readonly or ReadWrite and not both. If I assign it ReadOnly I can only access it in the PreExecute subroutine. If I assign it ReadWrite I can only access it in the PostExecute subroutine (in fact doesn't this just make it WriteOnly in fact?). So I can only either read in or read out a variable using this syntax, noth both. Is this right?
So the read and write a variable to a script, the VariableDispenser approach is the only option to use. Is this right? and is it documented somewhere that this is how to use variables in scripts. Thanks.
View 13 Replies
ADVERTISEMENT
Feb 16, 2008
I need to select all the records in a table, loop through them one by one, calculating some new field data, and then write the new data back to the same table. Here is the basic structure of what I've come up with:<CODE>SqlCmd = SqlConn.CreateCommandSqlStatement = "SELECT ProductID, Name FROM tblProducts"SqlCmd.CommandText = SqlStatementSqlRdr = SqlCmd.ExecuteReaderIf SqlRdr.HasRows Then While SqlRdr.Read If SqlRdr.FieldCount > 0 Then ... SqlWriteCmd = SqlConn.CreateCommand SqlStatement = "UPDATE tblProducts SET Name = '" & NewName & "' WHERE ProductID = " & CStr(ProductID) SqlWriteCmd.CommandText = SqlStatement SqlWriteCmd.ExecuteNonQuery() SqlWriteCmd = Nothing End If End WhileEnd IfSqlRdr.Close()SqlCmd = Nothing </CODE>I get an error that tells me to close out the Reader before trying to execute the write query. But I can't close it out for the loop to work properly. So I assume that there must be another way to do this simple task, but I'm so new to all of this that I need some help! Thanks!
View 1 Replies
View Related
Jun 13, 2007
I am new to database programming. What I want to do is have a database on a clients PC to use as data storage. This program will connect to a program running on our server (via TCP) which will store this information in another database. The clients database will only be accessed by my program - so I think that I don't need to register it with SQLEXPRESS. The servers database will probably need to be so another program can access it.
The problem I was having was writing and reading to a database that I created inside Visual Studio 2005. As I see it, there could be three issues - reading, writing, or setup of the database. To find out this issue I downloaded the Northwind database. The code below returns column 0 row 0 of the Customers table.
Code Snippet
NORTHWNDDataSet northwindDataSet = new NORTHWNDDataSet();
NORTHWNDDataSetTableAdapters.CustomersTableAdapter customersTableAdapter = new NORTHWNDDataSetTableAdapters.CustomersTableAdapter();
customersTableAdapter.Fill(northwindDataSet.Customers);
string data = (string)northwindDataSet.Customers.Rows[0].ItemArray[0];
Console.WriteLine("COL 0 - ROW 0: " + data);
This code returns "ALFKI". Which is what is in col 0 row 0 (This can be checked by right clicking on the Customers table in NorthWNDdataset.xsd and selecting preview data).
Next is writing. This is where I have the issues. The method below is one way that I tryed.
Code Snippet
DataRow row = northwindDataSet.Customers.NewRow();
row[0] = "1";
row[1] = "2";
row[2] = "3";
row[3] = "4";
row[4] = "5";
row[5] = "6";
row[6] = "7";
row[7] = "8";
row[8] = "9";
row[9] = "10";
row[10] = "11";
northwindDataSet.Customers.Rows.Add(row);
try
{ customersTableAdapter.Update(northwindDataSet.Customers);//table);
northwindDataSet.AcceptChanges();
MessageBox.Show("Update worked");
}
catch
{
MessageBox.Show("Update do not work!");
}
The messagebox shows "Update worked" - yet the data did not get added.
Thanks in advance.
Using:
Visual Studio 2005
C#
SQL Server Management Studio Express
View 3 Replies
View Related
Sep 9, 2004
I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?
Thanks a lot, Hugo
View 2 Replies
View Related
Nov 22, 2004
Halo, I am a bit new to this
Please can someone help me, I would like to write a file(Any type) to a SQL database like a attached document(s) for the current record and be able to detatch the document when needed.
I use VB.NET for a ASP.NET app.
I basicly would like to attach documents to a piece of equipment may it be any kind and if the user views the equipment he will be able to detatch the documents for that piece of equipment and open it with the correct software.
PLEASE HELP!!!!!!!
View 1 Replies
View Related
Jul 20, 2007
Hi,
I want to make an application that fills in the variables into a dtsx package (ssis).
I'm able to read the variables created in the package
Application app = new Application();
Package p = app.LoadPackage(pkg, null);
Connections myConns = p.Connections;
foreach( Variable v in p.Variables){
Console.WriteLine(v.Name);
Console.WriteLine(v.Value);
Console.WriteLine(v.Namespace);
Console.WriteLine("/////////////////////");
Console.WriteLine("/////////////////////");
}
i'm also able to add on
Variable myVar = p.Variables.Add("amyCustomVar", false, "User", "3")
When the applications is closed the package does not contain the variable. So this is all done in memory.
Is their a way to actually write the variable into the physical package.
View 1 Replies
View Related
Feb 14, 2008
Hi,
I have a data structure called 'Quote' which contains a number of different variables and controls ranging from text boxes, check boxes and radio buttons, i need to be able to read and write this from a database.
First I think a description of my overall project is needed:
Project Description
I have been given a brief that basically says: i have to create a programmed solution in VB to solve a problem. This problem can be anything we like, and I personally have chosen to create a program that manages quotes for building Log Cabins (this is very contrived and far from anything someone would do in the real world).
My solution will allow a generic user to create a quote (using a form with controls such as text boxes, check boxes, radio buttons) , and then save this to file. These users may then wish to load/edit this quote at a later date, from another form.
Whilst completing this project, i'll only have up to about 5 records (quotes) within the system, so i dont need the ability to store hundreds of records. And each record will be relatively short, with only about 10-15 data items within the data structure.
Also the Admin (or business owner in this case) need to be able to view all saved quotes in a presentable format, and edit them if needs be, from within this same program.
This solution does not need to be absolutely perfect and 100% efficiently coded, or have all the bells and whistles a real-world program would have. This is for an A level computing project by the way.
So basically, i need to be able to read from the database (to populate a Data Grid (i imagine this is best way?)) and so Admin can access any quote and edit it (editing is not vital, but viewing/printing is. Maybe i should stop at just viewing any quote?). Also i need generic users to be able to fill in the Edit Quote form and then save this data into the database.
And is a data structure really required for me to use a database?
I've never used databases in VB before (but have used them elsewhere, mainly Access) and so am completely new to this. Any help will be much appreciated.
Thanks
View 13 Replies
View Related
Apr 27, 2006
I'm doing a test package which reads a flat file, makes an adjustment using the derived column task and writes to the same flat file. But, the read locks the flat file, so the write can't access it. Any ideas for a resolution?
Thanks,
Dave
View 2 Replies
View Related
Mar 20, 2007
We have a package that is using a ForEach loop container to access files on a network drive. For some reason I am getting a message that the ForEach enumerator is empty and did not find any files that matched the pattern. For the pattern I left the default *.* for testing purposes. I have specified the file folder as \remoteserverfilesharesubfolder and also as \remoteserverc$filesharesubfolder and have gotten the same message. However when I map a network drive and set the file folder to the network drive it finds the files. Is this a permissions issue?
After I finish processing the file I want to move it to a new directory. Once this is deployed in production, the package will not be running under a domain account and probably won't have access to the network folder. Is there any way to specifiy in the connection manager itself that it should use a specific account to access the folder?
TIA,
Sabrina
View 1 Replies
View Related
May 4, 2006
I keep receiving errors while using variables to pass values to different part of my package. For example ...
Error: 2006-05-04 10:31:59.84
Code: 0xC00470EA
Source: EdwPostProcess
Description: Reading the variable "User::GvPathRoot" failed with error code 0xC0010009.
End Error
The way my variables are constructed is :
First the global variables (Gv*) are set by the parent package;
Then local variables (Lv*) are set using the EvaluateAsExpression property and giving it an expression that takes the Gv* variable and concatenate a string to it.
At execution time, while the expressions are resolved, I get the above error while it was resolved correctly in a previous task.
I tried different method including duplicating my variables but without success. I'm running out of ideas
Gilles
View 4 Replies
View Related
Dec 5, 2006
In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.
However this value is not then working in the following line:
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
This statement is not reading any value for ProjectID and so is selecting all from the table.....
How do I write this statement to pick up the variable above?????
Thanks in advance for your help!
Clare
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New Data.DataSet
Dim da As Data.SqlClient.SqlDataAdapter
Dim strSQL As String
Dim ProjectID As Int32
ProjectID = Request.QueryString("ProjectID")
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"
da = New Data.SqlClient.SqlDataAdapter(strSQL, connString)
da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile")
For Each tempRow As Data.DataRow In ds.Tables(0).Rows
tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))
Next
ImgGrid3.DataSource = ds
ImgGrid3.DataBind()
End Sub
View 12 Replies
View Related
Feb 21, 2008
is there a possibility to print the value of a variable in the debug console? it seems that Console.WriteLine in a script task doesn't work. or is there a better way in order to debug the value of a variable at a certain point?
View 7 Replies
View Related
Dec 5, 2006
I have a series of tasks in a Sequence Container. One of them is a Data Flow task, and inside that task is a Row Count transformation that counts the number of rows I add to a table. The Row Count transformation was added to record the number of rows written to a table in a log table.
When I try to retrieve the resulting variable (RowCount) in the Data Flow task, I get the default value (0). When I try to retrieve it in a subsequent task, I get the value 1. When I try to consume it in the OnPostExecute Event Handler of the original Data Flow task, I get a value of NULL.
1) When is the appropriate time to call the variable assigned to a Row Count transformation so it can be written to a log table?
2) Is there a way during debugging to see the set value of the RowCount variable?
I am using Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 in Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200)
Thank you in advance for your posts.
View 5 Replies
View Related
Aug 28, 2006
Hi,
I'm using a script task as source and I need to read a global variable.
I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)
This gives an errero on 'Dts.' saying it can not resolve it.
View 2 Replies
View Related
Oct 2, 2007
Hello,
I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J.
THE CODE I USE TO CALL THE SPROC
Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty)
THE STORED PROCEDURECREATE Proc [prs_VerifyUser
@regid uniqueidentifier,@regemail nvarchar(250) OUTPUT
ASBEGIN
IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid)
BEGIN
SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid
Return 1
END
Return 2
END
View 4 Replies
View Related
May 21, 2015
I have created a heat map and it is working pretty well. The only issue I am having is that the expression for the fill is using "DataSet1"
=Code.GetHeatmapColor(sum(Fields!AnnualPremium.Value),Min(Fields!AnnualPremium.Value,"DataSet1",Recursive),
Max(Fields!AnnualPremium.Value,"DataSet1",Recursive))
This is making the heatmap look at the whole dataset instead of just what I am grouping by. Within the Dataset there are Regions and Credit Unions. Since the Dataset is looking at an entire region, the heatmap is coloring based on all data for the region. I need to heatmap to color based on the Credit Unions in that region. The Credit Unions are a group. I need the group to be the value it is referencing in the heat map and not "DataSet1". I have been told to use scope or a variable but cannot get it to work correctly.
View 3 Replies
View Related
Jun 13, 2007
I have a variable defined as "Country". Based on the value, the header row printed needs to be different.
I've already created a 'HeaderRow' variable that I'm able to set using a script task. But how can you set the Header text value at run time from the variable? There is no expression defined for the Header with the Flat File Destination object, and when I attempt to reference the HeaderRow variable as the Header text, the variable name is printed as the header.
Another approach I tried was to write the Header Row separately through another data flow task, but the issue here is: what is the input source when all you have is a Country variable?
View 1 Replies
View Related
Feb 16, 2007
I am using a foreach loop, with the data from an ado recordset, which contains the table name that I wish to write data to an OLEDB data dest. The table names are retrieved from an execute sql task in the an object var. Within the foreach loop, for each table name, I then use a datareader to an ado.net source to pull data from that table, via an expression construct into a variable - i.e. "select * from " + @[User::table_name]. This works fine for the first table, in which mappings are setup using the SSIS design environment. The data is retrieved. I then use a variable and set the data access mode for the oledb destination to "Table name or view name variable". This also saves data fine for the first table in the loop in the oledb dest. When the next table name is retrieved from the ado provider in the foreach loop, the datareader fails, as it still thinks the metadata mappings are from the first table, which was used for the mapping in the design environment. I.E. FIN_CLASS is a column from the first table in the loop.
Error: 0xC0202005 at Data Flow Task, DataReader Source [7181]: Column "FIN_CLASS" cannot be found at the datasource.
I have set the following properties, that I thought (in my feeble mind), are supposed to avoid that behavior. For the datareader, I set ValidateExternalMetadata to false, and for the data flow task (container for the datareader), I set DelayValidation to true. These settings, according to the doc, are supposed to evaluate metadata for the datareader source at runtime (not design time), so that the column metadata is dynamic, and so that the subsequent oledb destination can use the "data access mode" for the oledb destination of "Table name or view name variable".
If I cannot get this to work, I have 2 options: Use OPENQUERY via dynamic t-sql statements, OR create 30 separate flows in SSIS - one for each table - not looking forward to that one.
View 5 Replies
View Related
Oct 25, 2006
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
View 1 Replies
View Related
Mar 6, 2008
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
View 4 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Oct 15, 2007
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
View 1 Replies
View Related
Feb 15, 2006
I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()
View 2 Replies
View Related
Nov 4, 2015
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
View 6 Replies
View Related
Oct 9, 2001
Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)
Thanks
View 1 Replies
View Related
Mar 7, 2005
When trying to upsize an access database to sql server using the upsize wizard, I get the following error:
"Object variable or With block variable not set."
Any assistance is greatly appreciated.
View 3 Replies
View Related
Oct 18, 2007
Hello!
I'm using SQL Server 2000.
I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)
SELECT @operation='DIS'
CREATE table #myTable(value VARCHAR(20))
INSERT into #myTable values('@operation')
SELECT top 1 @parameterValue = value from #myTable
-- Now @parameterValue is assigned the string '@operation'
-- Here I need some way to retrieve the value of the @operation variable I declared before (in fact
-- another process writes into the table I retrieved the value from), in this case 'DIS'
DROP TABLE #myTable
I've tried several ways, but didn't succeed yet!
Please tell me there's a way to solve my problem!!!
Thank you very much in advance!
View 7 Replies
View Related
May 27, 2015
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
View 8 Replies
View Related
Jun 21, 2008
I have some experience with MS SQL mostly installation, configuration, maintaining, etc. But I am trying to teach myself some TSQL. I have bought a few beginner books and have some test machines. In advance, I appreciate any feedback!
I have a database with two tables. I want to calc the min salary for a class of employees. The "wage" is in one table and the "status" is in another.
USE Database
GO
SELECT MIN(Wage) AS "Min Salary", Status
FROM Employee, Job
WHERE Status = '1'
GROUP BY Wage, Status
GO
The result set brings back all not the "status" class that I want.
Again, I am relatively new so take it easy on me!
Thanks,
grinch
View 4 Replies
View Related
Mar 3, 2008
Hello,
I wrote a stored procedure in SQL 2K5, and I would like to write to the sql log without using a rasieerror function. I just would like to log the sucessfull steps without exiting the proc. I am not sure how to do this. Any help would be appreciated.
Thanks,
Dave
View 3 Replies
View Related
May 13, 2007
Hi,
I am new to this programming and SQL.
I am following the: Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database video series. I cannot get the following to write changes to the SQL Database:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
BindingSource1.EndEdit()
'CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
Dim rowsAffected As Integer = 0
rowsAffected = CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
MessageBox.Show(rowsAffected.ToString())
End Sub
End Class
I do get the message box to popup showing 1 row of changes when I make one change, however that change does not get written to the database. Is this enough info to have any idea why?
Thanks
View 1 Replies
View Related
Jul 16, 2007
hi guys, Can anyone advise me how how to read an excel doc i have stored locally? I need to be able to start the read from say row number 6 and finish the read once i get to a row that contains a pre-determined word signifying the end of processing. I intend to store the parsed data in an array that will be used as the data source for a gridview or repeater object on another asp page. I'm using ASP.net 2.0 and C# by the way. thanks in advance for any help!!!
View 12 Replies
View Related
