Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

Overwriting an excel file using SSIS

Hi All,

I created a package which runs everydays and dumps the data into an excel file.

The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......

But i want it to delete the records already present and fill in the excel only with the new records...

Any help is greatly appreciated.

Thanks in Advance,

SVGP

Can you simply insert into a new file everyday that is datetime stamped then overwrite the apropriate one through a script task? If you keep the paths in variable names you can chose to delete or keep the timestamped files. This may require you to delay validation, but I believe I've found ways around that though.

Or you could delete and re-create the file everyday. You may also have to tweak the package validation.

|||

You can create the excel file on every execution. You can create a different file each time by adding a unique identifier to the file name; or you can use the same name every time; but then you have to have an additional step to delete/move the existing file

See if this post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

I have found Excel destinations a bit tricky to work with. I had a similar issue wherein each day I wanted to created a different file with the previous day's date appended to the file name. When I tried to execute the package however it would give me an error stating that the file did not exist. I have assumed that for Excel destinations SSIS requires the file to already exist prior to execution. Anyway, this is how I solved the issue.

First, I realized that Flat File destinations are a little more forgiving and a bit more flexible. I therefore chose to output the data in a comma-delimited format csv file. Because csv files open well in Excel I did not have any problem. The other advantage with Flat File destinations is that you can overwrite them.

If using a Flat File is not an option what you can do is to have a "template" Excel file that is always empty and has a different name than the one being populated by your package. Once your package is done populating the target use the File System Task to make a copy of the target file, renaming in the process. Use another File System Task to then copy the template file to the target file area and overwrite the target. You now have a fresh empty target to start with and your previous target has already been saved.

I hope this makes sense.

|||

OK, I just realiazed why I had to abandon the Excel method. Even when the SSIS package was presented with a new empty target it remembers how many rows were inserted from the previous run and consequently begins a fresh "append" at the n+1 th row in the new spreadsheet. In other words, if SSIS had inserted 50 records the previous day it begins the next day's insert at the 51st record even if the target is empty.

I wonder if there is a buffer that SSIS maintains and if there is a way to clear it.

I am now using a Flat File destination and it is working great. It is a really good alternative to using Excel destinations.

|||

SSIS 'does not remember' what was the last row number it inserted. Perhaps the way you removed the data from the existing file made SSIS to still 'see' those rows as been used.

If you use the technique I described in my blog, to create the excel file you won't have that problem.

|||

Hi Rafael,

I tried the way you mentioned in the blog but

Iam getting the following error,even after trying a lot iam unable to resolve this

Expression cannot be evaluated

The variable "user::excelFilename" was not found in the variable collection.The variable might not exist in the correct scope.

Attempt to parse the expression "@.[user::excelFilename]" failed and returned error code 0xC00470A6.The expression cannot
be parsed.It might contain invalid elements or it might not be well-formed.There may also be an out of memory error.

i tried both with package scope and task scope both didnt work as the same error was coming.

Did you face the same issue when you followed this method?

Any help on this is greatly aprreciated.

Thanks,

SVGP.

|||

Please provide the expression you are using and the property name where are you trying to apply it to.

|||

Hi

I created a global variable "filename" and then in the property window of the variable i changed the evaluate as expression to 'true' then i wrote an expression in the same properties window like

"H:\\Sharedrive\\Reports\\NAR1+select CONVERT(Char(10),getdate(),101)+.xls"

then i took the connection manager properties and in the expression pane i gave excel file path and in the expression builder i gave @.[user::filename] and iam getting the error that i wrote previously.

Please let me know if what iam doing is wrong.

Thanks,

SVGP

|||

That expression even when is valid; it evaluate to something like:

H:\Sharedrive\Reports\NAR1+select CONVERT(Char(10),getdate(),101)+.xls

Which is not a valid path/file name.

You need to work out the expression untill you get the desired date format.

|||

Thsi expression should work for you:

"H:\\Sharedrive\\Reports\\NAR1" + RIGHT("0" + (DT_WSTR,2)DAY(getdate()),2) + RIGHT("0" + (DT_WSTR,2)MONTH(GetDAte()),2) + RIGHT("0" + (DT_WSTR,4)YEAR(GetDAte()),4) + ".xls"

|||

Hi Rafael,

Thanks a lot for the code.

Your code is working but again iam getting this error.

' H:\sharedrive\Reports\NAR106092007.xls' is not a valid path.Make sure that the pathname is spelled correctly and that you are connected to the server on which the file resides.

Thanks,

SVGP

|||

Hi Rafael,

Kindly let me know if you come to know the cause of this error.

Thanks,

SVGP.

|||I do not think you can use the drive letter - you need to specify the name of the server with double backslashes - so you need \\\\server_name within quotes so that it will translate to \\server_name.|||

Swan,

the message is very explanatory. Is this path valid?

H:\sharedrive\Reports\

or should it be:

H:\sharedrive\Reports\NAR\

If the right one is the second option; then you need to modify the expression to add an extra '\'

Also make sure that the user executing the package (are you using BIDS?) has access to that directory and it can create files.

overwriting a database

I am very new to SQL and I hope I am in the right place for this question. I have a database (I believe it is just one table) that needs to be overwritten with an import of a text file every night. I know there is a very easy way to do it, I just don't have a clue what it might be.

The database is IPTVprogramsSQL and the table is programs. I am pretty sure the fields names are matched. Again, I am very new and have been thrown in way over my head.

Thanks for any help I can get.

What tools are you using? If you can execute SQL, just run:

TRUNCATE tablename

Or

DELETE tableName --if truncate won't work

to remove the data. Then just reload it however you are now.

|||if ur using DTS/ssis to do this..simply select the option to delete existing records...befor inserting from txt file.|||

first question - what tool will you use to import the data?

If you're on SQL2005 you should probably look at using SQL Server Integration Services (SSIS) to perform this if it's installed on your server. For SQL2000, you can do it with DTS pretty easily.

There should be an Import Data wizard in Enterprise Manager (or SQL Server Management Studio if you're on 05) that can help you get started. From SSMS, right click the DB you want to target and select Import Data from the Tasks sub-menu item.

Your question is a bit beyond the scope of this group - but hopefully this will get you started in the right direction.

If you want to overwrite the data, you can use the facility of the tool to specify this, or if you're doing it from a stored procedure or T-SQL batch, you can use the truncate table command to empty the table before you load it.

|||I would like to have this as a stored process that is executed by the server agent. I am using 2005 w/SP1. I tried to just import the data and save that procedure. I assume I would do the same thing with deleting or truncating command?|||

create procedure usp_truncate_tablename

as
truncate <table name>

go

Allen_Iowa wrote:

I would like to have this as a stored process that is executed by the server agent. I am using 2005 w/SP1. I tried to just import the data and save that procedure. I assume I would do the same thing with deleting or truncating command?

OverWrite SqlExpress .LDF file ??

Hi :)

I have a website that uses SqlExpress ...on it i have a database that was working ok ...until i made a few modifications to the database (had a few rows).

