I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>
In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
sql
Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts
Friday, March 30, 2012
How to trim XML's attribute values?
I have a program which generate a xml file from dataset using
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
DataSet.WriteXML function. The result is as follow:
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
12:00:00 AM" Name=" "
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
" ADD1=" " TRSS="3" TYPEDESC="
" >
<DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
" />
<DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
" />
<DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
" />
<DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
" />
<DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
" />
</TD>
</TB>
</DataSet>
Is there any setting to set it to generate xml file with trim space values
and result should output as :
<DataSet xmlns="http://tempuri.org/DataSet1.xsd">
<TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
E_MAIL="test@.test.com">
<TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
TYPEDESC="" >
<DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
<DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
<DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
<DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
<DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
</TD>
</TB>
</DataSet>In your SQL SELECT or stored procedure just use LTRIM(column_name) or
RTRIM(column_name) to remove unwanted spaces then the resulting dataset will
not have them for the WriteXML format.
So to correct your data below it would be something like:
SELECT
RTRIM(TB_NO),
RTRIM(DESCRIPTION)
...
"ABC" <abc@.abc.com> wrote in message
news:elMHZEiOHHA.4484@.TK2MSFTNGP02.phx.gbl...
>I have a program which generate a xml file from dataset using
> DataSet.WriteXML function. The result is as follow:
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN " DATE="1/16/2007
> 12:00:00 AM" Name=" "
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1 " LINE_NAME="FREE TRADE ZONE
> " ADD1=" " TRSS="3" TYPEDESC="
> " >
> <DESCRIPTION SEQ_NO="001 " MARKS="RESOURCES " DSCP="
> " />
> <DESCRIPTION SEQ_NO="002 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="003 " MARKS="P.O.:11111111 " DSCP="
> " />
> <DESCRIPTION SEQ_NO="004 " MARKS="MADE IN CHINA " DSCP="
> " />
> <DESCRIPTION SEQ_NO="005 " MARKS=" " DSCP="
> " />
> <DESCRIPTION SEQ_NO="006 " MARKS=" " DSCP="
> " />
> </TD>
> </TB>
> </DataSet>
>
> Is there any setting to set it to generate xml file with trim space values
> and result should output as :
> <DataSet xmlns="http://tempuri.org/DataSet1.xsd">
> <TB TB_NO="NN01001" DESCRIPTION="NN" DATE="1/16/2007 12:00:00 AM" Name=""
> E_MAIL="test@.test.com">
> <TD TD_NO="NN1-1" LINE_NAME="FREE TRADE ZONE" ADD1="" TRSS="3"
> TYPEDESC="" >
> <DESCRIPTION SEQ_NO="001" MARKS="RESOURCES" DSCP="" />
> <DESCRIPTION SEQ_NO="002" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="003" MARKS="P.O.:11111111" DSCP="" />
> <DESCRIPTION SEQ_NO="004" MARKS="MADE IN CHINA" DSCP="" />
> <DESCRIPTION SEQ_NO="005" MARKS="" DSCP="" />
> <DESCRIPTION SEQ_NO="006" MARKS="" DSCP="" />
> </TD>
> </TB>
> </DataSet>
>
>
Wednesday, March 28, 2012
How to transfer data to another site and update data later on
I have a project that requires to send dataset that meet certain criteria
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value has
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.
Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.
|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to move
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly undo
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> before
> somebody
> has
> TX's
> limited
>
>
|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
move[vbcol=seagreen]
TX[vbcol=seagreen]
undo[vbcol=seagreen]
criteria[vbcol=seagreen]
SQL[vbcol=seagreen]
value[vbcol=seagreen]
at[vbcol=seagreen]
the[vbcol=seagreen]
|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> move
> TX
> undo
> criteria
> SQL
> value
> at
> the
>
>
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value has
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.
Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.
|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to move
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly undo
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> before
> somebody
> has
> TX's
> limited
>
>
|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
move[vbcol=seagreen]
TX[vbcol=seagreen]
undo[vbcol=seagreen]
criteria[vbcol=seagreen]
SQL[vbcol=seagreen]
value[vbcol=seagreen]
at[vbcol=seagreen]
the[vbcol=seagreen]
|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> move
> TX
> undo
> criteria
> SQL
> value
> at
> the
>
>
How to transfer data to another site and update data later on
I have a project that requires to send dataset that meet certain criteria
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value has
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to move
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly undo
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > I have a project that requires to send dataset that meet certain criteria
> > stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> > Server 2000 at TX and then update parent's dataset that has been sent
> before
> > monthly for whatever changes that have been made over the time. Can
> somebody
> > teach me a way how to do it? As I don't know which record what data value
> has
> > been changed at our office over the time so as to update the dataset at
> TX's
> > SQL Server. It definitely will have changes. Also our SQL Server has
> limited
> > resource and the transmitted dataset will be about 400-500K records at the
> > first population.
> >
> > Thank you in advance for your help.
>
>|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
> > Take a look at DTS if you want to try to automate the process.
> >
> > If you would like to do it by hand, I would suggest that you use bcp to
move
> > the data out to a flat file, WinZip the file and ship it. On the TX
> > system, bcp that new data into a staging table and then perform your
> > updates.
> >
> > Note: Before performing your updates, you should probably back up the
TX
> > database. That way if you really do something strange, you can quickly
undo
> > it.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> > "Emily" <Emily@.discussions.microsoft.com> wrote in message
> > news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > > I have a project that requires to send dataset that meet certain
criteria
> > > stored in SQL Server 7.0 in my office at L.A. to the parent company's
SQL
> > > Server 2000 at TX and then update parent's dataset that has been sent
> > before
> > > monthly for whatever changes that have been made over the time. Can
> > somebody
> > > teach me a way how to do it? As I don't know which record what data
value
> > has
> > > been changed at our office over the time so as to update the dataset
at
> > TX's
> > > SQL Server. It definitely will have changes. Also our SQL Server has
> > limited
> > > resource and the transmitted dataset will be about 400-500K records at
the
> > > first population.
> > >
> > > Thank you in advance for your help.
> >
> >
> >|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> > Thanks for your help. But how can I tell which record has changes and
> > requires to do an update to the TX's database?
> >
> > B.Regards,
> > Emily
> >
> > "Rick Sawtell" wrote:
> >
> > > Take a look at DTS if you want to try to automate the process.
> > >
> > > If you would like to do it by hand, I would suggest that you use bcp to
> move
> > > the data out to a flat file, WinZip the file and ship it. On the TX
> > > system, bcp that new data into a staging table and then perform your
> > > updates.
> > >
> > > Note: Before performing your updates, you should probably back up the
> TX
> > > database. That way if you really do something strange, you can quickly
> undo
> > > it.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > > "Emily" <Emily@.discussions.microsoft.com> wrote in message
> > > news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > > > I have a project that requires to send dataset that meet certain
> criteria
> > > > stored in SQL Server 7.0 in my office at L.A. to the parent company's
> SQL
> > > > Server 2000 at TX and then update parent's dataset that has been sent
> > > before
> > > > monthly for whatever changes that have been made over the time. Can
> > > somebody
> > > > teach me a way how to do it? As I don't know which record what data
> value
> > > has
> > > > been changed at our office over the time so as to update the dataset
> at
> > > TX's
> > > > SQL Server. It definitely will have changes. Also our SQL Server has
> > > limited
> > > > resource and the transmitted dataset will be about 400-500K records at
> the
> > > > first population.
> > > >
> > > > Thank you in advance for your help.
> > >
> > >
> > >
>
>
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value has
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to move
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly undo
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > I have a project that requires to send dataset that meet certain criteria
> > stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> > Server 2000 at TX and then update parent's dataset that has been sent
> before
> > monthly for whatever changes that have been made over the time. Can
> somebody
> > teach me a way how to do it? As I don't know which record what data value
> has
> > been changed at our office over the time so as to update the dataset at
> TX's
> > SQL Server. It definitely will have changes. Also our SQL Server has
> limited
> > resource and the transmitted dataset will be about 400-500K records at the
> > first population.
> >
> > Thank you in advance for your help.
>
>|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
> > Take a look at DTS if you want to try to automate the process.
> >
> > If you would like to do it by hand, I would suggest that you use bcp to
move
> > the data out to a flat file, WinZip the file and ship it. On the TX
> > system, bcp that new data into a staging table and then perform your
> > updates.
> >
> > Note: Before performing your updates, you should probably back up the
TX
> > database. That way if you really do something strange, you can quickly
undo
> > it.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> > "Emily" <Emily@.discussions.microsoft.com> wrote in message
> > news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > > I have a project that requires to send dataset that meet certain
criteria
> > > stored in SQL Server 7.0 in my office at L.A. to the parent company's
SQL
> > > Server 2000 at TX and then update parent's dataset that has been sent
> > before
> > > monthly for whatever changes that have been made over the time. Can
> > somebody
> > > teach me a way how to do it? As I don't know which record what data
value
> > has
> > > been changed at our office over the time so as to update the dataset
at
> > TX's
> > > SQL Server. It definitely will have changes. Also our SQL Server has
> > limited
> > > resource and the transmitted dataset will be about 400-500K records at
the
> > > first population.
> > >
> > > Thank you in advance for your help.
> >
> >
> >|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> > Thanks for your help. But how can I tell which record has changes and
> > requires to do an update to the TX's database?
> >
> > B.Regards,
> > Emily
> >
> > "Rick Sawtell" wrote:
> >
> > > Take a look at DTS if you want to try to automate the process.
> > >
> > > If you would like to do it by hand, I would suggest that you use bcp to
> move
> > > the data out to a flat file, WinZip the file and ship it. On the TX
> > > system, bcp that new data into a staging table and then perform your
> > > updates.
> > >
> > > Note: Before performing your updates, you should probably back up the
> TX
> > > database. That way if you really do something strange, you can quickly
> undo
> > > it.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > > "Emily" <Emily@.discussions.microsoft.com> wrote in message
> > > news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> > > > I have a project that requires to send dataset that meet certain
> criteria
> > > > stored in SQL Server 7.0 in my office at L.A. to the parent company's
> SQL
> > > > Server 2000 at TX and then update parent's dataset that has been sent
> > > before
> > > > monthly for whatever changes that have been made over the time. Can
> > > somebody
> > > > teach me a way how to do it? As I don't know which record what data
> value
> > > has
> > > > been changed at our office over the time so as to update the dataset
> at
> > > TX's
> > > > SQL Server. It definitely will have changes. Also our SQL Server has
> > > limited
> > > > resource and the transmitted dataset will be about 400-500K records at
> the
> > > > first population.
> > > >
> > > > Thank you in advance for your help.
> > >
> > >
> > >
>
>
How to transfer data to another site and update data later on
I have a project that requires to send dataset that meet certain criteria
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value ha
s
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to mo
ve
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly un
do
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> before
> somebody
> has
> TX's
> limited
>
>|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
>
move[vbcol=seagreen]
TX[vbcol=seagreen]
undo[vbcol=seagreen]
criteria[vbcol=seagreen]
SQL[vbcol=seagreen]
value[vbcol=seagreen]
at[vbcol=seagreen]
the[vbcol=seagreen]|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> move
> TX
> undo
> criteria
> SQL
> value
> at
> the
>
>
stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
Server 2000 at TX and then update parent's dataset that has been sent before
monthly for whatever changes that have been made over the time. Can somebody
teach me a way how to do it? As I don't know which record what data value ha
s
been changed at our office over the time so as to update the dataset at TX's
SQL Server. It definitely will have changes. Also our SQL Server has limited
resource and the transmitted dataset will be about 400-500K records at the
first population.
Thank you in advance for your help.Take a look at DTS if you want to try to automate the process.
If you would like to do it by hand, I would suggest that you use bcp to move
the data out to a flat file, WinZip the file and ship it. On the TX
system, bcp that new data into a staging table and then perform your
updates.
Note: Before performing your updates, you should probably back up the TX
database. That way if you really do something strange, you can quickly undo
it.
Rick Sawtell
MCT, MCSD, MCDBA
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> I have a project that requires to send dataset that meet certain criteria
> stored in SQL Server 7.0 in my office at L.A. to the parent company's SQL
> Server 2000 at TX and then update parent's dataset that has been sent
before
> monthly for whatever changes that have been made over the time. Can
somebody
> teach me a way how to do it? As I don't know which record what data value
has
> been changed at our office over the time so as to update the dataset at
TX's
> SQL Server. It definitely will have changes. Also our SQL Server has
limited
> resource and the transmitted dataset will be about 400-500K records at the
> first population.
> Thank you in advance for your help.|||Thanks for your help. But how can I tell which record has changes and
requires to do an update to the TX's database?
B.Regards,
Emily
"Rick Sawtell" wrote:
> Take a look at DTS if you want to try to automate the process.
> If you would like to do it by hand, I would suggest that you use bcp to mo
ve
> the data out to a flat file, WinZip the file and ship it. On the TX
> system, bcp that new data into a staging table and then perform your
> updates.
> Note: Before performing your updates, you should probably back up the TX
> database. That way if you really do something strange, you can quickly un
do
> it.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:6CDB8949-4450-4F4E-9A07-F67950847298@.microsoft.com...
> before
> somebody
> has
> TX's
> limited
>
>|||Without looking at your table structures and how the data is handled, I
couldn't tell you.
How would you do it normally?
You could use a RowVersion datatype in the tables at both sites and then
compare them. For RowVersions that are different, you could perform your
updates on those rows.
HTH
Rick Sawtell
"Emily" <Emily@.discussions.microsoft.com> wrote in message
news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. But how can I tell which record has changes and
> requires to do an update to the TX's database?
> B.Regards,
> Emily
> "Rick Sawtell" wrote:
>
move[vbcol=seagreen]
TX[vbcol=seagreen]
undo[vbcol=seagreen]
criteria[vbcol=seagreen]
SQL[vbcol=seagreen]
value[vbcol=seagreen]
at[vbcol=seagreen]
the[vbcol=seagreen]|||The recordset is pulled from different tables by joining the foreign keys
with the main table. None of them has a column with timestamp datatype( I
guess this is the datatype that you refer to as there is no RowVersion
datatype in SQL Server 7.0) .
Correct me if I'm wrong.
All tables that are used to get the recordset have a primary key in integer
datatype like an autonumber but not include in the recordset.
Does it mean we should add an additional column to trigger if there is any
change?
Thank you very much for your help.
Emily
"Rick Sawtell" wrote:
> Without looking at your table structures and how the data is handled, I
> couldn't tell you.
> How would you do it normally?
> You could use a RowVersion datatype in the tables at both sites and then
> compare them. For RowVersions that are different, you could perform your
> updates on those rows.
> HTH
> Rick Sawtell
>
> "Emily" <Emily@.discussions.microsoft.com> wrote in message
> news:B40AE998-4ABF-4DC8-8954-4F0E938018C3@.microsoft.com...
> move
> TX
> undo
> criteria
> SQL
> value
> at
> the
>
>
Monday, March 26, 2012
How to tie ? to parameter name?
In the Visual Studio (2005) designer, I created a report, data source,
and dataset. The query is essentially Select * from Table1 where
ClientID = ?
Under Report Parameters I changed the name Parameter1 to CIDParam, so
that it is somewhat meaningful when I call it from code. But when I
try to preview the report, Studio complains that the query can't find
Parameter1. So how do I let Studio know that ? should look at
CIDParam?I go against Sybase and have to do this all the time (unfortunately). Go
back to the dataset tab, click on ..., parameters tab. Remap the query
paramters. Keep in mind that although RS creates the report parameters
automatically for you they are not the same thing. This is where they are
mapped.
Another time you will want to do this is if you want to use the same report
parameter multiple times. For instance you have a from and end date that is
used multiple times in your query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:l2jni255jalsgohek00ih5ac2h18vbh33a@.4ax.com...
> In the Visual Studio (2005) designer, I created a report, data source,
> and dataset. The query is essentially Select * from Table1 where
> ClientID = ?
> Under Report Parameters I changed the name Parameter1 to CIDParam, so
> that it is somewhat meaningful when I call it from code. But when I
> try to preview the report, Studio complains that the query can't find
> Parameter1. So how do I let Studio know that ? should look at
> CIDParam?|||On Tue, 10 Oct 2006 12:24:48 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>I go against Sybase and have to do this all the time (unfortunately). Go
>back to the dataset tab, click on ..., parameters tab. Remap the query
>paramters. Keep in mind that although RS creates the report parameters
>automatically for you they are not the same thing. This is where they are
>mapped.
>Another time you will want to do this is if you want to use the same report
>parameter multiple times. For instance you have a from and end date that is
>used multiple times in your query.
Yes, that seems to have done it. Thanks.
and dataset. The query is essentially Select * from Table1 where
ClientID = ?
Under Report Parameters I changed the name Parameter1 to CIDParam, so
that it is somewhat meaningful when I call it from code. But when I
try to preview the report, Studio complains that the query can't find
Parameter1. So how do I let Studio know that ? should look at
CIDParam?I go against Sybase and have to do this all the time (unfortunately). Go
back to the dataset tab, click on ..., parameters tab. Remap the query
paramters. Keep in mind that although RS creates the report parameters
automatically for you they are not the same thing. This is where they are
mapped.
Another time you will want to do this is if you want to use the same report
parameter multiple times. For instance you have a from and end date that is
used multiple times in your query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:l2jni255jalsgohek00ih5ac2h18vbh33a@.4ax.com...
> In the Visual Studio (2005) designer, I created a report, data source,
> and dataset. The query is essentially Select * from Table1 where
> ClientID = ?
> Under Report Parameters I changed the name Parameter1 to CIDParam, so
> that it is somewhat meaningful when I call it from code. But when I
> try to preview the report, Studio complains that the query can't find
> Parameter1. So how do I let Studio know that ? should look at
> CIDParam?|||On Tue, 10 Oct 2006 12:24:48 -0500, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>I go against Sybase and have to do this all the time (unfortunately). Go
>back to the dataset tab, click on ..., parameters tab. Remap the query
>paramters. Keep in mind that although RS creates the report parameters
>automatically for you they are not the same thing. This is where they are
>mapped.
>Another time you will want to do this is if you want to use the same report
>parameter multiple times. For instance you have a from and end date that is
>used multiple times in your query.
Yes, that seems to have done it. Thanks.
Friday, March 9, 2012
How to supply NT Credential on RS Report Parameter
I have a Filter Dropdown parameter which calls Storeprocedure DataSet
accepts NT Domain and Username as parameter. How will i supply this
parameter to make it work. Right now i hardcode values and it works fine.
In Dot.net Program we can set Identity Impersonate to true and then use
User.Identity Security Principals . How do i use on Reporting Services.
Any idea would be great!I figured it out.. User.UserID ( User running the report)
"RAGHAVAN JAYARAMAN" wrote:
> I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> accepts NT Domain and Username as parameter. How will i supply this
> parameter to make it work. Right now i hardcode values and it works fine.
> In Dot.net Program we can set Identity Impersonate to true and then use
> User.Identity Security Principals . How do i use on Reporting Services.
> Any idea would be great!
>|||I am having the same problem.
Where did you place this code? In the Parameter field itself? Inside of
the report code tab?
Thanks,
msflinx
"RAGHAVAN JAYARAMAN" wrote:
> I figured it out.. User.UserID ( User running the report)
> "RAGHAVAN JAYARAMAN" wrote:
> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> > accepts NT Domain and Username as parameter. How will i supply this
> > parameter to make it work. Right now i hardcode values and it works fine.
> >
> > In Dot.net Program we can set Identity Impersonate to true and then use
> > User.Identity Security Principals . How do i use on Reporting Services.
> >
> > Any idea would be great!
> >|||Query parameters do not have to map to a report parameter. RS automatically
creates matching report parameters but you can instead map the query
parameter to an expression. In the dataset tab click on the ..., parameters
tab and map to an expression. When the expression builder comes up, expand
the global variable and set it to the User!UserID global variable. Note that
this has domain\username so if you need just one or the other you need to
strip off the part you don't want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:133E55D5-265D-4AAB-A703-B59EC82C3ABB@.microsoft.com...
>I am having the same problem.
> Where did you place this code? In the Parameter field itself? Inside of
> the report code tab?
> Thanks,
> msflinx
> "RAGHAVAN JAYARAMAN" wrote:
>> I figured it out.. User.UserID ( User running the report)
>> "RAGHAVAN JAYARAMAN" wrote:
>> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
>> > accepts NT Domain and Username as parameter. How will i supply this
>> > parameter to make it work. Right now i hardcode values and it works
>> > fine.
>> >
>> > In Dot.net Program we can set Identity Impersonate to true and then use
>> > User.Identity Security Principals . How do i use on Reporting Services.
>> >
>> > Any idea would be great!
>> >
accepts NT Domain and Username as parameter. How will i supply this
parameter to make it work. Right now i hardcode values and it works fine.
In Dot.net Program we can set Identity Impersonate to true and then use
User.Identity Security Principals . How do i use on Reporting Services.
Any idea would be great!I figured it out.. User.UserID ( User running the report)
"RAGHAVAN JAYARAMAN" wrote:
> I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> accepts NT Domain and Username as parameter. How will i supply this
> parameter to make it work. Right now i hardcode values and it works fine.
> In Dot.net Program we can set Identity Impersonate to true and then use
> User.Identity Security Principals . How do i use on Reporting Services.
> Any idea would be great!
>|||I am having the same problem.
Where did you place this code? In the Parameter field itself? Inside of
the report code tab?
Thanks,
msflinx
"RAGHAVAN JAYARAMAN" wrote:
> I figured it out.. User.UserID ( User running the report)
> "RAGHAVAN JAYARAMAN" wrote:
> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> > accepts NT Domain and Username as parameter. How will i supply this
> > parameter to make it work. Right now i hardcode values and it works fine.
> >
> > In Dot.net Program we can set Identity Impersonate to true and then use
> > User.Identity Security Principals . How do i use on Reporting Services.
> >
> > Any idea would be great!
> >|||Query parameters do not have to map to a report parameter. RS automatically
creates matching report parameters but you can instead map the query
parameter to an expression. In the dataset tab click on the ..., parameters
tab and map to an expression. When the expression builder comes up, expand
the global variable and set it to the User!UserID global variable. Note that
this has domain\username so if you need just one or the other you need to
strip off the part you don't want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:133E55D5-265D-4AAB-A703-B59EC82C3ABB@.microsoft.com...
>I am having the same problem.
> Where did you place this code? In the Parameter field itself? Inside of
> the report code tab?
> Thanks,
> msflinx
> "RAGHAVAN JAYARAMAN" wrote:
>> I figured it out.. User.UserID ( User running the report)
>> "RAGHAVAN JAYARAMAN" wrote:
>> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
>> > accepts NT Domain and Username as parameter. How will i supply this
>> > parameter to make it work. Right now i hardcode values and it works
>> > fine.
>> >
>> > In Dot.net Program we can set Identity Impersonate to true and then use
>> > User.Identity Security Principals . How do i use on Reporting Services.
>> >
>> > Any idea would be great!
>> >
How to sub total a calculated column?
In my report body, there is table, which contains a column displaying calculated data based on a field from a dataset. The expression for the cell called "calculatedCost" below this column is :
"=Iif(Fields!Facotr.Value=0, Fields!cost.Value * Fields!Quantity.Value *1,Fields!cost.Value * Fields!Quantity.Value *Fields!Factor.Value)
The data row in the table will be grouped by fields called Code like "A", "B" and etc. Now I need to sub total the above calculated column under each group, i.e. Subtotals for Code A and Code B. How can I do this? I tried the following expression:
=Sum(ReportItems("calculatedCost").Value)
But I got an error saying that Aggregate functions couldn't be used in report body. Then how can I refer the value of cell "calculatedCost"? Any suggestion will be highly appreciated..
You could perform the calculation in your dataset and not display that field.
SELECT *, (<Expression HERE>) As CalcField FROM Query
Then use that field in the summary field.
=SUM(Fields!CalcField.Value)
|||Thanks a lot. I got it work!Wednesday, March 7, 2012
How to stored a stored procedures results into a table
Hi, How can I store a stored procedure's results(returning dataset) into
a table?
a table?
Bob
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Bob James" <BJ@.system.com> wrote in message news:41ed4eb1$1_2@.127.0.0.1...
> Hi, How can I store a stored procedure's results(returning dataset) into
> a table?
> Bob
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
insert into dbo.MyTable
exec dbo.MyProc @.parm1 = 'foo'
Where dbo.MyTable already exists and matches the structure (columns and data
types) of the procedure's result set. But there are a number of issues with
this approach:
http://www.sommarskog.se/share_data.html#INSERTEXEC
If you read the whole article you might find one of the other approaches
suits your needs better anyway, so you can avoid those issues.
Simon
Subscribe to:
Posts (Atom)