Conversion of ASP reports into RDL files

Hi,
I need to know if we have any automated process of converting reports
generated in asp and dispalyed as html format can be converted to SQL
Reporting Service rdl files.
This will reduce my effort of re designing all the asp reports into the SQL
reports.
Please need an urgent help on this.
Thanks
Raghavunfortunately no. You will have to redesign except for MS access report where
you can convert. This is because you must have used your own logic inside
your ASP files, it will be difficult to convert automatically.
Amarnath
"raghav78" wrote:
> Hi,
> I need to know if we have any automated process of converting reports
> generated in asp and dispalyed as html format can be converted to SQL
> Reporting Service rdl files.
> This will reduce my effort of re designing all the asp reports into the SQL
> reports.
> Please need an urgent help on this.
> Thanks
> Raghav

Conversion from SQL 7.0 to SQL 2000

We have recently converted a SQL 7.0 database to SQL
2000. There are a number of tables that have default
values set for inserting data. When accessing via the
front-end application (ASP page), the tables are not
inserting the default values instead it is inserting
<NULL> into the field. Any insight?Tom,
Thanks for the quick response. I did include the defaults
in the migration and the tables indicates the default
values (as well as the sp_help on the table). Our front
end application was running successfully when using the
SQL 7.0 database. We are not explicitly inserting the
NULL values into the table. If there are no values for
the specific fields it should just insert the default
values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>1. The defaults were not added in the migration. Use
sp_help on the table in question.
>2. The front-end is actually explicitly inserting
NULL's. Use the Profiler to find out.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in
message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL
>2000. There are a number of tables that have default
>values set for inserting data. When accessing via the
>front-end application (ASP page), the tables are not
>inserting the default values instead it is inserting
><NULL> into the field. Any insight?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This isn't an INSERT statement, since the word INSERT does not appear =anywhere in it. I'd be looking for something along the lines of:
INSERT MyTable (Col1, Col2) VALUES ('X', 3)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jp" <jp@.p.com> wrote in message =news:eADPuc6VDHA.1676@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert statement:
exec sp_cursor 180150001, 4, 0, N'tbl_Worksheets', @.L2 =3D '01', @.RepID ==3D '886048', @.SalesWk =3D 30, @.Name =3D 'FORTUNE, AMY = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D =36.00, @.WMU1 =3D 0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 ==3D 165.60, @.Sales_UC1 =3D 70.55, @.Cancels1 =3D 8, @.Cancels_Comp1 =3D =0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 =3D NULL, @.WMU2 =3D =NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, =@.Cancels_UC2 =3D NULL, @.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, =@.Comp3 =3D NULL, @.Sales3 =3D NULL, @.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D =NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, @.Cancels_UC3 =3D =NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, =@.Other_Doe =3D ' ', @.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O768Gn2VDHA.1676@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give you, i.e. what was the exact T-SQL =INSERT statement going into SQL Server. Also, have you tried something =like the following in QA?:
insert MyTable default values
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message =news:5a6201c35767$b817c780$a001280a@.phx.gbl...
Tom,
Thanks for the quick response. I did include the defaults in the migration and the tables indicates the default values (as well as the sp_help on the table). Our front end application was running successfully when using the SQL 7.0 database. We are not explicitly inserting the NULL values into the table. If there are no values for the specific fields it should just insert the default values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>
>1. The defaults were not added in the migration. Use sp_help on the table in question.
>2. The front-end is actually explicitly inserting NULL's. Use the Profiler to find out.
>
>-- >Tom
>
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL >2000. There are a number of tables that have default >values set for inserting data. When accessing via the >front-end application (ASP page), the tables are not >inserting the default values instead it is inserting ><NULL> into the field. Any insight?
> --=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This isn't an INSERT statement, since =the word INSERT does not appear anywhere in it. I'd be looking for =something along the lines of:
INSERT MyTable (Col1, Col2) =VALUES ('X', 3)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jp" wrote in =message news:eADPuc6VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert =statement:
exec sp_cursor 180150001, 4, 0, =N'tbl_Worksheets', @.L2 =3D '01', @.RepID =3D '886048', @.SalesWk =3D 30, @.Name =3D ='FORTUNE, AMY &nbs=p;  =; = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D 36.00, @.WMU1 =3D =0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 =3D 165.60, @.Sales_UC1 =3D 70.55, =@.Cancels1 =3D 8, @.Cancels_Comp1 =3D 0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 ==3D NULL, @.WMU2 =3D NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, @.Cancels_UC2 =3D NULL, =@.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, @.Comp3 =3D NULL, @.Sales3 =3D NULL, =@.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, =@.Cancels_UC3 =3D NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, @.Other_Doe =3D ' ', =@.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" = wrote in message news:O768Gn2VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give =you, i.e. what was the exact T-SQL INSERT statement going into SQL Server. =Also, have you tried something like the following in QA?:

