Hello there
I'm having data store in database at interval of 2sec.
hence if i read data for 1 week(7days) it returns me huge amount of data
so what i want to do is to read data at some varying intervals e.g 30 sec or 50 sec for perticular datetime range
Please tell me suitable query to have such selected data base
Thanks in advance
AVD
hi there
i am using .net framework 1.1 with SQL 2000 .
i want the data in table to get deleted automatically after 30 days of inserting data.
so how do i achieve this?
Hi,
I have a flatfile source. I want to extract a specific row... let say row 2. how do i retrieve that row from the list of rows in my flat file?
thanks,
Cherriesh
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I'm trying to migration a database from PostgreSQL to SQL Server, but i've found a problem with interval data type, SQL server does not have an interval data type, can someone help me please? I need the solution ASAP due to work deadline =(
View 2 Replies View RelatedI am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies View RelatedI am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?
SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId
Current Output
MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
View 1 Replies View RelatedI posted this in the Windows Forms Data Binding section, and they directed me to the .Net data section. I posted it there and waited a week with no replies. I'm hoping someone here can at least give me an idea of what the problem might be.
View 1 Replies View RelatedHi, am trying to analyze output of profiler
I execute sp_sp_alex
Profiler results
CPU = 108
READ = 0
WRITE = 109
DURATION = 1709
code from sp_alex
step 1
read data from table A into cursor
(500 rows)
step 2
fetch cursor and if values in cursor do not exixts in table B(has 1000000 rows) then insert cursor data into table B
How I undestand result provided by profiler
CPU = 108 good or bad ?
READ = 0 good ,took no time read data ,all indexes in place
WRITE = 109 mmm.... something really wrong with writing to disks , why it took so long to insert 500 records
DURATION = 1709 good or bad ?
How would you read profiler results ?
Thank you
Alex
Is there any way to read a text file with one value in it and load that value into a variable?
View 1 Replies View RelatedHi,
I want to read xml from a string and save it in SQL. Can anyone help me plz.
Regards,
Hi,I have a Excel sheet that have a column with mixed data:column can contain data like "892-234-32A" or like "892298343233432"I need to get all data column such as "text" to avoid numeric field show asexponential number.I'm trying with:SELECT CAST (CODICE as bigint(25)) FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel8.0;IMEX=1;HDR=YES;Database=C: empxadpdist1.xls' , Foglio1$)But I get error conversion type "from nvarchar to bigint" when query meetthe alphanumeric field.Any tips ?Thanks in advancePieroItaly
View 1 Replies View Related
Not sure if I am posting this in the correct placed. Here is what I am trying to do. Read an xml files which is very large and is our store xml file. I want to import this data into a database nightly. I can down load the file and I have noticed that it has one line in it that will keep me from reading it: <!DOCTYPE StoreExport SYSTEM "http://..../doc/dtd/StoreExport.dtd"> Once I removed this line I can get the data needed to go into the database. I would removed the old table first then replace it with the information from here.
Can anyone help. I hope I have given enough information for this type of issue. I was told integration services would do this, but I am not sure how. Could someone point me in the right direction.
Thank you for any an all help.
Dee
I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"
My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.
Code Extract:
public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Hi,
My first post.
Problem:
I have 2 tables EmployeeA(Eng) and EmployeeB(Spanish) kept in seperate mdb's. I want to add the records into Sql Server 2005 table called StateEmployee.
Procedure:
1. Loop through 2 folders..one containing table EmployeeA in mdb and other containing tbl EmployeeB in diff mdb's.
2. Pick a file from EmployeeA and EmployeeB, both at the same time.
3. Count the total no of rows in both files. If equal proceed.
4. Compare the 'employeeid' of one row of employeeA to the employeeid of EmployeeB.
5. If employee id matches, load both the rows in Sql server else file it to the error table.
6. Loop through all rows simultaneously till end of row.
7. Go to next mdb.
How do i go about this step by step. I am fairly new to SSIS. I asked my other friends too but they have complex answers which i couldnt follow. Hope someone gives an 'easy to understand' solution with sample.
thanks.
Not sure if I am posting this in the correct placed. Here is what I am trying to do. Read an xml files which is very large and is our store xml file. I want to import this data into a database nightly. I can down load the file and I have noticed that it has one line in it that will keep me from reading it: <!DOCTYPE StoreExport SYSTEM "http://..../doc/dtd/StoreExport.dtd"> Once I removed this line I can get the data needed to go into the database. I would removed the old table first then replace it with the information from here.
Can anyone help. I hope I have given enough information for this type of issue.
Thank you for any an all help.
Dee
I have a table with a column with data type XML.I dont know the contents of the XML structure etc and I need to extract it
View 5 Replies View RelatedHi Everyone,
I am looking for some help, as i am pulling my hair out looking for information.
I have been using asp for many years and am now starting to learn .net. so far so good....
I am now wanted to connect to a database, execute a simple select statement and then read/write the information out. I can't help but think in old asp code and i am having a hard time finding what i need to perfom this simple task.
I have used the grid controls etc, and these are very good - however, i need to connect to a database in the code-behind file and perfom various functions in the background.
If any of you could be so kind as to perhaps show me some demo code i would be grateful.
I would like to do:
A) Connect to a database (sql server 2000)B) Execute a simple SQL select statementC) Read the returned informationD) put this information into variables used elsewhereE) how do you check if no records are returned? such as the .EOF in asp?
Many thanks
Darren
I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?
Thanks a lot, Hugo
Hi
I need to read a very big table more than 60,000 record but it is giving the following problem: But if it is small table there is no problem. Same problem also views.
Server Error in '/POBuilds' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
I am using SQL Server 2000, VS 2003
I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error.
while (dr.Read()){ Education e = new Education(); e.EducationId = dr.GetInt32(0); e.Country = dr.GetInt32(1); e.InstitutionName = dr.GetString(2); e.Grade = dr.GetString(3); e.CGPA = dr.GetFloat(4); // ERROR HERE e.Percentage = dr.GetFloat(5); e.PassingYear = dr.GetString(6);}
where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server
I have a database that contains news items. There's a column that contains the actual article. In that field, there are paragraphs with page breaks. The page breaks in the database are represented as squares (unrecognizable characters I guess). When I try to read in the data, it doesn't recognize the page breaks, and it comes out all in one large paragraph. Is there any way to get around this?
View 2 Replies View Related
Hi,
I have a data structure called 'Quote' which contains a number of different variables and controls ranging from text boxes, check boxes and radio buttons, i need to be able to read and write this from a database.
First I think a description of my overall project is needed:
Project Description
I have been given a brief that basically says: i have to create a programmed solution in VB to solve a problem. This problem can be anything we like, and I personally have chosen to create a program that manages quotes for building Log Cabins (this is very contrived and far from anything someone would do in the real world).
My solution will allow a generic user to create a quote (using a form with controls such as text boxes, check boxes, radio buttons) , and then save this to file. These users may then wish to load/edit this quote at a later date, from another form.
Whilst completing this project, i'll only have up to about 5 records (quotes) within the system, so i dont need the ability to store hundreds of records. And each record will be relatively short, with only about 10-15 data items within the data structure.
Also the Admin (or business owner in this case) need to be able to view all saved quotes in a presentable format, and edit them if needs be, from within this same program.
This solution does not need to be absolutely perfect and 100% efficiently coded, or have all the bells and whistles a real-world program would have. This is for an A level computing project by the way.
So basically, i need to be able to read from the database (to populate a Data Grid (i imagine this is best way?)) and so Admin can access any quote and edit it (editing is not vital, but viewing/printing is. Maybe i should stop at just viewing any quote?). Also i need generic users to be able to fill in the Edit Quote form and then save this data into the database.
And is a data structure really required for me to use a database?
I've never used databases in VB before (but have used them elsewhere, mainly Access) and so am completely new to this. Any help will be much appreciated.
Thanks
Hi, i'm writing a SOCKET Port Listener for a Database, it must be multi-threaded and listen on a port for a record that when it comes in, it must write the record to the SQL database (MS SQL Server). I've got the listener to read the data over the port already and write the record into a string which i have already sliced up. Now i need to create a connection to the database and insert the variables into the database.
If Someone will please be able to give me a rough idea of how i could accomplish this with some sample code, then i will be greatful, i'm new to C#, but here is my code that i have so far.
//This is the Connection that i have made and where i am currently stuck, i dunno how to go further. Any help will be welcome.
public class ConnectionToMSDatabase
{
public void InsertDataIntoDatabase(string TableName, string connectionString, string dataFields)
{
string InsertSQLStatement;
InsertSQLStatement = "INSERT INTO " + TableName + " VALUES (" + dataFields + ")";
OleDbConnection ConnectionToDatabase = new OleDbConnection(connectionString);
ConnectionToDatabase.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = ConnectionToDatabase;
command.CommandText = InsertSQLStatement;
command.ExecuteNonQuery();
command.Connection.Close();
}
}
//Here is my connection string, all the retrieved data is stored in a string array call fullRecord
ConnectionStringToDatabase = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=" +
"Administrator" + ";Initial Catalog=FORGE;Data Source=FORGE";
I have text output files which are semi-structured.(Headers + irregular length tables below)
Is there a simple method of getting them into sql format(line by line) to try and extract data from them?
I know this won't be easy but its been worrying me for a long time. I have a method of importing the data into excel, but although difficult, it must be possible to get a system to get it into sql server. This must be a fairly common issue.
John
hi there,
I installed the Adventureworks sample database and can see it from the server managment studio express. I am doing now an tutorial from Microsoft to display the data in a grid view using Visual Web Developer 2005 Express.
I am connecting to the DB with this connection string: "Data Source=STK-SASA-MX025SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True" and I can see all the tables of the database. When I try to select some fields from the database using the wizard, like: SELECT [Name] FROM [ProductCategory] I get the following error message:
Invalid object name: ProductCategory, and to check the syntax and the types, but it seems to me quite standard SQL.
Anybody could help me please?
Thanks
Theo
Hi everyone I have a directory that contains a lot of text files that have data I need to draw from. I want to know if it is possible to write a program that will read all of the text files in the directory and pull out data and save it to a new textfile. For example: Each text file is formatted this wayColumn1, Column2, Column3"1","xxxx","yyyy""2", "xxxx", "yyyy""3", "XXXX", "yyyy" I want to put all lines that begin with 1 in one text file, all the lines that begin with two in another text file, and the same with all lines that begin with 3. my problem is I want to be able to point at the folder that contains those files and have it read every text file in the folder and perform the operation. If this is possible can someone point me in the right direction on how to get started.Thank you for any help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
View 1 Replies View RelatedHello,
I have a datareader and whenever the value that it is suppose to display in the Label is empty because there is no Data Entered Yet I gives me and error and I have the event set ONLOAD so the page with no data to disply does not load.
So the Ranking.text = a select statement i have, but when I dont have any data into the SQL database there is nothing to SELECT so the program gives of an error.
Can someone help me with this. Thank you.
Here is the code: Dim objReader As SqlDataReader
conn.Open()
objReader = comm.ExecuteReader()
objReader.Read()lblRanking.Text = objReader("Rating")
objReader.Close()
Hi all,I'm a complete newbie on ASP.Net.I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.When I try to open the site, I always get the same error:Invalid object name 'Members'I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.Here is the Code of the page so far:<%@ Page Language="VB" Debug="True" Strict="True" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SQlClient" %><script runat="server">Sub Page_Load (ByVal Sender As Object, _ ByVal E As EventArgs) Dim connStr As String connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" connStr += "database=tempdb;" connStr += "Truster_Connection=yes"
Dim conn As New SQLConnection(connStr) conn.Open() Dim sql As String sql = "SELECT COUNT (*) FROM Members" Dim cmd As New SQLCommand(sql, conn) Dim ergebnis As String ergebnis = cmd.ExecuteScalar().toString() Dim t As String t = "Die Tabelle Members hat " & _ ergebnis & " Zeilen. <br>" & _ "Das Kommando lautet: " & _ cmd.CommandText & "<br>" & _ "Der Kommandotyp ist: " & _ cmd.CommandType ausgabe.innerHTML = t End Sub</script><html><head><title>Demo zu SQLCommand.ExecuteScalar</title></head><body><h3>Demo zu SQLCommand.ExecuteScalar</h3><p runat="server" id="ausgabe" /></body></html>Thanks for your help an sorry for my english.GreetsFlash_Prince
Hi,
How would I be able to query a table (ie. all people with last name 'Smith'), have that set of data outputted to a regular text file (in a formatted way)
And what's the best way to manipulate that set of data to let's say update a Yes/No field in that table to mark that that those individuals('Smith') which were outputted in that text file?
What about the reverse? If I got a regular text file with Last Name, Social Security(delimited by tab), etc is there a way I can get SQL Server to read that text file and make an update to the database based on the Social security in that text file.
Any help would be immensely appreciated!
Angel