SQL Server 2008 :: Sum Result Type Character?
Feb 1, 2015i need result from '(1000/3)+(15/100)'. try exec() by failed.
View 5 Replies
ADVERTISEMENT
What Is This Box Type Character For A Blank Space In SQL Server?
Mar 8, 2007Hi,
I am using DTS for importing a table from FileMaker Pro (a database application) to SQL Server 2000. The problem is that everything gets imported correctly but in some values, it shows a box type character for a blank space. It does not happen for all the blank spaces. Because of this, that value is cannot be checked in the WHERE clause until I use a LIKE and % for that blank space.
I copied the value and pasted in Notepad, it only shows a blank space. What is this character?
Can someone help...
Thanks,
bullpit
SQL Server 2008 :: Cannot Drop Table With Non-Printable Character On Its Name
Sep 21, 2015I have a legacy table which name contains non-printable characters (CHAR(31), to be more specific). The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+_ shoots the CHAR(31) character (which means "US" - Unit Separator). The previous developer should've hit by mistake this combination, and created the table with this weird character on it.
When we issue a SELECT command against the table, it returns results. But when we try to issue any DDL to it (DROP, sp_rename, etc), it simply doesn't work.
Examples:
DROP Table_Name; raises "Msg 15225 - No item by the name of 'Table_Name' could be found in the current database 'MyDB', given that @itemtype was input as '(null)'".
sp_rename 'Table_Name', 'NewTableName'; raises "Msg 102 - Incorrect syntax near '_Name'".
I already duplicated the table with the correct name, and have corrected it on the referenced objects (SP's, Views etc). The remaining step is just dropping it from the database. when we copy+paste from SQL Server to Notepad++, it shows the hidden chacacter ("US") on the middle of the table name, beside the underscore.
SQL Server 2008 :: Finding Multiple Character Occurrence In String
Mar 24, 2015I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
How To Insert A UTF-8 Encoding Type Character Into A Sql Server Text DataType Field ?
Dec 4, 2007I want to insert a value which has the UTF-8 encoding into a field of the database which has the "text" data Type
but it saves like this : " ?????????????? "
I would be thankful if u tell me how should I save it ?
here is the SqlComand I wrote :
string comand = " Insert into Table1 ( title , Body ) values ( ' " + textBox1.text + " ' , ' " + textBox2.text + " ' ) " ;
Table1
=============
title nvarchar(1000)
body text
SQL Server 2008 :: Removing Null From Result Set
Aug 25, 2015I want to remove the nulls from the result set so the result is 1 line. Code and results are below:
SELECT
CustomerNumber,
(case when yearseq = 2012 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)
+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2012',
(case when yearseq = 2013 then
[Code] ....
What can I do to my code to remove the Nulls to the entire result is just 1 line?
SQL Server 2008 :: How To Result Query With Repeat Sequence Number
Mar 5, 2015I want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1
Field1Field2SeqNo
BVFUSBVFUS011
BVFUSBVFUS021
BVFUSBVFUS031
BVFRPBVFRP012
BVFRPBVFRP022
[Code] ....
I want the result also no ordered by Field1. it just as the natural order of the table..
SQL Server 2008 :: Speed Up Text Search In Large Result Set?
Jul 14, 2015I have a query below which filters detail field in the #TempLogins table. The details field is a text field which contains many types of text strings, some containing urls that have parts like "ResultID=5" which is what is contained in the ResultIDSearch and ResultSetIDSearch fields. The records with entries like "ResultID=5" are the ones I'm trying to filter for.
The problem I have is that the query takes way too long to run. The TempLogin table has around 200 K records and the TempSearch table has around 80 K records.
select * from #TempLogins a where exists
(select 1 from #TempSearch t1 where
a.detail like '%' + t1.ResultIDSearch + '%'
or
a.detail like '%' + t1.ResultSetIDSearch + '%')
SQL Server 2008 :: How To Get The Column Type And Length
Feb 23, 2015Below is a SQL statement that shows what columns belong to a certain view. My next question is how can I get a third column that shows the column type ?
VARCHAR(100), or UNIQUEIDENTIFIER or INTEGER or TEXT or NVARCHAR(256) or ........????
select top 100 a.name, b.name, from sys.views a inner join sys.columns b
on a.object_id = b.object_id where a.name like '%Case_Times%'
SQL Server 2008 :: Data Type And Size Of Each Row
Feb 27, 2015In my database there is a big table and format is something like this:
CREATE TABLE [dbo].[MyTable](
[aaa] [uniqueidentifier] NULL,
[bbb] [uniqueidentifier] NULL,
[ccc] [nvarchar](max) NULL,
[ddd] [nvarchar](100) NULL,.......etc.........
There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?
How Do I Split A Column Result By A Nonalphanumeric Character?
Jan 24, 2006I have a column that returns client numbers.
The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization.
In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number.
EG: client #4334.1 would become 4334 for client # and 1 for categorization number
or
Client #424561.3 would become 424561 for Client # and 3 for categorization number.
I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns.
Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second.
Is it possible to do this? I really need help on this one.
Thank you :)
SQL Server 2008 :: Table Type And Open RowSet
Feb 5, 2015I'm trying to store the output of my stored procedure into a temp table, which takes Table Type as Input.
CREATE TYPE [dbo].[Employee] AS TABLE(
[Field] [varchar](50) NULL,
[Criteria] [varchar](50) NULL,
[Value] [varchar](50) NULL
)
declare @Employee dbo.Employee
insert into @Employee values(N'Salary',N'is greater than',N'3520')
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'',''Server=localhost;Trusted_Connection=yes;'',''EXEC dbo.uspGetEmployee ' + @Employee + ')'
I need to create a stored procedure which uses output of the above stored procedure. Hence, I don't want to put declare and insert into OPENROWSET as I get those values as parameter to the new procedure.
Any other solution instead of using OpenRowSet.
SQL Server 2008 :: Specific Type Of Join For 3 Tables
Jul 3, 2015I have 3 tables as per following:
orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5
ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015
product
ProductdescMinQty
Item1This is 110
Item2This is 220
I want to pull only the 1 line for minqty for an item as follows
OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015
SQL Server 2008 :: Adding Columns To Table Type?
Jul 24, 2015Is there a good way to add columns to a table type?
I built several procs which make use of table-valued-parameters, and they work pretty nicely, until I need them to accept additional columns. Then I have to drop all the procs that use them, alter the types, and rebuild all the procedures, which is a huge pain in the rear.
Is there any good way (built in, or custom) to alter the def of a table type that's used as a parameter to multiple stored procedures?
SQL Server 2008 :: Data Type Of Calculated Fields
Aug 10, 2015I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.
Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
[Code] ....
SQL Server 2008 :: Size Limitation For XML Data Type?
Oct 7, 2015I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)
The first question is does the XML data type have a size limitation? For example do you do something like:
ConfigFile XML(1000) NULL
Or is it just something like this:
ConfigFile XML NULL
The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD.
SQL Server 2008 :: Allow Null On Data Type Money?
Oct 15, 2015Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
SQL Server 2008 :: Find Out Type Of Replication Pull Or Push
Apr 28, 2015SELECT
P.Publication
,P.Publication_type
,S.Subscriber_ID
,S.Update_Mode
FROM MSPublications P
INNER JOIN MSSubscriptions S
ON P.Publication_ID = S.Publication_ID
give me publication_type=0. So it is transactional replication but how do we know that is pull or push?
SQL Server 2008 :: Querying XML Data Type With UNICODE Characters
Oct 12, 2015I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML
SET @XMLVAR = '<?xml version="1.0"?>
<POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME
,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER
FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
SQL Server 2008 :: How To Bring Write Log Wait Type Count Down
Oct 29, 2015This is the scenario in my environment
WaitType Wait_Sec Resource_Sec Signal_Sec Wait Count Wait Percentage
WRITELOG920039.89887485.89 32554.00 23446032975.02
SQL Server 2008 :: Expression Of Non-Boolean Type Specified In Context Where Condition Is Expected
Apr 10, 2015I 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 ?
SQL Server 2008 :: Error Converting Data Type Varchar To Numeric
Jun 17, 2015As per my requirment using below script to convert from varchar to numeric.
declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)
Getting error : Error converting data type varchar to numeric
Determining DB Type/Character Set
Feb 26, 2002Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set?
Any help will be greatly appreciated. Thanks a lot!!
SQL Server 2008 :: Conversion Of Varchar Data Type To Datetime Resulted In Out Of Range Value
May 14, 2015I have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);Instead if try this I have error:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);
[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SQL Server 2008 :: Review Data Type Mapping (Import And Export Wizard)
Jun 5, 2015Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric
Oct 13, 2012When I run the following sql query:
"update table set price = price * 1.1 "..
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?
Feb 26, 2015Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View RelatedWhen Changing To Type Text From Nvarchar, The 255 Character Limit Is Maintained?
Jul 23, 2005Hi - I am changing a field from type nvarchar to type text, given thatI need to store strings longer than 255 characters. To do this Ichange the data type in SQL Server, then I change the parameter code inthe calling procedure, as per below:cmd.Parameters.Append(cmd.CreateParameter("@title", adVarWChar,adParamInput, 255, title));becomes:cmd.Parameters.Append(cmd.CreateParameter("@title", adLongVarWChar,adParamInput, 1073741823, title));However, when I do this, for some reason, the field is still limited to255 characters - when I try to update the field with 256 characters,the error 'Application uses a value of the wrong type for the currentoperation.' occurs.Why is this? I've checked that the correct data is contained in theparameter. When I look at the data in the database, the column inquestion shows the content, whereas the next column, which has alwaysbeen of type text, shows '<LongText>' - does this mean anything? Do Ineed to do something special to convert the column from nvarchar totext?Many thanks,Iain
View 1 Replies View RelatedConversion Failed When Converting Character String To Smalldatetime Data Type
Oct 12, 2006I am newbie in asp and sql, and I am using VS & SQL expresswhen I try to submit I get following error"Conversion failed when converting character string to smalldatetime data type"Following is my insert statement<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')"></asp:SqlDataSource>These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.I would appriciate if somebody could help.
View 7 Replies View RelatedConversion Failed When Converting Character String To Smalldatetime Data Type.
Mar 25, 2007Hello, I have problem with this code.(This program presents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.
Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.
Conversion failed when converting character string to smalldatetime data type.
Here is code:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string datePatt = @"yyyymmdd";
// Get start and end of day
DateTime StartDate = DateTime.Today;
string @StartDate1 = StartDate.ToString(datePatt);
string @EndDate = StartDate.AddDays(1).ToString(datePatt);
// Get start and end of week
string @startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);
string @startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);
switch (DropDownList1.SelectedValue)
{
case "1":
// day
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@StartDate1'" + " AND [DATE] < " + "'@EndDate'";
break;
case "2":
//week
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@startOfWeek'" + "AND [DATE] <" + "'@startOfNextWeek'";
break;
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
body {
font: 1em Verdana;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
Style="z-index: 100; left: 414px; position: absolute; top: 22px">
<asp:ListItem Selected="True" Value="1">jeden den</asp:ListItem>
<asp:ListItem Value="2">jeden tyden</asp:ListItem>
</asp:DropDownList>
<asp:GridView ID="GridView1" runat="server" Style="z-index: 102; left: 228px; position: absolute;
top: 107px" DataSourceID="SqlDataSource1" AutoGenerateColumns="True">
</asp:GridView>
<br/> <br/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=CRSQLEXPRESS;
Initial Catalog=MyConn;Integrated Security=True"
ProviderName="System.Data.SqlClient"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
Conversion Failed When Converting Character String To Smalldatetime Data Type
Oct 3, 2007I am running the following query:
select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and cast(HQDIS as varchar(8)) >= (Select CONVERT(varchar(8), getdate(), 1)) and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead order by hqvend,fprod
The bold sections are giving me the error message. The query works as written above, butis only evaluating on one date, contained in hqeff. However, when I try to modify the second date, HQDIS, by casting it the same way as the one before it:
select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and cast(cast(HQDIS as varchar(8)) as smalldatetime) >= (Select CONVERT(varchar(8), getdate(), 1)) and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead order by hqvend,fprod
I get the error message. I need to select based on both dates (hqeff AND HQDIS), but cannot seem to be able to it.... Both fields are of type Dedimal, with a length of 8, and dates stored in them look like: 20071003 (YYYYMMDD).
Any suggestions?
Syntax Error Converting Character String To Smalldatetime Data Type.
Aug 8, 2005Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords( @searchFor NVarchar (25) = NULL, @fromDate NVarchar (25) = NULL, @toDate NVarchar (25) = NULL)
AS
IF @fromDate IS NOT NULL DECLARE @fromDateString VarChar(200) SET @fromDateString = ' CONVERT(smalldatetime, ''' + @fromDate + ''') '
IF @toDate IS NOT NULL DECLARE @toDateString VarChar(200) SET @toDateString = ' CONVERT(smalldatetime, ''' + @toDate + ''') '
SELECT * FROM MIPR WHERE ID = @searchFor OR DESC_QTY = @searchFor OR REQ_ACTIVITY = @searchFor OR ACC_ACTIVITY = @searchFor OR MANYEARS = @searchFor OR TECH_POC = @searchFor OR RESOURCE_POC = @searchFor OR SI_DATE BETWEEN @fromDateString AND @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!
Conversion Failed When Converting Character String To Smalldatetime Data Type
Oct 21, 2007Hi,
I have SQL Server 2005 database table with the following data definition as follows:
ID int
LST_TIME varchar(5)
LST_DATE varchar(21)
LST_WEEK int
SUBJECT varchar(100)
Date format (Month,day YEAR) eg.- October, 21 2007
Time format, 00 - 23 hours, HH:MM eg. - 18:58
, and a VB code as below:
Dim Command As New SqlClient.SqlCommand("SELECT * FROM dbo.LISTS WHERE (LST_WK = DATEPART(wk, GETDATE())) AND (CONVERT(datetime, LST_DATE) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 110)))
AND(CONVERT(smalldatetime, LST_TIME) = CONVERT(smalldatetime, CONVERT(varchar(5), GETDATE(), 108)))", conn)
Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()
Dim NewAlert As New Alert(dataReader("SUBJECT").ToString(), DateTime.Parse(dataReader("LST_DATE").ToString(),
DateTime.Parse(dataReader("LST_TIME").ToString())))
:
:
:
I am encountering error: System.Data.SqlClient.SqlException: Conversion failed when converting character string to smalldatetime data type, which points to the codes in bold. Just wondering anyone out there has got a solution to my problem. Thank you.