Poor Performance - Nested Views & Complex Joins

The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?

SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

Thanks, DaveYou've been told wrong.

There is nothing bad about nested subquery. They, like any coding technique, should not be used unnecesarilly, but the optimizer will incorporate them in its query plan. You can try eliminating the nested UNION subquery, but you will need to link the account and TransactionalData tables into both YTD tables. This may allow you to better benefit from indexing, but will incur twice as many table scans. So whether this ends up making your code more efficient depends heavily upon your data. You just have to try it and test for yourself.

In your code, GETDATE() will not be executed for every record. It will only be executed once at the start of the query. You WILL, however, lose any benefit of an index on Process_Date by using this syntax: WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)Use this syntax instead:WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)

Other problems? Dump the "SELECT *" and enumerator your columns. "SELECT *" is sloppy programming and has not business in production code.|||Nested views are not derived tables...there's a major differences...and yes avoid netsed views...BUT

FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans

There is no good reason to do this|||I think Blindman may have meant to use

WHERE Process_Date > DATEadd(mm, -15, GETDATE())

instead of

WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)|||OK, I see it now

FROM ( SELECT *
FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT *
FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

First I would normalize the tables..YTD0xx tables should all be 1 table with a type of...whatever that stuff means..I'm guessing month?

Also, and I'm not sure, bu this might cause a scan: RIGHT(trans.Acnt, 5)

Why are the element size different? Did you overload 1 column with additional data instead of normalizing it into 2 columns?|||Thanks all. The design is definitely something that needs to be looked at.

I'm still not in favor of nested views. Most articles I've read about the pros and cons of nested views indicate a major con is how deeply nested they can become. While SQL Server does expand the views to their base table representation, this does create a certain amount of extra work behind the scenes that is not reported as part of the execution plan. I've also seen many times where developers find a view that comes close to meeting their needs and creates another view off of this view without taking the time to research if the first view is the base view. Several times this summer I've been asked to troubleshoot poor server performance where the CPUs are over 95% utilized. I ended up finding the source of the problem was a nested view. The execution plan revealed over 100-150 icons containing multiple table scans and involving 6-10 other views and multiple base tables all the result of one select statement off of a nested view. I know the argument can be made the real problem was a poorly designed nested view and not the use of nested views itself, however, at least in my experience, most people don't take the time needed to carefully analyze existing views before using them. I'm not even going to touch on the difficulty in troubleshooting performance problems caused by poorly designed nested views since I've typed too much already. Sorry about that.

Dave|||Dave why don't you post the ddl of the tables, sample data and expecxted results.

Read the hint sticky at the top of the board|||I think Blindman may have meant to use

WHERE Process_Date > DATEadd(mm, -15, GETDATE())

instead of

WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)

Yes. Copy/paste error. Thanks for catching it.

And yes, nested VIEWS are bad, if only because they serve more to obfuscate code than to clarify it.|||Unless I'm just WAY off base, the sample you posted isn't syntactically correct, so I'm not about to try to fix it until we get thing clarified. Simply reformatting what you posted, I get:SELECT
trans.Entry_Code, trans.D_C, trans.ADP_Security_#
, trans.TRID, trans.Batch_Code, trans.Last_Money
, null as Shares, Settle_date as Process_Date, null as Closing_Price
, trans.Dwnld_Date, trans.Acnt, null as Mktval
, cast(Null as varchar(20)) as Cusip_#
, ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION SELECT *
FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.AcntI guarantee you that won't work. Can you re-examine what you're doing, and cut-and-paste the actual code for us to help you fix?

-PatP|||Here's the actual code. Thanks

Insert Into actimize_Data.dbo.transactions (Transaction_Date_Time, Trans_Type_Cd, Account_Key, Local_Currency_Amount, Local_Currency_Cd, Row_Insert_Date)
SELECT Transaction_Date_Time, Trans_Type_Cd, Account_Key, Local_Currency_Amount, Local_Currency_Cd, Row_Insert_Date
FROM view_Transactions_1YR

CREATE VIEW dbo.view_Transactions_1YR
AS
SELECT Process_Date AS Transaction_Date_Time,
Transaction_Type AS Trans_Type_Cd,
RIGHT(Acnt, 5) AS Account_Key,
case WHEN last_money = 0.0
THEN Mktval * (CASE WHEN D_C = 'd' THEN - 1
ELSE 1 END)
ELSE Last_Money end as Local_Currency_Amount,
'USD' AS Local_Currency_Cd,
Dwnld_Date AS Row_Insert_Date
FROM dbo.view_TransactionsData_1YR trans
LEFT OUTER JOIN dbo.tbl_Transaction_Table
ON trans.Transaction_Type = dbo.tbl_Transaction_Table.Trans_Type_Cd
WHERE (dbo.tbl_Transaction_Table.Trans_Table = 'transactions')

CREATE VIEW dbo.view_TransactionsData_1YR
AS
SELECT transactions.Entry_Code, transactions.D_C,
transactions.ADP_Security_#,
dbo.getTransType(transactions.TRID,
transactions.Entry_Code, transactions.Batch_code,
transactions.D_C, transactions.ADP_Security_#,
transactions.Last_Money, transactions.Shares) AS Transaction_Type,
transactions.Process_Date, transactions.Shares,
transactions.Closing_Price, transactions.Dwnld_Date,
transactions.Acnt, transactions.Last_Money,
transactions.Mktval, transactions.Cusip_#, transactions.TRID
FROM (Select * from view_WBTransactions_DataIn_1YR
UNION
Select * from view_WBMargin_DataIn_1YR) transactions
WHERE (isnull(transactions.Entry_Code,'') NOT IN ('AIM', 'ALL', 'CEF', 'FED', 'FND', 'MMR', 'PPS', 'REI', 'WBR'))
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'NW3' ANd Batch_code ='3N')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'CON' ANd Batch_code ='RG')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'JNL' ANd Batch_code ='MF')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'CHK' ANd Batch_code ='MN')
And NOT (transactions.TRID = 'B' AND transactions.Entry_Code = 'ADJ' ANd Batch_code ='AS')
And ( Batch_code <> 'RG')
And ( Batch_code <> 'OT')

