Search Results

Search found 45340 results on 1814 pages for 'sql date functions'.

Page 258/1814 | < Previous Page | 254 255 256 257 258 259 260 261 262 263 264 265  | Next Page >

  • SQL to retrieve the latest records, grouping by unique foreign keys

    - by jbox
    I'm creating query to retrieve the latest posts in a forum using a SQL DB. I've got a table called "Post". Each post has a foreign key relation to a "Thread" and a "User" as well as a creation date. The trick is I don't want to show two posts by the same user or two posts in the same thread. Is it possible to create a query that contains all this logic? # Grab the last 10 posts. SELECT id, user_id, thread_id FROM posts ORDER BY created_at DESC LIMIT 10; # Grab the last 10 posts, max one post per user SELECT id, user_id, thread_id FROM post GROUP BY user_id ORDER BY date DESC LIMIT 10; # Grab the last 10 posts, max one post per user, max one post per thread???

    Read the article

  • More than 100 connection to sql server 2008 in "sleeping" status - Solved

    - by Allende
    I have a big trouble here, well at my server. I have an ASP .net web (framework 4.x) running on muy server, all the transactions/select/update/insert are made with ADO.NET. Well my problem is that after being using for a while (a couple of updates/selects/inserts) sometimes I got more than 100 connections on "sleeping" status when check for the connections on sql server with this query: SELECT spid, a.status, hostname, program_name, cmd, cpu, physical_io, blocked, b.name, loginame FROM master.dbo.sysprocesses a INNER JOIN master.dbo.sysdatabases b ON a.dbid = b.dbid where program_name like '%TMS%' ORDER BY spid I've been checking my code and closing every time I make a connection, I'm gonna test the new class, but I'm afraid the problem doesn't be fixed. It suppose that the connection pooling, keep the connections to re-use them, but until I see don't re-use them always. Any idea besides check for close all the connections open after use them? SOLVED(now I have just one and beautiful connection on "sleeping" status): Besides the anwser of David Stratton, I would like to share this link that help explain really well how the connection pool it works: http://dinesql.blogspot.com/2010/07/sql-server-sleeping-status-and.html Just to be short, you need to close every connection (sql connection objects) in order that the connection pool can re-use the connection and use the same connectinos string, to ensure this is highly recommended use one of the webConfig. Be careful with dataReaders you sould close its connection to (that was what make got out of my mind for while).

    Read the article

  • Why use SQL database?

    - by martinthenext
    I'm not quite sure stackoverflow is a place for such a general question, but let's give it a try. Being exposed to the need of storing application data somewhere, I've always used MySQL or sqlite, just because it's always done like that. As it seems like the whole world is using these databases, most of all software products, frameworks, etc. It is rather hard for a beginning developer like me to ask a question - why? Ok, say we have some object-oriented logic in our application, and objects are related to each other somehow. We need to map this logic to the storage logic, so we need relations between database objects too. This leads us to using relational database and I'm ok with that - to put it simple, our database rows sometimes will need to have references to other tables' rows. But why do use SQL language for interaction with such a database? SQL query is a text message. I can understand this is cool for actually understanding what it does, but isn't it silly to use text table and column names for a part of application that no one ever seen after deploynment? If you had to write a data storage from scratch, you would have never used this kind of solution. Personally, I would have used some 'compiled db query' bytecode, that would be assembled once inside a client application and passed to the database. And it surely would name tables and colons by id numbers, not ascii-strings. In the case of changes in table structure those byte queries could be recompiled according to new db schema, stored in XML or something like that. What are the problems of my idea? Is there any reason for me not to write it myself and to use SQL database instead?

    Read the article

  • missing value true / false: error in loop not in one-off

    - by vincent hay
    I am new on R and I have a problem with a test in a loop that I want to code. With a data frame (tabetest) like the one here after: Date 25179M103 1 14977 77.7309 2 14978 77.2567 3 14979 77.7507 I have: if(tabetest[3,"Date"]-tabetest[1,"Date"]1){print("ok")} [1] "ok" But: j=1 > position = 1 > price=tabetest for (i in 1:nrow(tabetest)-position){if(tabetest[i+position,"Date"]-tabetest[position,"Date"]>20){price[i+position,j]=price[i+position,j]/price[position,j]-1};position=position+1} Returns an error. R says that there is a missing value where true/false is required in: if (tabetest[i + position, "Date"] - tabetest[position, "Date"] > I have spent quite some time on that error but still don't understand where it comes from. Thanks for your help, Vincent

    Read the article

  • SQL Server 2000 intermittent connection exceptions on production server - specific environment probl

    - by StickyMcGinty
    We've been having intermittent problems causing users to be forcibly logged out of out application. Our set-up is ASP.Net/C# web application on Windows Server 2003 Standard Edition with SQL Server 2000 on the back end. We've recently performed a major product upgrade on our client's VMWare server (we have a guest instance dedicated to us) and whereas we had none of these issues with the previous release the added complexity that the new upgrade brings to the product has caused a lot of issues. We are also running SQL Server 2000 (build 8.00.2039, or SP4) and the IIS/ASP.NET (.Net v2.0.50727) application on the same box and connecting to each other via a TCP/IP connection. Primarily, the exceptions being thrown are: System.IndexOutOfRangeException: Cannot find table 0. System.ArgumentException: Column 'password' does not belong to table Table. [This exception occurs in the log in script, even though there is clearly a password column available] System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. [This one is occurring very regularly] System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. System.ApplicationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting. System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. And just today, for the first time: System.Web.UI.ViewStateException: Invalid viewstate. We have load tested the app using the same number of concurrent users as the production server and cannot reproduce these errors. They are very intermittent and occur even when there are only 8/9/10 user connections. My gut is telling me its ASP.NET - SQL Server 2000 connection issues.. We've pretty much ruled out code-level Data Access Layer errors at this stage (we've a development team of 15 experienced developers working on this) so we think its a specific production server environment issue.

    Read the article

  • T-SQL Query Results Not as Expected Deduplication

    - by Yoda
    Hi Guys, I am attempting to get all records where and Id field exists more than once, trouble is my query is returning nothing and I have no idea as to why!? And this is the only method I know. Here is my code: select [Customer Number], [Corporate Customer Number], [Order Date], [Order Number], [Order No], [Order Line Status], [Payment Method] , [ProcessOrder], [Order Platform] from Temp_ICOSOrder group by [Customer Number], [Corporate Customer Number], [Order Date], [Order Number], [Order No], [Order Line Status], [Payment Method] , [ProcessOrder] , [Order Platform] having COUNT([Order Number]) > 1 Any help is much appriciated!

    Read the article

  • Execute SQL on CSV files via JDBC

    - by Markos Fragkakis
    Dear all, I need to apply an SQL query to CSV files (comma-separated text files). My SQL is predefined from another tool, and is not eligible to change. It may contain embedded selects and table aliases in the FROM part. For my task I have found two open-source (this is a project requirement) libraries that provide JDBC drivers: CsvJdbc XlSQL JBoss Teiid Create an Apache Derby DB, load all CSVs as tables and execute the query. These are the problems I encountered: it does not accept the syntax of the SQL (it uses internal selects and table aliases). Furthermore, it has not been maintained since 2004. I could not get it to work, as it has as dependency a SAX Parser that causes exception when parsing other documents. Similarly, no change since 2004. Have not checked if it supports the syntax, but seems like an overhead. It needs several entities defines (Virtual Databases, Bindings). From the mailing list they told me that last release supports runtime creation of required objects. Has anyone used it for such simple task (normally it can connect to several types of data, like CSV, XML or other DBS and create a virtual, unified one)? Can this even be done easily? From the 4 things I considered/tried, only 3 and 4 seem to me viable. Any advice on these, or any other way in which I can query my CSV files? Cheers

    Read the article

  • Parsing/Tokenizing a String Containing a SQL Command

    - by Alan Storm
    Are there any open source libraries (any language, python/PHP preferred) that will tokenize/parse an ANSI SQL string into its various components? That is, if I had the following string SELECT a.foo, b.baz, a.bar FROM TABLE_A a LEFT JOIN TABLE_B b ON a.id = b.id WHERE baz = 'snafu'; I'd get back a data structure/object something like //fake PHPish $results['select-columns'] = Array[a.foo,b.baz,a.bar]; $results['tables'] = Array[TABLE_A,TABLE_B]; $results['table-aliases'] = Array[a=TABLE_A, b=TABLE_B]; //etc... Restated, I'm looking for the code in a database package that teases the SQL command apart so that the engine knows what to do with it. Searching the internet turns up a lot of results on how to parse a string WITH SQL. That's not what I want. I realize I could glop through an open source database's code to find what I want, but I was hoping for something a little more ready made, (although if you know where in the MySQL, PostgreSQL, SQLite source to look, feel free to pass it along) Thanks!

    Read the article

  • Problem with Zend_Date and ISO_8601 format

    - by Noah Goodrich
    Frankly, I'm flummoxed. Can anyone tell me why I would get a failure message with this code? $date = Zend_Date::now(); $date = $date->getIso(); if(Zend_Date::isDate($date, Zend_Date::ISO_8601)) { print('success'); } else { print('failure'); } exit; It also fails if I just pass in a Zend_Date object. UPDATE: a var_dump of the initial $date object looks like this: object(Zend_Date)#107 (8) { ["_locale:private"]= string(5) "en_US" ["_fractional:private"]= int(0) ["_precision:private"]= int(3) ["_unixTimestamp:private"]= int(1257508100) ["_timezone:private"]= string(14) "America/Denver" ["_offset:private"]= int(25200) ["_syncronised:private"]= int(0) ["_dst:protected"]= bool(true) } And a var_dump of the $date string after calling $date-getIso() looks like this: string(25) "2009-11-06T04:48:20-07:00" I am using ZF 1.9.5 on PHP 5.2.8. I am using XAMPP for Windows too if that makes a difference.

    Read the article

  • Organising dates in PHP

    - by richzilla
    Hi All, Im wanting to organize a series of dates in the following format. As a bit of context, the dates are the dates of upcoming events that can be added by my users. The format: January 2011 date - event date - event date - event February 2011 date - event date - event date - event with all events in a given month, arranged by the date on which they occur. All of my event dates are stored in my database as unix timestamps. Problem is, i dotn know where id start to develop a function that would order all of my events in this way. Can anyone help me out? Cheers

    Read the article

  • Best way to deploy VB.NET Code / create an assembly on a SQLServer

    - by 1passenger
    I've created some functions / procedures with VB.NET and want to deploy them to a SQLServer. Within Visual Studio you can click right and select "deploy". An assembly will be created on the server and a lot of functions and procedures. It's really easy. Now I want to script the whole deployment process. What is Visual Studio doing when I make a deployment? Can Visual Studio autoscript all the deployment steps for me? Can I save it to a file and execute it manually?

    Read the article

  • Converting SQL statement into Linq

    - by DMan
    I'm trying to convert the following to a LINQ to SQL statement in C#. Can anyone give me a hand? Basically my table keeps record of all history of changes such that the created date max date for each seedlot is the most recent record and the correct one to show. SELECT reports.* FROM [dbo].[Reports] reports WHERE reports.createdDate IN ( SELECT MAX(report_max_dates.createdDate) FROM [dbo].[Reports] report_max_dates GROUP BY report_max_dates.Lot ) So far this is what I have. var result = (from report in db.Reports where report.createdDate == (from report_max in db.Reports group report_max by report_max.Lot into report_max_grouped select report_max_grouped).Max() select report); I can't figure out how to get the MAX dates for all reports and how to do an IN statement on the report.createdDate. Thansk, Dman

    Read the article

  • oData/ADO.NET Data Services using LINQ-to-SQL with a decryption layer

    - by Program.X
    I have written an application using LINQ-to-SQL that submits a web form into a database. I absact the LINQ-to-SQL away using a Repository pattern. This repository has the basic methods: Get(), Save(), etc. As a development of the project, I needed to encrypt certain fields in the form. This was trivial, as I just added the encryption calls to the Get(), Save() methods in the Repository. Now, I want to put an oData layer over it, to allow RESTful extraction from MS Excel 2010 (when it comes out). I have this working, after a few stumbles on useless error messages, etc. However, obviously, those encrypted fields are still encrypted. My repository pattern would have decrypted these for me. As far as I know, I have to directly bind my oData service to the LINQ-to-SQL context for the schema, etc. to work - unless I enter a whole world of pain (any URLs appreciated). Is there a way I can insert my encryption/decryption layer into the request so decryption is done "on the fly"? I looked at the OnStartProcessingRequest() overload of DataService but this doesn't seem that useful.

    Read the article

  • PostgreSQL - Error: SQL state: XX000.

    - by rob
    I have a table in Postgres that looks like this: CREATE TABLE "Population" ( "Id" bigint NOT NULL DEFAULT nextval('"population_Id_seq"'::regclass), "Name" character varying(255) NOT NULL, "Description" character varying(1024), "IsVisible" boolean NOT NULL CONSTRAINT "pk_Population" PRIMARY KEY ("Id") ) WITH ( OIDS=FALSE ); And a select function that looks like this: CREATE OR REPLACE FUNCTION "Population_SelectAll"() RETURNS SETOF "Population" AS $BODY$select "Id", "Name", "Description", "IsVisible" from "Population"; $BODY$ LANGUAGE 'sql' STABLE COST 100 Calling the select function returns all the rows in the table as expected. I have a need to add a couple of columns to the table (both of which are foreign keys to other tables in the database). This gives me a new table def as follows: CREATE TABLE "Population" ( "Id" bigint NOT NULL DEFAULT nextval('"population_Id_seq"'::regclass), "Name" character varying(255) NOT NULL, "Description" character varying(1024), "IsVisible" boolean NOT NULL, "DefaultSpeciesId" bigint NOT NULL, "DefaultEcotypeId" bigint NOT NULL, CONSTRAINT "pk_Population" PRIMARY KEY ("Id"), CONSTRAINT "fk_Population_DefaultEcotypeId" FOREIGN KEY ("DefaultEcotypeId") REFERENCES "Ecotype" ("Id") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "fk_Population_DefaultSpeciesId" FOREIGN KEY ("DefaultSpeciesId") REFERENCES "Species" ("Id") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); and function: CREATE OR REPLACE FUNCTION "Population_SelectAll"() RETURNS SETOF "Population" AS $BODY$select "Id", "Name", "Description", "IsVisible", "DefaultSpeciesId", "DefaultEcotypeId" from "Population"; $BODY$ LANGUAGE 'sql' STABLE COST 100 ROWS 1000; Calling the function after these changes results in the following error message: ERROR: could not find attribute 11 in subquery targetlist SQL state: XX000 What is causing this error and how do I fix it? I have tried to drop and recreate the columns and function - but the same error occurs. Platform is PostgreSQL 8.4 running on Windows Server. Thanks.

    Read the article

  • SQL Server Index cost

    - by yellowstar
    I have read that one of the tradeoffs for adding table indexes in SQL Server is the increased cost of insert/update/delete queries to benefit the performance of select queries. I can conceptually understand what happens in the case of an insert because SQL Server has to write entries into each index matching the new rows, but update and delete are a little more murky to me because I can't quite wrap my head around what the database engine has to do. Let's take DELETE as an example and assume I have the following schema (pardon the pseudo-SQL) TABLE Foo col1 int ,col2 int ,col3 int ,col4 int PRIMARY KEY (col1,col2) INDEX IX_1 col3 INCLUDE col4 Now, if I issue the statement DELETE FROM Foo WHERE col1=12 AND col2 > 34 I understand what the engine must do to update the table (or clustered index if you prefer). The index is set up to make it easy to find the range of rows to be removed and do so. However, at this point it also needs to update IX_1 and the query that I gave it gives no obvious efficient way for the database engine to find the rows to update. Is it forced to do a full index scan at this point? Does the engine read the rows from the clustered index first and generate a smarter internal delete against the index? It might help me to wrap my head around this if I understood better what is going on under the hood, but I guess my real question is this. I have a database that is spending a significant amount of time in delete and I'm trying to figure out what I can do about it. When I display the execution plan for the deletion, it just shows an entry for "Clustered Index Delete" on table Foo which lists in the details section the other indices that need to be updated but I don't get any indication of the relative cost of these other indices. Are they all equal in this case? Is there some way that I can estimate the impact of removing one or more of these indices without having to actually try it?

    Read the article

  • useful customize/enhanced php functions that make thing easy and better

    - by I Like PHP
    Hello All, i like to work in php bcoz it's just amazing language. please share basic, useful, enhanced and customize function that make things better and easy in php and must be used in our all PHP project, i m sharing some of them please share your customize function that may be useful for everyone alternative/ enhanced print_r() and var_dump() function watch( $what ) { echo '<pre>'; if ( is_array( $what ) ) { print_r ( $what ); } else { var_dump ( $what ); } echo '</pre>'; } usage: 1. watch($_POST); // to see all post variable 2. watch($array); // to see any variable may b array, string or a variable enhanced mysql_escape_string() for multidimensional array to prevent sql injection function recursive_escape(&$value) { if (is_array($value)) array_map('recursive_escape', $value); else $value = mysql_escape_string($value); } usage array_map('recursive_escape', $_POST); ---------------------For encoding Get variables-------------------------------------- function nkode($k) { if ( is_array( $k ) ) return array_map("base64_encode",$k); else return base64_encode($k); } ---------------------for decoding varaibles from GET--------------------------------- function dkode($k) { if ( is_array( $k ) ) return array_map("base64_decode",$k); else return base64_decode($k); } Usage <a href="somelink.php?pid=<?php echo nkode($someid)?>"> and on next page(somelink.php) $findID=dkode($_GET[pid]); date convert to mm/dd/yyyy to yyyy-mm-dd( if we use date datatype in mysql) and also change into mm/dd/yyyy to disply on page function dateconvert($date,$func) { if ($func == 1){ //insert conversion list($month, $day, $year) = split('[/.-]', $date); $date = "$year-$month-$day"; return $date; } if ($func == 2){ //output conversion list($year, $month, $day) = split('[-.]', $date); $date = "$month/$day/$year"; return $date; } } usage $firstDate=dateconvert($_POST['firstdate'],1); // for insertion in database $showDate=dateconvert($fetch->date_field,2) // to display on browser to clean(sql injection proof) data before doing some action with that variable function cleandata($data) { $success=0; $data=trim($data); $data=strtolower($data); $data=strip_tags($data); return $data; } usage cleandata($_POST[username]); cleandata($_GET[pid]); please share any basic function that must be used , Thanks

    Read the article

  • Django aggregate query generating SQL error

    - by meepmeep
    I'm using Django 1.1.1 on a SQL Server 2005 db using the latest sqlserver_ado library. models.py includes: class Project(models.Model): name = models.CharField(max_length=50) class Thing(models.Model): project = models.ForeignKey(Project) reference = models.CharField(max_length=50) class ThingMonth(models.Model): thing = models.ForeignKey(Thing) timestamp = models.DateTimeField() ThingMonthValue = models.FloatField() class Meta: db_table = u'ThingMonthSummary' In a view, I have retrieved a queryset called 'things' which contains 25 Things: things = Thing.objects.select_related().filter(project=1).order_by('reference') I then want to do an aggregate query to get the average ThingMonthValue for the first 20 of those Things for a certain period, and the same value for the last 5. For the first 20 I do: averageThingMonthValue = ThingMonth.objects.filter(turbine__in=things[:20],timestamp__range="2009-01-01 00:00","2010-03-00:00")).aggregate(Avg('ThingMonthValue'))['ThingMonthValue__avg'] This works fine, and returns the desired value. For the last 5 I do: averageThingMonthValue = ThingMonth.objects.filter(turbine__in=things[20:],timestamp__range="2009-01-01 00:00","2010-03-00:00")).aggregate(Avg('ThingMonthValue'))['ThingMonthValue__avg'] But for this I get an SQL error: 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' The SQL query being used by django reads: SELECT AVG([ThingMonthSummary].[ThingMonthValue]) AS [ThingMonthValue__avg] FROM [ThingMonthSummary] WHERE ([ThingMonthSummary].[thing_id] IN (SELECT _row_num, [id] FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY [AAAA].[id] ASC) as _row_num, [AAAA].[id] FROM ( SELECT U0.[id] FROM [Thing] U0 WHERE U0.[project_id] = 1 ) AS [AAAA]) as QQQ where 20 < _row_num) AND [ThingMonthSummary].[timestamp] BETWEEN '01/01/09 00:00:00' and '03/01/10 00:00:00') Any idea why it works for one slice of the Things and not the second? I've checked and the two slices do contain the desired Things correctly.

    Read the article

  • Full Text Search in SQL Server 2008 shows wrong display_item for Thai language

    - by ensecoz
    I am working with SQL Server 2008. My task is to investigate the issue where FTS cannot find the right result for Thai. First, I have the table which enables the FTS on the column 'ItemName' which is nvarchar. The Catalog is created with the Thai Language. Note that the Thai language is one of the languages that doesn't separate the word by spaces, so '????' '???' '????' are written like this in a sentence: '???????????' In the table, there are many rows that include the word (????); for example row#1 (ItemName: '???????????') On the webpage, I try to search for '????' but SQL Server cannot find it. So I try to investigate it by trying the following query in SQL Server: select * from sys.dm_fts_parser(N'"???????????"', 1054, 0, 0) ...to see how the words are broken. The first one is the text to be broken. The second parameter is to specify that we're using Thai (WorkBreaker, so on). Here is the result: row#1 (display_item: '????', source_item: '???????????') row#2 (display_item: '????', source_item: '???????????') row#3 (display_item: '??', source_item: '???????????') Notice that the first and second row display the wrong display_item '?' in the '????' isn't even Thai characters. '?' in '????' is not a Thai character either. So the question is where did those alien characters come from? I guess this why I cannot search for '????' because the word breaker is broken and keeping the wrong character in the indexes. Please help!

    Read the article

  • Error when calling SQL SP via LINQ

    - by PaulC
    Newbie problem: I have a SQL SP with ten parameters (eight input, two output) but when I attempt to call it via LINQ from code I get the following error message: "The best overloaded method match for 'DataClassesDataContext.ST_CR_CREATE_CASE_BASIS(string, string, string, string, System.DateTime?, string, string, string, ref int?, ref int?)' has some invalid arguments". The params with ? appear to be unrecognized, but I'm baffled: the data types match the SQL types, the number of parameters match, the other parmeters don't exhibit the same behaviour. Can anyone tell me what's going on? Thanks in advance. -- SQL SP: create procedure ST_CR_CREATE_CASE_BASIS @p_Pers_No nvarchar (50), @p_Subject nvarchar (255), @p_RQ_XML nvarchar(max), @p_RQ_XSL nvarchar(max), @p_Date_Submit smalldatetime, @p_User_ID_Submit nvarchar (255), @p_RQ_Status nvarchar (50), @p_User_ID_OnBehalf nvarchar (255), @p_Case_Number int output, @p_RQ_ID int output as begin -- ... etc.; the SP works fine when called from SSMS The code-behind proc from the aspx page looks like this: protected void cmdSubmit_Click(object sender, EventArgs e) { using (DataClassesDataContext vDataCont = new DataClassesDataContext()) { Int32 vNewCaseNr; Int32 vNewReqNr; DateTime vNow = System.DateTime.Now; vDataCont.ST_CR_CREATE_CASE_BASIS("101", "Test Subject Late Wed", null, null, vNow , "101", "1", "101", ref vNewCaseNr, vNewReqNr); } }

    Read the article

  • Getting "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Microsoft.'

    - by brohjoe
    Hi Experts, I'm getting an error, "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Microsoft.' Here is the code: Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim stSQL As String Public Sub loadData() 'This was set up using Microsoft ActiveX Data Components version 6.0. 'Create ADODB connection object, open connection and construct the connection string object which is the DSN name. Set conn = New ADODB.Connection conn.ConnectionString = "sql_server" conn.Open 'conn.Execute (strSQL) On Error GoTo ErrorHandler 'Open Excel and run query to export data to SQL Server. strSQL = "SELECT * INTO SalesOrders FROM OPENDATASOURCE(Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Workbook.xlsx;" & _ "Extended Properties=Excel 12.0; [Sales Orders])" conn.Execute (strSQL) 'Error handling. ErrorExit: 'Reclaim memory from the cntection objects Set rst = Nothing Set conn = Nothing Exit Sub ErrorHandler: MsgBox Err.Description, vbCritical Resume ErrorExit 'clean up and reclaim memory resources. conn.Close If CBool(cnt.State And adStateOpen) Then Set rst = Nothing Set conn = Nothing End If End Sub

    Read the article

  • Create a SQL query to retrieve most recent records

    - by mattruma
    I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows: Date User Status Notes ------------------------------------------------------- 1/8/2009 12:00pm B.Sisko In Out to lunch 1/8/2009 8:00am B.Sisko In 1/7/2009 5:00pm B.Sisko In 1/7/2009 8:00am B.Sisko In 1/7/2009 8:00am K.Janeway In 1/5/2009 8:00am K.Janeway In 1/1/2009 8:00am J.Picard Out Vacation I would like to query the data and return the most recent status for each user, in this case, my query would return the following results: Date User Status Notes ------------------------------------------------------- 1/8/2009 12:00pm B.Sisko In Out to lunch 1/7/2009 8:00am K.Janeway In 1/1/2009 8:00am J.Picard Out Vacation I am try to figure out the TRANSACT-SQL to make this happen? Any help would be appreciated.

    Read the article

  • SQL query in JSP file pulling variable from VXML file

    - by s1066
    Hi I'm trying to get an SQL query to work within a JSP file. The JSP file is pulled by a VXML file here is my JSP file code: <?xml version="1.0"?> <%@ page import="java.util.*" %> <%@ page import="java.sql.*" %> <% boolean success = true; // Always optimistic String info = ""; String schoolname = request.getParameter("schoolname"); String informationtype = request.getParameter("informationtype"); try { Class.forName("org.postgresql.Driver"); String connectString = "jdbc:postgresql://localhost:5435/N0176359"; String user = "****"; String password = "*****"; Connection conn = DriverManager.getConnection(connectString, user, password); Statement st = conn.createStatement(); ResultSet rsvp = st.executeQuery("SELECT * FROM lincolnshire_school_information_new WHERE school_name=\'"+schoolname+"\'"); rsvp.next(); info = rsvp.getString(2); }catch (ClassNotFoundException e) { success = false; // something went wrong } %> As you can see I'm trying to insert the value of the variable declared as "schooname" into the end of the SQL query. However when I come to run the jsp file it doesn't work and I get an error "ResultSet not positioned properly". When I put a standard query in (without trying to make it value of the variable it works fine) Hope that makes sense, and thank you for any help!

    Read the article

  • Index Tuning for SSIS tasks

    - by Raj More
    I am loading tables in my warehouse using SSIS. Since my SSIS is slow, it seemed like a great idea to build indexes on the tables. There are no primary keys (and therefore, foreign keys), indexes (clustered or otherwise), constraints, on this warehouse. In other words, it is 100% efficiency free. We are going to put indexes based on usage - by analyzing new queries and current query performance. So, instead of doing it our old fashioned sweat and grunt way of actually reading the SQL statements and execution plans, I thought I'd put the shiny new Database Engine Tuning Advisor to use. I turned SQL logging off in my SSIS package and ran a "Tuning" trace, saved it to a table and analyzed the output in the Tuning Advisor. Most of the lookups are done as: exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',1 exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',2 exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',3 exec sp_executesql N'SELECT [Active], [CompanyID], [CompanyName], [CompanyShortName], [CompanyTypeID], [HierarchyNodeID] FROM [dbo].[Company] WHERE ([CompanyID]=@P1) AND ([StartDateTime] IS NOT NULL AND [EndDateTime] IS NULL)',N'@P1 int',4 and when analyzed, these statements have the reason "Event does not reference any tables". Huh? Does it not see the FROM dbo.Company??!! What is going on here? So, I have multiple questions: How do I get it to capture the actual statement executing in my trace, not what was submitted in a batch? Are there any best practices to follow for tuning performance related to SSIS packages running against SQL Server 2008?

    Read the article

  • Finding Common Phrases in MS SQL TEXT Column

    - by regex
    Hello All, Short Desc: I'm curious to see if I can use SQL Analysis services or some other MS SQL service to mine some data for me that will show commonalities between SQL TEXT fields in a dataset. Long Desc I am looking at a subset of data that consists of about 10,000 rows of TEXT blobs which are used as a notes column in a issue tracking (ticketing) software. I would like to use something out of the box (without having to build something) that might be able to parse through all of the rows and find commonly used byte sequences in the "Notes" column. In other words, I want to find commonly used phrases (two to three word phrases, so 9 - 20 character sections of the TEXT blob). This will help me better determine if associate's notes contain similar phrases (troubleshooting techniques) that we could standardize in our troubleshooting process flow. Closing Note I'd really rather not build an application to do this as my method will probably not be the most efficient way to do it. Hopefully all this makes sense. Please let me know in the comments if anything needs clarification. Thanks in advance for your help.

    Read the article

< Previous Page | 254 255 256 257 258 259 260 261 262 263 264 265  | Next Page >