Padding fields with zeros.

I am trying to create a field in SQL query builder that takes the input number 12345 and formats it 0000012345. In MSAccess there is a great Format() function which allowed this to be done easily, but I cant find an equivalent in SQLServer. Can anyone provide me with a new function and possibly a sample? Thanks in Advance,

Michael.

try this

SELECT cast(replace(str(12345 ,10),' ','0') AS char(10))

OR write Stored Procedure as you need ( i think)

CREATE FUNCTION fnPadLeft

( @.PadChar char(1), @.PadToLen int, @.BaseString varchar(100) )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @.Padded varchar(1000)

DECLARE @.BaseLen int

SET @.BaseLen = LEN(@.BaseString)

IF @.BaseLen >= @.PadToLen

BEGIN

SET @.Padded = @.BaseString

END

ELSE

BEGIN

SET @.Padded = REPLICATE(@.PadChar, @.PadToLen - @.BaseLen) + @.BaseString

END

RETURN @.Padded

END

Now try this

SELECT dbo.fnPadLeft('0', 10, 12345) ==> 0000012345

Parameters:

First = Padding character

Second = Total Length

Third = Your Column

Best of Luck

Regards,

Thanks.

Gurpreet S .Gill

|||

You can also try this

select Replicate('0',10-Len(Column1))+ Convert(Varchar,Column1) from Table1

I am assuming Column1 in the table is INT. If it is varchar, no need to for conversion.

|||

SELECT RIGHT('0000000000' + CONVERT(VARCHAR(10),col1) , 10) FROM table1

|||

Thanks all! I will give it a shot...

Michael

Package Stuck on "Pre-Execute phase is beginning."

Hi all,

I have a monster SQL query (SQL Server connection) that I'm trying to export to flat file. My package appears to just stick on the "Pre-Execute phase is beginning" stage. (I left it running overnight, and it's still going... no error message, I think it's just hung. If I include a "Top 10" in the my source query it completes without a problem. I'm wondering if it's an out-of-memory issue, but when I've run into this in the past there have been error messages generated. I've tried using both a Data Reader and an OLEDB Datasource. Does anyone have any ideas of what's going on and how I might fix it?

Thanks!!!

JessYou should run the query in management studio and look at its explain plan... Chances are that it has nothing to do with SSIS. Perhaps you have locks in place that are blocking parts of this query such that it never completes?

You could always wrap the query into a view and use the view as the source to your package. That might help the pre-execute phase.

Also, try setting your DelayValidation property to TRUE for the connection manager that's being used for this query.|||

Thanks for your response. I finally got it working. Our off-shore team actually wrote the query for this project and it was fairly convoluted. I just have the pleasure of actually trying to RUN it.

Anyhow, there was no descernable blocking from sp_who2 'active.' While switching the source query over to a view (per your suggestion), I discovered that the source sql inserted a number of records into a (non-indexed) table variable via a cursor. Placing this data into a permanent, indexed table seems to be the change that got the package working.

I've done some more testing, and whenever I use a table variable in the source query I seem to get stuck at "Pre-Execute." Is this by-design?

Thanks again for your input.

Jess

|||

Hi Jessica,

It is never by design to get stuck.

Pre-execute is the time when your queries get prepared and all the problems in them are likely to show up at that point. Looking at the SQL Profiler might help you figure out what exactly is causing issues while queries are processed.

HTH.

Package attempts to query source db constantly when editing

Trying to set up a tranform task between a mysql db using and ADO.NET connection and sql server.
My query to pull from the mysql db is something like "select x,y,z from table where last_updated" > @.User::LastUpdated. This command is set up as an expression for the Data Flow Task and is the value for the [DataReader Source].[SqlCommand]

I have two questions.

Why does the package attempt a query against the mysql database all the time?
And Why is the query attempting to pull the entire table instead of having any regards for my where clause?

I've even added where last_updated > greatest('2006-08-15', '" + @.User::LastUpdated to attempt to get it a where clause even when the parameter isn't set yet.