CREATE TABLE [tbl_Transaction_Table] (
[Trans_Type_Cd] [varchar] (50) NOT NULL ,
[Trans_Table] [varchar] (50) NULL ,
CONSTRAINT [PK_tbl_Transaction_Table] PRIMARY KEY CLUSTERED
([Trans_Type_Cd]
) )

CREATE VIEW dbo.view_WBTransactions_DataIn_1YR
AS
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_#, trans.TRID,
trans.Batch_code, trans.Last_Money, trans.Shares,
trans.Process_Date, trans.Closing_Price, trans.Dwnld_Date,
trans.Acnt, trans.Mktval, trans.Cusip_#, ACTIMIZE_DATA.dbo.account.account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACTIMIZE_DATA.dbo.account
ON ACTIMIZE_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

CREATE VIEW dbo.view_WBMargin_DataIn_1YR
AS
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# , trans.TRID,
trans.Batch_Code, trans.Last_Money, null as Shares,
Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACTIMIZE_DATA.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBMRGN.dbo.YTD05M
WHERE (DATEDIFF(mm, Dwnld_Date, GETDATE()) <= 14)
UNION
SELECT * FROM ADPDBMRGN.dbo.YTD06M) trans
INNER JOIN ACTIMIZE_DATA.dbo.account
ON ACTIMIZE_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN dbo.tbl_Accounts_TransactionalData
ON trans.Acnt = dbo.tbl_Accounts_TransactionalData.Acnt

CREATE TABLE [YTD05B] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Process_Date] [datetime] NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[Shares] [float] NULL ,
[Security_Desc] [varchar] (30) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Cusip_#] [varchar] (9) NULL ,
[Last_Money] [float] NULL ,
[Closing_Price] [float] NULL ,
[Mktval] [float] NULL ,
[Dividend_Factor] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[Bond_Mat_Date] [datetime] NULL ,
[Bond_Int_Rate] [float] NULL ,
[Bond_Book_Value] [float] NULL ,
[Frac_Qty] [float] NULL ,
[Price_Multipler] [varchar] (1) NULL ,
[Option_Ind] [varchar] (1) NULL ,
[MSD_Code_1] [varchar] (1) NULL ,
[MSD_Code_25] [varchar] (4) NULL ,
[MSD_Code_67] [varchar] (2) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[AdvTC] [varchar] (2) NULL ,
[AdvST] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL ,
[LedgerD] [varchar] (6) NULL
)

CREATE TABLE [YTD06B] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Process_Date] [datetime] NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[Shares] [float] NULL ,
[Security_Desc] [varchar] (30) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Cusip_#] [varchar] (9) NULL ,
[Last_Money] [float] NULL ,
[Closing_Price] [float] NULL ,
[Mktval] [float] NULL ,
[Dividend_Factor] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[Bond_Mat_Date] [datetime] NULL ,
[Bond_Int_Rate] [float] NULL ,
[Bond_Book_Value] [float] NULL ,
[Frac_Qty] [float] NULL ,
[Price_Multipler] [varchar] (1) NULL ,
[Option_Ind] [varchar] (1) NULL ,
[MSD_Code_1] [varchar] (1) NULL ,
[MSD_Code_25] [varchar] (4) NULL ,
[MSD_Code_67] [varchar] (2) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[AdvTC] [varchar] (2) NULL ,
[AdvST] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL ,
[LedgerD] [varchar] (6) NULL
)

CREATE TABLE [account] (
[account_key] [varchar] (50) NOT NULL ,
[organization_key] [varchar] (50) NULL ,
[branch_key] [varchar] (50) NULL ,
[primary_representative_key] [varchar] (50) NULL ,
[split_key] [varchar] (50) NULL ,
[acct_num] [varchar] (50) NULL ,
[acct_first_name] [varchar] (50) NULL ,
[acct_middle_name] [varchar] (50) NULL ,
[acct_last_name] [varchar] (50) NULL ,
[acct_maiden_name] [varchar] (50) NULL ,
[acct_other_name] [varchar] (50) NULL ,
[acct_type_cd] [varchar] (50) NULL ,
[acct_classification_cd] [varchar] (50) NULL CONSTRAINT [DF_account_acct_classification_cd] DEFAULT ('N/A'),
[primary_party_key] [varchar] (50) NULL ,
[acct_open_date] [datetime] NULL ,
[acct_close_date] [datetime] NULL ,
[last_review_date] [datetime] NULL ,
[sector_cd] [varchar] (50) NULL ,
[investment_objective_cd] [varchar] (50) NULL ,
[risk_tolerance_cd] [varchar] (50) NULL ,
[credit_limit_local_currrency_value] [float] NULL ,
[stated_liquid_net_worth] [float] NULL ,
[stated_total_net_worth] [float] NULL ,
[stated_account_activity] [float] NULL ,
[stated_account_value] [float] NULL ,
[is_suspect] [bit] NULL ,
[is_dvp_rvp] [bit] NULL ,
[is_active] [bit] NULL ,
[is_online_account] [bit] NULL ,
[avg_balance] [float] NULL ,
[verification_status] [varchar] (50) NULL ,
[verification_date] [datetime] NULL ,
[household_id] [varchar] (50) NULL ,
[is_proprietary] [bit] NULL ,
[is_street_side] [bit] NULL ,
[is_error_account] [bit] NULL ,
[is_institutional] [bit] NULL ,
[is_employee_account] [bit] NULL ,
[is_tax_deferred] [bit] NULL ,
[is_single_joint] [bit] NULL ,
[is_fee_based_acct] [bit] NULL ,
[is_anonymous_acct] [bit] NULL ,
[is_option_approved] [bit] NULL ,
[option_approval_level_cd] [varchar] (50) NULL ,
[is_discretionary] [bit] NULL ,
[managed_acct_type] [varchar] (50) NULL ,
[row_update_date] [datetime] NULL ,
CONSTRAINT [pk_account] PRIMARY KEY CLUSTERED
(
[account_key]
) )