insert MyTable default values
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Laura Reynolds" --Original Message-->Two =possibilities:>>1. The defaults were not added in the migration. Use sp_help on the =table in question.>2. The front-end is actually =explicitly inserting NULL's. Use the Profiler to find =out.>>-- =>Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON =Canada>www.pinnaclepublishing.com/sql>>>"Laur=a Reynolds" We have recently converted a SQL 7.0 database to SQL >2000. =There are a number of tables that have default >values set for =inserting data. When accessing via the >front-end application (ASP =page), the tables are not >inserting the default values instead it is inserting > into the field. Any insight?>

---

--=_NextPart_000_000D_01C35787.64B12BC0--

conversion *= to left outer joins not yielding desired results

Hi

I have a query which works fine on sql 2000 with *= but doesn't yield the same when converted to 2005 using joins

Here is the original query

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c, product_family pf,
ship_to_country sc, dealcountry_tatsla dcts, tatsla_countrydefaults tscd, #TAT_EXPORT_DEALVALUES tmpex
WHERE
c.id_country = sc.id_country
AND sc.id_deal = tmpex.id_deal

AND sc.id_country *= dcts.id_country

AND sc.id_deal *= dcts.id_deal
AND pf.id_product_family *= dcts.id_product_family
AND sc.id_country = tscd.id_country
AND pf.id_product_family = tscd.id_product_family

The Converted one is below

select tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
from
ship_to_country sc INNER JOIN country c
ON sc.id_country = c.id_country
INNER JOIN #TAT_EXPORT_DEALVALUES tmpex
ON (sc.id_deal = tmpex.id_deal )
INNER JOIN tatsla_countrydefaults tscd1
ON (sc.id_country = tscd1.id_country)
LEFT OUTER JOIN
dealcountry_tatsla dcts
ON (sc.id_country = dcts.id_country and sc.id_deal=dcts.id_deal )
LEFT OUTER JOIN product_family pf
ON (pf.id_product_family = dcts.id_product_family)
INNER JOIN tatsla_countrydefaults tscd
ON (pf.id_product_family = tscd.id_product_family)

Please let me know whether I am missing something

The join query results in cartesian product

If I remember old style syntax correctly, the new version will looks like this:

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c
inner join ship_to_country sc on c.id_country = sc.id_country
inner join #TAT_EXPORT_DEALVALUES tmpex on sc.id_deal = tmpex.id_deal
inner join tatsla_countrydefaults tscd on sc.id_country = tscd.id_country
inner join product_family pf on pf.id_product_family = tscd.id_product_family
left join dealcountry_tatsla dcts on sc.id_country = dcts.id_country
and sc.id_deal = dcts.id_deal and pf.id_product_family = dcts.id_product_family

Not sure it is equivalent, though, so thorough testing is highly recommended.|||Your Query seems to be correct. Have you checked the result set. Is there any flaw in that.|||No, I've not checked this - I have no underlying data to test on. If I had, I'd have no doubt

But since you have both the tables and the data, you always may test its correctness - either on MSSQL 2000 or, if all that you have is MSSQL 2005, on a separate database with compatibility level set to 80. In the latter case, both queries will work even on 2005.sqlsql

