SSRS Report Paramets - Date Type Datetime !Strange Behaviour!
Dec 12, 2007
I have, I think a strange one.
I have a simple query which has a parameter 'between @Start and @Finish'
My user within the SQL management studio is set to British English.
My report language properties are set to English (United Kingdom)
If I set the parameter to data type string and use start and finish of 31/12/2007, 30/12/2008 the report works fine.
If I change the data type of the parameter to Datetime (to use the calander pickers) the date values are no longer valid.
Where else should I be looking for regional settings? Or is there another reason for this?
Hello to everybody. I am experiencing a strange behaviour of Report Builder. I have an AS2005 database with a cube with calculated members (all measures calculation). In the client application they need to see only the calculated members so I setup the visible option to "false" for all the others measures. If I browse the cube with browser of AS I have no problem, I can see the data in any shape possible. To give an example I have a report that shows for each Store how many visitors I received each month. The report has the store in the rows and the months in the columns.
If I try to create the same reports with Visual Studio 2005 I have no problem either. Everything works fine. After doing these test I build up a model for the report builder. I connected to the report builder and I tried to create the same report and I got this message: "No data was found that matched your report criteria". After that error I need to create a new report and start all over again. If I create a simple report with just the store and the number of visitors I do not have any problem.
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub
Hi to all! i had tried to install ads on a windows 5. i am able to connect to the pocket database. however when i trie to connect to the database that's on desktop i see a little window (probably a message) without anything and when i tap ok i see a message telling that the connection to the desktop database was not done! i had made the configuration before. can you tell me what is going wrong?
We have this webiste which uses SQL express as database engine. Sometimes certain features of the website stop working. Like membership provider and other database related things. I have described the problem in more details here: http://forums.asp.net/t/1172253.aspx In consice the problem is: One query with fixed inputs does not always return the same results, though the data has not changed, you restart the SQL express and the problem resolives! I think thats a problem with SQL express, because when you restart SQL express everything starts working. Our database is kinda big. Like above 500 MB with up to 50 concurrent users. And our machine got a 3.2 CPU with 512 MB of ram. And our application is the only application runing there. What do you think please?
I have a scheduled job that inserts some records into a table. It fails with the following message, Violation of PRIMARY KEY constraint 'PK_FuturesOut'. Cannot insert duplicate key in object 'FuturesOut'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
The strange thing about this is, if I copy the SQL statement from the job and paste it into Query Analyzer, it works without any modifications.
If anyone can explain this I would be most grateful.
I use bcp fairly often in SQLServer2000 but have never run across this before. In a 512 SQLCHAR column containing notes, when two spaces are encountered (i.e. ' '), bcp is replacing ' ' with ' '.
I figured it was a problem with my format file, but I have not found enough specific info on the MSDN site to resolve this.
This is my bcp command:
bcp "SELECT DivCode,CommCode,ContactID,substring(Notes, 0, 512) from frep.dbo.BeBackExtract" queryout "D:BeBackDetail.dat" -f "D:BeBackDetail.fmt" -e "D:BeBackDetailErrors.dat" -U user -P pass -S server
Incidentally I also tried using the REPLACE function which seems to work great until trying to replace 2 spaces with 1 space, in which case it doesn't do anything.
I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query
'SELECT ID FROM Table2'
returns: Invalid column name 'ID'. OK. Now when I run the query
'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)'
it returns all the records from Table1.
What gives? Is this a bug, or am I missing something?
I am facing a strange problem. SSIS is executing a piece of code which is inside comment !!!
I have got a master package which executes number of other packages. When I right click on a package and click "Execute task", SSIS respects the comments and ignores code inside it.
But when I execute master package, SSIS executes piece of code which is inside comment!!!
The only thing I can think of is when executed from Master package, SSIS somehow picks very old precompiled binary. BTW, precompile=false for the script component so it shouldn't use precompiled binary at all.
I am sure it will be fixed if I delete and recreate this particular script component with the same script. I (luckily!) picked up this problem but the thought of SSIS not executing intended binary scares the hell out of me.
Any thoughts??? or its just me happened to be using SSIS on Friday?
We have a view looking at all the columns of a table on a SQL server 7.0 db. When we link this view to MSAccess, we are seeing the old data which was there previously in the original table! not the latest data. We recompiled the table and views and also re-linked the view to MSAccess with no help,we still see the old data only.This is happening only for one of the views not for all.Any help??? sa.
When write the following in Query Analyzer (SQL-Server 7 SP1):
declare @aA int select @aa = 1
I get:
Server: Msg 137, Level 15, State 1, Line 3 Must declare the variable '@aa'.
SQL-Server 6.0 and 6.5 seems to work OK.
I have tried several other random combinations f.ex.:
declare @Aa int select @aa = 1
and all seems to work OK
I dicovered the error when I used the upgrade wizard to move a database from SQL-Server 6 to SQL-server 7
sp_helpsort gives the following:
Unicode data sorting ---------------------- Locale ID = 1044
case insensitive, kana type insensitive, width insensitive
Sort Order Description ---------------------------------------------------------------------------- -------------------------------------------------------- Character Set = 1, iso_1 ISO 8859-1 (Latin-1) - Western European 8-bit character set. Sort Order = 183, danno_nocasepref Case-insensitive Danish/Norwegian dictionary sort order, with case preference for collating purposes. Uses the ISO 8859-1 ch aracter set with Windows 3.1 additional characters.
I have written an udf that checks for validations of an email address and returns 0 / 1 as per the validations. I came across an artical on MSDN that describes the steps to make my own system udf. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp
with help of that artical, I made my udf as a system udf. and now I can use it as follows.
select * , fn_isvalidemail(email) from #tp
now, I wanted to use this as a declarative check constraints in my tables. so I tried
HiI've done a search and found plenty on ORDER BY problems, but mineshopefully a syntax error.This is a test segment from my stored procedure.DECLARE @Name varchar(50), @SortType IntSET @Name = "canal lock"SET @SortType = 8SELECT dbo.ite_Item.ite_IDFROM dbo.ite_Item LEFT OUTER JOINdbo.iti_ItemImages ONdbo.ite_Item.ite_ID = dbo.iti_ItemImages.iti_LinkToItemWHERE iti_ItemImages.iti_LinkToPhotoType=1AND ite_Name LIKE '%' + @Name + '%'ORDER BYCASEWHEN @SortType = 3 THEN dbo.ite_Item.ite_DateWHEN @SortType = 5 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 7 THEN dbo.ite_Item.ite_DescriptionEND ASC,CASEWHEN @SortType = 2 THEN dbo.ite_Item.ite_NameWHEN @SortType = 4 THEN dbo.ite_Item.ite_DateWHEN @SortType = 6 THEN dbo.ite_Item.ite_ViewsWHEN @SortType = 8 THEN dbo.ite_Item.ite_DescriptionEND DESC,CASEWHEN @SortType <> 2 THEN dbo.ite_Item.ite_NameEND ASCI get various errors:@SortType = 2. This is a Name field varchar(50)Syntax error converting the varchar value 'Canal Lock' to a column ofdata type smallint.I only have one smallint col and that is the ite_Views column. Why isit trying to convert a varchar(50) col?@SortType = 3. This is a ISO date field nchar(8)The conversion of the nvarchar value '20030909' overflowed an INT2column. Use a larger integer column.Why?@SortType = 7, 8. These are description fields varchar(5000)Syntax error converting the varchar value '<snip>' to a column of datatype smallint.Again, why?All other sort types are fine.What I'm thinking is that when the ORDER BY code is run, it does someconversion but what?Many thanks in advance if anyone has any ideas?Sam
A colleague of mine has discovered some behaviour in the XML Source component that I am having trouble understanding or explaining. Here is the (obfuscated) XML document that we are looking to parse:
I'd like to draw your attention to the bit I've highlighted in red. SSIS has incorrectly defined this attribute as an unsignedLong. if you look at the data above you'll see that it has leading zeros therefore we need it to be interpreted as a string. Unfortunately (and here's the problem) there doesn't seem to be a way for us to change the generated xsd and thus refresh the external column metadata. The only way to change it is to go into the Advanced Editor and manually change the external column and output column metadata.
Is this by design? It seems very limiting if you ask me to not let us have control of what the metadata should be.
I've got a SSIS package which comprises a pair of Sql Task and after that a Data Flow. Such Data Flow own two Data Viewers, one between my Excel Source and my Derived Column and the another one between my Derived Column and my Ole DB Destination. Up to here, everything's fine.
Package really works properly but when I execute only the first Sql Task runtime shows (as usual) Excel Source Ouput Data Viewer1 and Derived Column Output Data Viewer1. Both windows empty, of course. Problem comes when I choose 'Copy Data'. Inmediately, appears a dialog with the message: "Windows has encountered a problem and needs to close"
I wonder, at first, why on earth these two windows are showed when I execute a task which belongs to Control Flow no Data Flow????? What happen?
I defined a report based on a matrix having 3 field groups. I defined Interactiveheight to have fixe number of rows in HTML rendering. My problem is that this parameter is only taken into account after first page. My first page always breaks after end of first field group + 1 row of next group.
Do you have any idea, what's causing such behaviour? And what would be the solution?
I have a VB6 front end connecting to SQL Server 2005 using the SQL Native Client.
When I execute a stored procedure the returned recordset contains more fields then it should. These extra fields seem to be the fields that the tables are joined on.
This happens with every stored procedure(simple to complex).
If I change the ODBC to use the standard SQL Server driver it works fine.
Under Management Studio, when I right-click a stored proc, select Modify, change the stored proc, then select Execute, the stored proc is updated on the server. But then I noticed that the sql tab holding the changed stored proc (in the right pane of Management Studio) still diplays and asterisk (*). When I right-clicked the tab it offeres the Save option but that is to save the sql file (with the * in its tab) to a file. This is confusing behaviour.
Is there any way to change this behaviour so running Execute causes the (*) to disapeear?
I have a fact table which is a custim query in the data source view, I process and work with the data, after somewhile I make some changes in the data source view query but all my changes does not reflect in the cube after processing the same old data returned.
Our VB application throws .dtsx on-demand by using Threads. Everything's fine but a little detail: when it goes to launch a SSIS package with a only task, concretely a Sequence Container task (empty, without tasks inside) fails Prevalidate method.
When select this data with an order by like: select test from table_2 order by test The result will be:
Code Snippet
test 1-1.00.00.00 1000 2000 If you sort the data by the SORT block of the SSIS the result will be:
Code Snippet
test 1000 1-1.00.00.00 2000This is annoying and dangerous, because it causes the next bug.
2/ Two datasources sorted by ORDER BY clause can give problems in a Merge Join.
If you have 2 data sources both correctly sorted by an order by in the query. When you join these 2 datasources with a Merge Join, you can lose some records in the dataflow. This happens with larger datasets than examples above.
Eversince I've moved my import packages to our new dev server, I have been experiencing strange behaviour in my packages that does the archiving of the .csv files which we import.
These 'Archive' packages doesn't do anything fancy in my opinion and most of them work. To explain how the logic works:
The package consists of a For Each Loop that picks up the file, passes it to a string variable. Inside the For Each Loop container, I have 2 File system tasks. 1 Renames the file and then flows out to another that moves it (I am aware of the fact that I can do the rename and move in 1 go but prefered to keep it separate). The FST that does the renaming, gets its source from the For Each Loop and after renaming the file, the file's new fully qualified path is stored in another variable. This variable then gets to be the source of the FST that moves it to the desired location...like i said...nothing fancy
I am getting errors where it tells me there is a mistake on my Rename FST's expresssion, which is nonsense since I run it on exactly the same files on my machine without problems. Another error is even more baffling...I get the Moving FST erroring out and complaining about not finding the renamed file?? how can this be?? it works also on my local machine, I wonder if it sometimes doesn't pass the variables correctly??
I recently got to install SQL service pack 2 on the box, which improved some things but these packages have been copied over from another server which had service pack 2 on. There are also quite a bit of updates that this server should be running and I was wondering whether these might solve the instability I get?
There's *strange* behaviour with snapshot size during subscription initialization.
So...
The initial snapshot has size about 9Mb at the publisher.
After subscription creation, I notice that the subscriber downloads about 21 MB.
It seems to me, that the snapshot is unpacked on the IIS, next, part of the files are packed again and just after it the changed snaphot is sent to the subscriber.
Is there a performance impact (if so, how great is it) when comparing a date to a datetime data type?
An educated guess suggests yes, as there will be a type casting... but then again I don't believe there's an issue when comparing an int to a tinyint and there's an assumption these would play by the same rules?
This has only come about because I'm considering changing the data type used in my standard calendar script and this popped in to my head.