CREATE TABLE [tbl_Accounts_TransactionalData] (
[Acnt] [varchar] (9) NOT NULL ,
CONSTRAINT [PK_tbl_Accounts_TransactionalData] PRIMARY KEY CLUSTERED
(
[Acnt]
))

CREATE TABLE [YTD05M] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Last_Money] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[US_Tax_Status] [varchar] (1) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[ADVtc] [varchar] (2) NULL ,
[ADVst] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6) NULL
)

CREATE TABLE [YTD06M] (
[Branch] [varchar] (3) NULL ,
[TRID] [varchar] (1) NULL ,
[Entry_Code] [varchar] (3) NULL ,
[Batch_Code] [varchar] (2) NULL ,
[Acnt] [varchar] (9) NULL ,
[Settle_Date] [datetime] NULL ,
[D_C] [varchar] (1) NULL ,
[ADP_Security_#] [varchar] (7) NULL ,
[Last_Money] [float] NULL ,
[AcntType] [varchar] (1) NULL ,
[Chkdigit] [varchar] (1) NULL ,
[US_Tax_Status] [varchar] (1) NULL ,
[Security_Spin] [varchar] (1) NULL ,
[Dwnld_Date] [datetime] NULL ,
[ADVtc] [varchar] (2) NULL ,
[ADVst] [varchar] (2) NULL ,
[LedgerLM] [varchar] (6)
)

Poll For a File

Hi All,

I am using ActiveX code in a DTS package (Package1) to search a directory for a file.
When the file is there I want to start a DTS package (Package2) to load
in this file.

I will schedule Package1 to run at 10pm, however since the external file is being generated externally I cannot guarentee that the file will be there at 10pm. Therefore I want to Poll the folder and not start package2 until I have the file. I am using the If file exists method.

How do I poll for a file, and if its not there wait for 5 mins and poll again until I find the file.

Thanks in advanceHave you tried FSO objects?

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")|||I have tried the FSO and thats what I am using.

My problem is that if the file is not there, I want to wait for a period of
time ie 5 mins and then try again. And keep trying every five mins until I get the file.

Some thing like this:

========================
Function Main()

dim result

set objFSO = CreateObject("Scripting.FileSystemObject")

result = 1

DO WHILE result = 1
If objFSO.FileExists("c:\test.txt") Then
//Start other Package
result = 99
Else
wait(5)
//Go to start and check for file again
End if

LOOP

End Function
=========================

So in essence the above code will LOOP until it finds the file.
I just need something to replace the wait(5)

Any Ideas

Thanks in advance

I have wait(5) but wait is not a valid function|||EXEC [master].[dbo].[xp_fileexist] 'c:\new_auth.dat1'

This might help|||Originally posted by superquinn
I have tried the FSO and thats what I am using.

My problem is that if the file is not there, I want to wait for a period of
time ie 5 mins and then try again. And keep trying every five mins until I get the file.

Some thing like this:

========================
Function Main()

dim result

set objFSO = CreateObject("Scripting.FileSystemObject")

result = 1

DO WHILE result = 1
If objFSO.FileExists("c:\test.txt") Then
//Start other Package
result = 99
Else
wait(5)
//Go to start and check for file again
End if

LOOP

End Function
=========================

So in essence the above code will LOOP until it finds the file.
I just need something to replace the wait(5)

Any Ideas

Thanks in advance

I have wait(5) but wait is not a valid function

Why do not create a new function (I did not check it, sorry) and call it:

Function wait(theDate)
Do Until DateDiff("n", Now, theDate)<5
Loop
End Function|||I dont want to write a loop function like you have suggested as this constant looping might be very hard on the CPU. I was hoping there was a clever way or else an in built wait/sleep function available to me in the ActiveX component of the DTS.

Can Anyone help?|||You could write a Agent Job that will run every 5 minutes from 10 to 12. Sounds like the easiest way.|||Hi,

Im not quiet sure I understand what you mean by an agent Job.

I am only new to SQL Server and DTS.

The polling for a file is causing a lot of trouble for me.
Can someone please help|||In enterprise Manager, expand Management, SQL Server Agent, then click on Jobs. From there you can add a new job, which can execute ActiveX scripts, T-SQL scripts, even DTS packages. You can add one or many schedules to kick off the job.

I think it may be what you are looking for.

You can get started by right-clicking on the DTS package you created, and selecting Schedule Package, which will set the DTS package up to run as a job. You can then adjust the schedule to your liking, add more steps, etc.|||Would the command WAITFOR DELAY be useful?

Here is the help from MS SQL Server Books Online...
WAITFOR
Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.

Syntax
WAITFOR { DELAY 'time' | TIME 'time' }

Arguments
DELAY

Instructs Microsoft SQL Server to wait until the specified amount of time has passed, up to a maximum of 24 hours.

'time'

Is the amount of time to wait. time can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date portion of the datetime value is not allowed.

TIME

Instructs SQL Server to wait until the specified time.

Remarks
After executing the WAITFOR statement, you cannot use your connection to SQL Server until the time or event that you specified occurs.

To see the active and waiting processes, use sp_who.

Examples
A. Use WAITFOR TIME
This example executes the stored procedure update_all_stats at 10:20 P.M.

BEGIN
WAITFOR TIME '22:20'
EXECUTE update_all_stats
END

For more information about using this procedure to update all statistics for a database, see the examples in UPDATE STATISTICS.

B. Use WAITFOR DELAY
This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.

CREATE PROCEDURE time_delay @.@.DELAYLENGTH char(9)
AS
DECLARE @.@.RETURNINFO varchar(255)
BEGIN
WAITFOR DELAY @.@.DELAYLENGTH
SELECT @.@.RETURNINFO = 'A total time of ' +
SUBSTRING(@.@.DELAYLENGTH, 1, 3) +
' hours, ' +
SUBSTRING(@.@.DELAYLENGTH, 5, 2) +
' minutes, and ' +
SUBSTRING(@.@.DELAYLENGTH, 8, 2) +
' seconds, ' +
'has elapsed! Your time is up.'
PRINT @.@.RETURNINFO
END
GO
-- This next statement executes the time_delay procedure.
EXEC time_delay '000:00:10'
GO

Here is the result set:

A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.|||Hi,

Thanks for your reply. I dont think I am explaining myself fully

I know how to sechdule the Package.
I know how to check if file exists

What I need to know is how to halt processing in a activex script
for a period of time.

If the file is not there initially, I want the wait a while and the reloop to
the start of the package and so on until the file is there

==========ACTIVEX SCRIPT========

Set foundfile = 0

Do While foundfile = 0

if foundfile = 1 then
set foundfile = 1 (This breaks loop)
//Carry on processing
Set Success
else
//Halt procesing for 5 mins and then continue
//This is where I need HELP!!

end if

loop|||I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.|||The problem with having an ActiveX script wait, is that you are tying up a thread and processor resources just counting away clock cycles. The SQL Agent dooes that already, so you might as well take advantage of it.|||Originally posted by bpdWork
I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.

=====================================
This sounds EXACTLY like what I want to do. Im sorry about this
but Im new to SQL Server.

How do you get a Job to reschedule itself in code?
I assume this is done in ActiveX|||Off the top of my head, I would say that you would need to use an ActiveX script to access the SQLDMO object, and control it from there. I have done such a thing before. I will see what I can find in my old code heap.|||I think I can help

You should create your package wiht the activex script ect...
Then schedule it to run every 5 mins. as a SQL job.

The active x script should look like this. No need to make the activex script wait, just have it run again and again. If no file, it just quits, when it finds one it runs the other import package.

Function Main()

Dim objFSO
Dim objFolder
Dim objFile
Dim oPKG
Dim fileName
Dim folderName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\server\share$\dir\")

For Each objFile in objFolder.Files

fileName = objFile.Path

Set oPKG = CreateObject("DTS.Package")
oPKG.LoadFromSQLServer "Server", "sa", "", , , , , "2nd package name here"
oPKG.GlobalVariables("fileName").Value = fileName
oPKG.Execute
oPKG.Uninitialize()
Set oPKG = Nothing

objFSO.MoveFile fileName, folderName 'move file somewhere
Next

Main = DTSTaskExecResult_Success

End Function

Hope this helps

Steve|||yes thats the easiest way,

The first step in your package will be a activeX task with the following script inside

==========================================
Function Main()
Dim fs, MyFile
Dim FileLocation
Dim FileName
Dim FileInfo

FileName = "file.txt"

FileLocation = DTSGlobalVariables("Invoice_Input_Location").Value

FileInfo = FileLocation & FileName

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(FileInfo)) Then
fs.MoveFile (FileInfo),(FileLocation & "new_file.txt")
set fs=nothing
Main = DTSTaskExecResult_Success
exit function
Else
Main = DTSTaskExecResult_Failure
exit function
End If