I have upload the database (only the .mdf file) to the app_data folder ...but now i get this message :

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Cannot open database "ArtWork" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Log file 'c:\xxxx\xxx\xxxx\xxxx\xxxx\App_Data\ArtWork_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

I try to delete the .LDF file but it gives me access denied ...i cheched the permissions for the file and everything is ok

How can i solve this??

Thanks and Cheers

Have you moved both the .mdf file and .ldf file to new location (c:\xxxx\xxx\xxxx\xxxx\xxxx\App_Data\)?

Overwrite SQL exported Excel sheet with new data

Hi,

I have set up a DTS package to export data to an excel sheet. If I schedule it the data appends to the exisitng data in the sheet.

How do I overwrite the data or update the data in sheet so that I may schedule a fresh updated copy to be sent to same file?

Thanks

Quote:

Originally Posted by daywho

Hi,

I have set up a DTS package to export data to an excel sheet. If I schedule it the data appends to the exisitng data in the sheet.

How do I overwrite the data or update the data in sheet so that I may schedule a fresh updated copy to be sent to same file?

Thanks

In the create table task, add a drop table :

DROP TABLE `myexcelsheetname`
GO

CREATE TABLE `myexcelsheetname` (
`tDescription` LongText ,
`tIdentifier` VarChar (16) ,
`tUpdateDate` DateTime
)

Overwrite query

Can I call web service to overwrite the query ? If yes, how can do it ?Only by republishing the report. Or you can make the query based on an
expression and pass the value in.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> Can I call web service to overwrite the query ? If yes, how can do it
> ?|||In reporting services books online, I find some class such as
DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
other example that teach us to overwrite the reports original query ?
Since I have some reports which the template and parameter are same but with
different select statment.
"Brian Welcker [MSFT]" wrote:
> Only by republishing the report. Or you can make the query based on an
> expression and pass the value in.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > Can I call web service to overwrite the query ? If yes, how can do it
> > ?
>
>|||When i try to overwrite the query, the following error appear:
The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
performing this operation. -->
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
performing this operation. at
Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
report, DataSource[] dataSources) at
Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
batchId, String report, DataSource[] dataSources) at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
Report, DataSource[] DataSources) -- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
Can anyone help me ?
"May Liu" wrote:
> In reporting services books online, I find some class such as
> DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
> other example that teach us to overwrite the reports original query ?
> Since I have some reports which the template and parameter are same but with
> different select statment.
> "Brian Welcker [MSFT]" wrote:
> > Only by republishing the report. Or you can make the query based on an
> > expression and pass the value in.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > > Can I call web service to overwrite the query ? If yes, how can do it
> > > ?
> >
> >
> >|||I have solve it but another problem come out. The error is as follow:
System.Web.Services.Protocols.SoapException:
System.Web.Services.Protocols.SoapException: The required field Extension is
missing from the input structure. -->
Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
The required field Extension is missing from the input structure. at
Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
at
Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
name, String originalName, DataSourceDefinition definition) at
Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
item, String name) at
Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
dataSource) at
Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
dataSources) at
Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
report, DataSource[] dataSources) at
Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
batchId, String report, DataSource[] dataSources) at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
Report, DataSource[] DataSources) -- End of inner exception stack trace --
at
Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
My code is as follow:
dataSrcDef.ConnectString = "data source=TEST"
dataSrcDef.UserName = "user"
dataSrcDef.Password = "password"
QueryDef.CommandText = txtSQL.Text
dataSetDef.Query = QueryDef
dataSetDef.Name = "DS_DEVE"
rsDataSource(0) = New DataSource
rsDataSource(0).Item = dataSrcDef
rsDataSource(0).Name = "DS Deve"
RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
RptSvc.SetReportDataSources(reportPath, rsDataSource)
What happens to my code ? Can anyone help me ?
"May Liu" wrote:
> When i try to overwrite the query, the following error appear:
> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> performing this operation. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The
> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> performing this operation. at
> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> report, DataSource[] dataSources) at
> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> batchId, String report, DataSource[] dataSources) at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources) -- End of inner exception stack trace --
> at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String Report, DataSource[] DataSources)
> Can anyone help me ?
> "May Liu" wrote:
> > In reporting services books online, I find some class such as
> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there any
> > other example that teach us to overwrite the reports original query ?
> > Since I have some reports which the template and parameter are same but with
> > different select statment.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> > > Only by republishing the report. Or you can make the query based on an
> > > expression and pass the value in.
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> > > > Can I call web service to overwrite the query ? If yes, how can do it
> > > > ?
> > >
> > >
> > >|||The error reflects the fact that you need to set the DataSource.Extension to
"SQL". However, you are going down the wrong path - you can't set the query
this way, only the connection string. The query is not directly accessible
from the server.
I would go down the path of dynamically constructing the query in the
report.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:586B7F51-D5CD-4CFE-BECF-43393AFD6A12@.microsoft.com...
>I have solve it but another problem come out. The error is as follow:
> System.Web.Services.Protocols.SoapException:
> System.Web.Services.Protocols.SoapException: The required field Extension
> is
> missing from the input structure. -->
> Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
> The required field Extension is missing from the input structure. at
> Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
> at
> Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
> name, String originalName, DataSourceDefinition definition) at
> Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
> item, String name) at
> Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
> dataSource) at
> Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
> dataSources) at
> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> report, DataSource[] dataSources) at
> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> batchId, String report, DataSource[] dataSources) at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources) -- End of inner exception stack
> trace --
> at
> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> Report, DataSource[] DataSources)
> My code is as follow:
> dataSrcDef.ConnectString = "data source=TEST"
> dataSrcDef.UserName = "user"
> dataSrcDef.Password = "password"
> QueryDef.CommandText = txtSQL.Text
> dataSetDef.Query = QueryDef
> dataSetDef.Name = "DS_DEVE"
> rsDataSource(0) = New DataSource
> rsDataSource(0).Item = dataSrcDef
> rsDataSource(0).Name = "DS Deve"
> RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
> RptSvc.SetReportDataSources(reportPath, rsDataSource)
> What happens to my code ? Can anyone help me ?
> "May Liu" wrote:
>> When i try to overwrite the query, the following error appear:
>> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient
>> for
>> performing this operation. -->
>> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
>> The
>> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
>> performing this operation. at
>> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
>> report, DataSource[] dataSources) at
>> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
>> batchId, String report, DataSource[] dataSources) at
>> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
>> Report, DataSource[] DataSources) -- End of inner exception stack
>> trace --
>> at
>> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
>> Report, DataSource[] DataSources)
>> Can anyone help me ?
>> "May Liu" wrote:
>> > In reporting services books online, I find some class such as
>> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there
>> > any
>> > other example that teach us to overwrite the reports original query ?
>> > Since I have some reports which the template and parameter are same but
>> > with
>> > different select statment.
>> >
>> > "Brian Welcker [MSFT]" wrote:
>> >
>> > > Only by republishing the report. Or you can make the query based on
>> > > an
>> > > expression and pass the value in.
>> > >
>> > > --
>> > > Brian Welcker
>> > > Group Program Manager
>> > > SQL Server Reporting Services
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> > > rights.
>> > >
>> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
>> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
>> > > > Can I call web service to overwrite the query ? If yes, how can
>> > > > do it
>> > > > ?
>> > >
>> > >
>> > >|||Sorry, I don't understand what you mean. Does reporting service provide web
service that can allow me to change the query dynamically. I see there are
many web service such as DataSourceDfinition, DataSetDefinition or
QueryDefinition. Actually, I am quite confusing about them and their
relationship.
"Brian Welcker [MSFT]" wrote:
> The error reflects the fact that you need to set the DataSource.Extension to
> "SQL". However, you are going down the wrong path - you can't set the query
> this way, only the connection string. The query is not directly accessible
> from the server.
> I would go down the path of dynamically constructing the query in the
> report.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:586B7F51-D5CD-4CFE-BECF-43393AFD6A12@.microsoft.com...
> >I have solve it but another problem come out. The error is as follow:
> >
> > System.Web.Services.Protocols.SoapException:
> > System.Web.Services.Protocols.SoapException: The required field Extension
> > is
> > missing from the input structure. -->
> > Microsoft.ReportingServices.Diagnostics.Utilities.MissingElementException:
> > The required field Extension is missing from the input structure. at
> > Microsoft.ReportingServices.DataExtensions.DataSourceInfo.ValidateDefinition()
> > at
> > Microsoft.ReportingServices.Library.Soap.DataSourceDefinition.ThisToDataSourceInfo(String
> > name, String originalName, DataSourceDefinition definition) at
> > Microsoft.ReportingServices.Library.Soap.DataSourceDefinitionOrReference.ThisToDataSourceInfo(DataSourceDefinitionOrReference
> > item, String name) at
> > Microsoft.ReportingServices.Library.Soap.DataSource.ThisToDataSourceInfo(DataSource
> > dataSource) at
> > Microsoft.ReportingServices.Library.Soap.DataSource.ThisArrayToDataSourceInfoCollection(DataSource[]
> > dataSources) at
> > Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> > report, DataSource[] dataSources) at
> > Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> > batchId, String report, DataSource[] dataSources) at
> > Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> > Report, DataSource[] DataSources) -- End of inner exception stack
> > trace --
> > at
> > Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> > Report, DataSource[] DataSources)
> >
> > My code is as follow:
> > dataSrcDef.ConnectString = "data source=TEST"
> > dataSrcDef.UserName = "user"
> > dataSrcDef.Password = "password"
> >
> > QueryDef.CommandText = txtSQL.Text
> > dataSetDef.Query = QueryDef
> > dataSetDef.Name = "DS_DEVE"
> >
> > rsDataSource(0) = New DataSource
> > rsDataSource(0).Item = dataSrcDef
> > rsDataSource(0).Name = "DS Deve"
> >
> > RptSvc.Credentials = System.Net.CredentialCache.DefaultCredentials
> > RptSvc.SetReportDataSources(reportPath, rsDataSource)
> >
> > What happens to my code ? Can anyone help me ?
> >
> > "May Liu" wrote:
> >
> >> When i try to overwrite the query, the following error appear:
> >>
> >> The permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient
> >> for
> >> performing this operation. -->
> >> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException:
> >> The
> >> permissions granted to user 'WS-HKG-IT5670A\ASPNET' are insufficient for
> >> performing this operation. at
> >> Microsoft.ReportingServices.Library.RSService._SetReportDataSources(String
> >> report, DataSource[] dataSources) at
> >> Microsoft.ReportingServices.Library.RSService.SetReportDataSources(Guid
> >> batchId, String report, DataSource[] dataSources) at
> >> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> >> Report, DataSource[] DataSources) -- End of inner exception stack
> >> trace --
> >> at
> >> Microsoft.ReportingServices.WebServer.ReportingService.SetReportDataSources(String
> >> Report, DataSource[] DataSources)
> >>
> >> Can anyone help me ?
> >>
> >> "May Liu" wrote:
> >>
> >> > In reporting services books online, I find some class such as
> >> > DataSetDefinition, DataSourceDefinition or QueryDefinition. Is there
> >> > any
> >> > other example that teach us to overwrite the reports original query ?
> >> > Since I have some reports which the template and parameter are same but
> >> > with
> >> > different select statment.
> >> >
> >> > "Brian Welcker [MSFT]" wrote:
> >> >
> >> > > Only by republishing the report. Or you can make the query based on
> >> > > an
> >> > > expression and pass the value in.
> >> > >
> >> > > --
> >> > > Brian Welcker
> >> > > Group Program Manager
> >> > > SQL Server Reporting Services
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> > > rights.
> >> > >
> >> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> >> > > news:F26B1DA5-B0E0-4536-913B-6325421FBBB2@.microsoft.com...
> >> > > > Can I call web service to overwrite the query ? If yes, how can
> >> > > > do it
> >> > > > ?
> >> > >
> >> > >
> >> > >
>
>