Contraints for Defaults?

I recently converted a Sql Server 2000 database to Slq Server 2005. I am a
relative rookie to 2005 and I was poking around SSMS.
I noticed a Contstraint for for each table column with a default value. Is
this the norm? I didn't notice it in 2000 but then again I am not sure I
looked.
Thanks,
RJI found the Answer. Yes a constraint is built for default values
"RJ" wrote:
> I recently converted a Sql Server 2000 database to Slq Server 2005. I am a
> relative rookie to 2005 and I was poking around SSMS.
> I noticed a Contstraint for for each table column with a default value. Is
> this the norm? I didn't notice it in 2000 but then again I am not sure I
> looked.
> Thanks,
> RJ|||Hi
It is usually good practice to explicitly name your constraints (including
defaults) rather than allow SQL server to give it a name, that way you know
what it is called on all instances of the database.
John
"RJ" <RJ@.discussions.microsoft.com> wrote in message
news:B6628A4E-FA2B-435F-B33F-AE988FE610DD@.microsoft.com...
>I found the Answer. Yes a constraint is built for default values
> "RJ" wrote:
>> I recently converted a Sql Server 2000 database to Slq Server 2005. I am
>> a
>> relative rookie to 2005 and I was poking around SSMS.
>> I noticed a Contstraint for for each table column with a default value.
>> Is
>> this the norm? I didn't notice it in 2000 but then again I am not sure I
>> looked.
>> Thanks,
>> RJ|||Hi John,
I came to exactly the same conclusion and standardized the naming of all my
objects. I inhertited an Access > Sql Server 2000 > Sql Server 2005 project
and object names were all over the place.
Thanks for your input,
RJ
"John Bell" wrote:
> Hi
> It is usually good practice to explicitly name your constraints (including
> defaults) rather than allow SQL server to give it a name, that way you know
> what it is called on all instances of the database.
> John
> "RJ" <RJ@.discussions.microsoft.com> wrote in message
> news:B6628A4E-FA2B-435F-B33F-AE988FE610DD@.microsoft.com...
> >I found the Answer. Yes a constraint is built for default values
> >
> > "RJ" wrote:
> >
> >> I recently converted a Sql Server 2000 database to Slq Server 2005. I am
> >> a
> >> relative rookie to 2005 and I was poking around SSMS.
> >>
> >> I noticed a Contstraint for for each table column with a default value.
> >> Is
> >> this the norm? I didn't notice it in 2000 but then again I am not sure I
> >> looked.
> >>
> >> Thanks,
> >> RJ
>
>

constraints and altering tables

