Way Of Segregating Data Based On Datatype Using Any Query?
Oct 22, 2013Is there any way of segregating data in sql, based on data-type using any query?
View 2 Replies
ADVERTISEMENT
Transact SQL :: Based On Data Convert Datatype And Make Wrong Date As NULL
Apr 21, 2015In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)
INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL
[Code] ....
SQL Query - Use Date Format Data And Match It To MS-SQL's Datetime Datatype?
Apr 12, 2007Need some quick help here.. The data I got from the text file use the "04/11/2007" date format and the StockDate in MS-SQL use the datetime datatype.
My understanding is that the "04/11/2007" will default to the 04/11/2007 12:00 am" format in MS-SQL.
So, when I use the sql query, how do search only for the date part of the data in MS-SQL and match it to the data from text file? I tried this SQL Query below.
--snip--
SELECT RawID FROM tblPurchaseRaw WHERE VIN = '" & sVin.ToString.Trim & "' AND StockDate = '" & dStockDate.ToString.Trim & "'
--snip--
That way, if I get a row then I know the data is there. If I don't get a row then I know the data is not there.
Bold: I get it now. It is all automatic as MS-SQL does it automatically...
Query Data Based On Moving Datetime.
Feb 5, 2008I'm trying to query data from a database for a report that looks for the last 2 weeks starting at 10pm, taking a value once every 24 hours. Using
AND DateTime >= DateAdd(wk,-2,GETDATE())
AND DateTime <= GetDate()")
I easily get the last two weeks but the query obviously only grabs the data at the time the query runs. I need to be able to run it any time of the day but only grab the data at 10pm. I'm very new at this so please excuse my ignorance but I could really use some help with this. Thanks very much.
Web Based Data Mining Query... Installation Issue..
Dec 4, 2006Hey gang,
I have ran into an installation issue that has been bugging me all weekend. I'm trying to run the preditive analytics query from a web based application (ASP.NET). I have installed a data pump as directed by this article "Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003" because this isn't set up under a domain. I also set appropriate roles in Data Mining Database (set it to everyone for testing purpose).
ConnectionString is "MSOLAP.3;Persist Security Info=True;Data Source=http://1.0.0.1/olap/msmdpump.dll;Initial Catalog=DataMiningDB"
Try
Dim connection As AdomdConnection = Me.Connect
Dim command As New AdomdCommand(txtQuery, connection)
Dim adapter As New AdomdDataAdapter(command)
Dim dataset As New DataSet
' Fill the dataset
adapter.Fill(dataset)
' Bind to grid
Return dataset
Catch ex As Exception
Throw ex
End Try
Return Nothing
Exception Details: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.
Errors in the high-level relational engine. A connection could not be made to the data source specified in the query.
OLE DB error: OLE DB or ODBC error: Login failed for user 'VMSQL2006IUSR_VMSQL2006'.; 28000.
It happens on the adapter.Fill(dataset). Any ideas?
I have also tried to set the olap application pool to use different identity instead of "Network Services", but I always get the same error message (but with user I specified instead of I_USR).
-Young K
Sql Query To Retrieve Comments Based On Selected Month And Data
Jan 4, 2008hi,
i am generating report for my project.
my table goes like
ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone
1 25 4 True False False Mail me False True False Visit me
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively..
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
if any one know the solution for the above problem,Please help me
Thanks & Regards,
Query To Select Data Based On Alphanumeric (surname) Information.
Jul 31, 2007Hello,
I am trying to write a query that will be able to select different segments of data based on spelling of the last name.
For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'
then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...
I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?
Thanks
Transact SQL :: Query Based On Date - Get Data From Table Above WHERE DateofBirth Is Tomorrow
Oct 30, 2015Lets say I have a table, tblPersons
FirstName | LastName | DateofBirth
----------------------------------------
Thomas | Alva Edison | 10-10-2015
Benjamin | Franklin | 10-10-2015
Thomas | More | 11-10-2015
Thomas | Jefferson | 12-10-2015
Suppose today's date is 09-10-2015 in (dd-MM-yyyy format), I want to perform a query in such a way that I should get the data from the table above WHERE DateofBirth is tomorrow, so I could get the following result.
FirstName | LastName | DateofBirth
----------------------------------------
Thomas | Alva Edison | 10-10-2015
Benjamin | Franklin | 10-10-2015
ODBC-based To OLE DB-based Data Transfer
Oct 7, 2006I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
View 1 Replies View RelatedDatatype Query
Mar 20, 2008Hi i have a table where the week number is held in it as a tiny int. I can't change this tables datatype.
But i can change the lookup table coulmn as am using a an expresstion to create it.
I'd like to know what datatype i can assign my new week_no column so it will join up with my other week_no column its currently set as a tinyint.
Datatype Conversion Query
Mar 17, 2005Have a simple question -
Will it be possible to convert a varchar data type column (having values that are numbers or null only) to a numeric data type column having the number values and the null values not being replaced by 0.
Col A Col A
(varchar) (int or numeric)
123 123
124 124
NULL 0 <expected blank>
125 125
I tried using CAST(Col A as int) and it converts the NULL to 0. Will
CAST(ISNULL(ColA,'') as int) work? The reason for this requirement is the value 0 will give different meaning to the data. Any insights will be appreciated.
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
Nov 7, 2007I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
T-SQL (SS2K8) :: Varchar Datatype Field Will Ignore Leading Zeros When Compared With Numeric Datatype?
Jan 28, 2015Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
Numeric Datatype To Ssis Variable Datatype Conversion Problem
Apr 24, 2008
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
Converting Data From One Datatype To Another.
May 1, 2000I am populating tables with Columns of fixed length (of a text format)
from a different table with some Columns of Money ( DataType).
It would not allow me to populate these Columns.
Can anyone please give me a hint?????
Thanks in advance.
M. Khan
How To Use Value Calcuated In Query In Subsequent Query, All Based On Value In Querystring?
Jun 5, 2008I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string. But, I don't know how to get a value returned from one sql statement into a second. Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4 ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID (Select OfficeID from tMatter where Mid=4) ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6 (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement?
Returning Results Based On One Query Into Existing Query
Aug 2, 2012linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
Query DateTime DataType For Current Or Future Events
Oct 12, 2006I have a sql server express 2005 database with a table named Events with a column named Date (datetime datatype). I want a query that will display all rows that are either current or future but not past. I suspect there is a simple way of doing this. As a Newbie searching archived threads this is what I have come up with so far. I determine the number of days from present:SELECT DATEDIFF(day, Date, GETDATE()) AS NumberOfDays FROM EventsThis yields number of days from present with positive numbers in the past and negative numbers in the future. Thus setting a WHERE clause to <= 0 would limit my results to present or future events. Something like this:SELECT * FROM EventsWhere DATEDIFF(day, Date, GETDATE()) AS NumberOfDays <= 0The error message states: "Incorrect syntax near the keyword 'AS'"This feels like a clumsy way to approach this problem, but I have to start where I am.Any suggestions on how to proceed will be greatly appreciated.
View 2 Replies View RelatedSimple SQL Query (To Neglect Time From DateTime DataType)
Nov 23, 2005we have a table like thisOrderNo OrderDate1 2005-11-04 01:12:47.00022005-11-19 04:26:54.00032005-11-16 11:03:23.00042005-11-21 15:58:37.00052005-11-24 21:45:04.000what will be the sql query, so that the Result look like this.only to neqlect the time factor from datetime data type .OrderNoOrderDate1 2005-11-0422005-11-1932005-11-1642005-11-2152005-11-24
View 4 Replies View RelatedUsing DTS To Move Data To A New Table With Some Datatype Changes
Jul 23, 2005I have a production application that I am building some upgrades usinga second (empty) copy the database. A few of the upgrades includedchanging the datatypes of a few fields from varchar to int(all thevalues in this column are already numbers)I am not trying to move the data from the production database into theblank database using DTS and the fields that had their datatypeschanged are getting dropped by dts.Is there anyway to move this data easily through dts or through anothermethod and not have the fields with the datatype changes get dropped?I have about 60 tables so it is not as simple as just copying andpasting the data..
View 2 Replies View RelatedHow To Insert Data Into To Image Datatype
May 22, 2006i have a table gg. It has two fields one is ggno int, ggfig image .how can i insert into the record into 'gg' table help me with exampple
View 1 Replies View RelatedProperly Copying Data Of Datatype Image
Dec 12, 2007I have a column called "Image" in a table that stores all the user information. Image holds the data for the user's badge photo. I'm currently working on a project to move some of the data from the users table to more relevant tables. With the SQL script I wrote to copy the data into the new tables the image data appears to not have been transferred correctly. I had tried storing the image data into a variable of type varbinary(8000) before inserting it back in. Is there a certain datatype that must be used to store the data when reading from a column of data type "image" and then inserting it into another column of data type "image" without getting truncation or corruption of the data?
View 1 Replies View RelatedData Access :: How To Get Column Datatype Using Namespace
Jan 2, 2013which is to parse a create table script using Microsoft.SqlServer.TransactSql.ScriptDom namespace..I get a list of statements : StatementList SQLStatement = parser.ParseStatementList(StreamReader, out errors);then I cast a relevant statement to the CreateTableStatement type which gives me access to the ColumnDefinition class.
foreach (TSqlStatement T in SQLStatement.Statements)
{ if (T is CreateTableStatement) { CreateTableStatement TC= (CreateTableStatement)T}}
with foreach (ColumnDefinition CD in TC.Definition.ColumnDefinitions)
I am ready to loop through the columns so far so good. I can get to the column name using CD.ColumnIdetifier.Value, to the data type using CD.DataType.Name.BaseIdentifier.Value, but can't seem to get to the length definition. Meaning, I can get column1 , varchar; but I need column1, varchar(40). where do I get (40) from?I tried using ScriptTokenStream under C.DataType, but it seems to iterate through all tokens not just ones relevant to that fragment.
Problem Inserting Sql Query Into Database Float Datatype Field Using SQL Transaction
Aug 22, 2006I have this problem of inserting my query into database field. My code is as of below.
The @AVERAGESCORE parameter is derived from
Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)
and the value returned is (averagescore.toString("0.00"))
However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?
Try
Dim i As Integer
For i = 0 To arraySql.Count - 1
myCommand = New SqlCommand
Dim consolidatedobjitem As ConsolidatedObjItem = arraySql(i)
myCommand.CommandText = sqlStr
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
With myCommand.Parameters
.Add(New SqlParameter("@AVERAGESCORE", consolidatedobjitem.getaveragescore))
End With
myCommand.ExecuteNonQuery()
Next
myTrans.Commit()
myConnection.Close()
Catch ex As Exception
Console.Write(ex.Message)
myTrans.Rollback()
myConnection.Close()
End Try
Convert Char Datatype To Datetime Datatype
Sep 17, 2003Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
Modify Nvarchar Datatype To Datatime Datatype
Mar 14, 2008Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
SQL Server 2008 :: Function To Replace Data In A Column With X Based On LEN Of Data
Sep 4, 2015I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Reporting Services :: Data Not Available For Selected Values To Be Set Based On Data Source
Jul 10, 2015I have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values" for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.
View 3 Replies View RelatedHow To Optimize Data Import With Huge Volumes And Joins Across Data Sources Not All SQL Server Based?
Jun 7, 2006I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
Taking Data From A Different Databases/Data Warehouses Using A Sequel Server 2005 Based Database.
Apr 1, 2008
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
Stephen
AS 400 Data Being Interpreted As Unicode Strings (DT_WSTR) Datatype
Jun 23, 2006I am building a data warehouse. Some of the data comes from an AS 400 EPR system. I used the OLEDB connector when first pulling the data into SQL Server doing simple import data from table option. That worked great for getting the initial data load into SQL Server and creating the base SQL Server tables although it was excruciatingly slow (that was probably due to the transport from the AS 400).
Now, I need to get new records that are added to the AS400 side of things on a daily basis. For that, I was trying to use the OLEDB AS400 connector. However, I found that the OLEDB connector wouldn't work when I was trying to specify an SQL Statement for what to get; i.e., a simple query like Select * from TWLDAT.STKT where BYSDAT >= '2005-01-27' would simply not work. Found articles here explaining that it is probably a problem on the AS400 side of things and where people recommended using an ADO ODBC data reader source for this type of thing. So, I'm trying to implement that. However, I have a huge problem with it.
The original tables that got created were mapped to use NVARCHAR fields for character data. When the ADO ODBC data reader source accesses the AS400 data, it insists on interpreting the string type fields as being unicode strings and giving it a data type of DT_WSTR when what I need it to have is a plain old DT_STR data type. When the strings are interpreted as unicode strings, they cannot be converted in a way that allows the NVARCHAR fields to be filled with the data. The exact error message I get for all the fields that should wind up being nvarchar fields is as follows:
Column "BYStOK" cannot convert between unicode and non-unicode string data types.
Okay, so I try to change the data types in the ADO ODBC data reader to be plain DT_STR data types and I cannot do so.
Does anyone have any idea why the ADO ODBC data reader source insists on interpreting the string data coming from the AS 400 as unicode string data or why it refuses to allow that to be changed to DT_STR data type?
Thanks in advance for any info. By the way, if there is a better way than the ADO ODBC data source to get at this data when I need to specify an SQL command, I would love to hear about it. Not wild about using ODBC in the OLEDB age.
Steve Wells
Data Type In Oracle Is Not Translating In SSIS Datatype
Sep 25, 2006My OLDB data source in the Lookup Transformation is not converting
When I try to join the colunms it claims the data types do not match the only differnece is the percision.??
ORACLE
select CAST(group_id AS NUMBER(18)) group_id from blah blah table
SSIS data type = [DT_NUMERIC], Percision = 38
SQL Sever data source
SELECT
CAST(PRINCIPAL_ID as numeric) GROUP_ID,
SSIS data type = [DT_NUMERIC], Percision = 18
Design For Loading Data Without Knowlged Of Datatype Or Column Count
Jul 17, 2006I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?
Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?
Does this make sense? Surely other foplks have run into this....
My thanks!