how to get total number of variables using scripttask

I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.

Use the count property on the variables collection.|||

that is what I did, Dts.Varaiables.count, but it did not return the right numbers. I have over 10 variables and it return 1.

|||

Have you added your 10+ variables to either the ReadOnlyVariables or ReadWriteVariables list? The Dts.Variables collection is generated based on those preset list of variables.

Thanks,
Patrik

|||

no, I did not.

OK, let me back out a little bit, I try to use the event handler as a trigger for my custom audit logging, for each task when I am done, I want to use a script to find out what is in my variables pool, loop thru them and build a query, (“select count(*) from ‘[“ + @.variable1), and then the next execute SQL task will execute this and return the count to a variable so I can have the OnPostExecute event write out the record counts, so I only have 1 variable “MySQL” in the ReadWriteVariables list, since what I want is when I create a new package, all I have to do is to add or remove local variables, no code changes is need it. if I have to put in all the variables, then I defeat my intention, is there a work around for this? Any suggestion is greatly appreciated.

|||

What sort of processing are you trying to achieve with the Script Task? Which variables are you using and looking for in the loop? Is the collection of variables you're interested in always the same? If you don't fill in the ReadOnlyVariables and ReadWriteVariables properties you can do the work using the VariableDispenser (http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.variabledispenser.aspx); however, you would still need to know the name of the variables you're interested in reading.

If you're using the Script Task just to create a SQL command statement for the Execute SQL Task you could look at using expressions (http://msdn2.microsoft.com/en-us/library/ms141214.aspx).

Cheers,
Patrik

|||

Hi..

You can read all variables by using ActiveX Script Task instead of using Script Task.

Function Main()
Dim oVal

Dim isSystem, varType, varName
Dim str
MSGBOX "Total Variable = " & DTSGlobalVariables.Count

For each oVal in DTSGlobalVariables
isSystem = oVal.SystemVariable
varType = oVal.DataType
varName = oVal.QualifiedName

MSGBOX varName & " / " & varType & " / " & "System:" & isSystem

Next
Main = DTSTaskExecResult_Success

End Function

HTH

ADConsulting / SQLLeader.com / Daeseong Han

How to get the value of number of rows in groupfooter

I want to show how many records populated in the groupby clause, i want to show the number of rows value in the group footer.

Thank you very much.

If you put this in the group footer, you should see the count for each group.

=CountRows()

Hope this helps.

Jarret

sql

How to get the Sequence number without using temp table

Here is my problem:
I have a table with following columns:
PersonID FirstName LastName
102 John Ben
103 Josh Parker
104 Mark Ben
Now if I type SELECT * FROM Person WHERE LastName = 'Ben' these two records will be displayed
PersonID FirstName LastName
102 John Ben
104 Mark Ben
But I want this to return with one additional Sequence column like this:
New Column PersonID FirstName LastName
1 102 John Ben
2 104 Mark Ben
How can I add this so called "New Column" ?


Here's one way:
SELECT
COUNT(*) AS [New Column],
P1.PersonId,
P1.FirstName,
P1.LastName
FROM Person AS P1
JOIN Person AS P2 ON
P2.LastName = P1.LastName
AND P2.PersonId <= P1.PersonId
WHERE P1.LastName = 'Ben'
GROUP BY
P1.PersonId,
P1.FirstName,
P1.LastName
|||You are AWESOME !

How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)

|||SELECT * FROM TABLE WHERE CONDITION ORDER BY DESC FIELDNAME LIMIT 1,1sql

How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)

how to get the rowNumber in a matrix?

I am working with reporting services recently.

when using a matrix, I want to get the row number to dynamically set the row color.

but i can not get it by using the rownumber function,

can anyone help me with it?

thank you very much.

The row number doesn't really help you in a matrix because you are always dealing with grouped data in a matrix. You have to follow the approach discussed in the following blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

How to get the Row Number per Distinct Records?

Row Number	Name	Phone Number
1	John Doe	(555) 123-1221
1	John Doe	(555) 144-9989
2	Smith	(666) 191-1201
3	Jane Doe	(555) 188-0191
3	Jane Doe	(555) 189-0192
3	Jane Doe	(555) 190-0193