What is the trick? This is not feasible when pulling from multi-million row tables.

lorijean44 wrote:

Why does the package attempt a query against the mysql database all the time?

Can you qualify what you mean by "all the time"? Do you mean at execution time or design time? What is the package doing when it issues these queries?

lorijean44 wrote:

And Why is the query attempting to pull the entire table instead of having any regards for my where clause?

How have you determined that it is pulling from the entire table?

-Jamie

|||Thank you for your response.
"All the time" means while in design mode, if I click on the Data Reader Source to edit it, when I go to column mappings, when I close the data reader source gui, when I attempt to go back to the control flow window.

I determined it was pulling from the entire table by looking at "show processlist" on the mysql db. I did discover that I needed to do "show full processlist", so that I could see the where clause. But once I figured that out, I could see that it was pulling "select x,y,z from table where last_seen > '1900-01-01'". 1900-01-01 is our default value anytime we are using datetime.

I ended up cheating the system by having it do "select x,y,z from table where last_seen > greatest('2006-08-15', @.last_updated)", so that when it ran the query it would look like "select x,y,z from table where last_seen > greatest('2006-08-15', '1900-01-01')", and not pull so much.
I guess I could set the default to be something more recent. I guess I'm just trying to figure out why does it run the query so often.

Thanks for your time.
-Lori

P

Hello -

I have a report based on a query that currently uses the = operator and I'd like to change it to the LIKE operator and allow a wildcard...using the %.

So...from something like, "....WHERE zipcode = @.ZipCode" to "...WHERE zipCode = @.zipCode plus the wildcard character.

Is this possible?

Thanks,

- will

You cannot do it direcly from query , You can write procedure and then call this procedure from report.

alter procedure TestReport(

@.param varchar(20)

)

as

declare @.SQLstring as nvarchar(200)

set @.SQLstring='select getdate() where zipcode like ''%'+@.zipcode+'%'''

exec sp_executesql @.SQLstring;

|||

Will Durning wrote:

Hello -

I have a report based on a query that currently uses the = operator and I'd like to change it to the LIKE operator and allow a wildcard...using the %.

So...from something like, "....WHERE zipcode = @.ZipCode" to "...WHERE zipCode = @.zipCode plus the wildcard character.

Is this possible?

Thanks,

- will

You can very well achieve this in the query itself. Eg: select * from <table-name> where <column-name> like '%' + @.column-name + '%'

|||

Thanks for the replies.

I ended up using a stored procedure and just concatenate the '%' to the incoming parameter. I could not get it to work with an inline sql query in my report....and besides, I was planning on using stored procs anyway.

Owner of the table

I've got this question:
Is it possible to assign the 'current owner' using a query like the
following one:

