DateTime.Now Expression Expected Problem
Apr 3, 2007
Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column.
Using the default insert created, I have the following code:
Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Profile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now)
The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using:
Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Dim pd as partyDetails.partyDetailsDataTable
pd = da.GetPartyDetailsByID(Profile.partyid)
da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid)
Have I an error in my Insert section?
Thanks for any help,
Mark
View 1 Replies
ADVERTISEMENT
Aug 20, 2007
Hi,I am trying to create an update statementon a table with a foreign key to the Userstable (userid).I am getting the error:An expression of non-boolean type specified in a context where acondition is expected, near ')'.Below is the Update statement.UPDATELastLogin SETdate= '2007-08-14 05:34:09.910',status= 1 ,activity = 0WHERE(SELECT ll.status, ll.activity, ll.dateFROM LastLogin ll, Users uWHERE ll.userid = u.useridAND u.email = 'dushkin@hotmail.com')Thanks!
View 2 Replies
View Related
Apr 10, 2015
I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
[code]...
what I need to change in the WHERE clause to rectify this error ?
View 3 Replies
View Related
Jun 3, 2008
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59.
[SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code>
Here is my button click sub in its entirety:
1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs)
2 'Variable declarations...
3 Dim articleid As Integer
4 articleid = Request.QueryString("aid")
5 Dim strSelectQuery, strInsertQuery As String
6 Dim strCon As String
7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection()
8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand()
9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader
10 Dim MyHttpAppObject As System.Web.HttpContext = _
11 System.Web.HttpContext.Current
12 Dim strRemoteAddress As String
13 Dim intSelectedRating, intCount As Integer
14 Dim Ratingvalues As Decimal
15 Dim Ratingnums As Decimal
16 Dim Stars As Decimal
17 Dim Comments As String
18 Dim active As Boolean = False
19 Me.lblRating.Text = ""
20 'Get the user's ip address and cast its type to string...
21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress)
22 'Build the query string. This time check to see if IP address has already rated this ID.
23 strSelectQuery = "SELECT COUNT(*) As RatingCount "
24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'"
26 'Open the connection, and execute the query...
27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString
28 conMyConnection.ConnectionString = strCon
29 conMyConnection.Open()
30 cmdMyCommand.Connection = conMyConnection
31 cmdMyCommand.CommandType = System.Data.CommandType.Text
32 cmdMyCommand.CommandText = strSelectQuery
33 intCount = cmdMyCommand.ExecuteScalar()
34 intSelectedRating = Int(Me.rbRating.Text)
35 conMyConnection.Close()
36 'Close the connection to release these resources...
37
38 If intCount = 0 Then 'The user hasn't rated the article
39 'before, so perform the insert...
40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) "
41 strInsertQuery += "VALUES ("
42 strInsertQuery += intSelectedRating & ", '"
43 strInsertQuery += strRemoteAddress & "', "
44 strInsertQuery += articleid & ", '"
45 strInsertQuery += comment.Text & "', '"
46 strInsertQuery += active & "'); "
47 cmdMyCommand.CommandText = strInsertQuery
48 conMyConnection.Open()
49 cmdMyCommand.ExecuteNonQuery()
50 conMyConnection.Close()
51 Me.lblRating.Text = "Thanks for your vote!"
52 Comments = comment.Text.ToString
53
54 If Len(Comments) > 0 Then
55 emailadmin(comment.Text, articleid)
56 End If
57 'now update the article db for the two values but first get the correct ratings for the article
58 strSelectQuery = _
59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
61 conMyConnection.Open()
62 cmdMyCommand.CommandText = strSelectQuery
63 dtrMyDataReader = cmdMyCommand.ExecuteReader()
64 dtrMyDataReader.Read()
65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
67 Stars = Ratingvalues / Ratingnums
68 conMyConnection.Close()
69 'Response.Write("Values: " & Ratingvalues)
70 'Response.Write("Votes: " & Ratingnums)
71
72 UpdateRating(articleid, Stars, Ratingnums)
73 Else 'The user has rated the article before, so display a message...
74 Me.lblRating.Text = "You've already rated this article"
75 End If
76 strSelectQuery = _
77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount "
78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid
79 conMyConnection.Open()
80 cmdMyCommand.CommandText = strSelectQuery
81 dtrMyDataReader = cmdMyCommand.ExecuteReader()
82 dtrMyDataReader.Read()
83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString)
84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString)
85 Stars = Ratingvalues / Ratingnums
86 If (Ratingnums = 1) And (Stars <= 1) Then
87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
88 ElseIf (Ratingnums = 1) And (Stars > 1) Then
89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote"
90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then
91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
92 ElseIf (Ratingnums > 1) And (Stars > 1) Then
93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes"
94 End If
95
96 'Response.Write(String.Format("{0:f2}", Stars))
97 'Response.Write("Values: " & Ratingvalues)
98 'Response.Write("Votes: " & Ratingnums)
99 If (Stars > 0) And (Stars <= 0.5) Then
100 Me.Rating.ImageUrl ="./images/rating/05star.gif"
101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then
102 Me.Rating.ImageUrl = "./images/rating/05star.gif"
103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then
104 Me.Rating.ImageUrl = "./images/rating/1star.gif"
105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then
106 Me.Rating.ImageUrl = "./images/rating/15star.gif"
107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then
108 Me.Rating.ImageUrl = "./images/rating/2star.gif"
109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then
110 Me.Rating.ImageUrl = "./images/rating/25star.gif"
111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then
112 Me.Rating.ImageUrl = "./images/rating/3star.gif"
113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then
114 Me.Rating.ImageUrl = "./images/rating/35star.gif"
115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then
116 Me.Rating.ImageUrl = "./images/rating/4star.gif"
117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then
118 Me.Rating.ImageUrl = "./images/rating/45star.gif"
119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then
120 Me.Rating.ImageUrl = "./images/rating/5star.gif"
121 End If
122 dtrMyDataReader.Close()
123 conMyConnection.Close()
124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating:
http://www.link-exchangers.com/view_full_article.aspx?aid=51
Thanks for helping me figure this out.
View 4 Replies
View Related
Aug 1, 2007
Hello,
I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.
Any ideas?
Thank you for your help!
cdun2
View 4 Replies
View Related
Sep 20, 2006
Hi all,In the beginning of this month I've build a website with a file-upload-control. When uploading a file, a record (filename, comment, datetime) gets written to a SQLExpress database, and in a gridview a list of the files is shown. On the 7th of September I uploaded some files to my website, and it worked fine. In the database, the datetime-record shows "07/09/2006 11:45". When I try to upload a file today, it gives me the following error: Error: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.While searching in google, i found it might have something to do with the language settings of my SQLExpress, I've tried changing this, but it didn't help. What I find weird is that it worked fine, and now it doesn't anymore. Here is my code of how I get the current date to put it into the database:1 SqlDataSource2.InsertParameters.Add("DateInput", DateTime.Now.ToString());
Am I doing something wrong, or am I searching for a solution in the wrong place? best regards, Dimitri
View 3 Replies
View Related
Jan 7, 2008
Hi,
I'm having this error with my page, user picks the date -using the AJAX Control Toolkit Calender with the format of ( dd/MM/yyyy ).
It looks like the application current format is MM/dd/yyyy, because it shows the error page if the day is greater than 12, like: 25/03/2007
What is wrong?
Here is the error page:
Server Error in '/' Application.
Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
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.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
Any help will be appreciated!
View 3 Replies
View Related
Jun 12, 2014
The following codes give me the error "arithmetic overflow error converting expression to data type datetime" Unfortunately, the datatype of date of this database is still varchar(50)
select date from tbltransaction
where datepart(wk,convert(datetime,date,103)) = 15
View 3 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Aug 17, 2004
Hi,
I'm trying to set the value of the variable @prvYearMonth thru this sp. In the query analyzer I execute the following code to the see the results of my 'CabsSchedule_GetPrevYearMonth' SP, but the only see "The Command(s) completed successfully in the result. What am I missing??
Thanks in advance
CREATE PROCEDURE CabsSchedule_GetPrevYearMonth
(
@prvYearMonth int OUTPUT
)
AS
BEGIN
SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
GO
View 3 Replies
View Related
Jul 1, 2004
I have the following
SELECT @cLastBarcode
SELECT @tmpCount = RIGHT(@cLastBarcode, 4)
SELECT @tmpCount
SELECT @cLastBarcode returns '14001DT0010006'
BUT
SELECT @tmpCount returns nothing. The RIGHT(....) function does not render any results. I am expecting '0006'.
I read that the data type must be compatible with varchar. The @cLastBarcode was declare as char(25). I have even tried casting the @cLastBarcode char string to type varchar.
Any hints?
Mike B
View 2 Replies
View Related
Dec 31, 2007
I did a trace on a production DB for many hours, and got more than 7 million of "RPC:Completed" and "SQL:BatchCompleted" trace records. Then I grouped them and obtained only 545 different events (just EXECs and SELECTs), and save them into a new workload file.
To test the workload file, I run DTA just for 30 minutes over a restored database on a test server, and got the following:
Date 28-12-2007
Time 18:29:31
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time 31 Minutes
Time taken for tuning 31 Minutes
Expected percentage improvement 20.52
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 8116
Number of events in workload 545
Number of events tuned 80
Number of statements tuned 145
Percent SELECT statements in the tuned set 77
Percent INSERT statements in the tuned set 13
Percent UPDATE statements in the tuned set 8
Number of indexes recommended to be created 15
Number of statistics recommended to be created 50
Please note that only 80 of the 545 events were tuned and 20% of improvement is expected if 15 indexes and 50 statistics are created.
Then, I run the same analysis for an unlimited amount of time... After the whole weekend, DTA was still running and I had to stop it. The result was:
Date 31-12-2007
Time 10:03:09
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time Unlimited
Time taken for tuning 2 Days 13 Hours 44 Minutes
Expected percentage improvement 0.00
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 7534
Number of events in workload 545
Number of events tuned 545
Number of statements tuned 1064
Percent SELECT statements in the tuned set 71
Percent INSERT statements in the tuned set 21
Percent DELETE statements in the tuned set 1
Percent UPDATE statements in the tuned set 5
This time DTA processed all the events, but no improvement is expected! Neither indexes/statistics creation recomendation.
It does not seem that Tuning Advisor crashed... Usage reports are fine and make sense to me.
What's happening here? It looks like DTA applied the recomendations and iterated, but no new objects where found in DB.
I guess that recomendations from the first try with only 80 events were invalidated by the remaining from the long run.
I couldn't google an answer for this. Help!!!
Thanks in advance.
++Vitoco
View 1 Replies
View Related
Dec 12, 2007
My first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.
Here's the class member code:
Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long
' Returns the size of the specified file.
' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.
Dim lngFileSize As Long
Dim objFileInfo As FileInfo
' Confirm file exists.
If Not File.Exists(strTargetFolder & "" & strTargetFile) Then
Return -1
End If
Try
objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)
lngFileSize = objFileInfo.Length
Catch
' TODO: add error handling; system folders cause error during processed.
End Try
Return lngFileSize
End Function
In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
-- For file system access, set the database TRUSTWORTHY property.
ALTER DATABASE dba_use
SET TRUSTWORTHY ON
CREATE ASSEMBLY GetFolderInfo
FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'
WITH PERMISSION_SET = UNSAFE
Here's the udf declaration:
CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )
RETURNS bigint
AS EXTERNAL NAME
GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize
And the function call - note the target file is on a remote server. Actual file name differs slightly:
SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')
This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.
Any assistance is greatly appreciated.
View 3 Replies
View Related
Aug 8, 2007
I posted this at the asp.net forums but somone suggested I post it here. So:
Try this in sql server:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
W
Y
Ya
Z
þ
The second one sorts like this:
W
Y
ÿ
Ya
Z
Is this expected behavior?
View 1 Replies
View Related
Jul 18, 2006
So I€™m at a dead-end looking for the reason behind the following behavior. Just to make sure no one misses it, the 'behavior' is the difference in the number of reads between using sp_executesql and not.
The following statements are executed against a SQL 2000 database that contains >1,000,000 records in the act_item table. They are run using Query Analyzer and the Duration and Reads come from SQL Profiler
SQL 1:
exec sp_executesql N'update act_item set Priority = @Priority where activity_code = @activity_code', N'@activity_code nvarchar(40),@Priority int', @activity_code = N'46DF335F-68F7-493F-B55E-5F9BC6CEBC69', @Priority = 0
Reads: ~22000
Duraction: 250-350 ms
SQL 2:
DECLARE @Priority int
DECLARE @Activity_Code char(36)
SET @Priority = 0
SET @Activity_Code = '46DF335F-68F7-493F-B55E-5F9BC6CEBC69'
update act_item set Priority = @Priority where activity_code = @activity_code
Reads: ~160
Duration: 0 ms
Random information:
Activity_code is an indexed field on the table, although it is not the primary key. There are a total of four indexes on the table, none of which include the priority as one of the fields.
There are two triggers on the table, neither of which is executed for this SQL statement (there is an IF UPDATE(fieldname) surrounding the code in the trigger)
There are no foreign relationships
I checked (using perfmon) to see if a compilation/recompilation was happening. No it's not.
Any suggestions as to avenues that could be examined would be appreciated.
TIA
View 3 Replies
View Related
Feb 17, 2005
Hi All,
I am kindly seeking for help.
I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following:
Date ID R P M D... Y
1/1/90 A 1 2 3 4... 5
1/2/90 A 2 3 4 5... 1
...
2/11/05 A 3 4 5 6... 2
1/1/90 B 1 2 3 4... 5
1/2/90 B 2 3 4 5... 1
...
2/11/05 B 3 4 5 6... 2
...
The expected query results look like: ( this results from Date, ID and R fields)
Date A B
1/1/90 1 1
1/2/90 2 2
...
2/11/05 3 3
The SQL I wrote:
select date, ID,
A=sum(case when ID=A then R else 0 end),
B=sum(case when id=B then R else 0 end)
from MyTable
Group by date
I would also like to get another set of results with the same format but from date,ID and P fields:
Date A B
1/1/90 2 2
1/2/90 3 3
...
2/11/05 4 4
select date, ID,
A=sum(case when ID=A then P else 0 end),
B=sum(case when id=B then P else 0 end)
from MyTable
Group by date
The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this?
I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.
Any suggestion/comments are highly appreciated!
shiparsons
View 4 Replies
View Related
Oct 24, 2005
I use the following sproc to populate a table that is used as the base recordset for a report.
For some reason, when the sproc is run from a scheduled job, it doesn't repopulate the table. It does, however, truncate the table. If I run it manually from query analyzer, it works fine.
I've checked all the permissions on all the object touched by the sproc, and everything looks right there. Is there another problem I should be looking for?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
setuser N'mcorron'
GO
CREATE PROCEDURE mcorron.CreateDiscOrders
AS
/*
Creates table for Orders with disc items Actuate report
*/
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
TRUNCATE TABLE dbo.rptDiscOrders
INSERT INTO dbo.rptDiscOrders
SELECT *
FROM (SELECT ORD.product as prod_XREF, ORD.ORDER_NUMB, ORD.CustName, ord.units as ordunits, INV.Product,
INV.Units
FROM (SELECT TOP 100 PERCENT f.PARENT_SITE, f.SITE, dbo.vwCustBillTo.CustName, o.ORDER_NUMB, p.Prod_Xref, o.PRODUCT,
o.ORDER_TONS * 2000 / m.part_wt AS UNITS
FROM dbo.Lawn_Orders o INNER JOIN
dbo.PRODUCT_XREF p ON o.PRODUCT = p.Product INNER JOIN
dbo.FACILITY_MASTER f ON o.WHSE = f.SITE INNER JOIN
dbo.Lawn_PartMstr m ON o.PRODUCT = m.part_code INNER JOIN
dbo.vwCustBillTo ON o.BILLTO = dbo.vwCustBillTo.BillToNum
WHERE (o.SHIP_DATE < DATEADD(d, 30, GETDATE())) and prod_xref not like 'dead%') ORD INNER JOIN
(SELECT f.PARENT_SITE, x.Prod_Xref, i. Product, SUM(i.Qty) AS Units
FROM dbo.Lawn_Inventory i INNER JOIN
dbo.FACILITY_MASTER f ON i.Whse = f.SITE INNER JOIN
dbo.PRODUCT_XREF x ON i. Product = x. Product
WHERE (f.WHSE_TYPE = 'ship')
GROUP BY f.PARENT_SITE, x.Prod_Xref, i. Product) INV ON ORD.PARENT_SITE = INV.PARENT_SITE AND ORD.Prod_Xref = INV.Prod_Xref)
ordinv
WHERE (Prod_Xref <> Product)
GO
setuser
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks
View 4 Replies
View Related
Oct 18, 2007
Do you see anything wrong with this? The first select works and finds rows the second one does not. I have opened the Key since the first query does find rows.
select *
from [dbo].[dmTable]
WHERE cast(decryptByKey(field) as varchar(50)) = 'Value'
select *
from [dbo].[dmTable]
where field = EncryptByKey(Key_GUID('CLTCadminKey'),'Value')
View 1 Replies
View Related
Feb 19, 2008
I have a stored procedure that is Averaging a Difference in dates in seconds. All of the sudden it started throwing an Arithmetic overflow error. After running the query below on the same data, I can see that it is because the DateDiff in my procedure, which is calculating the difference in seconds, is returning a value greater than 68 years. Looking at the dates in the result table, I don't see how it is coming up with the values in the Years Difference column.
Code SnippetSELECT createdate, completeddate, DATEDIFF(y, createdate, completeddate) as 'years difference'
FROM tasks
WHERE (TaskStatusID = 3) and (createdate < completeddate) and (DATEDIFF(y, createdate, completeddate)>=68)
ORDER BY completeddate
Results:
CreateDate
CompletedDate
Years Difference
2007-07-28 16:26:17
2007-10-08 20:45:19
72
2007-07-28 15:56:13
2007-10-12 19:40:28
76
2007-07-28 16:06:20
2007-10-18 21:00:05
82
2007-07-30 01:15:54
2007-10-21 20:18:43
83
2007-07-29 23:12:08
2007-10-22 23:22:47
85
2007-08-16 11:17:29
2007-10-23 15:47:32
68
2007-07-25 21:20:44
2007-10-23 19:11:32
90
2007-07-29 23:04:53
2007-10-23 21:09:47
86
2007-08-22 18:29:50
2007-11-02 14:29:12
72
2007-08-23 18:33:38
2007-11-02 14:38:34
71
2007-07-29 21:46:10
2007-11-02 23:24:39
96
2007-08-14 05:18:17
2007-11-03 21:08:30
81
View 21 Replies
View Related
Jan 15, 2007
I have an Execute SQL Task that selects one column value from one row, so General > ResultSet = Single row. Result Set > Result Name = 0 (the first selected value) and Variable Name = User::objectTypeNbr. The task runs successfully, but after the it runs the value of User::objectTypeNbr is not changed.
User::objectTypeNbr > Data Type = Int32. When I declared the variable Value could not be empty so I set it to 0 arbitraily, assuming it would be overwritten when assigned a new value by the Execute SQL Task, but it remains 0 after the task runs. What am I missing here?
View 11 Replies
View Related
Oct 11, 2007
Hi,
I am trying to convert an active x script in a script task. Below is a snippet of code. The underlined AsOfDate has a blue squiggly line under it and if I hover over it, it says "Declaration Expected."
Public Class ScriptMain
Dim AsOfDate As String
AsOfDate = Dts.Variables("MyDate").Value
...
Can someone please tell me what I'm missing? I thought maybe I'm missing an import statement, but I have:
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
I have used similar syntax in script components and it works fine.
Thanks
View 6 Replies
View Related
Oct 9, 2006
Hi,
In SQL 2005 SP1 - In my transactional replication RMO C# script, I want my snapshot job schedule to run daily at 2:58 AM.
Instead it runs hourly in the 58th minute. Sample code below shows I use the value 025800. That should be interpretted as AM. The frequencytype is daily. The frequency interval is 1. There is no subday frequency. Yet the job runs hourly and disregards the specified hour.
Is there something missing in this code? Is this a SQL Server bug?
// Set the required properties for the trans publication snapshot job.
TransPublication tpublication = new TransPublication();
tpublication.ConnectionContext = conn;
tpublication.Name = publicationName;
tpublication.DatabaseName = publicationDbName;
tpublication.SnapshotSchedule.FrequencyType = ScheduleFrequencyType.Daily;
tpublication.SnapshotSchedule.FrequencyInterval = Convert.ToInt32(0x0001);
tpublication.SnapshotSchedule.ActiveStartDate = 20051101;
string newString = "025800";
tpublication.SnapshotSchedule.ActiveStartTime = Convert.ToInt32(newString);
tpublication.Create();
View 3 Replies
View Related
Oct 25, 2007
Greetings.
What do I now have:
A directory with Access Databases; around 20 databases, all dinamicly created;
Each Database has on average 300 tables inside, all equally structured, all created by software;
Each table has two DateTime fields, 4 double fields and 4 long int fields;
Each table has around 10000 records, average.
The Directory is shared in a Windows 2003 Enterprise server.
Around 20 users access the databases simultaneously, adding, retrieving and deleting data, over 100MBits LAN.
Here's the catch:
As fast as possible, the program needs to retrieve 1 single record matching a single date from a given table in a given database. All databases work together. It needs to gets litterally thousands of individual records in order to work properly. Per user. That means thousands of requests, but not much data in each request. That's its core job.
A small percentage of request write the record back , that is, update it. Maybe 2% of requests.
If I were to reproduce this situation in a SQL server 2005, what would be the expected time for lets say 50000 requests ?
Or should I stick to Access ?
Thanks,,
Any response will be apreciated.
Pedro Ramos
View 1 Replies
View Related
Aug 9, 2007
Hi,
I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called
External Table is not in the expected format
System.Data.OleDb.OleDbException: External table is not in the expected format
Any help will be appreciated.
Regards,
Karen
View 8 Replies
View Related
Feb 25, 2006
I was hoping that the Trim function inside the update command, cmdUpdate.Parameters.Add("@doc_num", Trim(txtDocNum.Text))
, would deal with any leading and trailing spaces, but it does not seem
to be doing anything at all. The value from the textbox still arrives
in the database table with leading spaces!!
Am I doing something wrong?
View 7 Replies
View Related
Jul 27, 2000
Hi,
I am trying to extract the non matching records and the matching ones
in two tables from two servers, one a linked server in one go.
For example if table A has records,
Rec1, Rec2, Rec3 and Rec6
AND Table B has
Rec1, Rec2, Rec3 and Rec7
I need to get in the result set
Rec1, Rec2, Rec3, Rec6 and Rec7.
The real query I ran is as follows. I want to know the list of all tables
in GlobalDB database in sg_qt1 and sg_q5 servers.
NOTE : sg_q5 is a Linked server to sg_qt1.
Select Substring(a.name,1,30), Substring(user_name(a.uid),1,19)
from sysobjects a
full outer JOIN sg_q5.globaldb.dbo.sysobjects b
ON a.name = b.name
where a.xtype = 'u' and b.xtype = 'u'
and a.name not in ('dtproperties','rowcounts','globalDBrowcounts')
If I run it from sg_qt1, the result I get contain all tables from sg_qt1
but not the non-matching ones from sg_q5.
I am obviously doing something wrong, but what is it?
Thanks.
If possible please reply to, r.wimalaratne@iaea.org
Ranjit
View 1 Replies
View Related
Nov 29, 2012
I am having a query
select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber
tarrifheadno SUM(INVOICEITEMS.ItemQuantity)
84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20
tarrifheadno is unique
Now if i use below query and add invoicetypecode field
select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode
This query return distinct of 11 records
84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2
How to get same 7 records
View 1 Replies
View Related
Apr 26, 2006
Hello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?
View 9 Replies
View Related
May 7, 2007
Hello,
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
I expect that the sorting will be performed within the entire table(data in both of groups will be sorted alphabeticaly by company name).
Instead of it, the sorting is performed independently for every group of table. What is the reason??
Here is the report
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<ConnectString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:wind.mdb</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<InteractiveWidth>8.5in</InteractiveWidth>
<Body>
<ReportItems>
<Table Name="table1">
<Left>0.375in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox3">
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>MediumTurquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<TableColumn>
<Width>3.15in</Width>
</TableColumn>
<TableColumn>
<Width>1.08in</Width>
</TableColumn>
<TableColumn>
<Width>0.27in</Width>
</TableColumn>
</TableColumns>
<Height>0.5in</Height>
</Table>
</ReportItems>
<Height>0.75in</Height>
</Body>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>select * from customers where country='Mexico' or country='Austria'</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
</Field>
<Field Name="City">
<DataField>City</DataField>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
View 4 Replies
View Related
May 2, 2007
I have a package that has a container containing multiple DF Tasks.
The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.
This works as expected.
However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.
However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.
This is not the functionality I see. The package restarts from the failed task within the container every time.
According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.
A previous forum message encountered the same issue it appears:
See SSIS Checkpoints 04 Dec 2006.
This is an extract from it:
"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.
In short, I felt that check points were not aware of transactions.
So, I ended up with this setting and it works for me:
Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.
That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."
While this makes sense to me it is not the same properties that the SSIS book has that work.
Additionally, this didn't work for me either !!
I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.
The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.
View 1 Replies
View Related
Mar 30, 2007
Hi,
I have a ForEach Loop that has 3 script tasks in it.
I have them set up so that they execute in order, such as:
script1 ---> script2 ---> script3
script1 creates a file
script2 creates a file
script3 compares the files using a diff command
Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.
The error is "The system cannot find the file specified".
Thanks
View 2 Replies
View Related
Nov 28, 2007
I'm reading a lot of data from a database using SqlDataReader. Now I'd like to report the progress to the user, but for this I need to know in advance how many items SqlDataReader will return.
Is there any way to get this 'number of expected results' from the SqlDataReader?
tia,
Sam
View 1 Replies
View Related