End Function
==========================================

rest of the steps inside the DTS will be after success of this task. The package will be schedules to run after every 5 mins, if your file is present, it will be renamed with a different name (so that next time when the package runs afetr 5 mins it does not process it all over again), this renamed file can be used for further transformations. This way the package will run successfully just once everyday and will fail for rest of the times. which is what you want.

Polish characters problem.

Hi,
First off all I am not SQL expert so pls forgive me if something will be
unclear.
I have old SQL 7.0 with Code page CP1252 and database application on it with
polish characters.
I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
database) and my application do not see polish characters.
Any idea?
Artur
Hi
If you had code page 1250 then I would expect a Polish collation to be used.
Extended data would be lost if you switched from 1250 to 1252. Therefore you
may want to try a Polish collation for the destination database.
How did you convert the database?
John
"arti" <artipp@.op.pl> wrote in message news:cll8au$odf$1@.news.onet.pl...
> Hi,
> First off all I am not SQL expert so pls forgive me if something will be
> unclear.
>
> I have old SQL 7.0 with Code page CP1252 and database application on it
with
> polish characters.
> I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
> database) and my application do not see polish characters.
> Any idea?
>
> Artur
>

Polish characters problem.

Hi,
First off all I am not SQL expert so pls forgive me if something will be
unclear.
I have old SQL 7.0 with Code page CP1252 and database application on it with
polish characters.
I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
database) and my application do not see polish characters.
Any idea?
ArturHi
If you had code page 1250 then I would expect a Polish collation to be used.
Extended data would be lost if you switched from 1250 to 1252. Therefore you
may want to try a Polish collation for the destination database.
How did you convert the database?
John
"arti" <artipp@.op.pl> wrote in message news:cll8au$odf$1@.news.onet.pl...
> Hi,
> First off all I am not SQL expert so pls forgive me if something will be
> unclear.
>
> I have old SQL 7.0 with Code page CP1252 and database application on it
with
> polish characters.
> I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
> database) and my application do not see polish characters.
> Any idea?
>
> Artur
>sql

Polish characters problem.

