Layout

Just a general note. I'm a .NET developer and enjoy the setup that visual
studio gives me when editing and working with my projects. I have a dual
screen setup and like to build my environment around it to increase my
productivity. I noticed that in the release of the new SQL 2005 Management
Studio there are a number of enhancements but one that I'd like is that when
I'm working with a specific table or view or anything, I should be able to
split up the components onto my different screens so that I can maximize my
space. So perhaps to have the query on one dockable window, the table in
another and the SQL in another. If I switch to another query or stored
procedure then all of those windows should be maintained but for the new
information.
Thanks.Send an e-mail to:
sqlwish@.microsoft.com
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew Middleton" <Andrew Middleton@.discussions.microsoft.com> wrote in
message news:BB7ADCA7-E3CC-4CF9-A779-6ECD2E2EA741@.microsoft.com...
> Just a general note. I'm a .NET developer and enjoy the setup that visual
> studio gives me when editing and working with my projects. I have a dual
> screen setup and like to build my environment around it to increase my
> productivity. I noticed that in the release of the new SQL 2005
Management
> Studio there are a number of enhancements but one that I'd like is that
when
> I'm working with a specific table or view or anything, I should be able to
> split up the components onto my different screens so that I can maximize
my
> space. So perhaps to have the query on one dockable window, the table in
> another and the SQL in another. If I switch to another query or stored
> procedure then all of those windows should be maintained but for the new
> information.
> Thanks.|||> productivity. I noticed that in the release of the new SQL 2005
> Management
> Studio there are a number of enhancements but one that I'd like
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1

Last_day and First_Day of the month

Hi,

Can I use Last_day(table_date) and first day? I have the query below and it is not working. If not is there any suggestions? I error is pointing at the "First_day".

SQL> SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'), 'MM/DD/YYYY') START_DATE,
2 to_char(to_date(last_day(s.key_date),'YYYYMMDD'),' MM/DD/YYYY') END_DATE
3 FROM schedule s;
SELECT to_char(to_date(first_day(s.key_date),'YYYYMMDD'), 'MM/DD/YYYY') START_DATE,
*
ERROR at line 1:
ORA-00904: invalid column name

Thanks a lotThere is an Oracle function called LAST_DAY that returns last day of month for given date, but not FIRST_DAY. For first day of month use TRUNC(s.key_date,'MM')|||What about formatting it like this ('MM/DD/YYYY'), remeber the actual table date is in 8 bytes varchar2.

Thanks|||My first reponse to that is: "why?" Why would anyone store dates as VARCHAR2 and not as DATE? Does not compute...

Anyway, you must convert the VARCHAR2 to a DATE before applying any other functions that work with dates:

LAST_DAY( TO_DATE(s.key_date,'MM/DD/YYYY') )

TRUNC( TO_DATE(s.key_date,'MM/DD/YYYY'), 'MM' )

Last working day from last year!

Dear Friends,

How can I retrieve the last working day from the currentyear-1?

Thanks

In SQL, or an expression inside SSIS?|||

Not sure what this has got to do with SSIS.

Which country are you in? Because different countries have different public holidays around the end of the year and this obviously makes a difference.

-Jamie

|||

I need to create a new column with the last working day for each row in the datasource... as for example inside a derived column...

How can I do it?

And I have the problem about holidays... but could be resolved if I have a table wich indicate the holidays for this case in my country Portugal... or have to apply for all the cases using a key to each country...

could help me?

Thanks!!

|||How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column?
Help me!!|||

PedroCGD wrote:

How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column?
Help me!!

What is considered the last working day of the year? The last Friday?|||YES|||

This is usually a table-driven information item such as a time dimension table. The DIMTIME records would be flagged for weekdays.

As Jamie mentioned, holidays are a regional thing so you would join entries in a DIMTIME table against a local holiday table to derive a list of working days. Then you could query the join to find the last working day of a given year.

This is not a SSIS issue, but a TSQL or engine question.

|||

No Benda, forget the holidays....

I need to transform each row, to add a new column that for each date I need to get the Reference Rate that is the last day of the last year for the each row of the data source....