Here are the records I get back using a Grouping on "Name". I would like to assign a Row Number for each "Distinct" row. I've tried all the possible aggregate functions with no luck! Can anybody help me with this? Thanks.

Please try something like this:

=RunningValue(Fields!Name.Value & Fields!PhoneNumber.Value ,CountDistinct, Nothing)

|||Thanks. I didn't know that Expression can take multiple values!!! Now I know!

How to get the parameter type in VB.net?

I am currently using the following embedded code to get the selected values from a Parameter and display in a textbox. I however have a number of reports and I don't want to include this in all of them. I have created a dll with some other functions but when i try to include the following function it throws an error on the Parameter type. What am I doing wrong, I am not all that familiar with dot net?

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String

If parameter.count <= 5 then
If parameter.IsMultiValue and parameter.count > 1 then
s = " "
For i as integer = 0 to parameter.Count-1
if i = parameter.count - 1
s = s + CStr(parameter.Label(i))
else
s = s + CStr(parameter.Label(i)) + ", "
end if

Next
Else
s = " " + CStr(parameter.Label(0))
End If
else
s = " Only a maximum of 5 selection values can be displayed. "
End if
Return s
End Function

What's the error message?|||I am sorry..it says the type Parameter is not defined.|||

A multi-valued parameter is a 0-based array of objects, I'm pretty sure. But you should be passing the *name* of the parameter you want to reference, as a string, not the object. No?

Then you can say Parameters(tsParamName).Label(i) and Parameters(tsParamName).Count in your code...

HTH,

>L<

|||

It simply means it cannot find the type Parameter, either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

In your code,you are passing parameter as Parameter in the signature of the function, this is where it's complaining, it cannot find the Assembly where the type Parameter is defined.

|||

>>either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