Hi,
First off all I am not SQL expert so pls forgive me if something will be
unclear.
I have old SQL 7.0 with Code page CP1252 and database application on it with
polish characters.
I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
database) and my application do not see polish characters.
Any idea?
ArturHi
If you had code page 1250 then I would expect a Polish collation to be used.
Extended data would be lost if you switched from 1250 to 1252. Therefore you
may want to try a Polish collation for the destination database.
How did you convert the database?
John
"arti" <artipp@.op.pl> wrote in message news:cll8au$odf$1@.news.onet.pl...
> Hi,
> First off all I am not SQL expert so pls forgive me if something will be
> unclear.
>
> I have old SQL 7.0 with Code page CP1252 and database application on it
with
> polish characters.
> I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
> database) and my application do not see polish characters.
> Any idea?
>
> Artur
>

plz help me to upload my sqlserver data base with query analyzer method

plz help me to upload my sqlserver data base with query analyzer method
plz write the code i should write in query analyzer box to uplode
my database(db) in my server(h_server)
and is it neccessary to uplode log file or not
,or guide me with intruducing a sitewhat xactly are you trying to do ? just load up query analyzer to test some queries ? its in the menu under microsoft sql server under your programs menu. or you can open it from tools - > query analyzer from sql server xplorer.

hth|||i understand what you ae saying but i dont know who i replace suitable elements in this code

CREATE DATABASE [co] ON (NAME = N'co_Data', FILENAME = N'e:\sqlserver 2000\MSSQL\data\co_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'co_Log', FILENAME = N'e:\sqlserver 2000\MSSQL\data\co_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'co', N'autoclose', N'true'
GO

exec sp_dboption N'co', N'bulkcopy', N'false'
GO

exec sp_dboption N'co', N'trunc. log', N'true'
GO

exec sp_dboption N'co', N'torn page detection', N'true'
GO

exec sp_dboption N'co', N'read only', N'false'
GO

exec sp_dboption N'co', N'dbo use', N'false'
GO

exec sp_dboption N'co', N'single', N'false'
GO

exec sp_dboption N'co', N'autoshrink', N'true'
GO

exec sp_dboption N'co', N'ANSI null default', N'false'
GO

exec sp_dboption N'co', N'recursive triggers', N'false'
GO

exec sp_dboption N'co', N'ANSI nulls', N'false'
GO

exec sp_dboption N'co', N'concat null yields null', N'false'
GO

exec sp_dboption N'co', N'cursor close on commit', N'false'
GO

exec sp_dboption N'co', N'default to local cursor', N'false'
GO

exec sp_dboption N'co', N'quoted identifier', N'false'
GO

exec sp_dboption N'co', N'ANSI warnings', N'false'
GO

exec sp_dboption N'co', N'auto create statistics', N'true'
GO

exec sp_dboption N'co', N'auto update statistics', N'true'
GO

use [co]
GO

CREATE TABLE [dbo].[users] (
[u_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[u_username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_joindate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_prof] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_fname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_lname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_coname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_location] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_nation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_website] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[u_account] [float] NULL
) ON [PRIMARY]
GO

Plug-in Algorithms in Managed Code

Can anyone update me on the status of the managed-code framework for plug-in algorithms? I've heard reports that it's "in the works" and "almost ready to release", and now I'm really anxious to get my hands on it!

We're still working out the delivery details for the component. Hopefully it will be available by April.

Thanks

-Jamie

Plug-in algorithm shell complilation error

I'm trying to create plug-in algorithm using tutorial from Max Chickering and Raman Iyer. I follow steps very carefully and as far as I know my code is exactly the same as Completed shell solution in source files of tutorial. But when I try to compile factory.cpp file I get this error:
Error 1 error C2259: 'ATL::CComObject<Base>' : cannot instantiate abstract class e:\development\ms visual studio 2005\vc\atlmfc\include\atlcom.h 1799

When I compile completed shell solution from source files it's OK. Do you have any ideas, where the problem could be?

Thank you

This most likely indicates that you haven't provided implementation for all the abstract methods from IDMAlgorithm interface. Unfortunately, the error does not point you to that, but rather fails in the code which instantiates the class (cannot instantiate an abstract class).

Hope this helps

Shuvro

|||

The error window of VS only includes this error fragment. However, if you check the Output window after the failed compilation, you will find details about the virtual method that lacks implementation.

Once you find the name of the missing method, you can search it (VS Ctrl+Shift+F, Find in Files) in the original sample source code. Make sure the file containing the code for that method is included and available in your copy of the sample

Hope this helps

Plug-in algorithm shell complilation error

I'm trying to create plug-in algorithm using tutorial from Max Chickering and Raman Iyer. I follow steps very carefully and as far as I know my code is exactly the same as Completed shell solution in source files of tutorial. But when I try to compile factory.cpp file I get this error:
Error 1 error C2259: 'ATL::CComObject<Base>' : cannot instantiate abstract class e:\development\ms visual studio 2005\vc\atlmfc\include\atlcom.h 1799

When I compile completed shell solution from source files it's OK. Do you have any ideas, where the problem could be?

Thank you

This most likely indicates that you haven't provided implementation for all the abstract methods from IDMAlgorithm interface. Unfortunately, the error does not point you to that, but rather fails in the code which instantiates the class (cannot instantiate an abstract class).

Hope this helps

Shuvro

|||

The error window of VS only includes this error fragment. However, if you check the Output window after the failed compilation, you will find details about the virtual method that lacks implementation.

Once you find the name of the missing method, you can search it (VS Ctrl+Shift+F, Find in Files) in the original sample source code. Make sure the file containing the code for that method is included and available in your copy of the sample

Hope this helps

Plug-in algorithm documentation and sample code

I've read in at least one book (and heard firsthand at the SQL Server launch event) that there's a plug-in framework for adding new algorithms to Data Mining. However, the book was apparently written against a Beta version, and the location it suggests for the plug-in sample code doesn't exist in the shipping version. I haven't been able to locate any documentation about the plug-in framework in the SQL Server Books Online, and I haven't been able to find any sample code, either.

Can someone give me a few pointers?Did you explicitly install the SQL Server Samples? You have to choose advanced options during setup to get them - they are not installed by default. If not, go to add/remove programs to run the installer again to add the samples to your installation.|||

