ForEachLoop Task Not Behaving As Expected

Mar 30, 2007

Hi,

I have a ForEach Loop that has 3 script tasks in it.

I have them set up so that they execute in order, such as:

script1 ---> script2 ---> script3

script1 creates a file

script2 creates a file

script3 compares the files using a diff command

Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.

The error is "The system cannot find the file specified".

Thanks

View 2 Replies

Issue With The ForEachLoop Task In SSIS

May 8, 2007

Hi,

I am using a SQL task to execute a stored procedure which returns a single field with multiple records. I want the records returned by the stored procedure to be processed one by one within a ForEachLoop container. How do I assign the records one by one to one variable and use it in a Script task running inside the ForEachLoop container.

I am using 2 tasks in my package.

In my first task I call a SQL task that executes a stored procedure which returns a list of reference numbers (TrackData). This works perfectly.

However, in my second task I must use the ForEachLoop task to loop through the above list and set the value of var_TrackData (a user variable declared by me) with the value of the TrackData present in it during that particular loop. I am not sure how to go about the second task. Any help would be greatly appreciated.

View 3 Replies View Related

ForEachLoop - Failed Task In It Doesn't Change Back To Green On Next Successful Iteration

Apr 23, 2008

How do I change the color of the task icon back to green? I have and FEL with tasks in it that occessionally fail. The error is trapped to allow the container to continue processing. I would like to change the color of the icon back to green on the next successful iteration of a task but I haven't found a way to do it.

View 7 Replies View Related

FTP Task Not Working As Expected - File Not Downloading

Feb 11, 2008

I have a package with an FTP task in the Control Flow. Nothing complicated, its configured to download a file from FTP with overwrite true, and the get and download paths in variables. Once this step completes it goes to a sequence container that does stuff with the file, that part works fine.

The problem is if i run the package in debug mode using Visual Studio, everything works perfectly (even if run over and over). The problem occurs if i use my "driver" application to try and execute the package. All my driver does is use C# code to create an Application object, set the PackagePassword, LoadPackage based on the path and then .Execute().

Here is when the strangeness begins:. If the file exists in the destination path, then the FTP task fires. If the file does not exist (lets say i manually delete it after its being downloaded before using the IDE) the FTP task dose not fire and my Sequence Container fails to use the fail, since it is of course missing.

Monitoring the FTP server that i am running locally i can see commands firing when the file exists, but when the file is killed it never even tries to connect to my server. Very odd.

Am i missing something? Why would this work from the IDE but never from my driver?

Any help would be appreciated. Please let me know if any additional detail is required.

Dmitry
http://blog.lyalin.com

View 6 Replies View Related

Challenging Search Task Is Not Working As Expected

Sep 14, 2007

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1
----------------------
-searchword- column name
--------------------------------------------
Learn more about melons row0
--------------------------------------------
%.txt row1
-------------------------------------------

table2
------------------------------
-testname- column name
--------------------------------------------
FKOV43C6.EXE
-------------------------------------------
frusdr.txt
-------------------------------------------
FRUSDR.TXT
------------------------------------------
SPGP_FWPkg_66G.zip
------------------------------------------
readme.txt
-----------------------------------------
README.TXT
----------------------------------------
watermelon.exe
----------------------------------------
Learn more about melons read me.txt
-------------------------------------------------------

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @table1 ta

JOIN @table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @table1)

)
===============================================================================

script to create tables
============================================================================

DECLARE @table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @table2

DECLARE @table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

View 4 Replies View Related

[Execute Process Task] Error:The Process Exit Code Was -1 While The Expected Was 0.

Mar 11, 2008

Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server

What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%

However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".

There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log

Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0

thanks in advance
Dave

View 1 Replies View Related

Execute Process Task Error - The Process Exit Code Was 1 While The Expected Was 0.

Jan 30, 2007

How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:

SSIS package "IngramWeeklyPOS.dtsx" starting.

Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".

Task failed: Unzip download file

SSIS package "IngramWeeklyPOS.dtsx" finished: Success.

Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:

SSIS package "IngramWeeklyPOS.dtsx" starting.

Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".

Task failed: Unzip download file

SSIS package "IngramWeeklyPOS.dtsx" finished: Success.

The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.

Thanks,

Monisha

View 1 Replies View Related

Execute Process Task - Error :The Process Exit Code Was 2 While The Expected Was 0.

Mar 20, 2008

I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.

For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.

I am using the Execute Process Task and the process parameters I am providing are:

WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat

