Hello Im having a little problem getting some values from a database.....
What I need is to get the value of the keyfield of the last entry introduced, I know how to to do the SQL statement for this, the problem is that i dont know how to save this value in a variable. I need to save this value in a variable in the session object or in a hidden field, and then use this variable in another different page. That's way i need to save this ID value, later in the other page I introduce data in a different table and one of the fields has to be this ID value which I have as a foreign key in the table so I need to use the same value in all the rows i introduce(Id value).
If you know how to accomplish this let me know, many thanks, Sergio
As there is no support of check points at data flow component level, we are trying to implement this on our own. In the process of implementing we have to save values of certain user defined variables in to an xml file. To do this, we added a data flow task in the event handler OnTaskFailure. Now we want to save values of the variables in to an xml file in the data flow. Can you please suggest me how can we save values of variables in an event handler?
What is the syntax for saving a single value returned by a Select statement in a variable? I have tried the following, but its telling me I "Must declare the variable '@TempCount'." which is already declared!
SET @SQL = 'Select @TempCount = count(*) FROM ' + @TblName + ' WHERE ' + ....... exec(@SQL)
I am trying to automate a basic task using SQL Server 2005 Express.
Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.
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?
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.
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.
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.
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.
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
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.
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.)
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:
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!!!
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
Please i need to save records using DataSet. I created the connectionstring as a global variable. In th button_Click event i have this : Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim InsAdapter As New SqlDataAdapter Dim StrInsert As String = "insert into Products(CategoryId,ProductName,ProductCode,ProductPrice,) values(@CategoryId,@ProductName,@ProductCode,@ProductPrice)" Dim ObjComm As New SqlCommand(StrInsert, ObjConn) ObjComm.Parameters.Add("@CategoryId", SqlDbType.Int).Value = drpCategory.SelectedValue ObjComm.Parameters.Add("@ProductName", SqlDbType.NVarChar, 30).Value = txtName.Text ObjComm.Parameters.Add("@ProductCode", SqlDbType.NVarChar, 10).Value = txtCode.Text ObjComm.Parameters.Add("@ProductPrice", SqlDbType.Int).Value = txtPrice.Text InsAdapter.InsertCommand = ObjComm End Sub But on clicking the button, the record is never saved. Please help
I am currently migrating my application from Access to SQL. It looks like it's working good but one thing I cannot understand is why when I have created a varchar(40) field and after I insert a field that is less then 40 characters long, the rest of the string is filled with the leading spaces. I have also create an nchar(40) that did not solve it ether. For example: the text is "Hello World". The text is 11 characters long but the data saved to the table is: "Hello Wolrd" + 29 spaces. Am I doing something wrong?Thanks for your help in advance.
When I open a DTS package, save it as a bas file, make changes, close the package and save it when prompted, where are my changes saved?
The answer appears to be nowhere. When I reopen the package it's back to it's original setup. When I check the bas file it's as it was when I originally saved it.
We have a table with 3.5 million records in it. We have been forced to add two fields and set indexes on those fields. But when we try to save the changes it times out. When we try to generate a change script it also times out. Is this a memeory issue? Any ideas on whats causing this or how to fix it woudl be greatly appriciated.
Hi, all.This might be a silly question... but I am very new to programming inSQL so please bear with me :)So. I'm using MS SQL Server 2005 Management Studio Express. I have atable that was created via an existing .sql file. Included were abunch of stored procedures. I went in to re-format these procedures.(They were written in haste by another programmer. It is my task to goback and improve their readabilities.) However, I am having difficultyfiguring out how to save the changes to these stored procedures. Thusfar, I have been individually saving each stored procedure in itsown .sql file. This is obviously not the way to go... but it's atemporary solution (so as not to lose the changes I have made todozens of procedures).Can someone please explain to me how I commit these changes to thedatabase?Thanks,Allie
Using vb 2005 express I have my grid veiw on the form and a icon to click to save my work.
But is there a passage of code I can write to pop up a message box to remind me to save my work when I close my form. Similar to ms office forms when you close they ask you if you want to save any changes.
Is it better to have users that upload pictures have them saved to SQL server or to a folder? I have a site where users enter things that they own (and each item has a unique id) and now I would like to add the functionality to add a picture. I am sure I should use the fileUpload control, but should this save to a folder created for each user or to the SQL record itself? Or possibly a separate SQL table just for pictures?Thanks for the help.
Hi, I want to save date into SQL server pls help me how to save this date into server. its give me syntax error that converting datetime to character string. and also I want to save date only in mm/dd/yy format in a sql not including time. The following code I am using sub SaveEvent(sender as object, e as EventArgs) Dim dt As DateTime = DateTime.Parse(eventdate.Text) lbl.text=dt dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ") dim cmd as new SQLCommand("insert into events values('" & dt & "','" & desc.text & "')",con) con.open() cmd.executenonquery() con.close() end sub
In my system, I generate invoices as PDF for my clients and I need to email them and save them on the database. What is the best approach for achieving that?!
Shall I save the files path in the SQL server 2000 as ( varchar ) and save files in a folder in the server or there is another approach?! is there any recommendation or advice about the procedure to send emails shall i save them first then send them or ...!??
Hi All 2 post in a row first time in ages so i must be getting better but i get stuck on crappy little things like this hopefully I have data in the following format 4.1399999999999997 4.2400000000000002 5.4800000000000004 5.1799999999999997 6.7699999999999996 i want to select the data as such 4.13 4.24 5.48 5.17 6.76 I keep chasing my tale on this one around and around any one got a simple idea i am missing, you would not believe how much time i have spent on this. Cheers Phil
database file is not full and not limited to growth. As a matter of fact batch transactions are done but our proble is very slow. 28 records has been successfully save after 45 mins. Before it's just 5 minutes. Then some other process are not working. Is there anything that I must do? We have 460 tables in our software using SQL server 2000 and our application is using VB 6.0
Im not really sure if this is the right thing to do. But i want to save a copy of the html from my invoice to sql so that i can keep a history of the invoices in case their are changes done to them. Anybody know what would be the best way to do this?
Hello,I use SQL Server 2005 Express and I would liketo save on db of mine...Have I only to save the .mdf and .ldf data fileor I need to stop some service or other programs?Can I use some utility to schedule this?ThanksM.
If you use the command UPDATE <tablename> SET <fieldname> = ' ... ' toupdate a CHAR or TEXT field there are some characters that can causeproblems such as a single quote. Are there any other type of charaters likethat?TIA
i created two tables. I placed both tables in a single form. When i run the application and enter some data in both table only data entered in first table is saved when i press the save button. As such when i run the application again and browse through the records of the first tables i cannot see the respective data in the second table though there is a relationship between both tables.
My install of SQL Server 2005 runs like a dog. The SQL Management Studio splash screen lasts for 10 minutes, then when the Studio window appears it takes another 5 minutes to initialize.
I have SQL server installed on a Germane Server running Windows Server 2K, SP4. It has quad Xeon 2.8 GHz processors with 2GB RAM. 4 300GB drives in a RAID 5 configuration.
I have created 2 databases which I would like to save, uninstall SQL, then reinstall it. (I didn't install it the first time.)