Overwrite flat file destination does not work

I have 3 packages that run consecutively in a main package. Each of these packages read data from a flat file and import it into the Database and then perform some updates and then I use a script component to write the data to a flat file destination and have overwrite flag on the flat file destination to false, however the flat file is not being appended to. All the 3 packages are set to wirte to the same flat file destination each package overwrites the content of the flat file created by the previous packages.

I am wondering why the overwrite = False does not work on the flat file destination. Is there something else that I need to set or is this a defect? Any inputs will be much appreciated.

Thanks,

M.Shah

It works fine for me, I can append to an exiting file from multiple packages. Double-check all your destinations for the setting. I am using SP1 + Hotfix, but I don't remember this being an issue before or having been fixed.|||

I have double checked and I have Overwrite = False for all the three flat file destinations and the flat file still gets overwritten. I have SP1 with no hotfix.

Any further inputs will be much appreciated.

Thanks,

M.Shah

|||I got it to work. Not absolutely certain on what was wrong.

Overwrite Excel spreadsheet

Hi,

I have a DTS package that imports a file with data,
performs several checks,
puts all invalid rows in a table,
exports the table to a excel spreadsheet.

My problem is that it appends the data every time. I want it to overwrite the existing spreadsheet. I can't find any option for that.

I use the "Transform Data Task" to do this.

Any suggestions?Do an EXECUTE PROCESS Task and supply a DELETE Command...

But I didn't think it did an append...|||I had the same problem this week. I made a template of the excel sheet, selected the columns needed and created a named range on the excel sheet, and added it as a linked server. Once it's linked like this you can treat the named range like a table and use a delete from statement to clear the sheet.

Run this command from QA to make the excel sheet a linked server, 'excelsource' can be any name you want to give the connection.

sp_addlinkedserver 'excelsource', 'jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'c:\temp\filename.xls', null, 'excel 5.0'

If the named range on the excel sheet is also named excelsource use

delete from excelsource...excelsource