The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"

the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.

The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :

[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]

Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.

Can anyone help ?

View 9 Replies View Related

SP Behaving Strangely

Sep 20, 2005

Hi All,
I have a DB of 100Gigs.
I have a SP which I came to knw frm developer that it is taking some more than 20 mins. Though when I just run the code frm the SP and replace the variables in the query with the values which I use in the executing the SP it completes in 2 mins.

I have recomiled the SP, drop and recreated the same.
I have updated the statistics of entire database checked whether the biggest table is having any defragmentation.

Any clues on this weird behaviour.
I have SQL Server 2000 with SP3.

View 3 Replies View Related

SQL Server DATEDIFF Behaving Badly

May 10, 2004

Hi,

I'm using the following SQL String in an ASP script with SQl Server:

SELECT time, DATEDIFF(minute, time, '18:00:00') AS difference FROM appointments

I have two appointments in the database - one with a time of 11:00 and one with a time of 14:00.

When I run this SQL query, 'difference' comes out exactly 48 hours more than it should do. So for the first appointment, it says that the difference is 3300 minutes and for the second it is 3120 minutes.

Any idea why this is happening? Is it something to do with the fact that I'm not using dates in the datetime format?

Paul

View 14 Replies View Related

Order By Caluse Behaving Differently

May 22, 2008

Hi Guys,

There is an int filed in my table called "WeekNo" and when I use order by WeekNo Desc, I am getting the following result.
9
8
7
7
6
5
4
3
2
18
17
16
15
15
14
13
13
12
11
10
10
1

This does not seem right, can anyone comment why i am getting this result.

Many Thanks

View 4 Replies View Related

MAX Of TOP In Derived Table Behaving Strange

Feb 19, 2008

Hi,

I am working for my client on SQL 2005 Enterprise & observed a strange behaviour. Consider a table(EMPTABLE) as below:

PK, INT

VARCHAR

EMPID

EMPNAME

1

A

2

B

3

C

4

D

5

E

6

F

7

G

8

H

9

I

10

J

Now, I wanted the 5th highest EMPID from 2 from the table. Now, we know that the SQL

SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 should (from all other possibilities) return me the desired value as the last row of the output table. I tried to get this in a derived table & get the MAX value i.e.

SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2
) der

But this returns me 10, when I would've thought 7 was the expected answer

I remodified my query as mentioned below to strangely get 7 as the result...
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 ORDER BY EMPID ASC
) der

How did the ORDER BY hep it???

Also, if I use
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >2
) der

I get the same result without having to specify the ORDER BY in my derived TABLE...

I tried looking for similar threads. But nothing could explain this to me...

If anyone knows what I am not able to understand, please let me know.

View 6 Replies View Related

Need Basic Help With ForEachLoop (ADO)

Mar 8, 2008

I'm missing something fundamental in getting a ForEachLoop to read ADO data.
The database connection is configured and tested, the query parses Ok, but then I get confused.

I've defined an Object variable to hoild the results, but I can't seem to figure out how to get to values (column) data.
When I go to the Variable Mapping tab, there is only my Object variable listed. Where are the columns?
The query has the column names in the Select (as opposed to Select *), but I still don't see them.
What am I missing?

TIA
dj

View 4 Replies View Related

Trigger Behaving Differently On Multi-row Update

Aug 1, 2007

Let me set the scene:

I have an update trigger on a table. When a specific column is updated, I get the rowid from 'inserted' and then pass it via service broker to another database that will fire off a maintenance routine at a later time. This whole process seems to work fine if I update a single row at a time through Query Analyzer.

During testing (of the service broker part) I found that if in Query Analyzer I run an update that updates all of the records at once, then the trigger seems to fire only once for the entire process, therefore killing the rest of my process.

I would have thought that regardless of how a record was being updated the trigger would fire atomically for each row.

Any guidance on this would be MOST appreciated!

View 20 Replies View Related

ForEachLoop And Object-Variable

Nov 20, 2006

Hi there!

I want to use a ForEachLoop. I've an object variable what i fill before going into the ForEachLoop. It contains 4 columns and in my testscenario it has two rows. In the ForEachLoop i want to set the current row values to 4 package variables (within package scope).

So i set the Enumerater as "Foreach-ADO-Enumerator", the Ado-source-variable is my objectvariable (what contains the recordset), and the enumerator-configuration i set to "rows in all tables" ("rows in the first table" works with equal result).

The variable-mapping looks like that:

