Hi,
Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.
Thanks for any help.
VinnieYou can do this
select cast(size * 8 as int) as Size from dbo.sysfiles
The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)
HTH|||Originally posted by rhigdon
You can do this
select cast(size * 8 as int) as Size from dbo.sysfiles
The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)
HTH
Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.
Thanks in advance.|||For log file you can do:
dbcc sqlperf('logspace')
I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.
HTH|||Originally posted by rhigdon
For log file you can do:
dbcc sqlperf('logspace')
I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.
HTH
Hi,
That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.
Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.
Run SP_HELPFILE to get the information for physical files associated to that database.
Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Data fields are not allowed in the Page Header section
How can I build the page header (Sales for John Smith) where John Smith is
returned in the query results? Data fields are not allowed in the Page
Header section.
All responses greatly appreciated.
--
Any and all contributions are greatly appreciated ...
Regards TJYou can use read-only parameter with default value from query (assuming that
you render separate instance of report for every person)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:O2Mt1aNoEHA.3564@.tk2msftngp13.phx.gbl...
> How can I build the page header (Sales for John Smith) where John Smith is
> returned in the query results? Data fields are not allowed in the Page
> Header section.
> All responses greatly appreciated.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>
>
returned in the query results? Data fields are not allowed in the Page
Header section.
All responses greatly appreciated.
--
Any and all contributions are greatly appreciated ...
Regards TJYou can use read-only parameter with default value from query (assuming that
you render separate instance of report for every person)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:O2Mt1aNoEHA.3564@.tk2msftngp13.phx.gbl...
> How can I build the page header (Sales for John Smith) where John Smith is
> returned in the query results? Data fields are not allowed in the Page
> Header section.
> All responses greatly appreciated.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>
>
Data extension question?
Hi,
when you use sql server data extension and set the query to a stored
proceduere, Reporting services automatically creates report parameters based
on the stored proc parameters. I'm creating a custom data processing
extension and i would like to create parameters based on a config file. How
do i do this?
thanks
shankarIn your custom data processing extension, you should implement
IDbCommandAnalysis. In GetParameters, you can access your config file.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Hi,
> when you use sql server data extension and set the query to a stored
> proceduere, Reporting services automatically creates report parameters
based
> on the stored proc parameters. I'm creating a custom data processing
> extension and i would like to create parameters based on a config file.
How
> do i do this?
> thanks
> shankar
>|||Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
interface and i got all the parameters from my config file. In the config
file , i also stored designer default values for all the parameter. I would
like the Reportdesigner to use the designer value when it executes the query
command. You could view the designer value as dummy value. For Example, if
you are using sql server stored proc, you would pass all the required
parameter as a part of command text.
When i implemented, IDbCommandAnalysis interface, i passed this
parameter,designer value pair. I think reporting services uses this
interface only to get the list of parameters and it simply ignores default
value.Is there any way to do this.
thanks
shankar
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> In your custom data processing extension, you should implement
> IDbCommandAnalysis. In GetParameters, you can access your config file.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "shankar" <sramasubramanian@.ozcap.com> wrote in message
> news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > when you use sql server data extension and set the query to a stored
> > proceduere, Reporting services automatically creates report parameters
> based
> > on the stored proc parameters. I'm creating a custom data processing
> > extension and i would like to create parameters based on a config file.
> How
> > do i do this?
> >
> > thanks
> >
> > shankar
> >
> >
>|||That is correct. The design tool uses the interface only to obtain the
parameter names, not to retrieve default values.
Using it for default values as well is a good idea. I'll forward it on to
the designer team. Thanks.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:OjXL2YLeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
> interface and i got all the parameters from my config file. In the config
> file , i also stored designer default values for all the parameter. I
would
> like the Reportdesigner to use the designer value when it executes the
query
> command. You could view the designer value as dummy value. For Example, if
> you are using sql server stored proc, you would pass all the required
> parameter as a part of command text.
> When i implemented, IDbCommandAnalysis interface, i passed this
> parameter,designer value pair. I think reporting services uses this
> interface only to get the list of parameters and it simply ignores default
> value.Is there any way to do this.
> thanks
> shankar
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > In your custom data processing extension, you should implement
> > IDbCommandAnalysis. In GetParameters, you can access your config file.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "shankar" <sramasubramanian@.ozcap.com> wrote in message
> > news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > when you use sql server data extension and set the query to a
stored
> > > proceduere, Reporting services automatically creates report parameters
> > based
> > > on the stored proc parameters. I'm creating a custom data processing
> > > extension and i would like to create parameters based on a config
file.
> > How
> > > do i do this?
> > >
> > > thanks
> > >
> > > shankar
> > >
> > >
> >
> >
>
when you use sql server data extension and set the query to a stored
proceduere, Reporting services automatically creates report parameters based
on the stored proc parameters. I'm creating a custom data processing
extension and i would like to create parameters based on a config file. How
do i do this?
thanks
shankarIn your custom data processing extension, you should implement
IDbCommandAnalysis. In GetParameters, you can access your config file.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Hi,
> when you use sql server data extension and set the query to a stored
> proceduere, Reporting services automatically creates report parameters
based
> on the stored proc parameters. I'm creating a custom data processing
> extension and i would like to create parameters based on a config file.
How
> do i do this?
> thanks
> shankar
>|||Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
interface and i got all the parameters from my config file. In the config
file , i also stored designer default values for all the parameter. I would
like the Reportdesigner to use the designer value when it executes the query
command. You could view the designer value as dummy value. For Example, if
you are using sql server stored proc, you would pass all the required
parameter as a part of command text.
When i implemented, IDbCommandAnalysis interface, i passed this
parameter,designer value pair. I think reporting services uses this
interface only to get the list of parameters and it simply ignores default
value.Is there any way to do this.
thanks
shankar
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> In your custom data processing extension, you should implement
> IDbCommandAnalysis. In GetParameters, you can access your config file.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "shankar" <sramasubramanian@.ozcap.com> wrote in message
> news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > when you use sql server data extension and set the query to a stored
> > proceduere, Reporting services automatically creates report parameters
> based
> > on the stored proc parameters. I'm creating a custom data processing
> > extension and i would like to create parameters based on a config file.
> How
> > do i do this?
> >
> > thanks
> >
> > shankar
> >
> >
>|||That is correct. The design tool uses the interface only to obtain the
parameter names, not to retrieve default values.
Using it for default values as well is a good idea. I'll forward it on to
the designer team. Thanks.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:OjXL2YLeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
> interface and i got all the parameters from my config file. In the config
> file , i also stored designer default values for all the parameter. I
would
> like the Reportdesigner to use the designer value when it executes the
query
> command. You could view the designer value as dummy value. For Example, if
> you are using sql server stored proc, you would pass all the required
> parameter as a part of command text.
> When i implemented, IDbCommandAnalysis interface, i passed this
> parameter,designer value pair. I think reporting services uses this
> interface only to get the list of parameters and it simply ignores default
> value.Is there any way to do this.
> thanks
> shankar
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > In your custom data processing extension, you should implement
> > IDbCommandAnalysis. In GetParameters, you can access your config file.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "shankar" <sramasubramanian@.ozcap.com> wrote in message
> > news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > when you use sql server data extension and set the query to a
stored
> > > proceduere, Reporting services automatically creates report parameters
> > based
> > > on the stored proc parameters. I'm creating a custom data processing
> > > extension and i would like to create parameters based on a config
file.
> > How
> > > do i do this?
> > >
> > > thanks
> > >
> > > shankar
> > >
> > >
> >
> >
>
Sunday, March 25, 2012
Data export to individual files
Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Data export to individual files
Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Data export from SQl in CSV format
Hi
I want to export data out of SQL database using a query into a Comma Delimited(CSV) file. Some one suggested that I can use BCP.exe, I am not sure how to use it properly.
Can some please suggest how to do it? I am not a SQL DBA & is struggling with this.It's much easier to do this using the DTS Data Export under the Tools menu
in the Enterprise Manager. Pick Text File as your destination. The default
format is CSV with embedded double quotes correctly handled.
If you use BCP, you'll have to deal with the embedded double quotes in each
column where that's an possibility, using a TSQL function such as REPLACE().
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"singh" <anonymous@.discussions.microsoft.com> wrote in message
news:6ECE17F7-1A1D-4526-A94E-DCD97F90E04C@.microsoft.com...
> Hi
> I want to export data out of SQL database using a query into a Comma
Delimited(CSV) file. Some one suggested that I can use BCP.exe, I am not
sure how to use it properly.
> Can some please suggest how to do it? I am not a SQL DBA & is struggling
with this.
>|||You can also use command line osql.exe and there is documentation in Books
on Line on the syntax.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand;
Could you please elaborate? Are you talking about the -s command line option
of osql.exe?
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:4roszLKqDHA.2604@.cpmsftngxa06.phx.gbl...
> You can also use command line osql.exe and there is documentation in Books
> on Line on the syntax.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||The -s parameter does allow you to define a column delimiter. The default,
I believe, is a tab.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I want to export data out of SQL database using a query into a Comma Delimited(CSV) file. Some one suggested that I can use BCP.exe, I am not sure how to use it properly.
Can some please suggest how to do it? I am not a SQL DBA & is struggling with this.It's much easier to do this using the DTS Data Export under the Tools menu
in the Enterprise Manager. Pick Text File as your destination. The default
format is CSV with embedded double quotes correctly handled.
If you use BCP, you'll have to deal with the embedded double quotes in each
column where that's an possibility, using a TSQL function such as REPLACE().
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"singh" <anonymous@.discussions.microsoft.com> wrote in message
news:6ECE17F7-1A1D-4526-A94E-DCD97F90E04C@.microsoft.com...
> Hi
> I want to export data out of SQL database using a query into a Comma
Delimited(CSV) file. Some one suggested that I can use BCP.exe, I am not
sure how to use it properly.
> Can some please suggest how to do it? I am not a SQL DBA & is struggling
with this.
>|||You can also use command line osql.exe and there is documentation in Books
on Line on the syntax.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand;
Could you please elaborate? Are you talking about the -s command line option
of osql.exe?
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:4roszLKqDHA.2604@.cpmsftngxa06.phx.gbl...
> You can also use command line osql.exe and there is documentation in Books
> on Line on the syntax.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||The -s parameter does allow you to define a column delimiter. The default,
I believe, is a tab.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Data encryption for results of query using ADO
Hi,
I'm using ADO to connect to a SQL Server.
I want to execute a query which reads sensistive information and ensure that
the results of the query cannot be sniffed on the network
Can I enable encryption for a specific connection to the SQL Server without
forcing either the client machine or sql server to enable
full fledged encryption. I have only one query which returns sensitive
information, hence I do not wish to enable encryption for the all sql server
connections/
using certificates via 'Force Protocol Encryption'
Thanks,
Alwyn
Alwyn,
Take a look at this site: http://www.activecrypt.com/index.htm. You might
want to test the xp_crypt procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alwyn Pereira" <alwynpereira@.hotmail.com> wrote in message
news:ucXSc9sqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm using ADO to connect to a SQL Server.
> I want to execute a query which reads sensistive information and ensure
that
> the results of the query cannot be sniffed on the network
> Can I enable encryption for a specific connection to the SQL Server
without
> forcing either the client machine or sql server to enable
> full fledged encryption. I have only one query which returns sensitive
> information, hence I do not wish to enable encryption for the all sql
server
> connections/
> using certificates via 'Force Protocol Encryption'
> Thanks,
> Alwyn
>
I'm using ADO to connect to a SQL Server.
I want to execute a query which reads sensistive information and ensure that
the results of the query cannot be sniffed on the network
Can I enable encryption for a specific connection to the SQL Server without
forcing either the client machine or sql server to enable
full fledged encryption. I have only one query which returns sensitive
information, hence I do not wish to enable encryption for the all sql server
connections/
using certificates via 'Force Protocol Encryption'
Thanks,
Alwyn
Alwyn,
Take a look at this site: http://www.activecrypt.com/index.htm. You might
want to test the xp_crypt procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Alwyn Pereira" <alwynpereira@.hotmail.com> wrote in message
news:ucXSc9sqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm using ADO to connect to a SQL Server.
> I want to execute a query which reads sensistive information and ensure
that
> the results of the query cannot be sniffed on the network
> Can I enable encryption for a specific connection to the SQL Server
without
> forcing either the client machine or sql server to enable
> full fledged encryption. I have only one query which returns sensitive
> information, hence I do not wish to enable encryption for the all sql
server
> connections/
> using certificates via 'Force Protocol Encryption'
> Thanks,
> Alwyn
>
Data encryption for results of query using ADO
Hi,
I'm using ADO to connect to a SQL Server.
I want to execute a query which reads sensistive information and ensure that
the results of the query cannot be sniffed on the network
Can I enable encryption for a specific connection to the SQL Server without
forcing either the client machine or sql server to enable
full fledged encryption. I have only one query which returns sensitive
information, hence I do not wish to enable encryption for the all sql server
connections/
using certificates via 'Force Protocol Encryption'
Thanks,
Alwyn
There isn't anything built-in to support this. I'm afraid you're going
to have to roll your own.
--Mary
On Mon, 4 Oct 2004 19:11:49 +0530, "Alwyn Pereira"
<alwynpereira@.hotmail.com> wrote:
>Hi,
>I'm using ADO to connect to a SQL Server.
>I want to execute a query which reads sensistive information and ensure that
>the results of the query cannot be sniffed on the network
>Can I enable encryption for a specific connection to the SQL Server without
>forcing either the client machine or sql server to enable
>full fledged encryption. I have only one query which returns sensitive
>information, hence I do not wish to enable encryption for the all sql server
>connections/
>using certificates via 'Force Protocol Encryption'
>Thanks,
>Alwyn
>
sql
I'm using ADO to connect to a SQL Server.
I want to execute a query which reads sensistive information and ensure that
the results of the query cannot be sniffed on the network
Can I enable encryption for a specific connection to the SQL Server without
forcing either the client machine or sql server to enable
full fledged encryption. I have only one query which returns sensitive
information, hence I do not wish to enable encryption for the all sql server
connections/
using certificates via 'Force Protocol Encryption'
Thanks,
Alwyn
There isn't anything built-in to support this. I'm afraid you're going
to have to roll your own.
--Mary
On Mon, 4 Oct 2004 19:11:49 +0530, "Alwyn Pereira"
<alwynpereira@.hotmail.com> wrote:
>Hi,
>I'm using ADO to connect to a SQL Server.
>I want to execute a query which reads sensistive information and ensure that
>the results of the query cannot be sniffed on the network
>Can I enable encryption for a specific connection to the SQL Server without
>forcing either the client machine or sql server to enable
>full fledged encryption. I have only one query which returns sensitive
>information, hence I do not wish to enable encryption for the all sql server
>connections/
>using certificates via 'Force Protocol Encryption'
>Thanks,
>Alwyn
>
sql
Thursday, March 22, 2012
Data Driven Subscription Query Parameters
Does anyone know if its possible to use report parameters as
parameters to a data driven subscription query. This would be useful
for changing the names of the files generated by the subscription
according to the parameters passed to the report.
For example, a report that takes a customer name as a parameter could
pass the customer name through to the data driven subscription query.
This would allow the customer name to be included in the file name of
the file that the data driven subscription generates.
Cheers,
David.In the parameters page, you are able to do a select statement & assign
parameters based on that.
So you could do a select * From names
Assuming your names table has a name field, then you could use the name &
field to assign it to a "file name".
"David Evans" wrote:
> Does anyone know if its possible to use report parameters as
> parameters to a data driven subscription query. This would be useful
> for changing the names of the files generated by the subscription
> according to the parameters passed to the report.
> For example, a report that takes a customer name as a parameter could
> pass the customer name through to the data driven subscription query.
> This would allow the customer name to be included in the file name of
> the file that the data driven subscription generates.
> Cheers,
> David.
>|||Yes, I know you can obtain the file name from a database query for a
data driven subscription. What I dont know is whether you can change
the file name based on the value of a report parameter.
Say I had a report with a parameter of customer name. If someone
created a data driven subscription and specified a customer of
customer1 for the customer name. I would like to be able to generate a
file name like "customer1_09-11-2004.csv".
I guess you would need to pass the customer name as the where clause
to the "select * from names" query mentioned below.
Is this possible.
Cheers,
David.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message news:<1D9F5CAA-3EFD-4747-9455-F51DB8016FEF@.microsoft.com>...
> In the parameters page, you are able to do a select statement & assign
> parameters based on that.
> So you could do a select * From names
> Assuming your names table has a name field, then you could use the name &
> field to assign it to a "file name".
> "David Evans" wrote:
> > Does anyone know if its possible to use report parameters as
> > parameters to a data driven subscription query. This would be useful
> > for changing the names of the files generated by the subscription
> > according to the parameters passed to the report.
> >
> > For example, a report that takes a customer name as a parameter could
> > pass the customer name through to the data driven subscription query.
> > This would allow the customer name to be included in the file name of
> > the file that the data driven subscription generates.
> >
> > Cheers,
> >
> > David.
> >
parameters to a data driven subscription query. This would be useful
for changing the names of the files generated by the subscription
according to the parameters passed to the report.
For example, a report that takes a customer name as a parameter could
pass the customer name through to the data driven subscription query.
This would allow the customer name to be included in the file name of
the file that the data driven subscription generates.
Cheers,
David.In the parameters page, you are able to do a select statement & assign
parameters based on that.
So you could do a select * From names
Assuming your names table has a name field, then you could use the name &
field to assign it to a "file name".
"David Evans" wrote:
> Does anyone know if its possible to use report parameters as
> parameters to a data driven subscription query. This would be useful
> for changing the names of the files generated by the subscription
> according to the parameters passed to the report.
> For example, a report that takes a customer name as a parameter could
> pass the customer name through to the data driven subscription query.
> This would allow the customer name to be included in the file name of
> the file that the data driven subscription generates.
> Cheers,
> David.
>|||Yes, I know you can obtain the file name from a database query for a
data driven subscription. What I dont know is whether you can change
the file name based on the value of a report parameter.
Say I had a report with a parameter of customer name. If someone
created a data driven subscription and specified a customer of
customer1 for the customer name. I would like to be able to generate a
file name like "customer1_09-11-2004.csv".
I guess you would need to pass the customer name as the where clause
to the "select * from names" query mentioned below.
Is this possible.
Cheers,
David.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message news:<1D9F5CAA-3EFD-4747-9455-F51DB8016FEF@.microsoft.com>...
> In the parameters page, you are able to do a select statement & assign
> parameters based on that.
> So you could do a select * From names
> Assuming your names table has a name field, then you could use the name &
> field to assign it to a "file name".
> "David Evans" wrote:
> > Does anyone know if its possible to use report parameters as
> > parameters to a data driven subscription query. This would be useful
> > for changing the names of the files generated by the subscription
> > according to the parameters passed to the report.
> >
> > For example, a report that takes a customer name as a parameter could
> > pass the customer name through to the data driven subscription query.
> > This would allow the customer name to be included in the file name of
> > the file that the data driven subscription generates.
> >
> > Cheers,
> >
> > David.
> >
Data Driven Subscription multi-line email content
I am trying to set up a data driven subscription where one of the fields
returned from the query is sent as the content of the email.
This works fine for a one line email by selecting that column in the
Comment field of the delivery extension screen but I need to create a
multi-line email.
I have tried storing the field with CHAR(13) & CHAR(10) separators in the
table but it still displays on one line when RS sends the email.
Does anyone know if this is possible and how I can achieve it ?The comment can be HTML. So make the comment use html to render on multiple
lines.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"magicboy" <magicboy@.discussions.microsoft.com> wrote in message
news:48673C18-06E1-47CA-A8C7-F6B691C404A4@.microsoft.com...
>I am trying to set up a data driven subscription where one of the fields
> returned from the query is sent as the content of the email.
> This works fine for a one line email by selecting that column in the
> Comment field of the delivery extension screen but I need to create a
> multi-line email.
> I have tried storing the field with CHAR(13) & CHAR(10) separators in the
> table but it still displays on one line when RS sends the email.
> Does anyone know if this is possible and how I can achieve it ?|||This works fine, thanks
"Daniel Reib [MSFT]" wrote:
> The comment can be HTML. So make the comment use html to render on multiple
> lines.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "magicboy" <magicboy@.discussions.microsoft.com> wrote in message
> news:48673C18-06E1-47CA-A8C7-F6B691C404A4@.microsoft.com...
> >I am trying to set up a data driven subscription where one of the fields
> > returned from the query is sent as the content of the email.
> > This works fine for a one line email by selecting that column in the
> > Comment field of the delivery extension screen but I need to create a
> > multi-line email.
> > I have tried storing the field with CHAR(13) & CHAR(10) separators in the
> > table but it still displays on one line when RS sends the email.
> > Does anyone know if this is possible and how I can achieve it ?
>
>
returned from the query is sent as the content of the email.
This works fine for a one line email by selecting that column in the
Comment field of the delivery extension screen but I need to create a
multi-line email.
I have tried storing the field with CHAR(13) & CHAR(10) separators in the
table but it still displays on one line when RS sends the email.
Does anyone know if this is possible and how I can achieve it ?The comment can be HTML. So make the comment use html to render on multiple
lines.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"magicboy" <magicboy@.discussions.microsoft.com> wrote in message
news:48673C18-06E1-47CA-A8C7-F6B691C404A4@.microsoft.com...
>I am trying to set up a data driven subscription where one of the fields
> returned from the query is sent as the content of the email.
> This works fine for a one line email by selecting that column in the
> Comment field of the delivery extension screen but I need to create a
> multi-line email.
> I have tried storing the field with CHAR(13) & CHAR(10) separators in the
> table but it still displays on one line when RS sends the email.
> Does anyone know if this is possible and how I can achieve it ?|||This works fine, thanks
"Daniel Reib [MSFT]" wrote:
> The comment can be HTML. So make the comment use html to render on multiple
> lines.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "magicboy" <magicboy@.discussions.microsoft.com> wrote in message
> news:48673C18-06E1-47CA-A8C7-F6B691C404A4@.microsoft.com...
> >I am trying to set up a data driven subscription where one of the fields
> > returned from the query is sent as the content of the email.
> > This works fine for a one line email by selecting that column in the
> > Comment field of the delivery extension screen but I need to create a
> > multi-line email.
> > I have tried storing the field with CHAR(13) & CHAR(10) separators in the
> > table but it still displays on one line when RS sends the email.
> > Does anyone know if this is possible and how I can achieve it ?
>
>
data driven subscription does not work
I defined a data driven subscription based on a query which returns a list of compagnies (90). For each one, the subcription has to fire a report which extracts the order for the compagny.
After lauching the subcription (apply button , the subscription status is Processed 0 for 90, 0 errors
I do not get any error message.
The report works fine via a subscription base on calendar (with a compagny by default), but not via the data driven subcription.
What could be the reason of this pb ?
By advance thanks
Georges
DId you have a look in the log files ? If the information gven there is not sufficient you can turn on verbose logging, which can be either turned on via the GUI (in SQL Server 2005) or by edition the .config file.
Also make sure that you retrieval process / query doesn′t create any locks on the table.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||I tuned on verbose the log, but nothing special that could help me, may be this extrat.
String was not recognized as a valid Boolean. from the ReportServerService file
Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.Deliver(Notification notification)
ReportingServicesService!library!b7c!05/27/2006-21:49:13:: Data Driven Notification for activation id e9779dcd-c025-4c70-bdad-957582640df2 was saved.
ReportingServicesService!library!b7c!05/27/2006-21:49:13:: Status: Failure writing file NorthWind : String was not recognized as a valid Boolean.
ReportingServicesService!dbpolling!b7c!05/27/2006-21:49:13:: NotificationPolling finished processing item b2f499cf-11d1-4a72-ad46-5b15335c4dca
ReportingServicesService!dbpolling!b20!27/05/2006-21:49:13:: NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!2f4!05/27/2006-21:49:13:: NotificationPolling processing item d62cfef7-babb-45b5-89d3-8e51fab3b995
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction begin.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ReportSnapshot(359966ad-f99e-4d32-a0eb-17696e8d0651) constructor of existing snapshot.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ReportSnapshot(43b6cae4-0795-4c73-8e6c-8a98a0eb708c) constructor of existing snapshot.
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.BeginCancelableState
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetReportChunk('Main', Main) this=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, #ReadChunks=0, #WriteChunks=0
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkWriteStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetChunkPointerAndLength(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, Main, 0)
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction begin.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream.Close() - closing... id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction commit.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - Closed! id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetReportChunk('Main', Main) this=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, #ReadChunks=1, #WriteChunks=0
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkWriteStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - constructor(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, 'Main', 0)
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### GetChunkPointerAndLength(43b6cae4-0795-4c73-8e6c-8a98a0eb708c, Main, 0)
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction begin.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream.Close() - closing... id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction commit.
ReportingServicesService!chunks!2f4!05/27/2006-21:49:13:: v VERBOSE: ### ChunkReadStream - Closed! id=43b6cae4-0795-4c73-8e6c-8a98a0eb708c, name='Main'
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.EndCancelableState
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.WaitForCancelException entered
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: ThreadJobContext.WaitForCancelException finished
ReportingServicesService!runningjobs!2f4!05/27/2006-21:49:13:: v VERBOSE: RunningJobList.RemoveJob: uljnfsigggboaxb5liswsa45 was removed
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Transaction commit.
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders: type (Render).
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders completed.
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Starting crypto operation DBUnProtectData
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Completed crypto operation DBUnProtectData
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Starting crypto operation DBUnProtectData
ReportingServicesService!crypto!2f4!05/27/2006-21:49:13:: v VERBOSE: Completed crypto operation DBUnProtectData
ReportingServicesService!notification!2f4!05/27/2006-21:49:13:: Handling notification of subscription to report NorthWind_multisite via delivery provider Report Server FileShare
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders: type (Render).
ReportingServicesService!library!2f4!05/27/2006-21:49:13:: v VERBOSE: Call to ListProviders completed.
ReportingServicesService!subscription!2f4!05/27/2006-21:49:13:: System.FormatException: String was not recognized as a valid Boolean.
at System.Boolean.Parse(String value)
at Microsoft.ReportingServices.FileShareDeliveryProvider.SubscriptionData.FromSettings(Setting[] settings)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.Deliver(Notification notification)
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
This is the query I defined. The only parameter I pass to the report is companyname. The others are defined via the subcription tab (path, report name, user, password ...)
Is my query not correct ? How can I find the wrong parameter ?
My query :
select companyname from customers
|||Hi,
the query seems ok, perhaps you check the other settings which might be not set all appropiate. But I hope you don′t you the companyname in a column / property of the scheduled report which expects a boolean value.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||I found the raison of the problem. It was the file suffix property (True/false) I set up to a wrong value !
Data Driven Query Task - question about Insert and Update queries
Does anyone know if, in an Update or Insert query within a DDQ task,
it's possible to have more than one statement in the query, and have
them both execute?
e.g.
update tablex set field1=1 where id=?
update tablex set field2 = 2 where id=?
Also, on the same subject, can you mix the statements, so let's say
even if it's the Update query you're using, could you tag an insert on
the end as well?
e.g.
update tablex set field1=1 where id=?
insert into tablex (field2) values (?)
Thanks for any help.Just as an afterthought, I suppose if this isn't possible it may be
useful to know whether the other two types of query can be 'hijacked'
and used as an extra insert (or whatever), but I'll wait to see if
anyone can confirm the original question or not before investigating
this one!!|||Quick note for anyone else looking at the same question: I've just
found the following on msdn:
"In order to refer to your SQL statements, you assign each statement a
name, called a query type. A query type, returned by your Microsoft=AE
ActiveX=AE script code, is used to select one of your SQL statements to
execute. Data Transformation Services (DTS) provides the following four
names:
Insert
Update
Delete
User
These query types should be viewed only as unique identifiers assigned
to each statement. It is in fact possible to perform any SQL operation
supported by the connection. It would be possible for example, to
perform four different updates, four different inserts or any mix of
these or stored procedures."
So I could do it by having an extra update as the 'delete' query, for
example. However, I'd feel it would be better 'practice' to have two
update statements within the Update query...I'd be grateful if anyone
with more experience than me could possibly provide an answer as to
whether you can have the two statements in the one query, as I've
trawled the internet and come up with nothing?
Many, many thanks indeed.,sql
it's possible to have more than one statement in the query, and have
them both execute?
e.g.
update tablex set field1=1 where id=?
update tablex set field2 = 2 where id=?
Also, on the same subject, can you mix the statements, so let's say
even if it's the Update query you're using, could you tag an insert on
the end as well?
e.g.
update tablex set field1=1 where id=?
insert into tablex (field2) values (?)
Thanks for any help.Just as an afterthought, I suppose if this isn't possible it may be
useful to know whether the other two types of query can be 'hijacked'
and used as an extra insert (or whatever), but I'll wait to see if
anyone can confirm the original question or not before investigating
this one!!|||Quick note for anyone else looking at the same question: I've just
found the following on msdn:
"In order to refer to your SQL statements, you assign each statement a
name, called a query type. A query type, returned by your Microsoft=AE
ActiveX=AE script code, is used to select one of your SQL statements to
execute. Data Transformation Services (DTS) provides the following four
names:
Insert
Update
Delete
User
These query types should be viewed only as unique identifiers assigned
to each statement. It is in fact possible to perform any SQL operation
supported by the connection. It would be possible for example, to
perform four different updates, four different inserts or any mix of
these or stored procedures."
So I could do it by having an extra update as the 'delete' query, for
example. However, I'd feel it would be better 'practice' to have two
update statements within the Update query...I'd be grateful if anyone
with more experience than me could possibly provide an answer as to
whether you can have the two statements in the one query, as I've
trawled the internet and come up with nothing?
Many, many thanks indeed.,sql
data driven query DTS activeX script
Hi guys, im using this vb script.
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("SES_STATUS") = DTSSource("Session Status")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_ID") = DTSSource("Start Date")
Main = DTSTransformstat_InsertQuery
End Function
what I would Like to know is how can I complete this script so that it knows when to run an update statement and when to run a insert statement (I have already created the update and insert statements)
as this dts package will run every night and the source database tables are updated and inserted into quite regualar. So something like this is needed:
if record exists in destination
do update statement
if record not exist
do insert startement
many thanks in advance.
As far as I remember this is / was not possible. You should use an executeSQL tasks to UPDATE first the rows which are existing in both tables (Using linked servers or OPENDATASOURCE) and then Insert the remaining ones which are not in the destination table yet.Jens K. Suessmeyer.
http://www.sqlserver2005.de
Data driven query - update only CERTAIN fields?
Hi,
I'm trying to set up a Data Driven Query task, to update only certain
fields in a table.
However, even though the update query only contains the fields I'm
wanting to update, when I try and run it I get:
"One or more destination parameter columns had no transform specified"
Thing is, I don't WANT to specify a transform for most of the
destination columns - I want them left alone!!
I would be so, so grateful if anyone who's done something like this
could help, as there are so precious few decent example of this sort of
thing on the net. Any links anyone has to good in-depth tutorials
covering more than just the basic 'update every single field' scenario
would be fantastic too.
Many, many thanks folks.
ChampersJust to quickly illustrate this (I'm not sure I explained this too well
yesterday)
The destination table (which is my binding table) has, let's say, 10
columns
During the update query, I only want maybe 2 fields to be updated
So my code would be something like...
Function Main()
If
IsEmpty(DTSLookups("DoesRecordExist").Execute(DTSSource("PersonID").Value))
Then
DTSDestination("Field3") = DTSSource("SURNAME")
DTSDestination("Field7") = DTSSource("FORENAME")
Main = DTSTransformstat_InsertQuery
Else..
End If
End Function
But I get the feeling that to avoid the error message above, I still
have to specify all the destination columns, even if I don't want to
update them...but, of course, I would have to set them to update to
something...which I don't want to do!
This is driving me crazy. Thanks in advance for any advice!|||I think I may have just cracked this, so I'll post the answer for
anyone else struggling with it. Basically, on the Transformations tab
(the one with the graphical list of all source and destination
columns), you have to select ALL destination columns (and presumably
all source ones too) regardless of whether you're using them in a query
or not. This is REALLY confusing, and I have not been able to find a
tutorial that explains this anywhere. I'm going to press on now with my
DTS task, and I'l post any other useful info I find on this thread, as
I'm sure other people must have been tearing their hair out over this.|||I'm nearly there with this now, but I have to admit it's such a
confusing thing to use.
I just have one more question that someone could maybe answer - I have
2 DDQ's, one to transfer data from some columns of the source table
into table 1, and another to transfer other columns into a separate
table, table 2.
Now, the first DDQ is OK. However, in the second, one of my queries
refers to a source column that doesn't directly transfer to a column in
table 2. Table 2 here is my binding table.
In order to 'reference' the source column, I'm having to basically map
that source column to a column in the binding 'version' of table 2 (one
that I'm not 'using' in this DDQ), so that I can use it in the
Parameters list.
Is this the correct way to do this? i.e. although the binding table
used is originally a real destination table, it's only actually used as
a way of mapping and referencing source colunmns, and in actual fact
bears no relevance to any data transformations (i.e. this 'mapping'
doesn't actually alter data in the destination column - only my QUERY
can does this, in which the destination table itself is used as a real
query destination table, rather than as the binding table).
Sorry to be so verbose...I'd be grateful if someone could set my mind
at ease and clarify that I've got this right in my head!
Thanks so much guys.
I'm trying to set up a Data Driven Query task, to update only certain
fields in a table.
However, even though the update query only contains the fields I'm
wanting to update, when I try and run it I get:
"One or more destination parameter columns had no transform specified"
Thing is, I don't WANT to specify a transform for most of the
destination columns - I want them left alone!!
I would be so, so grateful if anyone who's done something like this
could help, as there are so precious few decent example of this sort of
thing on the net. Any links anyone has to good in-depth tutorials
covering more than just the basic 'update every single field' scenario
would be fantastic too.
Many, many thanks folks.
ChampersJust to quickly illustrate this (I'm not sure I explained this too well
yesterday)
The destination table (which is my binding table) has, let's say, 10
columns
During the update query, I only want maybe 2 fields to be updated
So my code would be something like...
Function Main()
If
IsEmpty(DTSLookups("DoesRecordExist").Execute(DTSSource("PersonID").Value))
Then
DTSDestination("Field3") = DTSSource("SURNAME")
DTSDestination("Field7") = DTSSource("FORENAME")
Main = DTSTransformstat_InsertQuery
Else..
End If
End Function
But I get the feeling that to avoid the error message above, I still
have to specify all the destination columns, even if I don't want to
update them...but, of course, I would have to set them to update to
something...which I don't want to do!
This is driving me crazy. Thanks in advance for any advice!|||I think I may have just cracked this, so I'll post the answer for
anyone else struggling with it. Basically, on the Transformations tab
(the one with the graphical list of all source and destination
columns), you have to select ALL destination columns (and presumably
all source ones too) regardless of whether you're using them in a query
or not. This is REALLY confusing, and I have not been able to find a
tutorial that explains this anywhere. I'm going to press on now with my
DTS task, and I'l post any other useful info I find on this thread, as
I'm sure other people must have been tearing their hair out over this.|||I'm nearly there with this now, but I have to admit it's such a
confusing thing to use.
I just have one more question that someone could maybe answer - I have
2 DDQ's, one to transfer data from some columns of the source table
into table 1, and another to transfer other columns into a separate
table, table 2.
Now, the first DDQ is OK. However, in the second, one of my queries
refers to a source column that doesn't directly transfer to a column in
table 2. Table 2 here is my binding table.
In order to 'reference' the source column, I'm having to basically map
that source column to a column in the binding 'version' of table 2 (one
that I'm not 'using' in this DDQ), so that I can use it in the
Parameters list.
Is this the correct way to do this? i.e. although the binding table
used is originally a real destination table, it's only actually used as
a way of mapping and referencing source colunmns, and in actual fact
bears no relevance to any data transformations (i.e. this 'mapping'
doesn't actually alter data in the destination column - only my QUERY
can does this, in which the destination table itself is used as a real
query destination table, rather than as the binding table).
Sorry to be so verbose...I'd be grateful if someone could set my mind
at ease and clarify that I've got this right in my head!
Thanks so much guys.
Data Driven Query - Milliseconds
DTS
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts '
Thanks
SOn Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/defaul...;EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups? th...phx.g
bl
( http://tinyurl.com/3f63m )
cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhi
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts '
Thanks
SOn Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/defaul...;EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups? th...phx.g
bl
( http://tinyurl.com/3f63m )
cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhi
Data Driven Query - Milliseconds
DTS
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts ?
Thanks
S
On Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/default...EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups?thre...0a% 40phx.gbl
( http://tinyurl.com/3f63m )
cheers,
Ross.
"There is more to life than simply increasing its speed." - Mahatma Gandhi
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts ?
Thanks
S
On Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/default...EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups?thre...0a% 40phx.gbl
( http://tinyurl.com/3f63m )
cheers,
Ross.
"There is more to life than simply increasing its speed." - Mahatma Gandhi
Data Driven Query - Milliseconds
DTS
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts '
Thanks
SOn Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups?threadm=036f01c3bdc0%2494bbc510%24a101280a%40phx.gbl
( http://tinyurl.com/3f63m )
cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhisql
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts '
Thanks
SOn Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups?threadm=036f01c3bdc0%2494bbc510%24a101280a%40phx.gbl
( http://tinyurl.com/3f63m )
cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhisql
Data Driven Published Report - Not displaying recipients
I'm setting up my first data driven published report. I have a very simple
query that goes out and returns a list of email recipients. I enter my SQL,
then click "validate" and the server says that it has successfully validated
my query. This SHOULD allow the fields in the subsequent form to be
populated with my returned field list. However, the "Database Fields" drop
down in the delivery extension form only shows one blank line. When i select
that line, i get the error "Blank database field names cannot be used"
Where did my field names go?
TIA,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.comMake sure that the query has a column name for the email recipients. You
may need to add an 'As suchandsuch' to get a column name.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:#e75$ashEHA.3632@.TK2MSFTNGP09.phx.gbl...
> I'm setting up my first data driven published report. I have a very simple
> query that goes out and returns a list of email recipients. I enter my
SQL,
> then click "validate" and the server says that it has successfully
validated
> my query. This SHOULD allow the fields in the subsequent form to be
> populated with my returned field list. However, the "Database Fields" drop
> down in the delivery extension form only shows one blank line. When i
select
> that line, i get the error "Blank database field names cannot be used"
> Where did my field names go?
> TIA,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>|||Thanks for the reply Daniel.
Aliasing my one returned "email_address" field was something i tried, but to
no avail. My query simply returns one field, "email_address". I've also
validated that the query correctly returns 3 records when ran directly
against the database.
The database is Informix, using ODBC for connections. It validates just
fine, still no fields showing up though. This is looking more and more like
a bug.
Any other ideas?
Thanks,
Brian
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OtWODUthEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Make sure that the query has a column name for the email recipients. You
> may need to add an 'As suchandsuch' to get a column name.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:#e75$ashEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > I'm setting up my first data driven published report. I have a very
simple
> > query that goes out and returns a list of email recipients. I enter my
> SQL,
> > then click "validate" and the server says that it has successfully
> validated
> > my query. This SHOULD allow the fields in the subsequent form to be
> > populated with my returned field list. However, the "Database Fields"
drop
> > down in the delivery extension form only shows one blank line. When i
> select
> > that line, i get the error "Blank database field names cannot be used"
> >
> > Where did my field names go?
> >
> > TIA,
> >
> > --
> > Brian Grant
> > Senior Programmer
> > SI International
> > www.si-intl.com
> >
> >
>|||Update: When I moved the date into a SQL Server database, everything worked
just fine. So, the problem seems to only occurr when using an ODBC
connection to get the list of recipients.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OtWODUthEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Make sure that the query has a column name for the email recipients. You
> may need to add an 'As suchandsuch' to get a column name.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:#e75$ashEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > I'm setting up my first data driven published report. I have a very
simple
> > query that goes out and returns a list of email recipients. I enter my
> SQL,
> > then click "validate" and the server says that it has successfully
> validated
> > my query. This SHOULD allow the fields in the subsequent form to be
> > populated with my returned field list. However, the "Database Fields"
drop
> > down in the delivery extension form only shows one blank line. When i
> select
> > that line, i get the error "Blank database field names cannot be used"
> >
> > Where did my field names go?
> >
> > TIA,
> >
> > --
> > Brian Grant
> > Senior Programmer
> > SI International
> > www.si-intl.com
> >
> >
>
query that goes out and returns a list of email recipients. I enter my SQL,
then click "validate" and the server says that it has successfully validated
my query. This SHOULD allow the fields in the subsequent form to be
populated with my returned field list. However, the "Database Fields" drop
down in the delivery extension form only shows one blank line. When i select
that line, i get the error "Blank database field names cannot be used"
Where did my field names go?
TIA,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.comMake sure that the query has a column name for the email recipients. You
may need to add an 'As suchandsuch' to get a column name.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:#e75$ashEHA.3632@.TK2MSFTNGP09.phx.gbl...
> I'm setting up my first data driven published report. I have a very simple
> query that goes out and returns a list of email recipients. I enter my
SQL,
> then click "validate" and the server says that it has successfully
validated
> my query. This SHOULD allow the fields in the subsequent form to be
> populated with my returned field list. However, the "Database Fields" drop
> down in the delivery extension form only shows one blank line. When i
select
> that line, i get the error "Blank database field names cannot be used"
> Where did my field names go?
> TIA,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>|||Thanks for the reply Daniel.
Aliasing my one returned "email_address" field was something i tried, but to
no avail. My query simply returns one field, "email_address". I've also
validated that the query correctly returns 3 records when ran directly
against the database.
The database is Informix, using ODBC for connections. It validates just
fine, still no fields showing up though. This is looking more and more like
a bug.
Any other ideas?
Thanks,
Brian
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OtWODUthEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Make sure that the query has a column name for the email recipients. You
> may need to add an 'As suchandsuch' to get a column name.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:#e75$ashEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > I'm setting up my first data driven published report. I have a very
simple
> > query that goes out and returns a list of email recipients. I enter my
> SQL,
> > then click "validate" and the server says that it has successfully
> validated
> > my query. This SHOULD allow the fields in the subsequent form to be
> > populated with my returned field list. However, the "Database Fields"
drop
> > down in the delivery extension form only shows one blank line. When i
> select
> > that line, i get the error "Blank database field names cannot be used"
> >
> > Where did my field names go?
> >
> > TIA,
> >
> > --
> > Brian Grant
> > Senior Programmer
> > SI International
> > www.si-intl.com
> >
> >
>|||Update: When I moved the date into a SQL Server database, everything worked
just fine. So, the problem seems to only occurr when using an ODBC
connection to get the list of recipients.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OtWODUthEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Make sure that the query has a column name for the email recipients. You
> may need to add an 'As suchandsuch' to get a column name.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:#e75$ashEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > I'm setting up my first data driven published report. I have a very
simple
> > query that goes out and returns a list of email recipients. I enter my
> SQL,
> > then click "validate" and the server says that it has successfully
> validated
> > my query. This SHOULD allow the fields in the subsequent form to be
> > populated with my returned field list. However, the "Database Fields"
drop
> > down in the delivery extension form only shows one blank line. When i
> select
> > that line, i get the error "Blank database field names cannot be used"
> >
> > Where did my field names go?
> >
> > TIA,
> >
> > --
> > Brian Grant
> > Senior Programmer
> > SI International
> > www.si-intl.com
> >
> >
>
Monday, March 19, 2012
Data Conversion Issue
Hi,
I have a simple query that does the following :-
select desc1,desc2,desc3,desc4,desc5 from testdata
So I select the data for the above
What I would like to achieve without having to go to great lengths the
following:-
So taking the column desc1 I want to insert this into a table
e.g.
Desc1 is record 1 in the table
Desc2 is record 2 in the table
Desc3 is record 3 in the table
Desc4 is record 4 in the table
and so on
Is there a tool or a special type declaration that can be used ?
Regards
Andrew[posted and mailed, please reply in news]
Info (info@.schnof.co.uk) writes:
> I have a simple query that does the following :-
> select desc1,desc2,desc3,desc4,desc5 from testdata
>
> So I select the data for the above
> What I would like to achieve without having to go to great lengths the
> following:-
> So taking the column desc1 I want to insert this into a table
> e.g.
> Desc1 is record 1 in the table
> Desc2 is record 2 in the table
> Desc3 is record 3 in the table
> Desc4 is record 4 in the table
> and so on
I'm afraid that I don't really understand what you are asking for. Could
you provide the following:
o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired result from this sample data.
The reason I ask for this is that it clarifies your question, and with
the script it's possible to post a tested solution.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thursday, March 8, 2012
Data between two sql registrations
Hi how can i set a query to retrieve data from 2 registrations ?
i am using the default sql server registration and an mdf database that i
connected. The data are distributed to the two databases.
Can anyone help me please ?Makis
Do the databases reside on the same Server?
If they do ,please run
INSERT INTO DataBase1.dbo.Table (....)
SELECT ... FROM DataBase2.dbo.Table2
"makis" <sera@.mind.gr> wrote in message
news:O54VPFaIGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi how can i set a query to retrieve data from 2 registrations ?
> i am using the default sql server registration and an mdf database that i
> connected. The data are distributed to the two databases.
> Can anyone help me please ?
>|||Are those two SQL Server instances? Then use sp_addlinked server to create a
link between the instances, then by using properly qualified identifiers
(look up "identifiers" in Books Online) you can access data in either of the
databases (tables, etc.).
ML
http://milambda.blogspot.com/|||If "registrations" are meant with different servers, you have to
use/configure a linked server. Then you can extend the syntax from uri
by the following:
INSERT INTO Servername1.DataBase1.dbo.Table (....)
SELECT ... FROM Servername2.DataBase2.dbo.Table2
(four part notation)
If one server is local you sure can leave one Servername{0} out.
HTH, Jens Suessmeyer.|||Yep , I was
by this statement
>The data are distributed to the two databases.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138189932.137989.39870@.g44g2000cwa.googlegroups.com...
> If "registrations" are meant with different servers, you have to
> use/configure a linked server. Then you can extend the syntax from uri
> by the following:
> INSERT INTO Servername1.DataBase1.dbo.Table (....)
> SELECT ... FROM Servername2.DataBase2.dbo.Table2
> (four part notation)
> If one server is local you sure can leave one Servername{0} out.
> HTH, Jens Suessmeyer.
>|||the databases are in one server.
I meant that the data are spread accross the two databases.
I believe your query will help me, thanks for your help. Will post again if
any problems.
i am using the default sql server registration and an mdf database that i
connected. The data are distributed to the two databases.
Can anyone help me please ?Makis
Do the databases reside on the same Server?
If they do ,please run
INSERT INTO DataBase1.dbo.Table (....)
SELECT ... FROM DataBase2.dbo.Table2
"makis" <sera@.mind.gr> wrote in message
news:O54VPFaIGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi how can i set a query to retrieve data from 2 registrations ?
> i am using the default sql server registration and an mdf database that i
> connected. The data are distributed to the two databases.
> Can anyone help me please ?
>|||Are those two SQL Server instances? Then use sp_addlinked server to create a
link between the instances, then by using properly qualified identifiers
(look up "identifiers" in Books Online) you can access data in either of the
databases (tables, etc.).
ML
http://milambda.blogspot.com/|||If "registrations" are meant with different servers, you have to
use/configure a linked server. Then you can extend the syntax from uri
by the following:
INSERT INTO Servername1.DataBase1.dbo.Table (....)
SELECT ... FROM Servername2.DataBase2.dbo.Table2
(four part notation)
If one server is local you sure can leave one Servername{0} out.
HTH, Jens Suessmeyer.|||Yep , I was
by this statement>The data are distributed to the two databases.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138189932.137989.39870@.g44g2000cwa.googlegroups.com...
> If "registrations" are meant with different servers, you have to
> use/configure a linked server. Then you can extend the syntax from uri
> by the following:
> INSERT INTO Servername1.DataBase1.dbo.Table (....)
> SELECT ... FROM Servername2.DataBase2.dbo.Table2
> (four part notation)
> If one server is local you sure can leave one Servername{0} out.
> HTH, Jens Suessmeyer.
>|||the databases are in one server.
I meant that the data are spread accross the two databases.
I believe your query will help me, thanks for your help. Will post again if
any problems.
Subscribe to:
Posts (Atom)