Very Nice; That Worked.
Sep 29, 2006Very nice; that worked.
It seems like a lot of code for each date field, but we are up and running now.
ADVERTISEMENT
Nice Books?
Jun 14, 2006hi guys,
i m a newbee in SQL. i wanted to know which is the nice book to start with? anyone got ebooks of SQL2005?
What Happened To Nice BCP Interface In SQL 6.5
Nov 20, 1998In the SQL Object manager tools for sql server 4.21 there was a gui interface for running BCP where you could select tables and fields etc.
I haven't beem able to find abnything equivalent in sql 6.5 enterprise manager. Am I missing someting, or was this dropped?
Thanks.
Jerry Dunn
ViaHealth
jerry.dunn@viahealth.org
SP3a + Extended Procedures = Not Nice (help!)
Jul 20, 2005Hi(sorry this has turned into a bit of an epic...thought I'd as theexperts!)SQL 2k, sp3a. Dual xeon 2.4. 2 gig ram. everything on a 3 disk raid 5.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Enterprise Edition on Windows NT 5.0 (Build 2195: )Problem seems to be some extended procs that seemed to be running fineon a 'vanilla' install on a different server now seem to be failing.Failures occuring c 6 - 10 hours.Sometimes the failure logs as the xp failure with severity 20 that Ikindof expect :s. other times absolutely nothing is logged.There are a variety of error messages - or sometimes nothing at all.http://www.mapinfo.com/common/docs/.../SWMSS48_RN.pdfdescribes one of our issues, which seems to come out on the restartafter a failure:Error event id is 19011, Message "SuperSocket info:ConnectionListen(Shared-Memory (LPC)) : Error 5."That site suggests:[color=blue]>Workaround>Suggested solutions include:> Obtain a hotfix from Microsoft (http://www.microsoft.com). You will[/color]need to >cite the 94302[color=blue]>reference number.> Turn off shared memory in the client network configuration utility[/color]to >disable shared[color=blue]>memory.> Replace the SQL Server SSMSLPCN.DLL with the SSMSLPCN.DLL[/color](8.00.540) from >SQL[color=blue]>Server 2000 Service Pack 2.[/color]- but I cant find any sensible information which describes this tocorrelate! Searching MS for that number (94302) came up with zip.The dll version we are running is 2000.80.760.0, although earlierversions are elsewhere on the server (disk images).Interesting to note that we seem to be able to replicate the 'leakage'of improperly closed handles on this version, but not on the oldserver runningthat dll version 2000.80.194.0. This I understood to have been fixedin sp3.On one failure we caught the following (involving our extended procs):2004-02-15 15:03:31.40 spid58 Error: 0, Severity: 20, State: 02004-02-15 15:03:31.40 spid58 Stored function'xp_Contest_CalculateScores' in the library 'e:sqlxpga_procs.dll'generated an access violation. SQL Server is terminating process 58..2004-02-15 15:04:32.14 spid64 Error: 0, Severity: 20, State: 02004-02-15 15:04:32.14 spid64 Stored function 'xp_XML_GetSubTree'in the library 'e:sqlxpga_procs.dll' generated an access violation.SQL Server is terminating process 64..2004-02-15 15:04:48.48 spid53 Using 'dbghelp.dll' version '4.0.5'*Dump thread - spid = 53, PSS = 0x472d7200, EC = 0x725fc098*Stack Dump being sent to e:sqlMSSQLlogSQLDump0001.txt* ************************************************** ******************************* BEGIN STACK DUMP:* 02/15/04 15:04:48 spid 53This was followed about a minute later by:2004-02-15 15:04:55.75 spid53 Stack Signature for the dump is0x1CC032652004-02-15 15:04:55.75 spid53 SQL Server Assertion: File:<recbase.cpp>, line=1378Failed Assertion = 'm_offBeginVar < m_SizeRec'.2004-02-15 15:04:55.89 spid53 Using 'dbghelp.dll' version '4.0.5'*Stack Dump being sent to e:sqlMSSQLlogSQLDump0002.txt2004-02-15 15:04:56.01 spid53 SqlDumpExceptionHandler: Process 2292generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQLServer is terminating this process.* ************************************************** ******************************* BEGIN STACK DUMP:* 02/15/04 15:04:55 spid 53As a side issue we have a number of 'initialisation of notify failed'notes.Any one have any clue about this?Might the DLL need rolling back?Is sp3a knacked for extended procs?Is there some daft setting I've missed :/Any good events to track in particular?Cheers all!
View 1 Replies View RelatedONE ZERO ZERO -------&&> One Hundred = Nice String Formula !
Feb 21, 2008
Im trying to build myself a report that recieves a values as worded text e.g
zero zero one zero zero
it would be nice if i write some clever bit of code that would convert this to
One Hundred
Has anyone come up against this before, or could point me in the direction of a tutorial, i would appreciate it
thank you in advance
Jonny
What Happened To Nice SqlDataAdapter.Fill Method In ASP.net 2.0
Dec 26, 2005What happened to nice SqlDataAdapter.Fill method in ASP.net 2.0 + How can i now access and traverse trhough table.
ex : sqlDataAdapter.Fill(dataSetname.tblsname);
dataSet11.rows[][]
Nice Problem: How To Combine Different Columns Into A Table
Jan 18, 2004It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.
But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.
select col1 from table1
go
select col2 from table2
go
Now I want to combine them so table3 is made of col1 and col2.
Rolling Up Records Problem (nice Puzzle) :)
Feb 21, 2008Here's a nice puzzle for all you masochistic programmers LOL.
Actually, it's just something that's been giving me problems and I know this forum has some amazing programmers who will probably find my problem to be easy.
I'm using 2005.
Simply put...
Here's the data:
ID Start End X
123 10 15 a
123 9 10 b
123 11 18 d
123 14 16 z
123 19 21 x
234 16 18 bb
I'd like to roll up overlapping start and end numbers into one set.
So here would be the output:
ID Start End [Ttl X]
123 9 18 4
123 19 21 1
234 16 18 1
The way I'm thinking of attacking this is maybe a combination of RowNumber & a CTE of some sort.
Please no loops or cursors, or extra tables.
Any ideas guys/gals?
Thanks a mil,
Denvas
Now If A Update Worked In A SP
Jul 23, 2005HiI've a SP in MSDE that will update a table. How could I know if itworked to return a value to the SP caller ?Thanks in advanceJ
View 1 Replies View RelatedChecking If Update Worked
Dec 13, 2001Is there a system variable I can use to check if my update statement was successful and updated exactly one row.
Thanks
Worked In 2000 But Not In 2005
Feb 22, 2008Hi there,
I have a query with a rather large (46 table) 1:1 join. Technically, I'm creating a view then trying to select from that view. I seem to be able to create the view ok but cannot resolve a select from it. The error message is:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 191, Level 15, State 1, Line 1
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'WHERE'.
This worked in 2000 without a problem
Here is the join:
FROM [HUSKY].USEP0712E.dbo.AGAF_us T1 INNER JOIN [HUSKY].USEP0712E.dbo.AGAM_us T2 ON T1.CODE = T2.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGASN_us T3 ON T1.CODE = T3.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGBF_us T4 ON T1.CODE = T4.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGBLK_us T5 ON T1.CODE = T5.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGBM_us T6 ON T1.CODE = T6.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGHF_us T7 ON T1.CODE = T7.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGHM_us T8 ON T1.CODE = T8.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGHSP_us T9 ON T1.CODE = T9.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGIF_us T10 ON T1.CODE = T10.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGIM_us T11 ON T1.CODE = T11.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGIND_us T12 ON T1.CODE = T12.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGNH_us T13 ON T1.CODE = T13.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGNHF_us T14 ON T1.CODE = T14.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGNHM_us T15 ON T1.CODE = T15.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGOF_us T16 ON T1.CODE = T16.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGOM_us T17 ON T1.CODE = T17.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGOTH_us T18 ON T1.CODE = T18.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGWF_us T19 ON T1.CODE = T19.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGWHT_us T20 ON T1.CODE = T20.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.AGWM_us T21 ON T1.CODE = T21.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.WH_AOH_us T22 ON T1.CODE = T22.CODE
INNER JOIN [HUSKY].USEP0712E.dbo.WNH_AONH_us T23 ON T1.CODE = T23.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGAF_us T24 ON T1.CODE = T24.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGAM_us T25 ON T1.CODE = T25.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGASN_us T26 ON T1.CODE = T26.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGBF_us T27 ON T1.CODE = T27.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGBLK_us T28 ON T1.CODE = T28.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGBM_us T29 ON T1.CODE = T29.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGHF_us T30 ON T1.CODE = T30.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGHM_us T31 ON T1.CODE = T31.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGHSP_us T32 ON T1.CODE = T32.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGIF_us T33 ON T1.CODE = T33.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGIM_us T34 ON T1.CODE = T34.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGIND_us T35 ON T1.CODE = T35.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGNH_us T36 ON T1.CODE = T36.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGNHF_us T37 ON T1.CODE = T37.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGNHM_us T38 ON T1.CODE = T38.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGOF_us T39 ON T1.CODE = T39.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGOM_us T40 ON T1.CODE = T40.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGOTH_us T41 ON T1.CODE = T41.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGWF_us T42 ON T1.CODE = T42.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGWHT_us T43 ON T1.CODE = T43.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.AGWM_us T44 ON T1.CODE = T44.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.WH_AOH_us T45 ON T1.CODE = T45.CODE
INNER JOIN [HUSKY].USEP0712P.dbo.WNH_AONH_us T46 ON T1.CODE = T46.CODE
Not Sure If Upgrade To SQL 2005 Worked?
Feb 27, 2008
I have SQL 2000 Standard edition installed on a Windows server 2003 on a virutal box. SQL was upgraded to 2005 but during the install wizard, it did not prompt me for the Instance Name to upgrade. When done, I see the SQL Management Studio and when I connect, my databases are listed in the studio but the version number says 8.0.760 across the top of management studion which is SQL 2000 sp3.
Enterprise Manager is still available. It appears SQL 2005 was installed side by side with SQL 2000. Does that sound correct?
I downloaded SQL 2005 standard edition from my open license agreement. Don't know if that makes a difference.
I also tried upgrading SQL 2005 via the command prompt and it still shows verison number 8.0.760.
Where am I going wrong?
Thanks.
Calculate Time Worked By 15 Min Intervals.
Dec 19, 2007Ok, I know that there is a very smart programmer out there that can resovle my issue.
I am trying to calculate time worked by 15 minute intervals.
Example:
Emp 1 started work at 13:00:00 and worked 183 minutes
Emp 2 started work at 17:15:00 and worked 150 minutes
Emp 3 started work at 08:30:00 and worked 17 minutes
I need to show the following results:
time employee #of_min_worked
----------------------------------------------
08:30:00 3 15
08:45:00 3 2
09:00:00
08:30:00
08:45:00
09:00:00
09:15:00
09:30:00
09:45:00
10:00:00
10:15:00
10:30:00
10:45:00
11:00:00
11:15:00
11:30:00
11:45:00
12:00:00
12:15:00
12:30:00
12:45:00
13:00:00 1 15
13:15:00 1 15
13:30:00 1 15
13:45:00 1 15
14:00:00 1 15
14:15:00 1 15
14:30:00 1 15
14:45:00 1 15
15:00:00 1 15
15:15:00 1 15
15:30:00 1 15
15:45:00 1 15
16:00:00 1 3
16:15:00
16:30:00
16:45:00
17:00:00
17:15:00 2 15
17:30:00 2 15
17:45:00 2 15
18:00:00 2 15
18:15:00 2 15
18:30:00 2 15
18:45:00 2 15
19:00:00 2 15
19:15:00 2 15
19:30:00 2 15
19:45:00
20:00:00
20:15:00
20:30:00
20:45:00
21:00:00
21:15:00
21:30:00
21:45:00
22:00:00
22:15:00
22:30:00
22:45:00
23:00:00
23:15:00
23:30:00
23:45:00
How Does A View And SP Over A Linked Server Worked...
Feb 7, 2008
I am working with Two Server X and Z
In X server, I have a linked server named CustSrv which is connecting to Z server
In the Z server I have a Database named SalesDB
I have a view name vw_CusgtomerData in my X server which is selecting data from SALESDB..Customer_Tbl from the Z server through that linked server (CustSrv)
The View is simple selecting data from Customer_Tbl from SalesDB
SELECT * FROM CustSrv.SalesDB.dbo.CUSTOMER_Tbl
[Note here using * for all columns?? Is it ok for performance aspects]
Now I have some Application which are using that view through some stored procedure.Few of them passing some parameters like Cust_Id etc
Now my query is that.. Am I fulfilling all performance issues?
Or
What is the suggestive way to fetch data from that remote (Linked Server) server to get good performance benefit?
In my opinion we can fetch data 4 different way from that linked server¦
A.SELECT * FROM CustSrv.SalesDB.dbo.CUSTOMER_Tbl
B.exec GetCustomerData 65
[Sp_GetCustomerData is a Storedprocedure which is passing a parameter 65 that is Customer_ID and the procedure is selecting data from the view vw_CusgtomerData]
C.SELECT cu_customer_id, cu_customer_name FROM vw_CusgtomerData ORDER BY cu_customer_name
Or
SELECT * FROM vw_CusgtomerData where Customer_ID=65
D.select * FROM OPENQUERY
(CustSrv,'SELECT Customer_ID,cu_customer_name FROM SalesDB.dbo. CUSTOMER_Tbl ORDER BY cu_customer_name ')
Am I bypass the concept of view and fetch data directly in the stored procedure through the linked server ??
Why DTS Package Worked From DTS Designer But Failed On Scheduled Job?
Aug 11, 2006I created a DTS Package. It worked fine if I execute the DTS from DTS designer. It failed after I right clicked to Schedule Package, and than right clicked on the job to Start Job from Management - SQL Server Agnet - Jobs. When I clicked View Job History, the error was "The job failed. The last step to run was step 1 (DTSPackageName).
Why did it fail from scheduled DTS? Thanks.
DanYeung
Unable To Connect To SQL Server 2005 Worked Before
Mar 6, 2007I am developing an app in VB.Net 2005, and I installed SQL Server 2005 locally on my machine. My app access multiple databases on SQL and has worked like a charm in the past. Today, when I go to access my app, I receive an error when trying to connect to the SQL database.
"An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connection"
As I said before, this is a local install and worked fine until today. I checked the services running and Here is what I have:
SQL Server Integration Services - Running
SQL Server FullText Search - Stopped
SQL Server(SQLEXPRESS) - Running
SQL SERVER (MSSQLSERVER) - Stopped <---------- I tried restarting this and it times out
SQL Server Analysis Services (MSSQLSERVER) - Stopped
SQL Server Reporting Services (MSSQLSERVER) - Stopped
SQL Server Browser - Stopped
SQL Server Agent (MSSQLSERVER) - Stopped
I am unable to start any of these services... they all time out. Any help would be greatly appreciated!
Xp_cmdshell Statement Worked In SQL2000 Not In 2005
Dec 7, 2007Code Block
Hi All,
I've got a stored procedure called from a trigger on another database. There are two xp_cmdshell statements in the procedure. One writes a couple variables, passed from the trigger, into a text file. The second fires off an executable that uses the text file via a batch file.
Here's the code that worked on the old 32bit SQL2000 box
Code Block
begin
declare @cmd varchar(150)
select @cmd = 'echo ' + @sVariable1 + ',' + @sVariable2 +' > c:aFolderTextFile.txt'
exec master..xp_cmdshell @cmd
declare @cmd2 varchar(150)
select @cmd2 = 'c:aFolderRun.bat'
exec master..xp_cmdshell @cmd2
end
The new box is 64bit SQL2005. When invoked the stored procedure executes without an error. The textfile is written sucessfully. But, the executable called from the second xp_shell statement does not produce the expected result. Because there is no error I'm having trouble determining where the failure lies.
To troubleshoot the problem I've tried:
Running the batch file from the command prompt in the system32 directory... Success
Using sp_xp_cmdshell_proxy_account to be sure of the credentials of the running xp_cmdshell... Failure
My questions:
Does 64 Bit SQL2005 have a different set of parameters when invoking an executable from xp_cmdshell? ie Do I need to rewrite the batch file?
Is there another way call an executable, SQLCMD maybe?
Any help is appreciated...
Code Block
The batch file if you're curious:
"c:Program Files (x86)someVendersomeAppsomeEXE.exe" "C:aFolderTextFile.txt"
SQL Server 2012 :: SSIS Package Worked And Then Stopped?
Jun 22, 2014I create a package that has a foreach loop container, inside the FE container the is a dataflow, script task and a file system task, on the outside of the FE loop I have a SQL execute SQL task. I had it working then it just stopped. Been looking to see why but can't see why, is there something I missed. I changed the onError script task event handler, to propagate = false, because the last file in the source directory is being written to until 11:59 pm and is locked, and I get error file is being used by another process. I am at a loss as to why it would just stop working.
View 4 Replies View RelatedRemote Connection Problems, I've Read Many A Thread On This And Nothing Has Worked!
Apr 3, 2006Hi, I am trying to enable remote access on a SQL Server 2005 standard edition instance. I have enabled it in the properties using the Management studio and have enabled it under Sql Server Surface Area Configuration under "Database engine".
But it didn't work. I know for a fact that my connection string is correct (I tested it on the actual machine)
So I checked everything again and I noticed in the Sql Server Surface Area under "Analysis Services" there is another "remote connection" checkbox to be enabled, however it won't let me enable it there because it says the "Sql Browser" is not started. I checked and the Sql Browser is started.
I'm thinking maybe the problem there is that I have an instance of SQL 2000 running and perhaps it is the sql browser for that (I can remotely connect ot my SQL 2000 instance).
I checked under Computer management -> services and their is no SQL Browser for the instance of SQL 2005. Not even a thing to enable. Just the Default instance SQL Browser, which is for my SQL 2000 instance. Am I missing something here?
Also there is no SQL Browser to enable in the management or Surface Area Configuration programs.
I have also tried to connect using the default port number (1433) and the IP (to bypass the need for the SQL Browser). It apparently finds my Server 2000 instance because it doesn't accept my user name and password and connot find the specific database (note: I verified the username and pswd are correct by testing the values on the SQL machine). How do I find what port number a specific instance of SQL Server is running on?
Query That Never Finishes In SQL2005 But Worked Fine In 2000
Nov 14, 2007Hi,
I have a query which used to run fine on a rubbish SQL 2000 box in about a minute, but with SQL 2005 (SP2) it never finishes, even when left overnight. No errors in the logs or anything. It is the same database which was backed up from SQL 2000 and restored into 2005. Does anybody have any ideas?
Cheers
Steve
SELECT DISTINCT R1.RowVersionId, R2.EnumID AS A, R2.EnumID AS B, R4.EnumID AS C, R6.EnumID AS D, R8.EnumID AS E, R10.EnumID AS F, R12.EnumID AS G, R14.EnumID AS H
FROM
RowRuns AS R1
INNER JOIN XRunConfigEnum AS R2 ON R1.RunVersionID = R2.RunVersionId
INNER JOIN RowRuns AS R3 ON R1.RowVersionId=R3.RowVersionId
INNER JOIN XRunConfigEnum AS R4 ON R3.RunVersionID = R4.RunVersionId
INNER JOIN RowRuns AS R5 ON R1.RowVersionId=R5.RowVersionId
INNER JOIN XRunConfigEnum AS R6 ON R5.RunVersionID = R6.RunVersionId
INNER JOIN RowRuns AS R7 ON R1.RowVersionId=R7.RowVersionId
INNER JOIN XRunConfigEnum AS R8 ON R7.RunVersionID = R8.RunVersionId
INNER JOIN RowRuns AS R9 ON R1.RowVersionId=R9.RowVersionId
INNER JOIN XRunConfigEnum AS R10 ON R9.RunVersionID = R10.RunVersionId
INNER JOIN RowRuns AS R11 ON R1.RowVersionId=R11.RowVersionId
INNER JOIN XRunConfigEnum AS R12 ON R11.RunVersionID = R12.RunVersionId
INNER JOIN RowRuns AS R13 ON R1.RowVersionId=R13.RowVersionId
INNER JOIN XRunConfigEnum AS R14 ON R13.RunVersionID = R14.RunVersionId
WHERE
((R2.ParamID='ee72510e-3bab-49f6-1ff9-4d09cbe8670a' AND (R2.EnumID = '1a2868fb-72ef-e1d3-e79d-fbb5814ab481')))
AND
((R4.ParamID='7aadb3a4-3d13-8e0d-bfa4-4243ed1fdb35' AND (R4.EnumID = '745fb00c-0b16-7b4e-bf8f-da0f46777ca0')))
AND
((R6.ParamID='8c9aee3a-df1f-6ec5-131a-8fa0309ce1ff' AND (R6.EnumID = 'c7af1456-56bc-ba9c-f1e4-95cfd5542d10')))
AND
((R8.ParamID='61a714fa-8b20-1e7e-1adb-c680f72ddf0d'))
AND
((R10.ParamID='d9f0645c-e1be-b5c2-906f-ff3c5b9de0df'))
AND
((R12.ParamID='1916773f-1bf9-eea5-e702-5f293b3047a2'))
AND
((R14.ParamID='c37d4377-f6dd-69bc-16ef-bd06c76f400e'))
SQL Server 2012 :: Grouping By Percentage Of Time Worked By Person
Jul 2, 2014I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL
[Code] ....
EXPECTED RESULT:
Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5
Strange Exceptions With Views Usings Casts On 2005 (worked Fine On Sql2k)
Apr 3, 2007I have views that cast the data into data types.
On 2005 when retrieving the views using a where clause i get errors. I have put together an example below that illustrates the error.
CREATE TABLE TestData ( Id VARCHAR(10), [Description] VARCHAR(20), [Type] INT)
CREATE TABLE TestTypeLookup( [Type] INT, [Description] VARCHAR(20))
GO
INSERT INTO TestTypeLookup VALUES (1, 'Type 1')
INSERT INTO TestTypeLookup VALUES (2, 'Type 2')
INSERT INTO TestData VALUES ('AA', 'Description for AA', 1)
INSERT INTO TestData VALUES ('10', 'Description for 10', 2)
INSERT INTO TestData VALUES ('20', 'Description for 20', 2)
GO
CREATE VIEW TestView AS
SELECT TOP 100 PERCENT CAST(Id AS INT) Id, [Description]
FROM TestData
WHERE [Type] = (SELECT TOP 1 [Type] FROM TestTypeLookup WHERE [Description] = 'Type 2')
ORDER BY CAST(Id AS INT)
GO
SELECT * FROM TestView WHERE Id = 10
GO
DROP VIEW TestView
DROP TABLE TestData
DROP TABLE TestTypeLookup
GO
Code!func To Get Values In Header Only Works In IDE And Not Deployed Report (also Worked Ok In RS2000)
Jul 18, 2007I have code
Function GetDealCount(reportItems)
return iif(IsNothing(reportItems!txtDetailCountRows.Value), 0, reportItems!txtDetailCountRows.Value)
End Function
Function GetSumNotionalAmount(reportItems)
return iif(IsNothing(reportItems!txtDealSumNotionalAmount.Value), 0, reportItems!txtDealSumNotionalAmount.Value)
End Function
That I am calling from a textboxes in the page header
= Code.GetDealCount(ReportItems) & " Deal(s)"
also
= Parameters!BaseCurrency.Value + " " + Format(Code.GetSumNotionalAmount(ReportItems),"N2").ToString().Replace(",","'")
When I preview the report in VS.NET I get values showing.
When I deploy the report I just get #Error showing.
Also this report used to work fine in RS2000
Does anyone know the cause of this issue?
HTML Viewer - Toolbar And Parameters Section - No Style Applied In RS2005 Worked In RS2000
Nov 30, 2007Hi all,
Not sure what's going on here...
I had reports using the HTML Viewer working in RS 2000. The servers were both O/S Windows 2000, SQL Server 2000, IIS 5.0, Reporting Services 2000) The default toolbar and parameters area were formatted and looked fine.
We just migrated to new servers with Windows 2003 Server, SQL Server 2005, IIS 6.0, Reporting Services 2005. I have the reports working fine...except the toolbar and parameters areas look as if there is no formatting at all...as if the style sheet isn't being applied (not even the default one --see below).
Although I don't think this is necessary....I tried the following to resolve the issue....
Updated the RSReportServer.config file.
Driveletter:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config
Checked that the HtmlViewer.css file was in the ...ReportServerStyles folder.
Added the following line between the
<Configuration>.......</Configuration> tags in the RSReportServer.config file.
<HTMLViewerStyleSheet>HtmlViewer</HTMLViewerStyleSheet>
Find
|
Next
Select a format XML file with report data CSV (comma delimited) TIFF file Acrobat (PDF) file Web archive Excel
Export
I can't find anyone else having this issue...any ideas?
Hope someone can help...thanks for looking....
Leslie
Breaking Down Total Hours Worked Into Day And Evening Hours
Sep 21, 2006I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View Related