Thursday, March 29, 2012
Cursor Process Coding Problem
It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @.@.FETCH_STATUS = 0 to @.@.FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.
DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For
select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'
open cursor_update_rpt_Scr_B0000_MiniFinancials
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName
update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName
END
CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancialsyour update needs to be inside the WHILE Loop
But aren't you worried about datatypes...and what's wrong with nulls annyway
(Here we go again)|||Ok,
I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?
Here is the updated code:
DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For
select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'
open cursor_update_rpt_Scr_B0000_MiniFinancials
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName
WHILE @.@.FETCH_STATUS = 0
BEGIN
update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName
END|||Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.
USE Northwind
GO
CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
GO
INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 1 , null, '2006-01-01' UNION ALL
SELECT null, 'b' , '2006-01-02' UNION ALL
SELECT 3 , 'c' , null
GO
SELECT * FROM myTable99
GO
DECLARE @.sql varchar(8000), @.collist varchar(8000), @.TABLE_NAME sysname
SET @.TABLE_NAME = 'myTable99'
SELECT @.collist = COALESCE(@.collist+', ','') + COLUMN_NAME + ' = '
+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ') THEN ''''+'0'+'''' ELSE '0' END
+ ' ELSE ' + COLUMN_NAME + ' END'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @.TABLE_NAME
SELECT @.sql = 'UPDATE ' + @.TABLE_NAME + ' SET ' + @.collist
SELECT @.sql
EXEC(@.sql)
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO|||OK,
I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.
Dave|||We resolved it in-house. Here is the answer! Thanks again.
================================================== =====
DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For
select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'
open cursor_update_rpt_Scr_B0000_MiniFinancials
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute('
update rpt_Scr_B0000_MiniFinancials
set '+@.FieldName+' = 0
where '+@.FieldName+' is null
')
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName
END
CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials|||Well use mine anyway and blow their minds...cursors...ech
Besides mine will be faster|||WOuld setting default = 0 on the table structure accomplish what you want.|||I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?
Davesql
Tuesday, March 27, 2012
cursor on results from stored procedure?
DECLARE myCursor CURSOR
FOR (exec sp_splitstring @.array = @.searchstr, @.separator = ' ')
OPEN myCursorWhy not create a temp table instead of using a cursor.
create table #a
insert #a exec sp_...
This looks like you are processing csv strings - I usually do this by
create table #csvint (id int identity, myid int, value int)
exec spProcCsvint myid = 1, csv =@.csv
In this way you can hold a lot of array values in the same table identified by the myid value.
You can loop through the values using id - but usually you would just join to it.
Monday, March 19, 2012
Currency in Analysis Services
Hi,
All the reports with comes out as $ (ex.) ( $123,437,00) I am working for the requirement that the same report has to show in multiple currency (ex.) ($,Yen,Pound,AUS$ ).As i am in Analysis Side can anyone give good Suggestion to get the output. Do any one faced the same?
Take a look at the following entry on Mosha's Blogsite:
http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx
HTH,
Steve
Thursday, March 8, 2012
Culture Settings Problem
My name is Ed Allison. I am very new to reporting services (about 2 weeks,
in between working on a lot of other stuff), and have had no luck attempting
to track down a particular problem.
I am running Visual Studio 2005 and Reporting Services 2005 on Windows XP
(fully service packed etc.), but the data source for my reports is SQL
Server 2000. I only use VS2005 for reports, as we are still on vs2003 for
the rest of our products. It looks as if, somewhere along the line, a
culture setting has been set wrong. This problem is occurring at the design
stage, i.e. I have not loaded the reports onto the report server.
I am working in the UK. I have double checked that my regional and language
settings under Control Panel and they are set to English(UK).
Under Tools>Options>Environment>International Settings, Language is set to
'Same as Microsoft Windows'.
Whenever I format to currency (using "C" as the formatstring, generally),
the amount is returned in $, not £. Whenever I have date parameters on a
stored procedure for a report dataset, the dates are initially registered in
UK format (dd/mm/yyyy), but as soon as I click the 'view report button', the
dates are set in the parameter windows to mm/dd/yyyy format. This causes an
error when such a conversion yields an invalid date, e.g. '30/01/2006' to
'01/30/2006'.
I have searched in vain around visual studio 2005 for anything relating to a
culture setting specific to it without success. I have also tried MSDN and
Books Online, but no luck. I have checked every user account on my local
machine to see if one was entered with US culture settings, but no luck.
Report Services Configuration also does not appear to mention culture.
If anyone could help, I would be most grateful.
Ed AllisonI have found the solution to this, and am thoroughly embarrassed to have
missed it.
For the sake of anyone new like me who has a similar problem with their
culture settings apparently being ignored - there is a Language property on
the Report that defaults to English(United States). If this is set to the
global User.Language, formatting will correspond to culture settings on the
report client. This property is also present in individual textboxes.
"Ed Allison" <ed@.optix.co.uk> wrote in message
news:ODg7mZbJGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> My name is Ed Allison. I am very new to reporting services (about 2
> weeks, in between working on a lot of other stuff), and have had no luck
> attempting to track down a particular problem.
> I am running Visual Studio 2005 and Reporting Services 2005 on Windows XP
> (fully service packed etc.), but the data source for my reports is SQL
> Server 2000. I only use VS2005 for reports, as we are still on vs2003 for
> the rest of our products. It looks as if, somewhere along the line, a
> culture setting has been set wrong. This problem is occurring at the
> design stage, i.e. I have not loaded the reports onto the report server.
> I am working in the UK. I have double checked that my regional and
> language settings under Control Panel and they are set to English(UK).
> Under Tools>Options>Environment>International Settings, Language is set to
> 'Same as Microsoft Windows'.
> Whenever I format to currency (using "C" as the formatstring, generally),
> the amount is returned in $, not £. Whenever I have date parameters on a
> stored procedure for a report dataset, the dates are initially registered
> in UK format (dd/mm/yyyy), but as soon as I click the 'view report
> button', the dates are set in the parameter windows to mm/dd/yyyy format.
> This causes an error when such a conversion yields an invalid date, e.g.
> '30/01/2006' to '01/30/2006'.
> I have searched in vain around visual studio 2005 for anything relating to
> a culture setting specific to it without success. I have also tried MSDN
> and Books Online, but no luck. I have checked every user account on my
> local machine to see if one was entered with US culture settings, but no
> luck. Report Services Configuration also does not appear to mention
> culture.
> If anyone could help, I would be most grateful.
> Ed Allison
>
Cubes process forever
So everything has been working fine for 2 months now. Last night some updates got installed. SQL service pack 2 being one of them and some standard windows updates. The server is still running Windows server 2003 service pack 1. I now can't process my cubes or deploy them. It's the only thing that has changed so I am taking it as an assumption. Is there any other reason for cubes to suddenly just sit in the processing state forever?
This is a big problem for me and I am hoping that installing Service pack 2 for the windows server will sort it out but if it doesn't I am pretty much lost. So if you have any ideas please let me know.
Thanks in advance guys
Regards
RyanN
Do you make full process? how meny partitions has your database in all cubes together?
I had a similar problem as your one.
It was solved through increasing of <ThreadPool><Process><MaxThreads>
|||I can't answer your question but I have a similar problem. My processing doesn't take forever, but at least much longer with SP2 (see thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1472639&SiteID=1). I was thinking that maybe SP2 use (much) more memory than SP1, and therefore slows down my processing (or in fact all steps in our ETL process). I gonna set up perfmon to monitor memory, cpu and disks.
Vladimir: Was this after you installed SP2? Does <ThreadPool><Process><MaxThreads> apply to SQL Server Standard aswell?
|||Thanks, this is one partition. If I create a new cube it deploys and processes perfectly, maybe there is something wrong with the cube database, I don't actually know. But I am sure there is a solution out there. I will definitely post it once I have found it.
Thanks for your help guys. I am definitely exploring all the suggestions posted to me.
|||Well for those of you who want to know. I uninstalled and re-installed, went onto sp1 with all the hotfixes and it works perfectly now. I am going to set up a test environment and try duplicate the scenario and see what went wrong where. It doesn't seem to be too common, so I will se what went wrong|||Hi RyanN,
Something I did was to replace the named queries in the DSV, with index queries ("materialized queries") at the database level. This helps because the DB "precalculates" and actual stores / caches the views resultset, meaning that when the DSV references that indexed query, that there's no "heavy lifting" for the DB to do, so processing is quicker. However, if the named query in your DSV is already pretty basic, and the tables it's referencing are appropriately linked, this approach probably won't buy you much benefit.
HTH
Greg Withers
|||Sorry - my preceding reply to RyanN should have said
"and if the tables are appropriately indexed"
|||Hi HappyCow
Sure, the problem came with SP2 on x86. But on x64 it was on SP1 too.
I can't answer you regardin Std. edition. I use the Enterprise only.
|||Nope, can't believe that installing a new service should make me have to change the design of my cubes and cause me work at all. If anything it should make things more optimal. I am going to duplicate the steps I took and how the cubes reacted. This should never have happened.Cubes process forever
So everything has been working fine for 2 months now. Last night some updates got installed. SQL service pack 2 being one of them and some standard windows updates. The server is still running Windows server 2003 service pack 1. I now can't process my cubes or deploy them. It's the only thing that has changed so I am taking it as an assumption. Is there any other reason for cubes to suddenly just sit in the processing state forever?
This is a big problem for me and I am hoping that installing Service pack 2 for the windows server will sort it out but if it doesn't I am pretty much lost. So if you have any ideas please let me know.
Thanks in advance guys
Regards
RyanN
Do you make full process? how meny partitions has your database in all cubes together?
I had a similar problem as your one.
It was solved through increasing of <ThreadPool><Process><MaxThreads>
|||I can't answer your question but I have a similar problem. My processing doesn't take forever, but at least much longer with SP2 (see thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1472639&SiteID=1). I was thinking that maybe SP2 use (much) more memory than SP1, and therefore slows down my processing (or in fact all steps in our ETL process). I gonna set up perfmon to monitor memory, cpu and disks.
Vladimir: Was this after you installed SP2? Does <ThreadPool><Process><MaxThreads> apply to SQL Server Standard aswell?
|||Thanks, this is one partition. If I create a new cube it deploys and processes perfectly, maybe there is something wrong with the cube database, I don't actually know. But I am sure there is a solution out there. I will definitely post it once I have found it.
Thanks for your help guys. I am definitely exploring all the suggestions posted to me.
|||Well for those of you who want to know. I uninstalled and re-installed, went onto sp1 with all the hotfixes and it works perfectly now. I am going to set up a test environment and try duplicate the scenario and see what went wrong where. It doesn't seem to be too common, so I will se what went wrong|||Hi RyanN,
Something I did was to replace the named queries in the DSV, with index queries ("materialized queries") at the database level. This helps because the DB "precalculates" and actual stores / caches the views resultset, meaning that when the DSV references that indexed query, that there's no "heavy lifting" for the DB to do, so processing is quicker. However, if the named query in your DSV is already pretty basic, and the tables it's referencing are appropriately linked, this approach probably won't buy you much benefit.
HTH
Greg Withers
|||Sorry - my preceding reply to RyanN should have said
"and if the tables are appropriately indexed"
|||Hi HappyCow
Sure, the problem came with SP2 on x86. But on x64 it was on SP1 too.
I can't answer you regardin Std. edition. I use the Enterprise only.
|||Nope, can't believe that installing a new service should make me have to change the design of my cubes and cause me work at all. If anything it should make things more optimal. I am going to duplicate the steps I took and how the cubes reacted. This should never have happened.Saturday, February 25, 2012
Cube Name Changed After Processing (but I didn''t do anything)
Hello,
I have a cube that has been working fine for a while. Then I re-ran the SSIS to bring in refreshed data (which I've done many times before) and reprocessed the cube. I never changed the name of the cube or anything like that (at least on purpose).
Now when I open SSRS to run a report, it says that it cannot find the cube. I do have a Perspective in there, but I wasn't using it. Previously it had the cube's name Stage and the perspective's name Perspective. Now I see Perspective and Perspective 1.
Any idea what could have caused this and what I can do to change it back?
Thank you.
-Gumbatman
Hi,
Re-processing the cube will not rename the cube, something else should have happened.
To double check the cube name, open BI Development Studio -> File -> Open Analysis Services Database: this will connect to the database; open the cube editor and check (in the properties grid) what the cube name is. Or you can use SQL Management Studio and bring up the cube Properties dialog.
If the cube was renamed indeed from 'Stage' to 'Perspective 1', you can track what happened (who changed what) if you had the FlighRecorder tracing enabled (that logs all the commands sent to server, but usually it's not on for performance reasons).
Adrian
|||Adrian,
I checked out the properties and the name of the cube is still "Stage".
When I open the cube Browser in BIDS I can chose either Stage or Perspective. However, when I want to build a new report in Reporting Serivces (sorry I forgot to mention that) I only get the choices of Perspective or Perspective 1.
In RS, it was always Stage or Perspective and after I did the processing it changed to Perspective or Perspective 1.
Any ideas of where I should look? I have no idea if I had the recorder thing on to trace this. It is only on my laptop for development.
Thank you for the help.
-Gumbatman
Cube Name Changed After Processing (but I didn''t do anything)
Hello,
I have a cube that has been working fine for a while. Then I re-ran the SSIS to bring in refreshed data (which I've done many times before) and reprocessed the cube. I never changed the name of the cube or anything like that (at least on purpose).
Now when I open SSRS to run a report, it says that it cannot find the cube. I do have a Perspective in there, but I wasn't using it. Previously it had the cube's name Stage and the perspective's name Perspective. Now I see Perspective and Perspective 1.
Any idea what could have caused this and what I can do to change it back?
Thank you.
-Gumbatman
Hi,
Re-processing the cube will not rename the cube, something else should have happened.
To double check the cube name, open BI Development Studio -> File -> Open Analysis Services Database: this will connect to the database; open the cube editor and check (in the properties grid) what the cube name is. Or you can use SQL Management Studio and bring up the cube Properties dialog.
If the cube was renamed indeed from 'Stage' to 'Perspective 1', you can track what happened (who changed what) if you had the FlighRecorder tracing enabled (that logs all the commands sent to server, but usually it's not on for performance reasons).
Adrian
|||Adrian,
I checked out the properties and the name of the cube is still "Stage".
When I open the cube Browser in BIDS I can chose either Stage or Perspective. However, when I want to build a new report in Reporting Serivces (sorry I forgot to mention that) I only get the choices of Perspective or Perspective 1.
In RS, it was always Stage or Perspective and after I did the processing it changed to Perspective or Perspective 1.
Any ideas of where I should look? I have no idea if I had the recorder thing on to trace this. It is only on my laptop for development.
Thank you for the help.
-Gumbatman
Cube Name Changed After Processing (but I didn''t do anything)
Hello,
I have a cube that has been working fine for a while. Then I re-ran the SSIS to bring in refreshed data (which I've done many times before) and reprocessed the cube. I never changed the name of the cube or anything like that (at least on purpose).
Now when I open SSRS to run a report, it says that it cannot find the cube. I do have a Perspective in there, but I wasn't using it. Previously it had the cube's name Stage and the perspective's name Perspective. Now I see Perspective and Perspective 1.
Any idea what could have caused this and what I can do to change it back?
Thank you.
-Gumbatman
Hi,
Re-processing the cube will not rename the cube, something else should have happened.
To double check the cube name, open BI Development Studio -> File -> Open Analysis Services Database: this will connect to the database; open the cube editor and check (in the properties grid) what the cube name is. Or you can use SQL Management Studio and bring up the cube Properties dialog.
If the cube was renamed indeed from 'Stage' to 'Perspective 1', you can track what happened (who changed what) if you had the FlighRecorder tracing enabled (that logs all the commands sent to server, but usually it's not on for performance reasons).
Adrian
|||Adrian,
I checked out the properties and the name of the cube is still "Stage".
When I open the cube Browser in BIDS I can chose either Stage or Perspective. However, when I want to build a new report in Reporting Serivces (sorry I forgot to mention that) I only get the choices of Perspective or Perspective 1.
In RS, it was always Stage or Perspective and after I did the processing it changed to Perspective or Perspective 1.
Any ideas of where I should look? I have no idea if I had the recorder thing on to trace this. It is only on my laptop for development.
Thank you for the help.
-Gumbatman
Friday, February 24, 2012
Cube Browser not filtering the data for the user having only read definition permission
Hi,
I am started working with SAS2005 recently. Using Adventure works sample database.
When I login to the Management Studio as administrator and browse the cube, the browser filters all the null value columns and rows.
Steps:
1. Add Product.Product Categories to filter area and select only Bikes
2. Add Date.Calendar.Date Calendar year to filter area and select year 2005
3. Add Product.Product to Row area
4. Add Measures.Internet Sales.Internet Sales Amount in the Total or detail area
5. Add [Date].[Month of Year] in the column area.
When I logged in as administrator or the user with the role Full control (administrator) and all the nessasary permission set on all the objects. The cube browser displays the data where only it has values. If the entire row or column are null then the row and columns are not displayed.
When I do the exactly same with the user has a role instead of full control using just read definition option (I do not want the user to get full permission, like to make a role as read only) , the browse cube shows all the rows and columns, even the row and columns where all the values are null.
The empty cells are displayed as #N/A.
Is it possible to make the browser behave for the read only user as exactly like administrator user.
Any suggestions and recommendations are great.
Thanks
I am stuck with this problem. Please somebody help...Cube Browser FAILS to open. It HANGS
Hi, I am working on the Analysis Services Tutorial.
When I am in Business Intelligence Studio and I right-click on the cube and I choose browse to open the cube-browser, the Cube-Browser HANGS FOREVER. it never finishes opening. I always end up using the taskmgr to kill off Business Intelligent Studio. How is this HANGING problem fixed?
AIMDBA
The Event Viewer Applicaiton log is showing the following:
Hanging application devenv.exe, version 8.0.50727.42, hang module hungapp, version 0.0.0.0, hang address 0x00000000.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Fault bucket 230214627.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
AIMDBA
|||Try to see if re-installing Analysis Services will solve the problem.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Ctrl+Tab not working
Sunday, February 19, 2012
CTE, I have a dream...
for use in only one place.
/*
* CTE, I have a dream...
*/
With TopRatedEmployees
(
@.DepartmentId int
)
RETURNS TABLE
AS
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = @.DepartmentId
ORDER BY Rank DESC
)
With EmployeesCount
(
@.DepartmentId int
)
RETURNS int
AS
(
SELECT COUNT(*)
FROM Employees
WHERE DepartmentId = @.DepartmentId
)
SELECT Departments.Id,
Departments.Name,
dbo.EmployeesCount(Department.Id),
TopRatedEmployees1.EmployeeId,
TopRatedEmployees1.EmployeeName,
FROM Departments
CROSS APPLY
dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
ORDER BY Departments.Name;
GO<guercheLE@.gmail.com> wrote in message
news:1151617460.266574.183380@.j72g2000cwa.googlegroups.com...
> If they (CTE) were parameterized, I would stop working user functions
> for use in only one place.
> /*
> * CTE, I have a dream...
> */
> With TopRatedEmployees
> (
> @.DepartmentId int
> )
> RETURNS TABLE
> AS
> (
> SELECT TOP 10 EmployeeId,
> EmployeeName,
> Rank
> FROM Employees
> WHERE DepartmentId = @.DepartmentId
> ORDER BY Rank DESC
> )
> With EmployeesCount
> (
> @.DepartmentId int
> )
> RETURNS int
> AS
> (
> SELECT COUNT(*)
> FROM Employees
> WHERE DepartmentId = @.DepartmentId
> )
> SELECT Departments.Id,
> Departments.Name,
> dbo.EmployeesCount(Department.Id),
> TopRatedEmployees1.EmployeeId,
> TopRatedEmployees1.EmployeeName,
> FROM Departments
> CROSS APPLY
> dbo.TopRatedEmployees(Department.Id) AS TopRatedEmployees1
> ORDER BY Departments.Name;
> GO
>
Assuming I've understood your pseudo-code correctly you can do it like this:
SELECT D.DepartmentId,
D.Name,
D.DepartmentId,
TopRatedEmployees.EmployeeId,
TopRatedEmployees.EmployeeName
FROM Departments AS D
CROSS APPLY
(
SELECT TOP 10 EmployeeId,
EmployeeName,
Rank
FROM Employees
WHERE DepartmentId = D.DepartmentId
ORDER BY Rank DESC
) AS TopRatedEmployees
CROSS APPLY
(
SELECT D.DepartmentId, COUNT(*) AS cnt
FROM Employees
WHERE DepartmentId = D.DepartmentId
) AS EmployeesCount
ORDER BY D.Name;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
CTE and UNION Queries
I have a question regarding the use of CTEs.
The query I'm working on involves a UNION of four or five smaller queries, each doing different things. Each query, however, uses the same base set of records. eg:
SELECT ...
FROM Products p
JOIN ... -- many other tables
WHERE p.ProductIsActive = 1UNION
SELECT ...
FROM Products p
JOIN ... -- a different set of tables
WHERE p.ProductIsActive = 1UNION ... -- etc etc
So each subquery is working from the same base set of Products rows.
My idea was to pull that out into a CTE. Something like this:
WITH ActiveProducts AS (
SELECT ProductID
FROM Products
WHERE ProductIsActive = 1
)
SELECT ...
FROM ActiveProducts p
JOIN ... -- many other tablesUNION
SELECT ...
FROM ActiveProducts p
JOIN ... -- a different set of tablesUNION ... -- etc etc
So my question is: If I do it this way, will the "ActiveProducts" CTE get executed for every subquery? Or will it just get executed once and used like a view by each subquery?
Or is there a better way to do this that I'm overlooking?
Thanks!
The CTE should behave just like a view. The best way to see how it will optimize is to look at the plan of the query.
So it should be like:
create view ActiveProducts AS
SELECT ProductID
FROM Products
WHERE ProductIsActive = 1
go
SELECT ...
FROM ActiveProducts p
JOIN ... -- many other tables
UNION
SELECT ...
FROM ActiveProducts p
JOIN ... -- a different set of tables
UNION ... -- etc etc
Or:
SELECT ...
FROM (SELECT ProductID
FROM Products
WHERE ProductIsActive = 1) p
JOIN ... -- many other tables
UNION
SELECT ...
FROM (SELECT ProductID
FROM Products
WHERE ProductIsActive = 1) p
JOIN ... -- a different set of tables
declare @.ActiveProducts table (ProductId int)
INSERT INTO @.ActiveProducts
SELECT ProductId
FROM Products
WHERE ProductIsActive = 1
SELECT ...
FROM @.ActiveProducts p
JOIN ... -- many other tables
UNION
SELECT ...
FROM @.ActiveProducts p
JOIN ... -- a different set of tables
HTH,
ct_connect errors in xp_cmdshell
EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.t xt -c -t~ -SSYN0001
-UUserName -Ppassword '
The server is Microsoft SQL Server 2000 - 8.00.760
Then I installed sybase client in the PC inorder to get replication from
sybase to sql server.
It is giving the following error message.
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found.
Establishing connection failed.
NULL
Any help why is this happenning?
The server, username, password are all correct and I am able to work through
the enterprise manager and query analyser, its gving error only in the
xp_cmdshell and bcp.
After the install, the xp_cmdshell and bcp is not working anymore.
Hi
The Sybase BCP.exe is now being used by your call due to the environment
path settings written by the Sybase setup.
Fully qualify the BCP.exe path in the call and it will work. osql.exe is
also affected in the same way.
Regards
Mike
"Yog" wrote:
> I am running a bcp from xp_cmdshell, it was working good
> EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
> c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.t xt -c -t~ -SSYN0001
> -UUserName -Ppassword '
> The server is Microsoft SQL Server 2000 - 8.00.760
> Then I installed sybase client in the PC inorder to get replication from
> sybase to sql server.
> It is giving the following error message.
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
> NULL
> Any help why is this happenning?
> The server, username, password are all correct and I am able to work through
> the enterprise manager and query analyser, its gving error only in the
> xp_cmdshell and bcp.
> After the install, the xp_cmdshell and bcp is not working anymore.
>
>
|||Thank you Mike, that worked.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> The Sybase BCP.exe is now being used by your call due to the environment
> path settings written by the Sybase setup.
> Fully qualify the BCP.exe path in the call and it will work. osql.exe is
> also affected in the same way.
> Regards
> Mike
> "Yog" wrote:
ct_connect errors in xp_cmdshell
EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.txt -c -t~ -SSYN0001
-UUserName -Ppassword '
The server is Microsoft SQL Server 2000 - 8.00.760
Then I installed sybase client in the PC inorder to get replication from
sybase to sql server.
It is giving the following error message.
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found.
Establishing connection failed.
NULL
Any help why is this happenning?
The server, username, password are all correct and I am able to work through
the enterprise manager and query analyser, its gving error only in the
xp_cmdshell and bcp.
After the install, the xp_cmdshell and bcp is not working anymore.Hi
The Sybase BCP.exe is now being used by your call due to the environment
path settings written by the Sybase setup.
Fully qualify the BCP.exe path in the call and it will work. osql.exe is
also affected in the same way.
Regards
Mike
"Yog" wrote:
> I am running a bcp from xp_cmdshell, it was working good
> EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
> c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.txt -c -t~ -SSYN0001
> -UUserName -Ppassword '
> The server is Microsoft SQL Server 2000 - 8.00.760
> Then I installed sybase client in the PC inorder to get replication from
> sybase to sql server.
> It is giving the following error message.
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
> NULL
> Any help why is this happenning?
> The server, username, password are all correct and I am able to work through
> the enterprise manager and query analyser, its gving error only in the
> xp_cmdshell and bcp.
> After the install, the xp_cmdshell and bcp is not working anymore.
>
>|||Thank you Mike, that worked.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The Sybase BCP.exe is now being used by your call due to the environment
> path settings written by the Sybase setup.
> Fully qualify the BCP.exe path in the call and it will work. osql.exe is
> also affected in the same way.
> Regards
> Mike
> "Yog" wrote:
> > I am running a bcp from xp_cmdshell, it was working good
> >
> > EXEC master.dbo.xp_cmdshell 'bcp DATABASE1..V_TMP_KEYWORDSMMS2 out
> > c:\Keywords\KEYWORD_00000115_16_12_2004_17_47_27.txt -c -t~ -SSYN0001
> > -UUserName -Ppassword '
> >
> > The server is Microsoft SQL Server 2000 - 8.00.760
> >
> > Then I installed sybase client in the PC inorder to get replication from
> > sybase to sql server.
> >
> > It is giving the following error message.
> >
> > CTLIB Message: - L6/O8/S5/N3/5/0:
> > ct_connect(): directory service layer: internal directory control layer
> > error: Requested server name not found.
> > Establishing connection failed.
> > NULL
> >
> > Any help why is this happenning?
> >
> > The server, username, password are all correct and I am able to work through
> > the enterprise manager and query analyser, its gving error only in the
> > xp_cmdshell and bcp.
> >
> > After the install, the xp_cmdshell and bcp is not working anymore.
> >
> >
> >
Friday, February 17, 2012
CSV files
Use DTS. Just import your CSV file into SQL Database.
CSS in reporting services
Actually I am working on a project where in we will be doing 20 - 30 SQL
Reports The Necessity is that we need to assign all the text boxes(for
captions) and the tables with a CSS so that we need not change all the
reports individually if need arises.
Can anyone please guide me in this regard, is such a thing possible.
How to assign a CSS classes for the following
. Textbox
. Tables
. Images
And where to copy the CSS File?
Thanks..On Jul 14, 4:41 am, "Vijay" <techni...@.peoplewareindia.com> wrote:
> Hi All, I need a suggestion.
> Actually I am working on a project where in we will be doing 20 - 30 SQL
> Reports The Necessity is that we need to assign all the text boxes(for
> captions) and the tables with a CSS so that we need not change all the
> reports individually if need arises.
> Can anyone please guide me in this regard, is such a thing possible.
> How to assign a CSS classes for the following
> . Textbox
> . Tables
> . Images
> And where to copy the CSS File?
> Thanks..
This link should offer your CSS customization options.
http://msdn2.microsoft.com/en-us/library/ms345247.aspx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Tuesday, February 14, 2012
CrystalReportViewer and ASP.Net
I am Asad, software developer. I am working in ASP.Net2005 with VB.Net 2005 and using CR.Net 2005. I having some problems.
Whenever i click some button on crystalreportviewer's toolbar, the page bocomes empty. Report becomes invisible.
I got ur address from Code Project while searching abt reports problem.
Plz help Me.
Regards
Asad NaeemDid you find an answer to your problem? I believe I have the same problem - I put together a CRViewer project using VB.net (2003). When reports are displayed with crosstabs, there is nothing there. I also get an error when the report has drill down group level code (for header display)i.e, "DrillDownGroupLevel=0" the remaining text does not appear to be part of the formula. All of these reports work fine using Crystal (10) on our server, which is where they are created. The reports are saved with data, so it's not a databse issue at all. Drill down is OKed in the properties of the viewer, so that's not an issue..
Is this some sort of versioning problem? Any tips on finding the source of the problem would be appreciated.|||Hi...
Yes i found the solution. Infact the buttons of the toolbar are server side controls. I have applied the condition in the Page_Load i.e,
if Not PostBack() then .... End If.
The scenerio was:::
I have used the typed report (report bidded with typed dataset)
When i click the button the page starts rendering and and dataset and everything becoes empty and due to the condition the dataset did not fill again that is why reports becmes invisible. I found the solution which
i have used cache for this purpose. At the time of loading the page, i saved the dataset in the cache with the time duration of 10 mins. Whenever i click any toolbar button the data is retrieved from the cached every time before 10 mins paases. After 10 mins page again retrieves the data from the database otherwise it retrieves the data from the cache. In this way dataset is never empty.
Contacts
00923334471758
usan807@.hotmail.com
asad.naeem@.gmail.com
anuab_lg@.yahoo.co.uk
Regards
Asad Naeem|||Sounds like yours was a bit different than mine - one thing I have found - If I recreate the report using the Crystal designer in VB.Net (2003), than the crosstab(s) shows. If I open the original report in the (VB) designer, it looks OK (you see the crosstabs, headings etc. in the designer), but even if I do a save as, there is no data when the report is viewed, so it looks like it's at least somewhat related to the fact that the original crosstab reports were created in CR10. I don't know enough about this yet, but I wouldn't think that should be the case. Am I wrong on that?
I've seen other people have had similar problems, but most of the questions have gone unanswered. Others have indicated that things go blank when they do something that requires the screen to be redrawn - could the problem have anything to do with Windows.Drawing routines that might be treated differently from one version to another?