set current schema OWNER1 (in DB2 sql, this allow me to reference the tables
without an explicit indication of the owner (es: after this query I can
simply write 'Select * from dummy', instead of 'Select * from
OWNER1.dummy')).

Thank you
Federica"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckga73$t06$1@.atlantis.cu.mi.it...
> I've got this question:
> Is it possible to assign the 'current owner' using a query like the
> following one:
> set current schema OWNER1 (in DB2 sql, this allow me to reference the
> tables
> without an explicit indication of the owner (es: after this query I can
> simply write 'Select * from dummy', instead of 'Select * from
> OWNER1.dummy')).
> Thank you
> Federica

Not really - in MSSQL, it's considered good practice to always fully specify
the object owner. There is a SETUSER command which allows a sysadmin to
impersonate another user, but it's deprecated and it doesn't support Windows
authentication which is the preferred authentication method.

Simon|||"Simon Hayes" <sql@.hayes.ch> ha scritto nel messaggio
news:416bc568$1_3@.news.bluewin.ch...

> Not really - in MSSQL, it's considered good practice to always fully
specify
> the object owner. There is a SETUSER command which allows a sysadmin to
> impersonate another user, but it's deprecated and it doesn't support
Windows
> authentication which is the preferred authentication method.
> Simon

Thank you a lot!
Fede

owner of a temp table

is there a way to query the system table in SQL Server to determine the
owner of a temp table? I doubt this is possible because it seems as
though everyone is aliased as db_owner.<dlukac@.gmail.com> wrote in message
news:1112724040.143022.24070@.z14g2000cwz.googlegro ups.com...
> is there a way to query the system table in SQL Server to determine the
> owner of a temp table? I doubt this is possible because it seems as
> though everyone is aliased as db_owner.

I'm not sure what you mean - a temp table is unique in the session that
created it, regardless of owner, login or user name (unless it's a global
temp table, of course). Perhaps you can explain what problem you're trying
to solve, or what your aim is?

Simon

Own Query language for non-technical Users, any frameworks known?

Hello

We have the challenge to introduce for our database applications a
dynmaic search for the end users. The end users are non-technical
users.

I am looking therefore for possibilities which would allow to build own
query language with expressions according to the need and skills for
the users.

I found one product so far. "EasyAsk - Decision Adviser". does somebody
know also other tools or frameworks which can be integrated?

http://www.easyask.com/product*s/decision-advisor.cfm

The environment is currently MS SQL Servers and C# Webservices.My
vision is to have a framework or library which can be seamless
integrated into our existing solutions which is currently a smart C#
clients. i.e. by using web-service or an C# API.

thanks
Mark Egloffjoes (joes@.bluewin.ch) writes:
> We have the challenge to introduce for our database applications a
> dynmaic search for the end users. The end users are non-technical
> users.
> I am looking therefore for possibilities which would allow to build own
> query language with expressions according to the need and skills for
> the users.
> I found one product so far. "EasyAsk - Decision Adviser". does somebody
> know also other tools or frameworks which can be integrated?

SQL Server itself comes with English Query, but I have never looked at
it myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OWC, Pivot & "OR" queries

this is probably a very easy question but I can't seem to find an answer...

Is there any visual way of building a query in OWC that includes a couple of conditions included as 'OR'?

basically every dimension I drop becomes a condition but they all get added as 'AND' & I can't seem to have a way to do 'OR' - even cube browser in BI studio doesn't have this option...

You can do OR between members of the same hierarchy (hopefully on the same level). Just select multiple members from the same hierarchy.|||

if it was the same dimension it'd have been easy, but it's a bit complicated since it's a role playing dimension & so even though it's the same hierarchy & same level it becomes 2 different dimensions.

something like this: DateOpened.Year = 2000 or LastRenewalDate.Year = 2000?

|||You cannot do it in OWC. You will have to write your own MDX queries to do it.

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
> >> > > > ?
> >> > >
> >> > >
> >> > >
>
>

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

Overlapping times

Hi All,
I have a table which logs calls called calls There are 3 main columns
user_id, starttime,endtime
I would like to run a query (a self-join i guess) which lists all
overlapping calls for a particular user_id.
In theory it should not be possible for a user to make 2 or more
simulataneous calls.
Any help would be greatly appreciated.
SergioYOu should only compare the start time with the data you have to see wheter
it overlaps
Select UserID from YourTable t1
inner join Yourtable t2
on userid = userid
Where t1.Starttime between t2.starttime and t2.endtime
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Sergio, try,
select c1.userid, c1.starttime as s1, c1.endtime as e1,
c2.starttime as s2, c2.endtime as e2
from calls as c1
join calls as c2
on c1.key_col <> c2.key_col
and c1.userid = c2.userid
and c1.starttime <= c2.endtime
and c1.endtime >= c2.starttime
-- not tested
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all
> overlapping calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more
> simulataneous calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Unfortunately this query returns all rows in my table
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
> YOu should only compare the start time with the data you have to see
> wheter it overlaps
> Select UserID from YourTable t1
> inner join Yourtable t2
> on userid = userid
> Where t1.Starttime between t2.starttime and t2.endtime
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Serghios" <Serghios.Florides@.elthion.com> schrieb im Newsbeitrag
> news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>|||That's because a period always overlaps with itself. You can eliminate the
self match by filtering t1.key <> t2.key, assuming you have a single column
key in the table. If the key is composite, use:
on t1.key1 <> t2.key1 or t1.key2 <> t2.key2, etc.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ODHsQrdZFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Unfortunately this query returns all rows in my table
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:ON1yhjdZFHA.1152@.tk2msftngp13.phx.gbl...
>|||This can be more complicated that it looks on the surface. An overlap can ha
ppen
in one of two ways:
1. The start date of one range is between the start and end date of another
range.
2. The end date of one range is between the start and end date of another ra
nge.
It gets a little more complicated if end dates can be null.
Assuming no nulls, you would have something like:
Select F1..Fn
From Table As T1
Join Table As T2
On T1.PK <> T2.PK
And T1.User_Id = T2.User_Id
And (
(T1.Start >= T2.Start
And (T1.Start <= T2.End Or T1.End <= T2.End))
Or (T2.Start >= T1.Start
And (T2.Start <= T1.End Or T2.End <= T1.End))
)
If the dates can be null, you'll have to decide how that should be interpret
ed.
For example, if a null end date means that there is no end, then you would w
ant
to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
Thomas
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table which logs calls called calls There are 3 main columns
> user_id, starttime,endtime
> I would like to run a query (a self-join i guess) which lists all overlapp
ing
> calls for a particular user_id.
> In theory it should not be possible for a user to make 2 or more simulatan
eous
> calls.
> Any help would be greatly appreciated.
> Sergio
>
>
>|||Think about this in reverse: Two events do not overlap if one ends
before the other begins, or if it begins after the end of other one.
Write that and then negate it.
SELECT E1.*
FROM Events AS E1,
Events AS E2
WHERE E1.event_key <> E2.event_key
AND E1.user_id = E2.user_id
AND NOT (E1.start_time > E2.end_time
OR E1.end_time < E2.end_time)
Or you can use DeMorgan's law on the last predicate:
AND (E1.start_time <= E2.end_time
AND E1.end_time >= E2.end_time)
There is also an OVERLAPS predicate in SQL-92 for this purpose,but it
is a bit tricky.|||Thomas,
Itzik's solution is simpler and equivalent. In words, it may be
easier to think of in terms of two lives. As long as people do
not die before they are born (something you would implement
as a constraint in the data model), the lives of X and Y
overlap if and only if X died after Y was born and Y died after
X was born. It seems too simple to work, but it works. Your
comment about NULL end dates is important. Here's one
solution to handle NULL meaning "not ended yet"
...
and coalesce(X.End,'99991231') > Y.Start
and coalesce(Y.End,'99991231') > X.Start
Steve Kass
Drew University
Thomas Coleman wrote:

>This can be more complicated that it looks on the surface. An overlap can h
appen
>in one of two ways:
>1. The start date of one range is between the start and end date of another
>range.
>2. The end date of one range is between the start and end date of another r
ange.
>It gets a little more complicated if end dates can be null.
>Assuming no nulls, you would have something like:
>
>Select F1..Fn
>From Table As T1
> Join Table As T2
> On T1.PK <> T2.PK
> And T1.User_Id = T2.User_Id
> And (
> (T1.Start >= T2.Start
> And (T1.Start <= T2.End Or T1.End <= T2.End))
> Or (T2.Start >= T1.Start
> And (T2.Start <= T1.End Or T2.End <= T1.End))
> )
>If the dates can be null, you'll have to decide how that should be interpre
ted.
>For example, if a null end date means that there is no end, then you would
want
>to replace the end date with an arbitrarily large date (e.g. 9999-12-31).
>
>Thomas
>
>"Serghios" <Serghios.Florides@.elthion.com> wrote in message
>news:ORD1hXdZFHA.3648@.TK2MSFTNGP14.phx.gbl...
>
>
>|||On the contrary, the do not produce the same results.
Create Table Dates
(
Id Int Primary Key
, StartDate DateTime
, EndDate DateTime
)
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
--Original post
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate Between D2.StartDate And D2.EndDate
--My overlaps version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where (D1.StartDate >= D2.StartDate
And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
Or (D2.StartDate >= D1.StartDate
And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
--equivalent to Joe's version
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
--equivalent to Joe's version using DeMorgan's law
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Notice that in the first select statement only Item#2 shows as having an ove
rlap
whereas both values show in the second select statement. The problem with on
ly
looking at the start date on a given row is that it does not account for the
End
Date of that row overlapping with an existing range. Rather, it only account
s
for the overlapped range.
I actually modified my results from the Ovelaps specification in the SQL92 s
pec.
Joe's simplified version is actually cleaner and just as accurate. Thanks Jo
e.
Think I'll use that.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
> Thomas,
> Itzik's solution is simpler and equivalent. In words, it may be
> easier to think of in terms of two lives. As long as people do
> not die before they are born (something you would implement
> as a constraint in the data model), the lives of X and Y
> overlap if and only if X died after Y was born and Y died after
> X was born. It seems too simple to work, but it works. Your
> comment about NULL end dates is important. Here's one
> solution to handle NULL meaning "not ended yet"
> ...
> and coalesce(X.End,'99991231') > Y.Start
> and coalesce(Y.End,'99991231') > X.Start
> Steve Kass
> Drew University
>
> Thomas Coleman wrote:
>|||The only downside to your approach Joe is when one range is contained within
another. Granted, we are now getting into the semantics of what it really me
ans
to "overlap". However, if you wanted "overlap or contained within", your
algorithm would not work.
For example, using the structure below imagine we have:
Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050130')
This will show that the first item overlaps the second, but not the reverse.
That can easily be fixed by adding a clause that checks for containment like
so:
Select *
From Dates As D1
Join Dates As D2
On D1.Id <> D2.Id
Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
Or (D1.StartDate >= D2.StartDate And D1.EndDate <= D2.EndDate)
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23tTYa3hZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> On the contrary, the do not produce the same results.
> Create Table Dates
> (
> Id Int Primary Key
> , StartDate DateTime
> , EndDate DateTime
> )
> Insert Dates(Id, StartDate, EndDate) Values(1, '20050101', '20050131')
> Insert Dates(Id, StartDate, EndDate) Values(2, '20050115', '20050131')
> --Original post
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate Between D2.StartDate And D2.EndDate
> --My overlaps version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where (D1.StartDate >= D2.StartDate
> And (D1.StartDate <= D2.EndDate Or D1.EndDate <= D2.EndDate))
> Or (D2.StartDate >= D1.StartDate
> And (D2.StartDate <= D1.EndDate Or D2.EndDate <= D1.EndDate))
> --equivalent to Joe's version
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where Not (D1.StartDate > D2.EndDate Or D1.EndDate < D2.EndDate)
> --equivalent to Joe's version using DeMorgan's law
> Select *
> From Dates As D1
> Join Dates As D2
> On D1.Id <> D2.Id
> Where D1.StartDate <= D2.EndDate And D1.EndDate >= D2.EndDate
>
> Notice that in the first select statement only Item#2 shows as having an
> overlap whereas both values show in the second select statement. The probl
em
> with only looking at the start date on a given row is that it does not acc
ount
> for the End Date of that row overlapping with an existing range. Rather, i
t
> only accounts for the overlapped range.
> I actually modified my results from the Ovelaps specification in the SQL92
> spec. Joe's simplified version is actually cleaner and just as accurate.
> Thanks Joe. Think I'll use that.
>
> Thomas
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uzAJechZFHA.3960@.TK2MSFTNGP10.phx.gbl...
>

Overlapping Records by DateTime

Hi guys and gals,
I need to pick your brains for a date/time series question. I'm trying to
write a query that will displays accounts, with their different account type
s
that overlap using the start and end dates.
So say I have the following records:-
AccountId - AccountType - Start - End
1 1 2006-01-01 2006-01-07
2 1 2006-01-06 2006-01-09
3 2 2006-01-02 2006-01-09
I can see that Account 1 and 2 are the same account type, but they overlap
by 1 day however account 3 is different and therefore is fine.
I need to write a query, to decipher all these account that overlap in date
of the same account type. This is part of a larger system, so you may wonde
r
why I wouldn't just place constraints to prevent this from happening, but th
e
reason is that I will allow accounts to overlap, and I have somewhere else i
n
the system a means to elect overlapped accounts based on merit which isn't
required in the query.
I've done some DDL and Inserts here, any help would be greatly appreciated.
Andy
CREATE TABLE Accounts(
AccountId int not null identity(1,1),
AccountType int not null,
UtcDateStart datetime not null,
UtcDateEnd datetime not null
)
CREATE INDEX PK_Accounts_AccountId
ON Accounts (AccountId)
GO
declare @.utcDateTime datetime
set @.utcDateTime = getutcdate()
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
SELECT * FROM AccountsIs this what you're after...
select
*
from
Accounts t1
join Accounts t2 on t1.AccountType = t2.AccountType
where
t2.AccountId > t1.AccountId
and (t1.UtcDateStart between t2.UtcDateStart and t2.UtcDateEnd
or t2.UtcDateStart between t1.UtcDateStart and t1.UtcDateEnd)
HTH. Ryan
"Andy Furnival" <AndyFurnival@.discussions.microsoft.com> wrote in message
news:893FFA23-53D8-4909-995D-C40BD71AF394@.microsoft.com...
> Hi guys and gals,
> I need to pick your brains for a date/time series question. I'm trying to
> write a query that will displays accounts, with their different account
> types
> that overlap using the start and end dates.
> So say I have the following records:-
> AccountId - AccountType - Start - End
> 1 1 2006-01-01 2006-01-07
> 2 1 2006-01-06 2006-01-09
> 3 2 2006-01-02 2006-01-09
> I can see that Account 1 and 2 are the same account type, but they overlap
> by 1 day however account 3 is different and therefore is fine.
> I need to write a query, to decipher all these account that overlap in
> date
> of the same account type. This is part of a larger system, so you may
> wonder
> why I wouldn't just place constraints to prevent this from happening, but
> the
> reason is that I will allow accounts to overlap, and I have somewhere else
> in
> the system a means to elect overlapped accounts based on merit which isn't
> required in the query.
> I've done some DDL and Inserts here, any help would be greatly
> appreciated.
> Andy
> CREATE TABLE Accounts(
> AccountId int not null identity(1,1),
> AccountType int not null,
> UtcDateStart datetime not null,
> UtcDateEnd datetime not null
> )
> CREATE INDEX PK_Accounts_AccountId
> ON Accounts (AccountId)
> GO
> declare @.utcDateTime datetime
> set @.utcDateTime = getutcdate()
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 4, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (1,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 5, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 6, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (2,@.utcDateTime, dateadd(dd, 7, @.utcDateTime))
> set @.utcDateTime = dateadd(dd, 2, @.utcDateTime)
> INSERT INTO Accounts (AccountType, utcDateStart, utcDateEnd)
> VALUES (3,@.utcDateTime, dateadd(dd, 5, @.utcDateTime))
>
> SELECT * FROM Accounts|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:578107
On Tue, 17 Jan 2006 01:41:03 -0800, Andy Furnival wrote:

>Hi guys and gals,
>I need to pick your brains for a date/time series question. I'm trying to
>write a query that will displays accounts, with their different account typ
es
>that overlap using the start and end dates.
>So say I have the following records:-
>AccountId - AccountType - Start - End
>1 1 2006-01-01 2006-01-07
>2 1 2006-01-06 2006-01-09
>3 2 2006-01-02 2006-01-09
>I can see that Account 1 and 2 are the same account type, but they overlap
>by 1 day however account 3 is different and therefore is fine.
>I need to write a query, to decipher all these account that overlap in date
>of the same account type. This is part of a larger system, so you may wond
er
>why I wouldn't just place constraints to prevent this from happening, but t
he
>reason is that I will allow accounts to overlap, and I have somewhere else
in
>the system a means to elect overlapped accounts based on merit which isn't
>required in the query.
>I've done some DDL and Inserts here, any help would be greatly appreciated.
Hi Andy,
Thanks for the DDL and the INSERTS! Made posting a breeze and answering
more fun.
In addition to Ryan's suggestion, here's another one that will work:
SELECT * FROM Accounts
go
SELECT *
FROM Accounts AS a
INNER JOIN Accounts AS b
ON a.AccountType = b.AccountType
AND a.AccountId > b.AccountId
AND a.utcDateStart < b.utcDateEnd
AND a.utcDateEnd > b.utcDateStart
The benefot of this version is that it avoids the use of OR. If the
utcDateStart and utcDateEnd columns in your real table are indexed, my
version will give the optimizer better opportunities to use that index.
Bottom line: test both for performance; choose the one that performs
best or (if there's no significant difference) the one that you find the
easiest to understand.
Hugo Kornelis, SQL Server MVP|||I would use a Calendar table and a query with a BETWEEN predicate. I
would also get a real key as an account_id with a check digit that
comforms to International banking standards instead of that silly and
dangerous IDENTITY pseudo-column.
As a matter of ISO-11179 conventions, the names should be
"start_utedate".
Use a BETWEEN predicate with a COUNT(*) > 1|||> instead of that silly and
> dangerous IDENTITY pseudo-column.
IDENTITY is a property of a column and NOT a column.
There is nothing stopping you creating a check digit based around IDENTITY
either.
There is nothing 'dangerous' about the IDENTITY 'property'.
The IDENTITY property can be successfully used to create surrogate keys or a
natural primary key where no other one may exist, for instance a message
board.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137635843.478530.301780@.f14g2000cwb.googlegroups.com...
>I would use a Calendar table and a query with a BETWEEN predicate. I
> would also get a real key as an account_id with a check digit that
> comforms to International banking standards instead of that silly and
> dangerous IDENTITY pseudo-column.
> As a matter of ISO-11179 conventions, the names should be
> "start_utedate".
>
> Use a BETWEEN predicate with a COUNT(*) > 1
>

Overhead for Aliases

Hello,

I have a question, is there any performance overhead for using Aliases in a SQL query?

I mean would there be any difference in performance between the following two statements:

Select

Students.StudentID ,

Students.Name ,

Students.Code

from

Students inner join

Countries on Students.CountryID= Students.CountryID

Where

Students.IsDeleted=1

--

Select

std.StudentID ,

std.Name ,

std.Code

from

Students AS std inner join

Countries AS cntr on std.CountryID= cntr.CountryID

Where

std.IsDeleted=1

No, not as far as I have ever seen. There should be no performance impact|||Cool. But is there any explanation why? How does SQL Server treat aliases? does it affect the execution plan or something?|||What you have said is correct: an alias has no affect on the execution plan.|||

I also agree with you..

But I worried about the following BLOG. When I try to execute on Mac it is really Random result..

http://weblogs.sqlteam.com/brettk/archive/2007/03/27/60141.aspx

|||No, aliases do not cause a performance issue.

The blog referenced shows a difference when NOT using aliases. I would suspect, but don't know, the difference is in the compile time, not execution. The compiler has to figure out what database the field is coming from, if you don't qualify it with an alias. This takes time.

Also, the datetime variable has a maximum resolution of 3.33 ms. So a test as small as the blog describes is meaningless.

Overflow Error: Visual Web Developer

I am using Visual Web Developer to design an interface to query an Access Database. Everything seems to work fine, but when I select the most intensive option I recieve the following error. As I said, everything seems to work fine, unless the user selects all the options I have made available. How do I resolve this issue without limiting the user?

Overflow

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Overflow

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80040e57): Overflow] System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105 System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91 System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +58 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68 System.Web.UI.WebControls.GridView.DataBind() +5 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

I can post my code as well. It's three group bys, two sums divided by one another on a 30mb Access Database.

Thanks!

It seems like this would be better posted to an Access or ASP.NET forum.