Overwrite data file

Is there any way I can overwrite only one data file of the database with a
newer one? Both the files (old and new) belong to the same database but the
newer one has some additional data. Additional data was added in-house and
the same needs to be merged with the database at the client's site. Client's
database in the meatime has been collecting real time data which is stored on
a different file. Any help is appreciated. Thanks.No, SQL Server cannot guarantee a consistent database if it allowed such operation. And even if you
would try to force this, you will end up with a corrupt database. Just think of the correlation
between the data in the data pages and the entries in the transaction log.
So you need to look as some other means to do this, like providing the client with that data in a
file and BULK INSERT that data or similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:2B90AFBB-FEF7-430A-9D39-0CB787D2E1D3@.microsoft.com...
> Is there any way I can overwrite only one data file of the database with a
> newer one? Both the files (old and new) belong to the same database but the
> newer one has some additional data. Additional data was added in-house and
> the same needs to be merged with the database at the client's site. Client's
> database in the meatime has been collecting real time data which is stored on
> a different file. Any help is appreciated. Thanks.

Overwrite data file

Is there any way I can overwrite only one data file of the database with a
newer one? Both the files (old and new) belong to the same database but the
newer one has some additional data. Additional data was added in-house and
the same needs to be merged with the database at the client's site. Client's
database in the meatime has been collecting real time data which is stored o
n
a different file. Any help is appreciated. Thanks.No, SQL Server cannot guarantee a consistent database if it allowed such ope
ration. And even if you
would try to force this, you will end up with a corrupt database. Just think
of the correlation
between the data in the data pages and the entries in the transaction log.
So you need to look as some other means to do this, like providing the clien
t with that data in a
file and BULK INSERT that data or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:2B90AFBB-FEF7-430A-9D39-0CB787D2E1D3@.microsoft.com...
> Is there any way I can overwrite only one data file of the database with a
> newer one? Both the files (old and new) belong to the same database but th
e
> newer one has some additional data. Additional data was added in-house and
> the same needs to be merged with the database at the client's site. Client
's
> database in the meatime has been collecting real time data which is stored
on
> a different file. Any help is appreciated. Thanks.

Overwrite data file

Is there any way I can overwrite only one data file of the database with a
newer one? Both the files (old and new) belong to the same database but the
newer one has some additional data. Additional data was added in-house and
the same needs to be merged with the database at the client's site. Client's
database in the meatime has been collecting real time data which is stored on
a different file. Any help is appreciated. Thanks.
No, SQL Server cannot guarantee a consistent database if it allowed such operation. And even if you
would try to force this, you will end up with a corrupt database. Just think of the correlation
between the data in the data pages and the entries in the transaction log.
So you need to look as some other means to do this, like providing the client with that data in a
file and BULK INSERT that data or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:2B90AFBB-FEF7-430A-9D39-0CB787D2E1D3@.microsoft.com...
> Is there any way I can overwrite only one data file of the database with a
> newer one? Both the files (old and new) belong to the same database but the
> newer one has some additional data. Additional data was added in-house and
> the same needs to be merged with the database at the client's site. Client's
> database in the meatime has been collecting real time data which is stored on
> a different file. Any help is appreciated. Thanks.

overwrite backup don't overwrite the backup device

I created a plan for a backup. This first truncates the log and after backups the data with overwrite mode and after backups the transaction log with overwrite mode.
The process is using different backup devices for the data and log backups.
The first 2 step is success (truncate and data backup) but in the last step the backup process don't overwrite the backup device. Why ?It had been set to Full databases, after i set to specified database...it was success.
So if I'm setting to full databases, it's can't overwrite the log backup device (only append)...
sneaky |||

Hi Molnarcs,

Just FYI

The backup device can be overwritten even when doing the log backups.

Jag