Jonel, I honestly don't think that's correct... rather than including the reference to Parameter, I think the actual type he would need is ReportParameter here ... but in this case he could actually be passing the name of the parameter from the textbox expression (or wherever he's using the function), as I said...

>L<

|||

Hi, Lisa,

From within a custom assembly, you need to provide a fully qualified reference for the parameters type:

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters

If you use this reference, then when you install new releases of Reporting Services, you may need to recompile and redeploy the custom assembly.

|||

My apologies, Mary, Jonel, and everybody reading -- I misread the original post (saw the "embedded code" part but not that the poster was moving the code into a DLL!). Had a braino...

>L<

|||Thanks guys for your responses. After searching the net last week I found the answer. Thanks much for all your help.

How to get the numbers of row in a table using function. Simple but there is a problem i have

Here is the code. Trying to get the number of the rows in a table. If it is greater than 0 return false else return true. Later i changed this code with if else but i am wondering why it is giving this error?

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

CASE WHEN @.ProductNum >= 1 THEN @.Status = 'False' ELSE @.Status = 'True' END

RETURN @.Status

END

Code Snippet

Msg 156, Level 15, State 1, Procedure RestoranHazirMi, Line 25

Incorrect syntax near the keyword 'CASE'.

Msg 102, Level 15, State 1, Procedure RestoranHazirMi, Line 29

Incorrect syntax near 'END'.

CASE WHEN is a statement level construction, not flow control

You could use

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.Status = CASE WHEN COUNT(*)>=1 THEN 'False' ELSE 'True' END

FROM tblProduct

WHERE ProductID = @.ProductPrm

RETURN @.Status

END

OR (IF/ELSE for flow control):

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

IF (@.ProductNum >= 1)

SET @.Status = 'False'

ELSE

SET @.Status = 'True'

RETURN @.Status

END

|||thanks dude. I've learned it now.sql

How to get the number of queries fired to the database by a single ssis package

Hi,

Is there any way that I can know the count of queries and queries fired to the database after running a ssis package?

can any one help me out?

Thanks in advance.

Programatically yes, otherwise I haven't any idea.|||SQL Profiler could help you.

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie

How to get the day and day number for a specified month

Hi Guys,

I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-

Wednesday 1st

Thursday 2nd

Friday 3rd

... etc

Can anybody help?

TIA.

Create a calendar table with the necessary data (as many years as you want. it will not be a big table even if you use the entire smalldatetime or datetime date range). You can then write a simple query that retrieves the days based on the month. You can also store other attributes like holidays, different calendars (fiscal, yearly, iso), weekends etc.

|||

If you let them choose the month and year, you can build the start date variable and use this script.

Code Snippet

DECLARE @.dateStart datetime

SET @.dateStart = '08/01/2007'

WHILE MONTH(@.dateStart) = 8

BEGIN

print(DATENAME(dw, @.dateStart) + ' ' + CAST(DAY(@.dateStart) AS VARCHAR(2)))

SET @.dateStart = @.dateStart + 1

END

This gives the output

Code Snippet

Wednesday 1

Thursday 2

Friday 3

Saturday 4

Sunday 5

Monday 6

Tuesday 7

Wednesday 8

Thursday 9

Friday 10

Saturday 11

Sunday 12

Monday 13

Tuesday 14

Wednesday 15

Thursday 16

Friday 17

Saturday 18

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

Sunday 26

Monday 27

Tuesday 28

Wednesday 29

Thursday 30

Friday 31

sql

How to get String representation of a Hex number?

Hi,
I want to get the string representation of a hex number from a
varBinary column of a table.
For example I want to get the output : 'The Hex value is 0xFF'
but
select 'The Hex value is ' + convert(varchar(10), 0xFF)
retruns the ascii charecter for 0xFF

Any idea how do I get the hex form as it is?
thanks.
Supratim[posted and mailed, please reply in news]

Supratim (supratim@.sagemetrics.com) writes:
> I want to get the string representation of a hex number from a
> varBinary column of a table.
> For example I want to get the output : 'The Hex value is 0xFF'
> but
> select 'The Hex value is ' + convert(varchar(10), 0xFF)
> retruns the ascii charecter for 0xFF
> Any idea how do I get the hex form as it is?

You can use

select master.dbo.fn_varbintohexstr(@.binvalue)

Note, however, that fn_varbintohexstr is un documented function, and thus
unsupported.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That works exactly the way I want ..
Thanks a lot
-Supratim

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9558844747DDEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Supratim (supratim@.sagemetrics.com) writes:
> > I want to get the string representation of a hex number from a
> > varBinary column of a table.
> > For example I want to get the output : 'The Hex value is 0xFF'
> > but
> > select 'The Hex value is ' + convert(varchar(10), 0xFF)
> > retruns the ascii charecter for 0xFF
> > Any idea how do I get the hex form as it is?
> You can use
> select master.dbo.fn_varbintohexstr(@.binvalue)
> Note, however, that fn_varbintohexstr is un documented function, and thus
> unsupported.

how to get sql procedure parameters count and their type?

Hi All,
i am using DBLib and VC++. i want to know the number of parameters
of a procedure at run time. is it posible if yes then how can i do this.
thanks.Use ADO's OpenSchema method with the schema ID: adProcedureParameters|||is there another way of getting parameter count other than ADO.
actually we dont use ADO. some winodws api etc.

thanks for reply.

omar|||Check out INFORMATION_SCHEMA.ROUTINE_COLUMNS

How to get specific number of rows from each group

I need a SQL query to get 2 items from each catergory.
And if possible, 2 items with price < $100 and 1 item with price >= $100
from each caterogy.
Table: tblItems
Fields: ItemID, CategoryID, ItemName, ItemPrice
A stored procedure with multiple queries also works.
Thanks,
--
GeeviSelect * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price
Union
Select Top 1 PK From Table
Where Price >- 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Sorry, Union won't work because you can;t use Order by in parts of a Union..
.
So you have to use separate In Predicate conditions...
Select * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||aaaghhh!! Forgot to alias the first Table...
Select * From Table As T -- Left off the "As T" Before
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Burying the TOP .. ORDER BY should work:
Select * From Table as T
Where PK In (
select PK from (
Select Top 2 PK From Tbl
Where Price < 100
And Category = T.Category
Order By Price
) PartA
union
select PK from (
Select Top 1 PK From Tbl
Where Price >= 100
And Category = T.Category
Order By Price Desc
) PartB
)
-- I don't know how to get 2 items, of which 2 have prices < $100
-- and one has price >= $100.
Steve Kass
Drew University
CBretana wrote:
> Sorry, Union won't work because you can;t use Order by in parts of a Union
..
> So you have to use separate In Predicate conditions...
>
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price)
> Or PK In
> (Select Top 1 PK From Table
> Where Price >= 100
> And Category = T.Category
> Order By Price Desc)
>
> "Geevi" wrote:
>|||Great! It works!
I could not use the Order By "Price" as "ORDER BY items must appear in the
select list if the statement contains a UNION operator."
But this is a big time saver for me.
Thanks CBretana, for the solution!
"CBretana" wrote:
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price
> Union
> Select Top 1 PK From Table
> Where Price >- 100
> And Category = T.Category
> Order By Price Desc)
>
>
>
> "Geevi" wrote:
>