You can also find the plug-in algorithm tutorial (with complete source code for a sample) here: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/Links_LinkRedirector.aspx?id=62

The plug-in algorithm whitepaper is available here:
http://www.sqlserverdatamining.com/DMCommunity/Whitepapers+and+Articles/Whitepapers/Links_LinkRedirector.aspx?id=64

Raman Iyer
SQL Server Data Mining

|||Yes, I did install the samples. In fact, I tried on two different installations of SQL Server -- one in which I'd installed the samples with the original installation, and one in which I installed them after-the-fact. In neither case could I find any of the plug-in samples.|||Thank you very much!|||

Hi,

This site is very good but I need to ask aquistion.

Can I use the C# language instead of VC++ in order to create the new mining algorithm.

Is there any example?

Thanks in advance for any help,

Aya.

|||We are working on a framework/SDK for developing plug-in algorithms using managed code. We hope to make it available for download, along with sample code, in the next month or so.

Plug-in algorithm documentation and sample code

I've read in at least one book (and heard firsthand at the SQL Server launch event) that there's a plug-in framework for adding new algorithms to Data Mining. However, the book was apparently written against a Beta version, and the location it suggests for the plug-in sample code doesn't exist in the shipping version. I haven't been able to locate any documentation about the plug-in framework in the SQL Server Books Online, and I haven't been able to find any sample code, either.

Can someone give me a few pointers?Did you explicitly install the SQL Server Samples? You have to choose advanced options during setup to get them - they are not installed by default. If not, go to add/remove programs to run the installer again to add the samples to your installation.|||

You can also find the plug-in algorithm tutorial (with complete source code for a sample) here: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/Links_LinkRedirector.aspx?id=62

The plug-in algorithm whitepaper is available here:
http://www.sqlserverdatamining.com/DMCommunity/Whitepapers+and+Articles/Whitepapers/Links_LinkRedirector.aspx?id=64

Raman Iyer
SQL Server Data Mining

|||Yes, I did install the samples. In fact, I tried on two different installations of SQL Server -- one in which I'd installed the samples with the original installation, and one in which I installed them after-the-fact. In neither case could I find any of the plug-in samples.|||Thank you very much!|||

Hi,

This site is very good but I need to ask aquistion.

Can I use the C# language instead of VC++ in order to create the new mining algorithm.

Is there any example?

Thanks in advance for any help,

Aya.

|||We are working on a framework/SDK for developing plug-in algorithms using managed code. We hope to make it available for download, along with sample code, in the next month or so.

Pls-00378

Hello PL/SQL gurus,

I am facing a seemingly simple problem compiling a function.

When I try to compile the folllowing code...

CREATE OR REPLACE FUNCTION WDF_UNITS_SHIPPED_FN ( p_trf_number IN PLS_INTEGER ) RETURN NUMBER ;

I am getting.....

PLS-00378: invalid compilation unit for this release of PL/SQL

Any ideas?

Please helpOriginally posted by pusht
Hello PL/SQL gurus,

I am facing a seemingly simple problem compiling a function.

When I try to compile the folllowing code...

CREATE OR REPLACE FUNCTION WDF_UNITS_SHIPPED_FN ( p_trf_number IN PLS_INTEGER ) RETURN NUMBER ;

I am getting.....

PLS-00378: invalid compilation unit for this release of PL/SQL

Any ideas?

Please help
Your function definition is incomplete - it is just a specification, there is no body. This is correct in a package specification, but not for a stand alone function.

pls..help me to find out the error in the code

hi,

when i tried to run the following code , the code keeps on running without creating the required table...............

pls can anyone help me to figure out the problem........

waiting for your replies...

int row_head = 3;

int col_head = 3;

TextBox[] columns1=new TextBox[5];

TextBox[] rows = new TextBox[2];

for (int i = 0; i < 3; i++)

columns1[ i ] = new TextBox();

for (int i = 0; i < 2; i++)

rows[ i ] = new TextBox();

columns1[0].Text = "col1";

columns1[1].Text = "col2";

columns1[2].Text = "col3";

rows[0].Text = "rowh1";

rows[1].Text = "rowh2";

String[] description=new string[10];

for (int k = 0; k < 10; k++)

description[k] = k.ToString();

int index_value = 0;

int des_index = 0;

string datatype = "";

string command = "";

SqlConnection sqlCN = new SqlConnection(connectionString);

for (int i = 0; i < col_head + 1; i++)

{

if (i == 0)

datatype += "(col " + i + " varchar(20),";

else if (i + 1 == col_head+1)

datatype += "col" + i + " varchar(20))";

else

datatype += "col" + i + " varchar(20),";

}

MessageBox.Show(datatype);

MessageBox.Show("create table " + textBox1.Text + " " + datatype);

string selectCommandText = "create table " + textBox1.Text +datatype;

MessageBox.Show("executed");

try

{

SqlCommand sqlCmd = new SqlCommand(selectCommandText, sqlCN);

sqlCN.Open();

MessageBox.Show("open ");

// the following command is not executed.....

sqlCmd.ExecuteNonQuery();

MessageBox.Show("open and execu");

for (int i = 0; i < row_head + 1; i++)

{

command="";

if (i == 0)

{

command="' ',";

for(int j=0;j<col_head;j++)

{

if(j+1==col_head)

command+="'"+columns1[j].Text+"'";

else

command+="'"+columns1[j].Text+"',";

}

}

else

{

command = "'"+rows[index_value++].Text+"',";

for (int j = des_index; j < des_index + col_head; j++)

{

if (j +1== des_index+col_head)

command += "'" + description[j] + "'";

else

command += "'" + description[j] + "',";

}

des_index = des_index + col_head;

}

MessageBox.Show("INSERT INTO " + textBox1.Text + " values(" + command + ")");

sqlCmd.CommandText = "INSERT INTO " + textBox1.Text + " values("+command+")";

sqlCmd.ExecuteNonQuery();

}

MessageBox.Show("fini");

selectCommandText = "SELECT * FROM " + textBox1.Text;

sqlCmd = new SqlCommand(selectCommandText, sqlCN);

sqlCmd.ExecuteNonQuery();

SqlDataAdapter sqlDA = new SqlDataAdapter(selectCommandText, sqlCN);

DataSet DS = new DataSet();

sqlDA.Fill(DS);

MessageBox.Show("fini 2");

DataGridView1.DataSource = DS.Tables[0].DefaultView;

}