Mypackvar1        - Index 0
Mypackvar2        - Index 1
Mypackvar3        - Index 2
Mypackvar4        - Index 3

Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).

Am i on the right way? Is it great bullshit what i am doing?

Thanks for any suggestion,

Torsten

View 3 Replies View Related

ForEachLoop Container And Variables

Apr 20, 2007

Hi Guys

I am trying to do the following and am quite new to SSIS.

I have to select a dataset from a database on server A, check if it exists on server B and perform an Update or Insert dependant on the existence.

I have created a SQL task to do the Select from server A with the results set passed to a variable of Vendors. I have added a ForEach Loop container with an enumerator of Foreach ADO Enumerator and the source variable is set to Vendors.

I have created 2 variables in the Foreach Loop called Code and Supplier - both as strings - as there are 2 fields from the initial Select that need to be passed to the final Update/ Insert.

I have then created another SQL task insert the Foreach which will perform the Update/Insert.

obviously when I run it at the moment it performs the Update/ Insert but just adds the rows with both Code and Supplier as NULL.

having looked at a couple of examples in books I have i know i need to add something in the Expressions of the Update/Insert SQL task but it is here i get a bit lost.

Which of the properties from the drop down do i need to use to map the variables against?

Any help would be massively appreciated asI am tearing my hair out!

Thanks

Scott

View 5 Replies View Related

SQL 2012 :: Identity Key (unique Column) Not Behaving Properly

Jul 19, 2013

I've seen this error on several of my databases again and again this week:

Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here). The statement has been terminated.

The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.

How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.

Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.

View 9 Replies View Related

ForEachLoop Container - How To Force Next Iteration -

Aug 29, 2007

How can I force a Next Iteration in a ForEach Loop container?

I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).

If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.

Is it possible to force "Next Interation' on the parent container?

Thanks - Covi

View 1 Replies View Related

ForEachLoop Container - How To Force Next Iteration -

Aug 29, 2007

View 3 Replies View Related

What Does The (seemingly Undocumented) FileNameRetrieval Property Of ForEachLoop Container Do?

Oct 4, 2007

Just wondering.

View 1 Replies View Related

Expected Value Not Being Set.

Aug 17, 2004

Hi,
I'm trying to set the value of the variable @prvYearMonth thru this sp. In the query analyzer I execute the following code to the see the results of my 'CabsSchedule_GetPrevYearMonth' SP, but the only see "The Command(s) completed successfully in the result. What am I missing??

Thanks in advance

CREATE PROCEDURE CabsSchedule_GetPrevYearMonth
(
@prvYearMonth int OUTPUT
)

AS
BEGIN
SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
GO

View 3 Replies View Related

Send Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task

Jun 21, 2007

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

View 5 Replies View Related

RIGHT(string, #), I Am Not Getting What's Expected!

Jul 1, 2004

I have the following

SELECT @cLastBarcode
SELECT @tmpCount = RIGHT(@cLastBarcode, 4)
SELECT @tmpCount

SELECT @cLastBarcode returns '14001DT0010006'

BUT

SELECT @tmpCount returns nothing. The RIGHT(....) function does not render any results. I am expecting '0006'.

I read that the data type must be compatible with varchar. The @cLastBarcode was declare as char(25). I have even tried casting the @cLastBarcode char string to type varchar.

Any hints?

Mike B

View 2 Replies View Related

DTA: Expected Improvement 0%

Dec 31, 2007

I did a trace on a production DB for many hours, and got more than 7 million of "RPC:Completed" and "SQL:BatchCompleted" trace records. Then I grouped them and obtained only 545 different events (just EXECs and SELECTs), and save them into a new workload file.

To test the workload file, I run DTA just for 30 minutes over a restored database on a test server, and got the following:
Date 28-12-2007
Time 18:29:31
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time 31 Minutes
Time taken for tuning 31 Minutes
Expected percentage improvement 20.52
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 8116
Number of events in workload 545
Number of events tuned 80
Number of statements tuned 145
Percent SELECT statements in the tuned set 77
Percent INSERT statements in the tuned set 13
Percent UPDATE statements in the tuned set 8
Number of indexes recommended to be created 15
Number of statistics recommended to be created 50
Please note that only 80 of the 545 events were tuned and 20% of improvement is expected if 15 indexes and 50 statistics are created.

Then, I run the same analysis for an unlimited amount of time... After the whole weekend, DTA was still running and I had to stop it. The result was:
Date 31-12-2007
Time 10:03:09
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time Unlimited
Time taken for tuning 2 Days 13 Hours 44 Minutes
Expected percentage improvement 0.00
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 7534
Number of events in workload 545
Number of events tuned 545
Number of statements tuned 1064
Percent SELECT statements in the tuned set 71
Percent INSERT statements in the tuned set 21
Percent DELETE statements in the tuned set 1
Percent UPDATE statements in the tuned set 5
This time DTA processed all the events, but no improvement is expected! Neither indexes/statistics creation recomendation.

It does not seem that Tuning Advisor crashed... Usage reports are fine and make sense to me.

What's happening here? It looks like DTA applied the recomendations and iterated, but no new objects where found in DB.

I guess that recomendations from the first try with only 80 events were invalidated by the remaining from the long run.

I couldn't google an answer for this. Help!!!

Thanks in advance.

++Vitoco

View 1 Replies View Related

SQL CLR Udf Returns 0 Instead Of Expected Value

Dec 12, 2007

My first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.

Here's the class member code:

Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long

' Returns the size of the specified file.

' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.

Dim lngFileSize As Long

Dim objFileInfo As FileInfo

' Confirm file exists.

If Not File.Exists(strTargetFolder & "" & strTargetFile) Then

Return -1

End If

Try

objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)