thanks

|||This assumes you have a VARIABLE containing the year. If you don't have that, and need to dynamically read the year, replace the variable here with DATEPART("yyyy",[your_date_field])

So you'd replace "(DT_WSTR,4)@.[User::Year]" with "DATEPART("yyyy",[your_date_field])" if that's the case.

DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) < 6 ? DATEADD("d",-1 * (DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) + 1),(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) : DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) > 6 ? DATEADD("d",-1,(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) : (DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])|||

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code?

Thanks!

|||

PedroCGD wrote:

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code?

Thanks!

That's a locale issue then... DATEPART("dw",[datefield]) == 6 for Fridays here in the US.

Never the less, you should be able to adjust the logic by playing around with it a bit. Try to read the expression I've built for you and work on it your own. It's pretty simple logic, really, despite the fact the expression looks complicated.|||your code in US give you the friday?|||

PedroCGD wrote:

your code in US give you the friday?

Yes, absolutely. I tested it for several years. 2006 = 12/29/2006 = Friday|||

your code for me gives me 2006 = 12/30/2006 = Saturday!

:-(

bbrrr.... :-(

Last straw

Upgraded SQL 2005 to Dec CTP sp2. Had previously run Nov CTP and installed the Sharepoint add-in. Had that all working.

Now trying to add the Dec CTP sharepoint add-in. But it won't complete. Keeps rolling back and then ending with dialog '.... setup was interrupted'

Anyone else seeing problems with this Dec CTP install?

I was geting the same error when I was trying to install under an user with not enough rights. I loged in with the local Administrator and the installation works well.

I’m not sure it is the same scenario for you, but may be can help you …

Dan

|||

Thanks for the advice, but not a matter or permissions. Hmm, you said local admin, I have only tried domain accounts thus far. I'll try a local computer account and see what that does. Nope, no luck with that either. Very frustrating since I had the Nov CTP working and have my sharepoint 3.0 server running in integrated mode with reporting services. Since I put in the Dec CTP sp2 for SQL server, that is broken too.

I'd hate to reinstall since significant sharepoint structure was already in place.

|||

When you run the SharePointRS.msi, it creates a log file in the Temp folder - RS_SP_<number>.log.

Can you send us the latest RS_SP_<number>.log?

thanx,
Prash

|||

I don't get a log in the C:\temp folder. And I'm running this from C:\temp in fact. It always 'rollsback' near the end of the install. I have tried using win installer cleanup to remove the previous CTP of this addin. No help. The CTP sp2 for SQL 2005 seemed to work fine.

the only newly created log file is located in C:\Program Files\Common Files\Microsoft Shared\web server extensions\LOGS and it's called WISMDCN6-20070108-0738.log. It's about 775 kb and so I don't want to paste it here. I also see several other files generated in the same folder right at the time of the aborted install. They are: datalast.dat; data024.dat; watsonrc.wat; data023.dat. Are any of these files of concern? Is there some way to attach these files after zipping them. I cannot see pasting the contents of these large files into a message here.

I think that the install of December CTP sp2 did the same thing that the Nov CTP did to my WSS 3 install. Namely, made the website inaccessible. That is still true. I've not revisited the troubleshooting notes on Brian Welckers blog about this. Perhaps until I get this resolved I will have no hope of installing SharepointRS.msi?

If I knew how to do attachments to forum messages I could also send along a couple of screenshots. Or should I just use your email address?

tia

|||

The location of the file should generally be in the <Drive>:\Documents and Settings\_AWTEST\Local Settings\Temp folder.

You should be able to cut-paste the contents of the latest RS_SP_<number>.log file into the reply.

Let me know if this does not help and I will contact you offline.

thanx,
Prash

|||

Ah, now that directory has what we are looking for. Okay, here is the text of the log file:

User: Administrator
Installing Report Server feature.
Calling copyappbincontents command.

copyappbincontents command completed successfully.
Adding Report Server feature to farm.
Installed Report Server feature.
Activating Report Server feature to root level site collections.
Activating feature in web app 'SharePoint - 80'
Activating feature to root site collection: http://wismdcn6
Install error: A duplicate name "Report Builder Report" was found.
Uninstalling Report Server feature.
Uninstalling Report Server feature.
Report Server feature was uninstalled.
Stopping W3SVC service.
Removing Report Server binary files
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories8\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories4\_app_bin.
Removing web.config updates
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories8\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories8\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories4\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories4\web.config saved with entries removed.
Removing DocIcon.xml file updates
Starting W3SVC service.
SSRSCustomActionError: A duplicate name "Report Builder Report" was found.

I reviewed a half dozen previous logs from other failed attempts and they all have the same exact entries right up to the failure point.

|||

Can you delete the "Report Builder Report" and "Report Data Source" content types from the Document Libraries to which you added them and then re-try the CTP3 install?

Hope this helps.

thanx,
Prash

|||Well, that will depend on whether or not I get the Sharepoint server working again. Have not had the time to go back and start working on that. I seen to have more than one sharepoint server now after installing the Dec sp2 ctp. Any tips on getting my WSS3 server working again?|||

Are you now getting the following error when browsing to any SharePoint site :
Server Error in '/' Application.
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed.
OR
CS0101: The namespace 'Resources' already contains a definition for 'ReportServer'

If yes, then check the following in Internet Information Services Manager for each SharePoint web site:
- Explore into <Web site>\App_GlobalResources
- Do you see any Reportserver.<culture>.resx files (where culture is a string like EN / FR/ CS etc.)?
- If yes, then delete all Reportserver.<culture>.resx except for ReportServer.resx file.

If not, can you give me more details on the error you are seeing when you imply your SharePoint server is not working?

Hope this helps.

thanx,
Prash

|||

Removing the <culture>.resx files did the trick. I was able to open my WSS 3.0 site again. And I drilled down into every document library I could find that had report builder or report data source document types. Could be I missed one. Here is the same error log I am still getting when running SharePointRS.msi. Apparently this is what leaves those <culture>.resx files in its wake. They were back and prevented the WSS 3 site from opening again.

Is there any way I can test to be absolutely sure I removed all the old reporting services document types?

I tried once again after a very thorough removal of report types in document libraries. Still no go. Same error log as below and same problem with .resx files left behind.

thanks, and please stick with me to get this resolved.

--

User: dotwcd
Installing Report Server feature.
Calling copyappbincontents command.

copyappbincontents command completed successfully.
Adding Report Server feature to farm.
Installed Report Server feature.
Activating Report Server feature to root level site collections.
Activating feature in web app 'SharePoint - 80'
Activating feature to root site collection: http://wismdcn6
Install error: A duplicate name "Report Builder Report" was found.
Uninstalling Report Server feature.
Uninstalling Report Server feature.
Report Server feature was uninstalled.
Stopping W3SVC service.
Removing Report Server binary files
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\80\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\29408\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\21144\_app_bin.
Removing web.config updates
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\29408\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\29408\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\21144\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\21144\web.config saved with entries removed.
Removing DocIcon.xml file updates
Starting W3SVC service.
SSRSCustomActionError: A duplicate name "Report Builder Report" was found.

|||Did I mention that Microsoft Office Forms server is installed on this server too? seems to be difficult to remove though after the sp2 ctp is installed.|||

Great! The forms server failed uninstall completely removed the sharepoint sites. I'd like to know how to recover the site now. I'm about ready to toss the December sp2 CTP out the window.

|||

I setup a repro machine and here are the steps I took to get CTP3 working:
- Uninstall Reporting Services Add-in for SharePoint CTP2.
- Install Reporting Services Add-in for SharePoint CTP3. This should now run to complet
- You may still run into the Server error due to the ReportServer.<culture>.resx files (This is an Add-In setup bug we are working on fixing in the RTM release.). If yes, then as before a remove the appropriate ReportServer.<culture>.resx files. Do not remove the ReportServer.resx file.
- Repeat the configuration steps from the Central Administration site to re-integrate the Report Server and SharePoint.

About restoring - not sure how effective this would be in your case, however you may want to check out the restore command provided by SharePoint's stsadm.exe tool ( in the \Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN).

thanx,
Prash

|||Hi Prash, I have downloaded the December CTP which I gather is the CTP2 (33.8MB) which is giving me the same error in the log file "Report Builder Report" duplicate. As this is a new install (Fresh install of WSS 3.0, SQL2005 with SP2, just configured report server to SharePoint Integration mode) On the first attempt installing the Report Viewer Add-in, it rolled back right at the end. I have no content on the SharePoint site, so I am at a loss as to what "Report Builder Report" and "Report Data Source" I can delete. Any Pointers would be fantastic.

You keep mentioning the CTP3. Is this available for download, if so where do I find this (URL Please Please Please). Or does the same CTP download page continue to update with the latest CTP version.

I have setup a system running what must have been the November CTP release (3MB) and have had no problems with it. But I really need some help in getting either the CTP3 or December version past this "Duplicate Report Builder Report" Error.

Thanks heaps,

Sean

Large table, really slow queries

I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.

The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:

select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0

As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).

may be you can try table partitioning in sql 2005..............but i am not sure about this....refer,

http://dumthata.com/sqlchn/articles/dpart.htm

|||

You should rewrite the query to remove the DATEDIFF function from the WHERE clause. This causes SQL Server to scan the table.

It is better to use WHERE dt_date BETWEEN '2007-07-26' AND '2007-07-27' for example.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wow, what a world of difference it makes if I don't use datediff()!

Is there a general rule that will tell me whether the functions I use in my WHERE clause will cause a table scan versus index look-up? I'm not a DBA, just a .NET developer working on the back-end at the moment.

Immensely helpful, thank-you.
|||

Not just a rule of thumb, but an absolute no-no.

If you 'wrap' a field with a function, the query processor MUST apply that function to every row in the table (or index) to determine if the 'functionalized' value meets the criteria. As a result, the query will NOT efficiently utilize any indexing. It will require at least an index scan, and perhaps even a scan of the entire table.

If in a query, all rows newer than seven days ago are sought, you can either add seven days to the stored value and compare to the current datetime, or you can subract seven days from the current datetime and compare to the stored value.

For example, in this WHERE clause, :

WHERE dateadd( day, 7, MyColumn ) >= getdate()

Indexing on MyColumn cannot be properly used, the query will need to do an index scan (if MyColumn is indexed), and a table scan otherwise.

However you can easily use a function on the non-field side of the equality and still use indexes.

WHERE MyColumn <= dateadd( day, -7, getdate() )

Large Keyword Search

I'm working on a project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are 'text' fields that hold paragraphs of text. The user interface has a 'general search' option so that they can enter a number of key words and the database will return a count of the records found containing the keywords.

At the moment I split the input and then build a query based on their input. For instance if they enter 'hello world' the input is split into two strings 'hello' and 'world'. I then build the query in a loop and get a query like so:

Select Count(ID) as myCount FROM myTable WHERE (colOne like '%hello%' AND colOne like '%world%') OR (colTwo like '%hello%' AND colTwo like '%world%') OR (colThree like '%hello%' AND colThree like '%world%')

Unfortunately this query runs EXTREMELY slowly and just seems wrong. Is there a more efficient way I should be doing these types of searching? This method works ok on 100 records, but this is the first time I have worked on such a large database.

Is it also possible to search a text column and look for exact matches?

For instance I have 2 records with their textfield containing:

Rec 1: the news for today is blah blah.
Rec 1: this is a new item

If I currently search for 'new' (select colID from myTable where colOne like '%new%') I will get both these records, but I'd really only like to pull out the second record.

Any help would be great appreciated! :)

You could use Full Text and use Microsoft proprietry CONTAINS, FREETEXT and CONTAINSTABLE and FREETEXTTABLE. The former are predicate and the later are row functions. They are dependent on Full Text index which require the Microsoft Search Service to be populated. Hope this helps.

Sample

SELECT product_id, product_name, From products
WHERE FREETEXT (description, 'manage')

SELECT product_id, product_name, From products
WHERE CONTAINS (description, ' "config" ')

Kind regards,

Gift Peddie