catch (Exception eio)

{ }}

Sweety

Appearantly you are getting an error during the execution. Having a try / catch block, with no actions or error retrieval functionalty will keep the error under the covers.

Please post the error message in here which ca be retrieved by reading the eio.Message and post the script which is used for the creation of the table.

BTW if you want to create a table you could use SMO rather than doing a script composing and executing this against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

I tried to print the message , I got " Incorrect syntax near '0'"..can some one tell me what does it mean...

bye

Sweety

|||

This is a syntax error in your query. Post the query (commandtext of the command here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is your mistake: You have put a space after col so your create table command looks like create table (col 0 varchar(20), ... which is not correct, remove the space and it will start working.

for (int i = 0; i < col_head + 1; i++)

{

if (i == 0)

datatype += "(col " + i + " varchar(20),";

else if (i + 1 == col_head+1)

datatype += "col" + i + " varchar(20))";

else

datatype += "col" + i + " varchar(20),";

}

Thanks

Waseem

PLS HELP! sql exception: SQL Server does not exist or access denied

hI all,
I have sql server 2000 installed on one machine runningwindows server 2003 and using VS 2003 to code and run IIS 5.0 onanother machine running windows XP.
I'm getting the error: sql exception: SQL Server does not exist oraccess denied. But i tried using osql to connect to the sqlserver from the XP machine and it works fine!
Can someone pls help me and tell me why i get this error for my webappbut i can connect to the sql server with osql? Thanks in Advance

bear in mind that the connectionstring is differernt for each machine depends on the machine setting for hte SQL SErver so check that carefully

www.connectionstrings.com
check that and if you have the same problem get back to us with the connection string and error message so we might help

pls help

Hi I'm a newbie on SASS so pls have patience with me.

Sales Sales Amount Item Code

Items Item code Item description buyer code

Buyers Buyer code Name

sales refers to items -- items refer to buyers to get the buyer name.

I currently have 1 measure & 2 dimensions. My problem is, the items dimension contains buyer codes that doesn't exist anymore in the buyers table. I tried to set the NullProcessing property of the buyer code attribute in the Items dimesion to "Unknown Member". I've also set the Unknown Member property of the Items dimension to Visible. However, once I process the Items dimension, an error occurs.

Error message:

Internal error: The operation terminated unsuccessfully.

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Errors in the OLAP storage engine: An error occurred while the 'Items' attribute of the 'Items' dimension from the 'HMAX2Trial' database was being processed.

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Buyers, Column: Buyer_x0020_Code, Value: 201.

Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Items of Dimension: Items from Database: HMAX2Trial, Record: 85852.

By the way, I just used the default Error Configuration and didn't make any changes to it.

Does anybody have an idea on how to solve this? I need some help on this.

Thank you so much,

JOP79

JOP79

Keep One Row for Unknown Buyers in Buyers Table

Buyer Code

Name

0

Unknown

And durring ETL replace Buyer Code with 0 not found in Buyers Table. Then you will find your result.

Bhudev

|||

Hi,

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Buyers, Column: Buyer_x0020_Code, Value: 201."

This is your mainly problem.

we can define it with : Your buyer dimension didn't have member with value 201 in column "Buyer Code"

solution : You must reprocess dimesion Buyer before process fact, but ensure your dw had this value

or you must change setting processing,

you could find it when Process windows appear.

"Change setting" have 2 tab, click Dimension key error,

change to "Use custom error configuration"

ensure "key error action" : Convert to unknown

You can change when "key not found" setting to ignore error.

Finnaly all of your "not found" key, converted to unknown.

but for accuracy data you must add this value to your DW

akhukum

Indra P

|||

Hi,

Thank you so much for your suggestions. They were really helpful!

JOP79

please verify syntax

I haven't written any .net database updates before. I wrote the following code for a visual web developer application and it appears to work. There is no close function or anything. Will the adapter, builder, dataset, and row objects be cleaned up and disposed of properly? Please tell me if I need to include anything else. The connection object is made elsewhere. Thanks

Dim adapterAsNew Data.SqlClient.SqlDataAdapter("SELECT * FROM Inventory WHERE InUnique=" & ListValue.Value, connect)

Dim builderAsNew Data.SqlClient.SqlCommandBuilder(adapter)

Dim datasetAs Data.DataSet =New Data.DataSet()

adapter.Fill(dataset,"Inventory")

Dim datarowAs Data.DataRow = dataset.Tables(0).Rows(0)

datarow("InAgUnique") = Val(InAgUnique.Text)

datarow("InTyUnique") = Val(InTyUnique.Text)

datarow("InName") = InName.Text

datarow("InPnUniquePrimary") = Val(InPnUniquePrimary.Text)

datarow("InPnUniqueSecondary") = Val(InPnUniqueSecondary.Text)

datarow("InPnUniqueTertiary") = Val(InPnUniqueTertiary.Text)

datarow("InPnUniqueSupervisor") = Val(InPnUniqueSupervisor.Text)

datarow("InDescription") = InDescription.Text

datarow("InProblemInstructions") = InProblemInstructions.Text

adapter.Update(dataset,"Inventory")

If the connection is not open, Adapter would open it and once done with the operation, would close itself (as per .net 1.1), not sure of the chanegs in 2.0 though!

Please someone out there has to know... I am going crazy

ok I will post no code this time. I need help I have tried a half of dozen methods and they all only work half way. I need to create a search page that searches on all keywords in a textbox. I have tried split functions, like commands, freetext catalogs.

I want to go with the free text search.

How?

Has anyone used a freetext search in asp or c#? please post some code I re arrange it or let me know how to pass the freetext search param to the sql server....

Please....

The detailed explanation of the Full text feature of SQL Server is out of the scope of this post. Visit below links to understand the feature and how to implement it.

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

http://technet.microsoft.com/en-us/sqlserver/bb331773.aspx

http://www.eggheadcafe.com/articles/20010422.asp

http://www.sql-server-performance.com/articles/all/tb_search_optimization_p1.aspx

Some links are for SQL 2000 and some for SQL 2005. Feel free to ask any queries on this feature if you've any doubts after studying the concept.

Hope this will help.

|||

http://forums.asp.net/p/801329/803263.aspx

Look at aptbid's third post in the thread. His SP splits the words in the searchterm and then returns ranked results based on the position of the search term in the fields searched. Obviously you will need to adapt the table and field names to your environment.

|||

the problem was that sql server 2005 does not like to auto populate I had to turn change tracking to off the do a incremental population this worked happens on two different machine.

PLEASE PLEASE HELP - How can I get a return value from a SQL Stored Proc is ASP.NET?

Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.

Here is my Stored Procedure code.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
@.sUPC varchar(50),
@.sRetUPC varchar(50) OUTPUT

AS

BEGIN
SET NOCOUNT ON;
SET @.sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =
@.sUPC)
RETURN @.sRetUPC

END

Here is my ASP.NET code.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim oConnSQL As ADODB.Connection

oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()

Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"

Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")

Dim res
res = oSproc("sRetUPC")

Response.Write(res.ToString())

End Sub

If I put the line -
oSproc.Execute()

above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@.sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?jbonifacejr wrote:

Quote:

Originally Posted by

>
Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.
>
Here is my Stored Procedure code.
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
@.sUPC varchar(50),
@.sRetUPC varchar(50) OUTPUT
>
AS
>
BEGIN
SET NOCOUNT ON;
SET @.sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =
@.sUPC)
RETURN @.sRetUPC
>
END
>
Here is my ASP.NET code.
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
>
Dim oConnSQL As ADODB.Connection
>
oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()
>
Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"
>
Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")
>
Dim res
res = oSproc("sRetUPC")
>
Response.Write(res.ToString())
>
End Sub
>
If I put the line -
oSproc.Execute()
>
above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@.sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?

Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one

