I have a test query that i have ordered by the expected result order.
select t from (
select cast('1' as nvarchar(20)) as t
union all select '1---qa'
union all select '1q'
union all select '1q1'
union all select '1qz'
[Code] ....
and the question is... why is minus character (at 3rd line in '1---qa' string) ignored by "order by" clause? That row is put after '1q1' and not after '1'.
When you replace '-' with '+' or any other special char it works as expected but not with '-'. It is just ignored no matter if there is one or more of them.
Don't mind local chars like čž, they are for testing purposes with collate (thus commented) and with bin collation that is not doing proper sorting but minus order is ok in that case.
Tested on SQL 2008 R2 and SQL express 2012, database collation is 'Croatian_CI_AS'.
Supposing I have a sort order/char set installed and some databases working under these circumstances.Is there any way to change order and sets without losing my old databases? If I try to rebuild databases, I lose them,and when i try to restore them, there's a conflict between the new SQL Server 6.5 configuration and the Databases previously created in old sort order/char sets?Is there any solution to this,or once I have chosen the database configurations,I'll have to either recreate the data or die with them??? Neede answers ASAP....
Can someone comment on why joining two tables on CHAR fields of different lengths would generate unexpected results?
I had an issue where I ran an update that used an inner join on two tables. The field I used in the join was char(50) in one table and char(13) in another table. The result gave bad matches. After changing the field types both to varchar(30), the problem was eliminate.
I am currently trying to import data from a table in 1 database into a table of the same name in another database. This in it's self is simple, however to add a twist to the proceedings there is data that exists in both tables. I just want to import the data that doesn't exist in the table I am importing into.
Please can you advise as to the best method to use
I have a varchar field which holds IDs like (1, 3, 5, 19, 23) when I order it, i get it ordered in ASCII order like (1, 19, 23, 3, 5) rather than (1, 3, 5, 19, 23) Even if I convert it to int, I won't be able to order it.
is there any way I can order a varchar field numerically?
I have a problem with ordering and I am hoping that someone is able to help.
In my table I have two fields, "requestdate" and "sentdate", and when I display the records I would like to sort by BOTH fields. I want to do this so that the full query is in order by date.
I tried:
ORDER BY requestdate, sentdate DESC
But obviously all that does is order by requestdate (which is NULL or a date) and than it will order by sentdate.
Can somebody tell me how to order by both as if they were the same field?
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
Hi, Can we validate the returned order of fields from a stored procedure? Infact, i am taking a query as user input and extracts the results based on the query but for that order of fields specified in a query is important. Can i check the order after the query is run i.e if this is entered "select field1,field2,field3 from table" then i need to check the order of the resultset generated. I can't check the query before the resultset is generated because a user can enter bunch of queries. Any way will work, tsql or .net app. Thanks,
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like: UPDATE "TABLENAME" SET DATAFIELD1 = DATAFIELD2 WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a description that is dynamically built for a part, with variables for this part split by line feeds. For example
100" widget Color: White Holes: Many Material: Wood
This makes everything nice an neat on reports, etc, and the world is good. There are a lot of better ways to do this I am sure, but due to the limitations of the software we are using, this fits the bill best.
In SQL view, it LOOKS like the lines are all run together, but when you actually pull the data, it formats properly.
Enter a new need to identify just the first "line" of info. I would like to be able to query all characters left of the CHAR(10). In the example above, the results would be just 100" Widget. I have tried a couple ways using Left(field,charindex(char(10))-1 but nothing seems to get me data back. Is this even possible within a View?
im trying to convert char (18) data type to decimal (18,6) but it wont let me do it. It gives an arithematic error. what would be other way to solve this problem. Can i do it with float if yes how so? or any other suggested way. Thanks
I have an access database that I am about to move over to SQL Server. In there I have member table that has a memo field. This table has the potential to grow quite big. My question is: what issues does having a large text field have on my database. Will it slow down my updates, searches or inserts? I plan on using this field for such things like a biography of the member. Is using a text field the best way to go, or should I look at text files instead? Many thanks,
A SQL table has a field named "pay-day" with Char(8) data type.
I tried to change its data type to datetime but only with an error message like this. I did right-click the table and tried to modify a data type.
- Unable to modify table. The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Is there any truth to this: the placement of fields in a table relates to field access speed. So, frequently accessed fields should be placed in the beginning of the table while fields infrequently used can be placed toward the end.
I have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page. This line should give me access to the dataset but how to I access the fields? Dim myDataSource As DataView = DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView) When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.If myDataSource.Read Then If Convert.IsDBNull(myDataSource("CusAgentPhoto")) Then ImageAgent.ImageUrl = "/photos/nophoto.gif" Else ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString End If If Convert.IsDBNull(myDataSource("CusCompanyLogo")) Then ImageCompany.ImageUrl = "/photos/nophoto.gif" Else ImageCompany.ImageUrl = OfficeImagePath & myDataSource("CusCompanyLogo").ToString End IfEnd If What would be the correct way to get at the DataSet fields that contain the filenames?
( @CompanyID NVARCHAR(36), @DivisionID NVARCHAR(36), @DepartmentID NVARCHAR(36), @ItemID NVARCHAR(36), @OrderNo NVARCHAR(36), @LineNo NVARCHAR(36), @TAllotedQty Numeric, @EmployeeID NVARCHAR(36), @Trndate datetime ) AS BEGIN By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same) IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID END
