|
Hi! I know it's possible to have a data-driven subscription in Reporting Services, but I'm looking for a way to have a data-driven schedule. Every month I send out some reports, but can only have these sent out once other data has been updated. Is there any way that I can trigger the subscription schedule based on queried data? Thanks!
|
|
|
|
|
Anyone pls help me!! i am newbie. i got query below, i want the result as NF 20000.00 if it is refinanceAmount. i know below is not a proper query
IF L.LoanID EXISTS IN (SELECT LoanID, RefinanceAmount FROM FastLoan_Applications.dbo.tblLoanPurpose)
SET @LoanAmount = 'N.F.'@RefinanceAmount
Here is the Main SP
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[upUpfront_GetPending]
@UserID varchar(10)
AS
BEGIN
--ASSERTION: If a weekly and a monthly upfront run is in pending simultaneously, this SP will only return the weekly upfront.
--To get monthly upfront, weekly must be processed first.
--GET CURRENT UPFRONT COMMISSION RUN (WEEKLY OR MONTHLY, BUT ONLY ONE)
DECLARE @CommissionRunID int
SELECT @CommissionRunID = CommissionRunID
FROM tblCommissionRun
WHERE CommissionRunTypeID IN (1,2)
AND Pending = 1
--UPFRONT LOANS
SELECT U.UpfrontID,
AG.IntroducerID [AggregatorID],
AG.TradingName [Aggregator],
I.IntroducerID,
I.TradingName,
U.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
L.LoanAmount [Amount],
U.UpfrontRate,
U.UpfrontAmount,
U.ApplicationFee,
U.UpfrontGST,
U.UpfrontTotal
FROM tblUpfront U
INNER JOIN FastLoan_Applications.dbo.vwIntroducer I ON I.IntroducerID = U.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = U.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
LEFT JOIN FastLoan_Applications.dbo.vwIntroducer AG ON AG.IntroducerID = I.Aggregator
IF L.LoanID EXISTS IN (SELECT LoanID, RefinanceAmount FROM FastLoan_Applications.dbo.tblLoanPurpose)
SET @LoanAmount = 'N.F.'@RefinanceAmount
WHERE U.CommissionRunID = @CommissionRunID
AND LS12.LoanStageTypeID = 6
AND U.LoanIncreaseID IS NULL
UNION ALL
--UPFRONT LOAN INCREASES
SELECT U.UpfrontID,
AG.IntroducerID [AggregatorID],
AG.TradingName [Aggregator],
I.IntroducerID,
I.TradingName,
U.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
LI.IncreaseAmount [Amount],
U.UpfrontRate,
U.UpfrontAmount,
U.ApplicationFee,
U.UpfrontGST,
U.UpfrontTotal
FROM tblUpfront U
INNER JOIN FastLoan_Applications.dbo.vwIntroducer I ON I.IntroducerID = U.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanIncreaseID = U.LoanIncreaseID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = LI.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = L.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
LEFT JOIN FastLoan_Applications.dbo.vwIntroducer AG ON AG.IntroducerID = I.Aggregator
WHERE U.CommissionRunID = @CommissionRunID
AND LIS12.LoanStageTypeID = 6
AND U.LoanIncreaseID IS NOT NULL
ORDER BY I.TradingName
END
Thanks in Advance.
|
|
|
|
|
Your SP does not declare a @RefinanceAmount variable, you cannot use it. Should it be a variable? Or maybe it is a column?
|
|
|
|
|
its not column, its a variable, i need to select it(@refinanceAmount) if, the loanId is present in Refinance(table). i need select the @RefinanceAmount but need to prefix or suffix characters i.e 'NF.Amt' $10000.00
Thanks for your concern
|
|
|
|
|
hi frndz,
I am using MSSQL 2000
I have 2 tables
Tbl1
ID Name
1 AAA
2 BBB
3 CCC
4 DDD
Tbl2
ID parentid
1 0
2 0
I need to select the values in tbl1 and not in tbl2
ie
ID NAme
3 CCC
4 DDD
how it possible??
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
i got one solution using "NOT IN"
i would like to know any other method..
SELECT ID from tbl1 where ID not in<br />
(SELECT ID from tbl2)
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
<br />
select * <br />
from Tbl1<br />
where Tbl1.ID not in<br />
(select ID from Tbl2)<br />
or
<br />
select t1.* <br />
from Tbl1 t1<br />
left join Tbl2 t2<br />
on t1.ID = t2.ID<br />
where t2.ID is null<br />
-- modified at 10:54 Monday 22nd October, 2007
You always pass failure on the way to success.
|
|
|
|
|
Hi all,
Table schema
empid primary key
emp_name
emp_age
emp_skills
emp_Isactive
I am storing multiple skills of a customer in same column separated with commas.I have designed a search form where in the employer enters skills in search box separated using commas. I want to match these skills irrespective of commas.
for this purpose i used a stored proc and passed skills as parameters
select skill from tbljob where skill like '%'+ISNULL(@skill1,skill)+'%' or skill like '%'+ISNULL(@skill2,skill)+'%' or skill like '%'+ISNULL(@skill3,skill)+'%'
but if one of the skill 1,2,3 is null ie if user enters only 1 or 2 , it returns all rows because the other condition is always true .
How can i use "OR" with other statements so that it works out
|
|
|
|
|
change your ISNULL() NULL result to something that can't be located:
ISNULL(@skill1,'&^%$')
|
|
|
|
|
Hi im executing a sql command from my C# windows form.
Its a count command : SELECT [COLUMN NAME], COUNT(*) FROM [DATABASE] GROUP BY [COLUMN].
2 Columns load from the results, the column name, and the number of records (count).
How can i display these results in a C# windows form? Datagrid, large textbox?
Please help me on this.
|
|
|
|
|
You can display them pretty much whichever way you want. What are the requirements? Grid seems like a good idea to me.
|
|
|
|
|
Hi,
How to convert .csv file .mdb file?
I am working in PL/SQL (toad) and C#
Here is my sample code:
string strConn = "Data Source=;User ID=; Password=;";
OracleConnection conn = new OracleConnection(strConn);
conn.Open();
OracleCommand cmd = new OracleCommand();
string strSQL;
strSQL = "";
strSQL += "INSERT INTO [MY_TBL] (";
strSQL += "F_NAME, ";
strSQL += "L_NAME) VALUES ( ";
strSQL += "SELECT ";
strSQL += "F_NAME, ";
strSQL += "L_NAME ";
strSQL += "FROM ";
strSQL += @"[Text;DATABASE=C:\myPath;]";
strSQL += ".";
strSQL += "[poc.csv])";
cmd = new OracleCommand(strSQL,conn);
cmd.ExecuteNonQuery();
conn.Close();
I am getting the error: invalid table name.
If any of you worked on this, can you please share the code with me.
shyam
|
|
|
|
|
The general method would be to import the text file (csv) into Access (into a temporary table), then manipulate the data from the temporary table into the correct locations using queries/code etc.
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi everyone.
I'm making a custom tool for registering working hours for my employer against a Visual FoxPro database.
I've created a procedure for executing sql queries against the database, but my insert statments aren't commited.
The return value (affected rows) says that the query was successfull.
I'm using a normal "insert into (col1, col2, col(n)) VALUES(val1, val2, val(n))" statement - so nothing fancy.
I've tried using transactions and myTransaction.Commit(); without success.
I've also tried using keywords like "GO" or "COMMIT" in the query - but the FoxPro ODBC driver isn't too keen on those keywords. 
Am I missing something here?
I thought ODBCCommand.ExecuteNonQuery() used the auto-commit property.
Is there and specific properties that must be set, or keywords I must include to make it commit?
Sidenote:
If I copy/paste the query into an ODBC tool (freeware tool I just downloaded for testing purpouses) and execute the query,
the row is added successfully.
I'd really appreciate any feedback you've got.
-Larantz-
-- modified at 5:18 Monday 22nd October, 2007
I copied the database into a cvs folderstructure the other day.
Forgot to update the configuration file with the connectionstring the very next day...
*humble Larantz signing out*
-Larantz-
|
|
|
|
|
Hello.
I have a _CommandPtr that has the type CommandTypeEnum::adCmdTex, and the CommandText a query("Select test from Table_1") and this select returns one row and one column that has a binary data(image) in it.
<br />
CommandPtr pCommand;<br />
pCommand.CreateInstance(__uuidof(Command)); <br />
pCommand->ActiveConnection = this->pConnection;<br />
pCommand->CommandType = CommandTypeEnum::adCmdText;<br />
pCommand->CommandText = _bstr_t("select test from Table_1"); <br />
<br />
pRecordset = pCommand->Execute(NULL,NULL,CommandTypeEnum::adCmdText); <br />
BYTE* buffer = NULL;<br />
if(pRecordset != NULL)<br />
{<br />
while (!pRecordset->GetEndOfFile())<br />
{<br />
VARIANT var = pRecordset->Fields->GetItem("test")->Value;<br />
void * pData = NULL;<br />
SafeArrayAccessData(var.parray, &pData);<br />
long size = GetArraySize(var, NULL);<br />
buffer = new BYTE[size];<br />
memcpy(buffer, pData, size);<br />
SafeArrayUnaccessData(var.parray); <br />
pRecordset->MoveNext();<br />
}<br />
pRecordset->Close();<br />
}<br />
where
<br />
long GetArraySize(const VARIANT& var, long * nElems)<br />
{<br />
if ( !(var.vt & VT_ARRAY) ) return -1; <br />
<br />
long size = 0;<br />
long dims = SafeArrayGetDim(var.parray);<br />
long elemSize = SafeArrayGetElemsize(var.parray);<br />
long elems = 1;<br />
for ( long i=1; i <= dims; i++ )<br />
{<br />
long lbound, ubound;<br />
SafeArrayGetLBound(var.parray, i, &lbound);<br />
SafeArrayGetUBound(var.parray, i, &ubound);<br />
elems *= (ubound - lbound + 1);<br />
}<br />
<br />
if ( nElems ) *nElems = elems;<br />
<br />
size = elems*elemSize;<br />
return size;<br />
}<br />
When I debug the application, I get the record in the VARIANT var = pRecordset->Fields->GetItem("test")->Value; but when I put par.parray in Memory explorer in VS, i only see this data "fe ee fe ee fe ee fe ee fe ee fe ee fe ee ...fe ee" and of course it brakes at
SafeArrayGetLBound(var.parray, i, &lbound);
Can someone tell me where I am doing a very bad thing?
P.S. I was able to read the binary data correctly from the server using C#.NET 2.0.
Eusebiu
|
|
|
|
|
I found the solution... it's the GetChunck method....
<br />
if(pRecordset != NULL)<br />
{<br />
while (!pRecordset->GetEndOfFile())<br />
{<br />
ADO_LONGPTR size = pRecordset->Fields->Item["test"]->ActualSize;<br />
<br />
BYTE* buffer = new BYTE[size];<br />
_variant_t varChunk;<br />
varChunk = pRecordset->Fields->Item["test"]->GetChunk(size);<br />
<br />
BYTE * pData = NULL;<br />
SafeArrayAccessData(varChunk.parray, (void HUGEP* FAR*)&pData);<br />
memcpy(buffer, pData, size);<br />
SafeArrayUnaccessData(varChunk.parray);<br />
<br />
ofstream myFile ("C:\\Blue hills.jpg", ios::out | ios::binary);<br />
myFile.write((char*)buffer, size);<br />
myFile.flush();<br />
<br />
pRecordset->MoveNext();<br />
}<br />
pRecordset->Close();<br />
}<br />
<br />
Eusebiu
|
|
|
|
|
Send all the sql queries for programming
|
|
|
|
|
SreejithAchutan wrote: Send all the sql queries for programming
SELECT ...
DELETE ...
UPDATE ...
INSERT ...
EXEC ...
Fill in the blanks to get desired results.
|
|
|
|
|
sometimes people just don't get it even when you are very clear about what you want. Could you please tell what Sql queries do someone has to send?
Regards
- J O N -
|
|
|
|
|
Check your Answer 
SreejithAchutan wrote: Subject:Re: SqlQueries
SqlQueries[^]
SreejithAchutan wrote: sql queries for programming
sql queries for programming [^]
 B Specific and Clear About Your Question 
|
|
|
|
|
Use Google
enjoy with Googling.... you will get everything
Pavan Pareta
|
|
|
|
|
if i run my view in the Designer at hte Enterprise manager i get that error message.. but if i run it through the SQL analyzer it's fine it wont have any errors.. 
|
|
|
|
|
You are Using any field which is keyword
so use all field name with [] ex//
Select name,group from employee
Select [name],[group] from [employee]
Best Regards,
Chetan Patel
|
|
|
|
|
ei tnx so much..
at least now i got a hint cause i dont know where to start fixing that error..
|
|
|
|
|
In a transaction block if any error for insert or update occures (e.g. caused by foreign key-primary key constraint violation) does the transaction automatically rolls back or do we need to manually roll it back ?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