HTH,
Gert-Jan|||Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...

I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter

Quote:

Originally Posted by

>From there, I appended the parameters in the ASP code...like this

oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.

Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()

This is not working. Do you know how I can get access to the value of
the parameter that is returned?

Quote:

Originally Posted by

Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one
>
HTH,
Gert-Jan

|||jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
>

Quote:

Originally Posted by

>>From there, I appended the parameters in the ASP code...like this

oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?

Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?

Since I don't even know how your code looks like right now, just two
notes:

1) Use parameter names with leading @.. The underlying provider may
prefer that.

2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.

Jan

Erland Sommarskog wrote:

Quote:

Originally Posted by

jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...

I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter

Quote:

Originally Posted by

>From there, I appended the parameters in the ASP code...like this

oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.

Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()

This is not working. Do you know how I can get access to the value of
the parameter that is returned?

>
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
>
Since I don't even know how your code looks like right now, just two
notes:
>
1) Use parameter names with leading @.. The underlying provider may
prefer that.
>
2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Why are you using stored proc when you can use a function(s)? I always
thought the output parameter was a bit of a hack and clumsy to use.

jbonifacejr wrote:

Quote:

Originally Posted by

If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.
>
Jan
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
>
>>From there, I appended the parameters in the ASP code...like this
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?

Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?

Since I don't even know how your code looks like right now, just two
notes:

1) Use parameter names with leading @.. The underlying provider may
prefer that.

2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

If you look at the top post you will see where I put the code I am
using.

Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.

Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.

Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.

So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.

I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object. I found a great KB article
that basically shows me everythig I am doing (right and wrong)...

http://support.microsoft.com/kb/306574
Erland Sommarskog wrote:

Quote:

Originally Posted by

jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

If you look at the top post you will see where I put the code I am
using.

>
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
>
Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.

Posting the same question independently to two forums is not a nice
thing to. This means that people can waste time on answering your post,
when it has already been answered elsewhere.

Quote:

Originally Posted by

Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.
>
So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.

Again, without seeing your code it's hard to tell. There is a difference
between ADO and SqlClient though: with ADO, the parameter names are
just local to the application, so if you misspell a parameter name,
you may get away with it. Not so with SqlClient.

Quote:

Originally Posted by

I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object.

Since your procedure has an output parameter, but no result set, the most
conventient method to use is ExecuteNonQuery, in which case you only need
the Command object.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Please help. How to set a Parameter in SSRS in runtime? Parameters!myParam.Value property is rea

You can set the default value of a reporting services parameter by any expression.

But with code I'm NOT allowed to do:

Parameters!myParam.Value = CDate("01.01.2007")

This is cause Value property is read-only.

So the question would be:

Are there any way I set a parameter value in runtime ?

Hope anyone can help...

Use VB.Net or C# code (Report -> Report Properties -> Code tab) to store the parameter in some shared variable. Use this variable to set and retrieve value whenever and wherever you need in the report by using the syntax Code.MySharedVar

Shyam

|||

Hi Shyam,

I have tried that also, but the problem is that the calendar control turn into a drop-down combo control when you apply either code or connect it to a dataset.

The parameter is a datetime type. As long as you add logic to the "default value" it showns the calendar control, if you add anything to "available values" the calendar disappears and a combo is shown. Any ida what I can do to keep it displaying the calendar control ?

|||

I guess you are raising a new issue now. I dont think your issue can be solved at the reporting services level.