|
This is a database forum and is not the right place to post this question. Please post it to vc++ forum.
|
|
|
|
|
Hi,
i want to Create a trigger [for intert/update] dynamically for a table which has been created dynamically.
Can anyone help me?
thanks in advance
sri
|
|
|
|
|
|
Hi, I am having a strange issue when using the RSClientPrint component to print reports from Reporting Services. I am printing tickets, and everything worked just fine until I tried to print a 2 pages report. The first page is printed ok, but the 2 page can't be printed.
By clicking the preview button of the component, I can see the first page of the report, but the second page shows an error.
Any suggestions? What am I missing? Is there a special attribute I have to define for my component? I am using the margins attributes, height and width, and cultures.
Thank you very much in advance.
|
|
|
|
|
Some code would be nice or at least pseudo just so we know what you have done. Anyhow, are you calculating when first page is ended so printing can continue to 2nd page? Just paste some code and then we can help.
|
|
|
|
|
Hi again and sorry for the delay. Thank you very much for your answer and here is the code I am using
RSClientPrint.MarginLeft = 0;
RSClientPrint.MarginTop = 0;
RSClientPrint.MarginRight = 0;
RSClientPrint.MarginBottom = 0;
RSClientPrint.Culture = 2058;
RSClientPrint.UICulture = 2058;
RSClientPrint.PageWidth = 85.2;
RSClientPrint.PageHeight = 279.4;
RSClientPrint.Print('http://ServerName\SQLinstance/ReportServer', '/FolderName/Impresion_Factura/Factura&CODIGO_SERIE='+serie+'&ID_SUCURSAL='+sucursal+'&CAJERO='+cajero+'&NIT='+nit+'&SUBTOTAL='+subtotal+'&DESCUENTOS='+descuentos+'&CODIGO_CAJERO='+codigo_cajero, 'Factura');
I really appreciate your help. You guys always get me out of trouble!!! ![Smile | :)](https://codeproject.global.ssl.fastly.net/script/Forums/Images/smiley_smile.gif)
|
|
|
|
|
Good Evening Everyone
i have following Query, that gives me an Error
<br />
<br />
<br />
SELECT IDENTITY(int, 1,1) AS REC_NO, LIS_KEY, <br />
FUNC_KEY, <br />
UNIT_NO, RIGHTS_ZONING, USE_CODE, OWNER, RATEABILITY, <br />
EXCLUSION, MARKET_VALUE, EFFECTIVE_DATE, CATEGORY_CODE, <br />
SUPPLE_NUM, AREA, PROPERTY_ID, OLD_MARKET_VALUE, VALUATION_ID, <br />
REASON, SORT_DATE<br />
INTO SDE.EXPORT_OITPS_GV_VUYISWA<br />
FROM<br />
(<br />
SELECT DISTINCT TOP 100 PERCENT P.LIS_KEY, <br />
CASE LEN(FUNC_KEY)<br />
WHEN 8 THEN SUBSTRING(NEW_ATTRIB_CODE,5,1)+ '0' + SUBSTRING(FUNC_KEY, 6, 3)<br />
ELSE SUBSTRING(FUNC_KEY,5, 5)<br />
End AS UNIT_NO,<br />
V.NEW_ATTRIB_CODE, P.ATTRIB_CODE,<br />
SUBSTRING(NEW_ATTRIB_CODE, 1, 2) AS RIGHTS_ZONING, <br />
SUBSTRING(NEW_ATTRIB_CODE, 3, 2) AS USE_CODE, <br />
ISNULL(OWN_NAME, '') AS OWNER, <br />
CASE LEN(FUNC_KEY)<br />
WHEN 8 THEN SUBSTRING(FUNC_KEY, 1, 5) + '0' + SUBSTRING(FUNC_KEY, 6, 3)<br />
ELSE FUNC_KEY<br />
END AS FUNC_KEY,<br />
CASE SUBSTRING(P.ATTRIB_CODE, 7, 1) <br />
WHEN '1' THEN 'R'<br />
WHEN '4' THEN 'E'<br />
ELSE 'N'<br />
END AS RATEABILITY, -- CASE 7TH WHEN 1 = R , 4 = E, ELSE = N<br />
CASE --P.PROP_CATEGORY_ID<br />
WHEN P.PROP_CATEGORY_ID = '2' THEN 'RES'<br />
WHEN P.PROP_CATEGORY_ID = '4' THEN 'PSI'<br />
WHEN SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '41' OR SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '42' THEN 'REL'<br />
ELSE ' ' <br />
END AS EXCLUSION,<br />
CONVERT(DECIMAL, ISNULL(NEW_IMPROVED_VALUE, -1)) AS MARKET_VALUE, <br />
(<br />
CASE WHEN CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE)) < 10<br />
THEN '0' + CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))<br />
ELSE CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))<br />
END +<br />
CASE WHEN CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE)) < 10<br />
THEN '0' + CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))<br />
ELSE CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))<br />
END + <br />
CONVERT(VARCHAR, DATEPART(YYYY, EFFECTIVE_DATE))) AS EFFECTIVE_DATE,<br />
(SELECT SUBSTRING(NEW_ATTRIB_CODE,7,2)<br />
FROM SDE.VALUATION V INNER JOIN SDE.PROPERTY PS<br />
ON V.PROPERTY_ID = PS.PROPERTY_ID<br />
) AS CATEGORY_CODE,<br />
' ' AS SUPPLE_NUM,<br />
CONVERT(INT, ISNULL(P.ACTUAL_EXTENT, 0)) AS AREA, P.PROPERTY_ID,<br />
0 AS OLD_MARKET_VALUE, VALUATION_ID, LU_V.VAL_REASON AS REASON, <br />
V.STATUS_DATE AS SORT_DATE<br />
<br />
FROM sde.PROPERTY P INNER JOIN SDE.VALUATION V--sde.VALUATION V<br />
ON P.PROPERTY_ID = V.PROPERTY_ID<br />
LEFT JOIN ( SELECT GISCODE, MIN(OWN_NAME) AS OWN_NAME<br />
FROM SDE.VW_PROPERTY_DEED<br />
GROUP BY GISCODE) D<br />
ON P.LIS_KEY = D.GISCODE<br />
LEFT JOIN SDE.LU_VAL_REASON LU_V<br />
ON V.VAL_REASON_ID = LU_V.VAL_REASON_ID<br />
WHERE --(<br />
--V.PROCESS_DATE IS NULL) AND <br />
P.ARCHIVE_DATE IS NULL AND<br />
V.ARCHIVE_DATE IS NULL AND<br />
V.VAL_REASON_ID = 1 AND <br />
LU_V.MULTIPLE = 0 AND<br />
V.EFFECTIVE_DATE = '2008/07/01'<br />
ORDER BY SORT_DATE<br />
) TMP
It gives me an Error
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
(0 row(s) affected)
How can i Fix this query
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Somewhere you have to use TOP 1 on your subquery to fix it.
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
Thanks i have Fixed it, the Top 1 was supposed to be in
<br />
(SELECT top 1 SUBSTRING(NEW_ATTRIB_CODE,7,2)<br />
FROM SDE.VALUATION V INNER JOIN SDE.PROPERTY PS<br />
ON V.PROPERTY_ID = PS.PROPERTY_ID<br />
) AS CATEGORY_CODE,
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Great
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
Vuyiswa wrote:
Msg 512, Level 16, State 1, Line 6Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Warning: Null value is eliminated by an aggregate or other SET operation.(0 row(s) affected)
Tells you the problem, your subquery is returning more than 1 row. Eithe rfix it by using SELECT TOP 1 ... or better yet write the query so the subquery can only return 1 value
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hiii..pls help me
1.How can i avoid insertion of null values into a foreign key attribute column....
-thr s a table EMPDETAILS with columns empid,name,age
-empid in EMPDETAILS foreign key references empid in
LOGIN table.
-LOGIN table has some data.
-when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
How can i solve this?????
2.How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
pls help
|
|
|
|
|
Arun Krishnan wrote: -when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
Even though it is a foreign key you still need to insert the data. Foreign keys are for referential integrity only.
Arun Krishnan wrote: 2.How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
Insert into the LOGIN table first, then the EMPDETAILS
Finally I suggest you try google to look for foreign keys as you clearly do not really understand them.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Arun Krishnan wrote: -thr s a table EMPDETAILS with columns empid,name,age
-empid in EMPDETAILS foreign key references empid in
LOGIN table.
-LOGIN table has some data.
-when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
How can i solve this?????
Do you pass the empid? if not, how do you expect the database to know which one do you mean?
Arun Krishnan wrote: How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
No problem, insert into EMPDETAILS table the same value of empid that you insert into LOGIN.
By the way, using s d instead of is the does not save you a lot of your time, but makes reading your posts harder.
|
|
|
|
|
|
First, I would make the foreighn key field a NOT NULL field to prevent accidental insert without a non-null value for EMPDETAILS.EMPID.
2nd, make EMPDETAILS.EMPID have a Foreign Key constraint - this would require that the value supplied be a value that exisits in LOGIN.EMPID column
Alter table EMPDETAILS (Alter column EMPID NOT NULL)
ALter table EMPDETAILS ADD CONSTRAINT FK_LOGIN FOREIGN_KEY (EMPID) references LOGIN (EMPID)
Create a stored procedure for the combined insert:
(use appropriate types and sizes for your tables,
this is just an example of how, hot a solution)
Create procedure AddNewEMPID (@Empid nvarchar (20), @name nvarchar(20), @age int, @loginstuff1 varchar(10),...,@loginstuffN int)
as
begin
insert into login (empid, lofinstuff1,...loginstuffN) values (@empid, @loginstuff1,...,@loginstuffN);
insert into EMPDETAILS(empid,name,age) values (@empid, @name,@age);
end
call the stored procedure with values for the parameters either in code (you didn't say what language, but most have a database library like ADO.net, ADO, ODBC etc. that support parameterized query execution)
of in sql query analizer as
exec AddNewEMPID 'empidstring','l1,...,'ln', 'name','age'
|
|
|
|
|
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. But when i'm using the following T-SQL as given below also doing rolling back with Truncate
Begin Tran
Truncate Table Rough1
Rollback
Plz make me clear.............
|
|
|
|
|
Truncate CAN be rolled back but ONLY when its part of an explicit transaction. This may help explain:
TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thnx u sir
but Delete can be rolled back plz let me knw how because i just knw how to implement as explicitly not implicitly
|
|
|
|
|
What's wrong with this?
Begin Tran
delete from Rough1
Rollback
Pits fall into Chuck Norris.
|
|
|
|
|
Nothing wrong with it.
Actually it is said tht Delete can be rolled back but truncate cannot be.
//This T-SQL will be rolled back for Delete command
Begin Tran
delete from Rough1
Rollback
//This T-SQL will also be rolled back for Truncate command
Begin Tran
Truncate Rough1
Rollback
So where is the difference between Delete and Truncate command in respect of Rollback
|
|
|
|
|
Within an explicit transaction either can be rolled back. You cannot roll back from the transaction logs for a truncate (outside an explict transaction) as the changes are not inserted into the transaction logs. A delete logs each record deleted to the transaction log so it can be recovered. That is the difference.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Sir,
Can u give me an example.........
Begin Transaction Logs
Truncate Table Rough1
Rollback
Is this T-SQL given abouve ok otherwise plz let me know so tht Truncate cannot be rolled back
|
|
|
|
|
Just do not put the transaction around the truncate
Truncate Table Rough1
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ok Thnx but still i m confused if i dont put the transaction around the truncate then it wont be Rolledback as i want
But if i also dont put the transaction around the delte then it wont be rolled back too.
So how can we say that a Delete can be rolledback but a truncate cannot be......
|
|
|
|
|