I'm in the process of trying to convert a database such that all the strings
(VARCHAR) are converted to wide strings (NVARCHAR). I have a script that
accomplishes this by removing all the primary key constraints, converts the
necessary columns, and then replaces the constraints. The script walks the
sysnames table and stores all the constraints in a table variable, and
constructs a script to recreate all the constraings based on the 'xtype'
column from sysindexes (this is based on the system stored procedure
sp_pkeys). The script creates a constraint if the xtype is of type 'PK', or
creates an index based on the INDEXPROPERTY of the index, whether it be
unique, and either clustered or non-clustered.
This works for the most part, but I have found that there are constraints
being created on some columns that did not exist before the conversion. For
example, I have a table which has a primary key on it's identity columns
defined to automatically insert a new value at each insert incremented by 1.
After the conversion, there is an additional constraint placed on this table
which prevents a value of NULL from being added, which should be a problem
due to the IDENTITY column, yet attempting to do an insert on this table
generates an error saying that a NULL value cannot be inserted. I'm not
manually inserting anything, this should just bump the id value by one and
do the insert, but this new constraint prevents this, leaving me with a
table I can no longer insert into.
In another case, I have several varchar columns that have default
constraints (simple text strings), which are also dropped before conversion.
Upon replacing the constraints read from sysnames, I get similar errors
regarding not being able to insert nulls into these columns, which I didn't
get before, as these columns had default values.
My questions are, is it possible to exactly recreate constraints
programmatically? Is there a preffered method for converting databases from
narrow to wide character?
Thanks for any advice,
-Gary> This works for the most part, but I have found that there are constraints
> being created on some columns that did not exist before the conversion.
> For
> example, I have a table which has a primary key on it's identity columns
> defined to automatically insert a new value at each insert incremented by
> 1.
> After the conversion, there is an additional constraint placed on this
> table
> which prevents a value of NULL from being added, which should be a problem
> due to the IDENTITY column, yet attempting to do an insert on this table
> generates an error saying that a NULL value cannot be inserted. I'm not
> manually inserting anything, this should just bump the id value by one and
> do the insert, but this new constraint prevents this, leaving me with a
> table I can no longer insert into.
Is the IDENTITY property still set for the column? If so, then what does
your INSERT statement look like? If not, then your script is, well,
imperfect.|||Yes, It is still set. the insert statement does inserts using enumerated
column names.
INSERT INTO RptTable (
ShortName, FullName, Description,
StockReport, ReportFilename,
AutoprintReport, PrintDaily, PrintWly, PrintMonthly,
ExportReport, ExportDaily, ExportWly, ExportMonthly, ExportFormat,
DisplayOrder, MinDateVariable, MaxDateVariable, Visible,
ReportCategoryId, HelpFilename, CLCompliant,
Param1_Label, Param1_Description)
VALUES
(@.InstallReport_ShortName, @.InstallReport_FullName,
@.InstallReport_Description,
0, @.InstallReport_ReportFilename,
0, 0, 0, 0,
0, 0, 0, 0, @.InstallReport_ExportFormat,
50, @.InstallReport_MinDateVariable, @.InstallReport_MaxDateVariable,
@.InstallReport_Visible,
dbo.idw_get_report_category_id( @.InstallCategory_ShortName ),
@.InstallReport_HelpFilename, 0,
@.InstallReport_Var1Title, @.InstallReport_Var1Description
);
The identity is on a column called ReportId, which is defined as (at table
creation time)
ReportId int PRIMARY KEY NOT NULL IDENTITY(1,1)
Thanks.
-Gary
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O39thSqzFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Is the IDENTITY property still set for the column? If so, then what does
> your INSERT statement look like? If not, then your script is, well,
> imperfect.
>
>|||Gary,
Perhaps there is an INSTEAD OF trigger on the table (which requires that
you specify values for all non-null columns that have no DEFAULT constraint,
even if those values will not ultimately be inserted anywhere), or you have
IDENTITY_INSERT set to ON for this table.
If these guesses are wrong, please cut and paste the error message you get.
Steve Kass
Drew University
Gary wrote:

>Yes, It is still set. the insert statement does inserts using enumerated
>column names.
>INSERT INTO RptTable (
> ShortName, FullName, Description,
> StockReport, ReportFilename,
> AutoprintReport, PrintDaily, PrintWly, PrintMonthly,
> ExportReport, ExportDaily, ExportWly, ExportMonthly, ExportFormat,
> DisplayOrder, MinDateVariable, MaxDateVariable, Visible,
> ReportCategoryId, HelpFilename, CLCompliant,
> Param1_Label, Param1_Description)
>VALUES
> (@.InstallReport_ShortName, @.InstallReport_FullName,
>@.InstallReport_Description,
> 0, @.InstallReport_ReportFilename,
> 0, 0, 0, 0,
> 0, 0, 0, 0, @.InstallReport_ExportFormat,
> 50, @.InstallReport_MinDateVariable, @.InstallReport_MaxDateVariable,
>@.InstallReport_Visible,
> dbo.idw_get_report_category_id( @.InstallCategory_ShortName ),
>@.InstallReport_HelpFilename, 0,
> @.InstallReport_Var1Title, @.InstallReport_Var1Description
> );
>The identity is on a column called ReportId, which is defined as (at table
>creation time)
>ReportId int PRIMARY KEY NOT NULL IDENTITY(1,1)
>Thanks.
>-Gary
>"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:O39thSqzFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>
>