lngFileSize = objFileInfo.Length

Catch

' TODO: add error handling; system folders cause error during processed.

End Try

Return lngFileSize

End Function

In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

-- For file system access, set the database TRUSTWORTHY property.

ALTER DATABASE dba_use

SET TRUSTWORTHY ON

CREATE ASSEMBLY GetFolderInfo

FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'

WITH PERMISSION_SET = UNSAFE

Here's the udf declaration:

CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )

RETURNS bigint

AS EXTERNAL NAME

GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize

And the function call - note the target file is on a remote server. Actual file name differs slightly:

SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')

This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.

Any assistance is greatly appreciated.

View 3 Replies View Related

Is This Expected Behavior?

Aug 8, 2007

I posted this at the asp.net forums but somone suggested I post it here. So:

Try this in sql server:

select COALESCE(a1, char(254)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

select COALESCE(a1, char(255)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:

W
Y
Ya
Z
þ

The second one sorts like this:

W
Y
ÿ
Ya
Z

Is this expected behavior?

View 1 Replies View Related

Expected Formatted Results??

Feb 17, 2005

Hi All,
I am kindly seeking for help.
I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following:
Date ID R P M D... Y
1/1/90 A 1 2 3 4... 5
1/2/90 A 2 3 4 5... 1
...
2/11/05 A 3 4 5 6... 2
1/1/90 B 1 2 3 4... 5
1/2/90 B 2 3 4 5... 1
...
2/11/05 B 3 4 5 6... 2
...
The expected query results look like: ( this results from Date, ID and R fields)
Date A B
1/1/90 1 1
1/2/90 2 2
...
2/11/05 3 3

The SQL I wrote:
select date, ID,
A=sum(case when ID=A then R else 0 end),
B=sum(case when id=B then R else 0 end)
from MyTable
Group by date

I would also like to get another set of results with the same format but from date,ID and P fields:
Date A B
1/1/90 2 2
1/2/90 3 3
...
2/11/05 4 4

select date, ID,
A=sum(case when ID=A then P else 0 end),
B=sum(case when id=B then P else 0 end)
from MyTable
Group by date

The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this?
I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.

Any suggestion/comments are highly appreciated!
shiparsons

View 4 Replies View Related

Sproc Not Executing As Expected

Oct 24, 2005

I use the following sproc to populate a table that is used as the base recordset for a report.

For some reason, when the sproc is run from a scheduled job, it doesn't repopulate the table. It does, however, truncate the table. If I run it manually from query analyzer, it works fine.

I've checked all the permissions on all the object touched by the sproc, and everything looks right there. Is there another problem I should be looking for?

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

setuser N'mcorron'
GO

CREATE PROCEDURE mcorron.CreateDiscOrders
AS
/*
Creates table for Orders with disc items Actuate report
*/
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

TRUNCATE TABLE dbo.rptDiscOrders

INSERT INTO dbo.rptDiscOrders
SELECT *
FROM (SELECT ORD.product as prod_XREF, ORD.ORDER_NUMB, ORD.CustName, ord.units as ordunits, INV.Product,
INV.Units
FROM (SELECT TOP 100 PERCENT f.PARENT_SITE, f.SITE, dbo.vwCustBillTo.CustName, o.ORDER_NUMB, p.Prod_Xref, o.PRODUCT,
o.ORDER_TONS * 2000 / m.part_wt AS UNITS
FROM dbo.Lawn_Orders o INNER JOIN
dbo.PRODUCT_XREF p ON o.PRODUCT = p.Product INNER JOIN
dbo.FACILITY_MASTER f ON o.WHSE = f.SITE INNER JOIN
dbo.Lawn_PartMstr m ON o.PRODUCT = m.part_code INNER JOIN
dbo.vwCustBillTo ON o.BILLTO = dbo.vwCustBillTo.BillToNum
WHERE (o.SHIP_DATE < DATEADD(d, 30, GETDATE())) and prod_xref not like 'dead%') ORD INNER JOIN
(SELECT f.PARENT_SITE, x.Prod_Xref, i. Product, SUM(i.Qty) AS Units
FROM dbo.Lawn_Inventory i INNER JOIN
dbo.FACILITY_MASTER f ON i.Whse = f.SITE INNER JOIN
dbo.PRODUCT_XREF x ON i. Product = x. Product
WHERE (f.WHSE_TYPE = 'ship')
GROUP BY f.PARENT_SITE, x.Prod_Xref, i. Product) INV ON ORD.PARENT_SITE = INV.PARENT_SITE AND ORD.Prod_Xref = INV.Prod_Xref)
ordinv
WHERE (Prod_Xref <> Product)
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks

View 4 Replies View Related

Encrypt Not Working As Expected

Oct 18, 2007

Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.

select *

from [dbo].[dmTable]

WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'

select *

from [dbo].[dmTable]

where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')

View 1 Replies View Related

DateDiff Not Calculating As Expected

Feb 19, 2008

I have a stored procedure that is Averaging a Difference in dates in seconds. All of the sudden it started throwing an Arithmetic overflow error. After running the query below on the same data, I can see that it is because the DateDiff in my procedure, which is calculating the difference in seconds, is returning a value greater than 68 years. Looking at the dates in the result table, I don't see how it is coming up with the values in the Years Difference column.

Code SnippetSELECT createdate, completeddate, DATEDIFF(y, createdate, completeddate) as 'years difference'
FROM tasks
WHERE (TaskStatusID = 3) and (createdate < completeddate) and (DATEDIFF(y, createdate, completeddate)>=68)
ORDER BY completeddate

Results:

CreateDate
CompletedDate
Years Difference

2007-07-28 16:26:17
2007-10-08 20:45:19
72

2007-07-28 15:56:13
2007-10-12 19:40:28
76

2007-07-28 16:06:20
2007-10-18 21:00:05
82

2007-07-30 01:15:54
2007-10-21 20:18:43
83

2007-07-29 23:12:08
2007-10-22 23:22:47
85

2007-08-16 11:17:29
2007-10-23 15:47:32
68

2007-07-25 21:20:44
2007-10-23 19:11:32
90

2007-07-29 23:04:53
2007-10-23 21:09:47
86

2007-08-22 18:29:50
2007-11-02 14:29:12
72

2007-08-23 18:33:38
2007-11-02 14:38:34
71

2007-07-29 21:46:10
2007-11-02 23:24:39
96

2007-08-14 05:18:17
2007-11-03 21:08:30
81

View 21 Replies View Related

Variable Value Not Changing As Expected

Jan 15, 2007

I have an Execute SQL Task that selects one column value from one row, so General > ResultSet = Single row. Result Set > Result Name = 0 (the first selected value) and Variable Name = User::objectTypeNbr. The task runs successfully, but after the it runs the value of User::objectTypeNbr is not changed.

User::objectTypeNbr > Data Type = Int32. When I declared the variable Value could not be empty so I set it to 0 arbitraily, assuming it would be overwritten when assigned a new value by the Execute SQL Task, but it remains 0 after the task runs. What am I missing here?

View 11 Replies View Related

Declaration Expected Error

Oct 11, 2007

Hi,

I am trying to convert an active x script in a script task. Below is a snippet of code. The underlined AsOfDate has a blue squiggly line under it and if I hover over it, it says "Declaration Expected."

Public Class ScriptMain

Dim AsOfDate As String

AsOfDate = Dts.Variables("MyDate").Value
...

Can someone please tell me what I'm missing? I thought maybe I'm missing an import statement, but I have:

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

I have used similar syntax in script components and it works fine.

Thanks

View 6 Replies View Related