|||when the backup was set to full databases then in the form the mode was overwrite but if i checked the sql statement (plan's history log- view T-SQL)...in the script was the "backup log model... NOINIT...backup log db_log....NOINIT"...
after when i set to specified database and i choose the db in the script was "backup log db_log INIT" so it didn't have inconsistency between the form and the ran sql script.
(sorry my english)|||

Hi Molnarcs,

you r right you have to secify INIT option with backup log.

Jag

overwrite a record on the subscriber

Hello. I have a table which I replicate to server B. Does it pose any
problems if I update the table Server B, then replication overlays the rows
a minute later? Someone told me that replicated tables keep track of the
last date/time the row was updated, but I thought that was only the
published table. Is this true?
Thank you,
Steve
You are free to update tables on the subscriber if the are part of a
transactional or snapshot publication.
With merge replication the change will make their way back to the publisher.
If you insert records with transactional replication on the subscriber you
may get a primary key violation. If you are ok with this, you can change
your profile of your distribution agent to continue on data consistency
errors. To do this expand Replication monitor in EM, expand the Replication
Agents folder, expand distribution agents folder, right click on your
distribution agent, and select agent profiles. Then select the continue on
data consistency errors profile. Stop and start your distribution agent.
Keep in mind you have lost database consistency between your publisher and
subscriber.
For most replication solutions this is NOT a good thing, but your particular
solution might benefit from it.
"SteveS" <ssinger@.trendmls.com> wrote in message
news:%23uznj2cFEHA.3080@.tk2msftngp13.phx.gbl...
> Hello. I have a table which I replicate to server B. Does it pose any
> problems if I update the table Server B, then replication overlays the
rows
> a minute later? Someone told me that replicated tables keep track of the
> last date/time the row was updated, but I thought that was only the
> published table. Is this true?
> Thank you,
> Steve
>

overvue

i want to build an xml based, driven web site.......... with sql......... in c#.

However i looked into visual web developer over a couple of weeks and

liked some of the display tools......then when i went back into c# i noticed

some of the display tools werent available and vis versa.

Also in vis web dev there does not seem to be the "under the bonnet"

dataset...person binding source etc.

The question is which way should I go?

Can I build and refine a sql in c# and then drop it into vis web dev or am I

just making a lot of work for no reason.

I just want to start correctly and not end up in some sort of swamp.

I don��t know if I got your ight, but the drag and drop functionality is mainly available through the GUI designer of the webforms / controls. After dragging and dorpping the controls on the form the code behind will automatically produced and can be changed afterwards.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens.... thanks again

have included this time the exceptions .....is this any help.

overview query

I'm trying to write a query to get a table that contains some basic information such as table names, table sizes (in rows) and such.

I am able to do a query to get the table names:
SELECT name FROM dbo.sysobjects WHERE type='U' and name !='dtproperties' ORDER BY name

This shows a listing sorta like:
table1
table2
table3
table4

I would like to do a
SELECT COUNT(*) table1
SELECT COUNT(*) table2
SELECT COUNT(*) table3
SELECT COUNT(*) table4

so that my output looks like
table1 13
table2 47
table3 0
table4 93

anyone know a way i can do this in 1 sql query, or know a way to use multiple sql statements with an asp.net datagrid?don't know of a single select to handel your request but could yo ucall a stored procedure from your ASP page?

create procedure TableRowCount
as
declare @.TableName sysname
create table #Tmp (TableName sysname, [RowCount] int)
select @.TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties'
while (@.TableName is not null) begin
insert into #Tmp
exec('select ''' + @.TableName + ''', count(*) from ' + @.TableName)
select @.TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties' and [name] > @.TableName
end
select cast(TableName as varchar) as TableName, [RowCount] From #Tmp
return 0
go

exec TableRowCount
go|||thanks! you're a genius. now i just have to figure out what all that means. i'm not used to stored procedures, but that gives the output i need, in the format i need it.

Thanks again!|||Basicaly, I created a temporary table to hold the results of counting the number of records in a table, then stepped through the sysobjects table looking for records that matched your original where clause. Once a table was found I did the count and stuffed the result set into the temp table. When there were no more table to count I returned the result set. All of this is basic stuff and Books Online will answer any questions you have.

I should have put comments in the code!|||na, it's fine how it was, because I understood most of it, and i'm just a sql beginner.

I just didn't understand the #Tmp being a temporary table, since i'd never seen those, but that's the idea i was going to try, but keeping track of the table was going to be a pain.

Hopefully now I'll be able to finish my application :)|||would #Tmp be what i'm hearing be referred to as a cursor? My boss does a lot of programming, and suggested that i make a cursor, but i had never heard that terminology.|||No #Tmp is a sesion temporary table. Once you log off the table is destroyed.

You can read up on cursors in Microsofts's Books Online. The have their uses but are VERY resource intensive.

The solution I posted is an alternative to a cursor.|||ok cool

you bet i'll be reading those ;) I'd really eventually like to do some more sql programming, it's kinda just going along with my asp.net stuff right now, but that's next on the list, since data is important to all applications ;)

thanks again paul

Overriding System Security

Hey folks!
First, if I am understanding correctly, even though I remove the
BUILTIN\Administrators from any roles and/or access in RS, they still have full
access to Report Manager, system settings and all. Is this correct?
If the above is true, has anyone found any way to circumvent this. I think this
a stupid concept. Just because a user is in the local Administrators group on a
server, doesn't mean he/she knows how to manage RS.
Thanks in advance.A local administrator can do just about anything on the box, including
format the hard drive. This does not mean the user should do it or knows
that he should not do it. That is why everyone is not an administrator.
Same applies to RS. Does the user need to be an administrator on the box?
If you remove the Builtin Administrators, they will not have full access to
RS. They can still do admin type work on RS, but will find they cannot view
reports and many other things.
--
| Date: Tue, 28 Jun 2005 10:01:41 -0500
| From: Darrell <Darrell.Wright.nospam@.okc.gov>
| User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Overriding System Security
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hey folks!
|
| First, if I am understanding correctly, even though I remove the
| BUILTIN\Administrators from any roles and/or access in RS, they still
have full
| access to Report Manager, system settings and all. Is this correct?
|
| If the above is true, has anyone found any way to circumvent this. I
think this
| a stupid concept. Just because a user is in the local Administrators
group on a
| server, doesn't mean he/she knows how to manage RS.
|
| Thanks in advance.
||||Brad Syputa - MS wrote:
> A local administrator can do just about anything on the box, including
> format the hard drive. This does not mean the user should do it or knows
> that he should not do it. That is why everyone is not an administrator.
>
Good point.
> Same applies to RS. Does the user need to be an administrator on the box?
>
There are a number of AD groups that these users are in that have admin rights
on the server so they can perform various maintenance tasks.
> If you remove the Builtin Administrators, they will not have full access to
> RS. They can still do admin type work on RS, but will find they cannot view
> reports and many other things.
As I said, the BUILTIN\Administrators was removed from RS altogether. Is there
any way, perhaps through IIS, that the local admins could have their access to
RS restricted?
> --
> | Date: Tue, 28 Jun 2005 10:01:41 -0500
> | From: Darrell <Darrell.Wright.nospam@.okc.gov>
> | User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
> | X-Accept-Language: en-us, en
> | MIME-Version: 1.0
> | Subject: Overriding System Security
> | Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> | Content-Transfer-Encoding: 7bit
> | Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
> | Lines: 1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hey folks!
> |
> | First, if I am understanding correctly, even though I remove the
> | BUILTIN\Administrators from any roles and/or access in RS, they still
> have full
> | access to Report Manager, system settings and all. Is this correct?
> |
> | If the above is true, has anyone found any way to circumvent this. I
> think this
> | a stupid concept. Just because a user is in the local Administrators
> group on a
> | server, doesn't mean he/she knows how to manage RS.
> |
> | Thanks in advance.
> |
>

Overriding repeating totals from non-referenced slicer dimension

I've run across what appears to be a change in the default behavior of Analysis Services from 2000 to 2005. My question is how to get the default behavior to revert to the AS 2000 method.

If you have a cube with multiple measure groups or multiple linked cubes, queries that try to slice a measure using a dimension that is not related to the measure result in the total amount for any other slicers being repeated across the "invalid" slicer.

For example, the following query slices the reseller amount by calendar year and employee department. The results are as expected. Only the Sales department has any totals for the measure and all other departments return null.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Reseller Sales Amount] );

The next query is identical, except it using the Internet Sales Amount measure, which comes from a measure group that does not have a link to the Employee dimension. The result is the total amount for internet sales is displayed across all employee departments. In AS 2000, the results would have been null or empty.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Internet Sales Amount] );

At one level I can agree with the change, but non-the-less, it is a change in the default behavior, and users hate it. Is ther any way to revert this behavior back to the AS 2000 approach?

Thanks!

What you are running into is the fact that AS2K5 now applies ValidMeasure() automatically. You can revert back to AS2K behavior by setting the "IgnoreUnrelatedDimensions" property associated with your measure group to "False".

HTH,

- Steve

|||

Steve,

Many thanks! I knew it would be something simple, but was getting tired of looking...

Overriding Defaults

I have a stored procedure that has @.BeginDate and @.EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @.BeginDate and GetDate()+10 for the @.EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'

What I assume is that if I override the default, the stored procedure will process what I put in the @.BeginDate and @.EndDate parameters.

The where statement looks like:

and (m.BeginDate >= @.BeginDate) and (m.EndDate <= @.EndDate)

I'm using Reporting Services 2005 and SQL Server 2005.

Thanks, Iris

I have figured out my own problem. In my where statement, in the stored procedure, I put in a case statement that calculates for today, yesterday, etc. for the BeginDate and EndDate parameters. I then created available values for today, yesterday, etc. I made the default for both parameters 'Today'. Then when I created my linked report from my master report, there was a drop down to select my date range options.

Thanks, Iris

Overriding any mdx statement using product line 'A' to also include product line 'B'

Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'? I started to look at the Scope keyword, but I'm at a loss for understanding it just yet.

The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Q:Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'?

A:

You can put set in Scope:

Scope ({[Product].[Product Line].[Accessory], [Product].[Product Line].[Components]});
This = 100;
END SCOPE;

Q: The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Mosha fixed me here. I thought it is hard to do, but he showed in the following post.

Vidas Matelis

|||

Well, you can always do the following:

Accessory = Accessory + Components;

But this will give you double counting of Components at the All level. To make this apply after 1/9/2007 - you can do:

SCOPE [1/9/2007] : NULL;

Accessory = Accessory + Components;

END SCOPE;

|||

Using the unique name for all of those items and then adding the scope script works, thanks.

Is there anyway to tackle the 'all' value being incorrect because of the duplication?
I tried to add another scope, but it didn't help - probably because all is always the aggregate of its children:

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

One way to deal with 'All' would be to do

FREEZE [All Products];

Before writing assignments on levels below All in Products dimension. This, of course, assumes that there are no other changes which may affect All further in the script.

|||Sweet, thanks!|||Searching through the forums, I have found that I have a use for this as well. The only exception is I need the calculation to work for certain calculated members and not all measures and calculated members. I tried adding another SCOPE statement with my specific measures, but it didn't work as I expected.
Here's an example of the code I have that works for all measures. It would be appreciated if I could get this updated to work for 3 specific measures. thanks

FREEZE [All Products];

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

FREEZE [All Products];

SCOPE ({Measure1, Measure2, Measure3}, [1/9/2007] : NULL);

Accessory = Accessory + Components;

END SCOPE

|||Works perfect. I realized I was trying to use calculated members instead of the measures in which they were derived.

Thanks for the help.

overriding aggregate value of a measure to only total based on the children of the an attributes

Going off of Adventure Works, there's a product attribute:

[Product].[Product].[Product]

and now I also have an old product attribute:

[Product].[Old Product].[Old Product]

(people wanted to see the before and after view of different product assignment).

I also have a estimate of sales for each product and old product. When using just this measure - that amount is around twice as much as before - that's ok, but when using just product or just old product, I just want to see the sum of its children.

How could one define a calculated measure so that if the current level is [Product].[Product].All, then the aggregate should be the sum of the children?

This is sort of how I'd be using it:

select [estimated sales] on 0, [Product].[Product].[Product] on 1

from [Adventure Works]

the all line should be the sum of the measure used for all the children (new behavior) and each product row would have the the estimated sales - so this would stay the same.

thinking about this a little more - the 'All' level would be the curren tlevel if the attribute wasn't even used, so I'm not sure what to do to really depict which product attribute was added. And if a person added both? I'd probably want to just show the product totals.

What I'm going to try for now is add another dimension measure with an attribute defaulted to 1 and in the estimated sales, I'll add a 1 to the new records and a 0 to the old ones. This should address the issue that I have.

Thanks!

Override SQL character

I'm trying to search for all records that contain a quotation character in the database. These records were migrated from a mainframe system.

I use the following command:
%'%

The results are:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' order by cliLastName, cliFirstName, cliBirthName'.

/ladds/lib/getrecordset.asp, line 6

Is there a way to override the quotation character temporarily?

Thanks ...You just have to escape the character. Use this:

like '%''%'

That is, two single quotes, and not a double quote.|||Thanks ... worked like a charm!

override labels for boolean report parameter

I'm trying to override the true/false labels you get when you create a
boolean report parameter. I've tried creating a dataset that I use for my
available values but that didn't work either... Has anyone ever done this or
have any ideas...If you are using the reportviewer you con do that by applying custom language
to it.
look for localysing the report viewer and you will find what you are looking
for.
"Alien2_51" wrote:
> I'm trying to override the true/false labels you get when you create a
> boolean report parameter. I've tried creating a dataset that I use for my
> available values but that didn't work either... Has anyone ever done this or
> have any ideas...

Override Identity Column using Datatable

I need copy a table from a remote (hosted) SQL 2000 database server to my local machine. I don't have access to backups and am unable to correctly configure my local machine to add a linked server. So I plan to retrieve the data to a datatable, copy it in code and save it to my local server. But the table contains an identity column which I will need to insert the values manually so they match the original.

Can anyone tell me how I can set the datatable's save to use my manual values instead of the autonumber value?

Thanks.

there are two options

1. First remove the constraint on identity column, then import the data, after that apply the constraint on iidentity column.

2. Add another column for it,

|||

SET IDENTITY_INSERT {YourTableName} ON

INSERT INTO {YourTableName} ....

SET IDENTITY_INSERT {YourTableName} OFF

|||

Thank you for the response Motley, but I don't want to have to create SQL statements; I want to do this directly in code using a datatable. Do you know how to do that?

|||

If you do not want to write sql query, then I suggest to go for answer1, turn off identity feature, import the data and turn it on.

|||

You would use the "SQL Statements" I gave above in a sqldataadapters's updatecommand.

|||

Hi,

Actually the code is also calling the SQL statements to do the update. So you will always need some SQL Statements for updating.

I suggest you use the way Girijesh has provided. Turn off the identity contraint off and import data.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Override constraints

I have to change someones username in my database in a couple of tables. When I try to make any changes, I get an error that there is a constraint. Is there a way to make the changes without removing all the constraints then replacing them?
Thanksheh. sucky. take a look at your constraints to see if they're really necessary. Or follow the correct insertion plan so the contstraints don't block you.

if it weren't 2000, I'd say SET DISABLE_DEF_CNST_CHK, but that's no longer supported.

Override Checking...?

Is it possible to create a view in TSQL that references a table that cannot
be verified.
Is there some sort of override.
I need to create a view that refences a table in another db. The other db ca
nnot be available during creation.
Thanks, RobertThere's no override option for this. SQL Server need to present meta-data fo
r your view in the
system tables, and that meta-data is derived from the table(s) that the view
references. Perhaps you
can create that other database temporarily, just so that you can create the
view?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:%23Zc32b6FGHA.3100@.tk2msftngp13.phx.gbl...
Is it possible to create a view in TSQL that references a table that cannot
be verified.
Is there some sort of override.
I need to create a view that refences a table in another db. The other db ca
nnot be available during
creation.
Thanks, Robert

Over-ride a trigger

I have a table which, until now had an update trigger only.
That trigger was used to record the record as it existed BEFORE the change,
and who made the change. It works fine.
Now I have a situation where a program will INSERT a row into the table
(which now has a new field). The value of the field is calculated. I have
two choices,
1) Re-write a lot of code to calculate the value before the insert
2) Write a simple insert trigger to calculate the value and update it in
the table.
Problem:
If I use the insert trigger to update the value, I am doing an update. The
update trigger will fire. I do not want the update trigger to fire when this
operation is performed.
Is there a way I can add code to the Insert trigger to tell it that when it
updates the value it should prevent the update trigger from firing'
Thanks.Roger,
If the value is calculated:
Can you use a calculated column?
Can you calculate the value in the select statement?
AMB
"Roger Twomey" wrote:

> I have a table which, until now had an update trigger only.
> That trigger was used to record the record as it existed BEFORE the change
,
> and who made the change. It works fine.
> Now I have a situation where a program will INSERT a row into the table
> (which now has a new field). The value of the field is calculated. I have
> two choices,
> 1) Re-write a lot of code to calculate the value before the insert
> 2) Write a simple insert trigger to calculate the value and update it i
n
> the table.
> Problem:
> If I use the insert trigger to update the value, I am doing an update. The
> update trigger will fire. I do not want the update trigger to fire when th
is
> operation is performed.
> Is there a way I can add code to the Insert trigger to tell it that when i
t
> updates the value it should prevent the update trigger from firing'
> Thanks.
>
>|||We cannot use a calculated column as the values must not change and the
values used to calculate can change over time.
We are currently using select statements that calculate but the process is
labourious and complex so we are trying to force consistent data with lower
cpu utilization (One insert is read many many times)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B0B63AC0-5557-4C66-83DE-BBFC526E8C44@.microsoft.com...
> Roger,
> If the value is calculated:
> Can you use a calculated column?
> Can you calculate the value in the select statement?
>
> AMB
> "Roger Twomey" wrote:
>|||Roger,
If I understood, you calculate the value during the insert but you want it
to be static after it is done?, In this case you can stop the update trigger
.
...
if update(calculated_column) return
...
If you do not use bulk insert, then I would recommend to insert the row
using a stored procedure. If as you said, the calculation is labourious and
complex, doing it in a trigger will make the transaction longer and will
impact the performance of the engine and scalability of the db.
AMB
"Roger Twomey" wrote:

> We cannot use a calculated column as the values must not change and the
> values used to calculate can change over time.
> We are currently using select statements that calculate but the process is
> labourious and complex so we are trying to force consistent data with lowe
r
> cpu utilization (One insert is read many many times)
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:B0B63AC0-5557-4C66-83DE-BBFC526E8C44@.microsoft.com...
>
>|||That is true, but the impact will only occur on insert, not on every read.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3CF33806-F33D-433E-8305-0450AF8E938B@.microsoft.com...
> Roger,
> If I understood, you calculate the value during the insert but you want it
> to be static after it is done?, In this case you can stop the update
> trigger.
> ...
> if update(calculated_column) return
> ...
> If you do not use bulk insert, then I would recommend to insert the row
> using a stored procedure. If as you said, the calculation is labourious
> and
> complex, doing it in a trigger will make the transaction longer and will
> impact the performance of the engine and scalability of the db.
>
> AMB
> "Roger Twomey" wrote:
>|||Thanks for the tip (If update(calculated_column) return)
I think that should work.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3CF33806-F33D-433E-8305-0450AF8E938B@.microsoft.com...
> Roger,
> If I understood, you calculate the value during the insert but you want it
> to be static after it is done?, In this case you can stop the update
> trigger.
> ...
> if update(calculated_column) return
> ...
> If you do not use bulk insert, then I would recommend to insert the row
> using a stored procedure. If as you said, the calculation is labourious
> and
> complex, doing it in a trigger will make the transaction longer and will
> impact the performance of the engine and scalability of the db.
>
> AMB
> "Roger Twomey" wrote:
>|||On Mon, 28 Feb 2005 13:07:46 -0500, Roger Twomey wrote:

>Thanks for the tip (If update(calculated_column) return)
>I think that should work.
Hi Roger,
That will only work if the calculated_column is never updated from other
sources than the insert trigger.
Other options to investigate are:
* Check if it's possible to disable nested triggers (note: this can only
be done at the server level; it will affect all databases on the
server).
* Checking the value of TRIGGER_NESTLEVEL() (or maybe even the value of
TRIGGER_NESTLEVEL(object_ID('upd_trigger
'))).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Override a foreign key constraint

A website that I'm working on has users sign in and keeps a log of the pages they go to. The log table has a foreign key in it that links to the username is the users table. I need to update the username for one of the users but the foreign key is preventing me from doing so. What is the benefit of having a foreign key like this? Can I delete it to update the username or is there a better way?

Foreign Key constraints ensure the integrity of the data is maintained. If there was no FK in place. then it would be easy to get orphaned and inconsistent data. In your example, if you just update a username to be Bob from Terry then all the records associated with Terry in the log table will now have no link back to the users record ie the Terry records will be orphaned.

Soooooo, in answer to your question, you can drop it make your update but it will throw an error when you try and recreate the FK unless you update the log records too.

You can create these constraints with a cascading updates/deletes which will filter your changes down to the child tables but in this case, it looks like its turned off.

I'd suggest just doing the following

Psuedo code:

UPDATE Log set Username = Bob WHERE username = Terry

UPDATE user set username = Bob WHERE username = Terry.

HTH!

Overloading add/subtract operators for CLR UDT

Hi,

I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I��m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:

Invalid operator for data type. Operator equals add, type equals Vector.

The following fragment does work:

DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector;

SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector)

SELECT @.v1 'v1', @.v2 'v2', @.v1.[Add](@.v2) 'v1 + v2'

And this fragment does not work:

DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector;

SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector)

SELECT @.v1 'v1', @.v2 'v2', @.v1+@.v2 'v1 + v2'

I guess that SQL Server is not aware of the operators�� overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @.a + @.b instead of @.a.[Add](@.b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?

Maxim

? No, unfortunately overloading in SQLCLR is not possible. By the way, I prefer to do statics for Add/Subtract/etc, so instead of: @.a.Add(@.b) You'd do: type::Add(@.a, @.b) That way it's a little bit closer to the "a + b" syntax... But obviously not quite the same. You'll also still have to create your own UDA. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Maxim Michtchenko@.discussions.microsoft.com> wrote in message news:03e7313f-081c-44f5-89f0-ce44d7de911d@.discussions.microsoft.com... Hi, I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I��m trying to use +/-/* operators in T-SQL over my UDT it returns the following error: Invalid operator for data type. Operator equals add, type equals Vector. The following fragment does work: DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector; SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector) SELECT @.v1 'v1', @.v2 'v2', @.v1.[Add](@.v2) 'v1 + v2' And this fragment does not work: DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector; SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector) SELECT @.v1 'v1', @.v2 'v2', @.v1+@.v2 'v1 + v2' I guess that SQL Server is not aware of the operators�� overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @.a + @.b instead of @.a.[Add](@.b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field? Maxim|||

Thanks,

I guess I'll have to accept that. By the way I agree with you - static method looks clearner.

Maxim

|||

I have a method that is overloaded. One takes 3 parameters and one takes 5. If overloading does not work, why does the CLR Function that I am trying to create work if I pass in 5 parameters? Does it take the method with the most parameters?

Overloaded Stored Procedure

Hi

I want to create two stored procedures with the same name but accepting different params. Lets say Procedure A can accept param1, Procedure A can also accept param1,param2.

Is there any way SQL Server supports overloaded procedures with different sigantures.

Regards
ImtiazYou can use a feature called numbered stored procedures. So you can create somesp;1 and somesp;2. But I would not recommend doing this since the feature is being deprecated. In general, for data access or modifications you want to keep the TSQL interfaces simple to use.|||Thanks..As expected u had replied.....|||Can we also have something like overloaded functions in SQL 2005.|||No, but optional parameters are supported: CREATE PROC YourProc @.Param1 INT, @.Param2 INT = 0 AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Imtiaz@.discussions.microsoft.com> wrote in message news:5dcaf46b-f56a-452b-b9a3-dc23f7c4e7a9@.discussions.microsoft.com...HiI want to create two stored procedures with the same name but accepting different params. Lets say Procedure A can accept param1, Procedure A can also accept param1,param2.Is there any way SQL Server supports overloaded procedures with different sigantures.RegardsImtiaz

Overload resolution failed because no accessible 'Round' can be called without a narrowing con

Can someone translate my error to English please:

Overload resolution failed because no accessible 'Round' can be called without a narrowing conversion:

You may need to explicitly convert the expression result into a numeric data type. For instance, CDbl() would convert into a double.
E.g.: =Round( CDbl( .....) )

-- Robert

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

Overlay of textboxes are not rendering properly

In the page header I have a rectangle that has 2 textboxes on the same line and both are the full length of the page. One textbox has text centered and the other has text right justified. When I render report in Studio or print it, the text appears on the same line and justified like I want, but when the report is rendered when I call the report by URL, the textboxes wrap, like it's taking the width of each textbox in account. Has anyone seen this? Any suggestions on how to overlay text on the same line and the text keep their own individual characteristics?

I do not believe that you can overlap two textboxes. One will push the next one down to the next line. Can't you make one text box only long enough to allow the text that you need, then Format that text box ittself to align Format-Center in Form-Horizonally. Create the second text box, only as large as you need to hold that text that you are displaying. Then put that text box all the way to the right of your page and align the text in it to right.

|||Yes, that sounds like that would work, but how do you format-center in the form-horizonally?|||Create the first text box and make it only large enough to display the text that you need it to display. Click on the text box itsself to select it, go up to the menu bar and click format, slide down to center in form, select horizontally. This will center that text box in the middle of your page. Then you could actually choose text align in your properties and middle align the text within the text box itsself if you needed to do that. Hope this helps.|||

Kim,

Thanks for trying, but that won't work for me because of a couple reasons: 1) The text is dynamic, so I have no idea on how wide to make my textbox. 2) Even if my text is static and I shorten them up and center the one text box, they still overlap.

Overlay lines on x axis

I need to overlay the bar lines on a chart in reporting services. I
have actual and budget numbers that need to be displayed, but they
need to be overlayed, not side by side as the column chart does by
default. Any ideas on how to do this?
Thanks,
JoshDid you look at this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
It is possible to combine column and line charts. However, this is not
possible for bar and line charts.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Josh Harris" <joshua_c_harris@.hotmail.com> wrote in message
news:eda768cf.0411241126.4e25fb10@.posting.google.com...
> I need to overlay the bar lines on a chart in reporting services. I
> have actual and budget numbers that need to be displayed, but they
> need to be overlayed, not side by side as the column chart does by
> default. Any ideas on how to do this?
> Thanks,
> Josh

Overlay charts

Hello all.
I need to overlay two charts (column & line) & I keep seeing references
to this technique on these groups but no explanation of how to do it
(nor in the help). At the moment when I overlay charts the top one is
visible but not the bottom on,e & I can't see how to make the
background transparent.
Has anyone does this before?
All help appreciated.
Cheers
AustinHTML renderer (which is used by report viewer in Report Manager) does not
support overlayed controls. Although if you render such report to pdf or jpg
you will get charts overlayed.
"AustinRathe" <austinr@.gmail.com> wrote in message
news:1115648678.321251.127760@.o13g2000cwo.googlegroups.com...
> Hello all.
> I need to overlay two charts (column & line) & I keep seeing references
> to this technique on these groups but no explanation of how to do it
> (nor in the help). At the moment when I overlay charts the top one is
> visible but not the bottom on,e & I can't see how to make the
> background transparent.
> Has anyone does this before?
> All help appreciated.
> Cheers
> Austin
>

Overlapping Trigger Precedence

Hi,
What is the execution precedence if there exist more than one update, delete
or insert triggers
I mean if there are more than one type of trigger such as more than one
update trigger.
Also, Can we specify a precedence for them?
and last If we call an update statement in the same update trigger and for
the same table that has this trigger, Is the trigger considers itself, I
mean is it recurs or not, or is it disables itself for that execution period
?.
Thanks in advance...
Emre GuldoganThere is no guarantee as to what order they'll fire in.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Emre Guldogan" <ask me please...> wrote in message
news:e$KbhKMJFHA.656@.TK2MSFTNGP14.phx.gbl...
> Hi,
> What is the execution precedence if there exist more than one update,
delete
> or insert triggers
> I mean if there are more than one type of trigger such as more than one
> update trigger.
> Also, Can we specify a precedence for them?
> and last If we call an update statement in the same update trigger and for
> the same table that has this trigger, Is the trigger considers itself, I
> mean is it recurs or not, or is it disables itself for that execution
period
> ?.
> Thanks in advance...
> Emre Guldogan
>|||You can use sp_settriggerorder to define the first and last trigger to
execute. For the rest the order is undefined. If it matters to you then
put all the logic in one trigger.
David Portas
SQL Server MVP
--|||On Wed, 9 Mar 2005 18:11:48 +0200, "Emre Guldogan" <ask me please...>
wrote:

>What is the execution precedence if there exist more than one update, delet
e
>or insert triggers
>I mean if there are more than one type of trigger such as more than one
>update trigger.
>Also, Can we specify a precedence for them?
Hi Emre,
Already addressed by David and Adam.

>and last If we call an update statement in the same update trigger and for
>the same table that has this trigger, Is the trigger considers itself, I
>mean is it recurs or not, or is it disables itself for that execution perio
d
>?.
There are two options governing this:
1. Recursive triggers. If this is off, a trigger can't fire itself. So
if an update trigger on table AA performans an UPDATE AA command, that
specific trigger is not called. This disables only direct recursion: if
the update trigger on AA performs a DELETE FROM BB statement, and the
delete trigger on BB performs UPDATE AA, the trigger will be called
recursively.
The recursive triggers option is a database setting: it will affect all
triggers in the current database, but it won't affect other databases on
the same server. You use ALTER DATABASE to change it.
2. Nested triggers. If this is off, statements executed from a trigger
don't fire any trigger at all.
The nested triggers option is a server setting: it will affect all
triggers in all databases on the server. You use sp_configure to change
it.
Other options to prevent recursive execution of triggers for specific
triggers only are:
* TRIGGER_NESTLEVEL() - check this to find how many nested trigger
executions there currently are.
* TRGIGER_NESTLEVEL(object_ID('trigger_nam
e')) - check this to find how
many times the specified trigger is currently nested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)