Passing Parameter To The SP

Passing Parameter

 to pass current datetime as a parameter to an SP. is like this : declare @rrr datetime = getdate() exec

The following will
droprouteprocessing @inpdate = @rrrnot work :
exec
droprouteprocessing GetDate()exec droprouteprocessing @inpdate = getdate()exec droprouteprocessing (select getdate())exec


In
separate droprouteprocessing select getdate() particular, the last two statements will work as though they are sql statements. Hence the SP will execute with default parametersand
the then select GETDATE() statement will be executed, which will display current datetime on console.

Suppose I want


The only way it will work To The SP

How to create a page total CRYSTAL REPORT

http://www.tek-tips.com/faqs.cfm?fid=1295


Creating a Page Total in Crystal

It is strange that a powerful program like Crystal does not have a simple way to calculate a page total.  Here is the best way we have found.

Step One:  Create a formula field called reset with the following expression:

//reset
WhilePrintingRecords;
shared numbervar total := 0

You can use any name to substitute for the word ‘total’.


Step Two:  Create a formula field called accum with the following expression:

//accum
WhilePrintingRecords;
shared numbervar total := total + {table.numberfield};

Step Three: Create a formula field call display with the following expression:

//display
WhilePrintingRecords;
Shared numbervar total;
Total

Place the formula field reset in the page header. Right-click on it, pick “format” and then pick the common tab. Pick “suppress”.

Place the formula field accum in the details section. Right-click on it, pick “format” and then pick the common tab. Pick “suppress”.

Place the formula field display in the page footer. Position and format it as required.

That is it!  

code project wcf series

http://www.codeproject.com/KB/architecture/wcfbyexample_baseline.aspx
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter02.aspx
WCF by Example - Chapter II - Repositories
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter03.aspx
WCF by Example - Chapter III - Response
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter04.aspx
WCF by Example - Chapter IV - Transaction Manager
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter05.aspx
WCF by Example - Chapter V - Contexts
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter06.aspx
WCF by Example - Chapter VI - WPF Client - Baseline
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter07.aspx
WCF by Example - Chapter VII - WPF Client - Contract Locator
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter08.aspx
WCF by Example - Chapter VIII - WPF Client - Relay Command
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter09.aspx
WCF by Example - Chapter IX - WPF Client - Notify Property Change
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter10.aspx
WCF by Example - Chapter X - Dependency Injection with Spring.Net
http://www.codeproject.com/KB/architecture/wcfbyexample_chapter11a.aspx
WCF by Example - Chapter XI - NHibernate Implementation
WCF by Example - Chapter XI - NHibernate Implementation

Computed Columns

*A computed column cannot be the target of an INSERT or UPDATE statement.
*Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query.
It is an error to insert values for computed
columns in a database :
Create Table #Test (a int, b int, C as a+b )
go
insert into #test values (1,2,5)
go
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.


*PERSISTED Keyword is used to persist the computed columns in database.

check constraint for TimeToReach

create table rsc_check_constraint_test(
TimeToReach ID int IDENTITY(1,1) , nvarchar(10) CHECK ( LEN(TimeToReach) = 5 AND SUBSTRING(TimeToReach , 3,1) = ':' AND ISNUMERIC(SUBSTRING(TimeToReach, 1,2)) = 1 AND ISNUMERIC(SUBSTRING(TimeToReach, 4,2)) = 1 AND CONVERT(int , SUBSTRING(TimeToReach , 1,2)) > 5 AND CONVERT(int , SUBSTRING(TimeToReach , 1,2)) < 19 )
)



insert
 select
 drop table rsc_check_constraint_test
* from rsc_check_constraint_test rsc_check_constraint_test values ('19:14')

A workaround for ORDER BY clause in Subqueries

A workaround for ORDER BY clause in Subqueries

We cannot use ORDER BY clause in subqueries.
Consider the following subquery for example :


select * into tempaaa from (
select routeid , stopno , stopname , timetoreach , droptime
from rsc_schule_pickupmaster
order by routeid , stopno ) A
When we execute this query , we will get the following  SQL error :

Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

But sometimes we require the data to be orderd.
A quick workaround for this is to use the TOP X clause with X = count of records returned by subquery.
For example, suppose the above subquery returns 73 recs.
We can safely use the following query to populate table tempaaa :


select * into tempaaa from (
select TOP 73 routeid , stopno , stopname , timetoreach , droptime
from rsc_schule_pickupmaster
order by routeid , stopno ) A

To Truncate Logfiles (to shrink logfiles )

ALTER DATABASE [9JVPD]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([9JVPD_log], 1);
ALTER DATABASE [9JVPD]
SET RECOVERY FULL WITH NO_WAIT