Showing posts with label scope. Show all posts
Showing posts with label scope. Show all posts

Tuesday, March 20, 2012

Problem with function

Hello,

I am unable to resolve an error message for following user defined function.

Scope of this function:

This functions generates a unique identifier no from a table and returns it to caller.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION SystemNoShiva1

( -- Add the parameters for the function here

)

RETURNS int

AS

begin

DECLARE @.st2 numeric(18,0)

DECLARE @.sys01 numeric(18,0)

declare sys cursor for Select Max(SystemNo) From TISSystemMst1

open sys

Fetch sys into @.sys01

begin

--DECLARE @.st2 numeric(18,0)

if (@.@.fetch_status = 0)

-- IF @.@.ROWCOUNT <> 0

close sys

Deallocate sys

select @.sys01 = @.sys01 + 1

--update TISSystemMst1 set systemno = (@.sys01)

insert into TISSystemMst1 values(@.sys01)

select @.st2 = @.sys01

end

RETURN @.st2

end

Messeg I receive is

Msg 128, Level 15, State 1, Line 17

The name "sys" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

How can I clear above error message?

Nilkanth Desai

Hi,

A typical cursor fetch as follows, however looking at the script, why are you using a cursor, if we are simply selecting the topmost (Max) value and incrementing it?

If you are simply incrementing the topmost value why not use something like

CREATE FUNCTION SystemNoShiva1

( -- Add the parameters for the function here

)

RETURNS int

declare @.st2 int

set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)

insert into TISSystemMst1 values (@.st2)

return @.st2

Go

--Cursor Option As Follows--


declare @.sys01 int

Declare sys Cursor For
Select Max(SystemNo) From TISSystemMst1


Open sys
fetch next from sys
into @.sys01

WHILE @.@.fetch_status=0
begin

set @.sys01 = (@.sys01+1)

insert into TISSystemMst1 values(@.sys01)

fetch next from sys
into @.sys01
end


close sys
deallocate sys

return @.sys01

Hope this helps

|||

Hello GDR,

Thx for u r prompt reply. I tried both samples but receive error as under.

Sample 1

While creating this function I receive message "Incorrect syntext near the keyword declare"

In Sample 2

In this code sample I receive same error message as I was receiving in my previous (main) message.

Nilkanth Desai

|||

My humble appologies, the 'AS' is missing, I wrote the script on the forum page, not on a SQL interface thus parsing was not an option,


CREATE FUNCTION SystemNoShiva1

RETURNS int

AS

begin

declare @.st2 int

set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)

insert into TISSystemMst1 values (@.st2)

return @.st2

End

problem with foreach loop container and excel files

well i am trying to follow the example of msdn help on how to loop through excel files and it doesn't work. the variables have the project scope. what can i do?

What exactly are you trying to do? Can you put the link to the msdn help here please.

Thanks

Sutha

|||

well i have a number of excel files and i want to use them in a loop so as to do calculations and load a table. when i use one file the process is ok, but when i use the steps described in that link

http://msdn2.microsoft.com/en-us/library/ms345182.aspx

i get errors.

thank u in advance

|||

You are not trying to do this on a 64 Bit by any chance?

"You may not be able to connect to as many data sources from a package executing in 64-bit mode as you can from a package executing in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers may not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version." (http://msdn2.microsoft.com/en-us/library/ms141766.aspx)

Thanks

Sutha

|||

no an i get the following message

TITLE: Microsoft Visual Studio

Error at Package3 [Connection manager "SourceConnectionExcel"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Pilote ISAM introuvable.".

Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.


ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)


BUTTONS:

OK

|||

Are you getting this error message on "To loop through Excel files by using the Foreach File enumerator" OR "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator".

I worked through the first one (To loop through Excel files by using the Foreach File enumerator) and it worked without any issues.

Thanks

Sutha

|||

i worked with To loop through Excel files by using the Foreach File enumerator

I don't know :)

Thanks

|||

well i tryied with the following way? i read a file with the import/export wizard, i followed the instructions of the link, i loaded the files in a separate table and it worked. the problem is with the excel source in the data flow. am i using the wrong compnent. because i want to read directly the files and not to load them in a table. am i not supposed to use "excel source"?

thnxs

|||

I have a working example that I have done can be download from here.

Uses c:\Temp as source directory for Foreach Loop

Thank

Sutha

|||

and how can i use the script in order to use a data flow task in order to use the data in other transformations?

thnxs

|||

All I have done in that example, is just to get the xls files in c:\temp, assign each file name and print the variable to a message box.

you still have to do data flow task.

Thanks

Sutha