How to get row size

using sql 2k, what's the fastest and easiest way to get the row size of a
row. I hava a number of rows I need to look at.
Thanks.
moondaddy@.noemail.noemailHello,
If you need to estimate the size of a table, you can use the following
Excel file:
http://www.microsoft.com/downloads/...&displaylang=en
The size of each row can be variable (if there are variable-length
columns in the table). If you want to see the min/max/avg row size in
an existing table, you can use:
DBCC SHOWCONTIG ('table name') WITH TABLERESULTS
AFAIK, there is no direct method to get the row size for a particular
row in an existing table. If you need this information, copy that row
in an empty table with the same structure and use DBCC SHOWCONTIG, as
shown above.
Razvan|||this will give you the min size and max size of the rows in you table, then
make an educated guess..
This is the easiest way
select minlen,xmaxlen from sysindexes where indid in (1,0) and id =
object_id('tbl_name')|||Thanks for the reply.
What I'm trying to determin is how close a table is to the max rowsize of
8060. I dont see what part of this is going to help me with that.
I used DBCC SHOWCONTIG ('table name') WITH TABLERESULTS
and got the result below. Should I be able to guestimate the total rowsize
from this?
tbLeaseDt
898102240
PK_tbLeaseDt
1
0
45
1325
120
1431
263.83100000000002
0
7
6
268.73300170898437
96.679847717285156
85.714285714285708
6
7
0.0
14.285714149475098
moondaddy@.noemail.noemail
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146811248.235424.294000@.e56g2000cwe.googlegroups.com...
> Hello,
> If you need to estimate the size of a table, you can use the following
> Excel file:
> http://www.microsoft.com/downloads/...&displaylang=en
> The size of each row can be variable (if there are variable-length
> columns in the table). If you want to see the min/max/avg row size in
> an existing table, you can use:
> DBCC SHOWCONTIG ('table name') WITH TABLERESULTS
> AFAIK, there is no direct method to get the row size for a particular
> row in an existing table. If you need this information, copy that row
> in an empty table with the same structure and use DBCC SHOWCONTIG, as
> shown above.
> Razvan
>|||Thanks. can you please explain how I would guess the approximate total row
size using the min size and max size in the table? I may have a number
varchar columns of large size along with many other columns. even though
none of the current data in the columns is more then a length of 50.
Therefore, wouldn't the max rowsize be superficially low?
moondaddy@.noemail.noemail
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:017A12EC-48E1-409B-9A03-190E9630B9C2@.microsoft.com...
> this will give you the min size and max size of the rows in you table,
> then
> make an educated guess..
> This is the easiest way
> select minlen,xmaxlen from sysindexes where indid in (1,0) and id =
> object_id('tbl_name')
>|||>From these results, you can see that the row size for the smallest row
is 120, the row size for the biggest row is 1431 and the average row
size is 263.83100000000002. These informations are referring to the
rows that exist in the table at this time.
If you want the row size of largest row that can be inserted in the
table, you can compute this size based on the definitions of your
columns (that can be found in the syscolumns table), using the
informations presented in the Books Online topic "estimating table
size":
http://msdn.microsoft.com/library/e...des_02_92k3.asp
Razvan|||Hello,
You could use DATALENGTH() for a quick measure of row size.
Please see "Estimating the Size of a Table" in BOL for some related
infrmation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get return value for the number of rows affected by update command

Hi,

i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?

Below is the normal way i did in vb.net, but how to check for the return value. Please help.

========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========

Thank you.you can add either of these statements to the SQL being called
[BOL} @.@.rowcount
[BOL] Rowcount_big

the difference is in the datatypes rowcount _big returns a bigint
and @.@.rowcount returns int

if you have over 2 billion rows user rowcount_big|||Hi Ruprect, thanks for your reply. My sql statement is a very simple insert query without using any parameters just like the one below:

sql = "INSERT INTO [Subscriber] ([SubID], [SubName], [SubEmail], [Status], [MailID], [SubscribeDate]) VALUES (SubID, SubName, SubEmail, 'Pending', MailID ,getDate())"

I'm unsure of how to include the " [BOL} @.@.rowcount ". Do you mean that i should add a parameter to return @.@.rowcount or there is other way to do it? I'm new to this, would you please give me an example.

Thanks for your time.|||@.@.Rowcount stored the number of records affected by the immediately prior statement. The value is lost as soon as another statement is executed, so you must either use it immediately or store it in a procedure variable:

declare @.RecordsAffected Int
.
.
.
.
.
Update/Select/Delete some records from somewhere...
set @.RecordsAffected = @.@.RowCount

Look up @.@.Rowcount in Books Online for more details.|||thanks BLIND MAN
i didnt getthis until late
[BOL] stands for Books Online it's the sql server help file
i was giving you the article title

and since blindman got it exactly i've no need to reiterate
good luck.|||see if there is something like mycommand.rowsaffected property.|||Thanks Blindman and Thanks Ruprect. I'll study BOL ;) for details of @.@.rowcount.|||You should also follow ms_sql_dba's suggestion to see if there is a method to return the value via VB.

It might be more appropriate if you are going to use the value in your VB code.|||Hi ms_sql_dba, there isn't any rowsaffected property, however there is this UpdatedRowSource and others ..

Thanks for your suggestion, although i'm unsure of their usage, i'll look into it and see if i can find something which stores the value of number of rows affected!|||Sure Blindman, i'll study both ways and see which one is more applicable for my situation. You have a great day.|||Hi Everyone,

I managed to find another solution to my question. Just simply assign the value like this line:-

rowsAffected = myCommand.ExecuteNonQuery()|||see, it was simple!|||Yea. Lesson learned! Cheers!!!

How to get number of transactions from log ?

Is there way to get the number of transactions from the SQL Server log, by
using some extended procedures ?
-NagsNags wrote:
> Is there way to get the number of transactions from the SQL Server
> log, by using some extended procedures ?
You may view the transaction log contents by executing:|||Nags wrote:
> Is there way to get the number of transactions from the SQL Server
> log, by using some extended procedures ?
You may view the log contents by executing DBCC log, but I don't know if it
works for you. See for yourself:
DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database in
question.
type - is the type of output:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length)
2 - very detailed information (plus object name, index name, page id, slot
id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the
current transaction log's row.
by default type = 0
To view the transaction log for the master database, you can use the
following command:
DBCC log (master)
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get number of records returned by a data set?

Hi,
I am looking for a way to determine a number of records returned by my
dataset. The ultimate goal is to hide a table and dispaly informative
message, in case there are no records returned. This should be fairly
simple, but I can't seem to figure out how to get it. Thank you in advance!
MichaelYou can get it using the Count function. The sintaxis is:
=Count(Fields!some_field.Value, "Your_dataset")
You can use this to display the number of rows of a data set in a textbox.
You can also use it as a condition inside an IFF clause:
=IIF(Count(Fields!some_field.Value, "Your_dataset") = 0, action1, action2)
I hope this helps|||Works like a charm! Thank you very much!
- Michael