Search Results

Search found 27339 results on 1094 pages for 'sql dmv'.

Page 237/1094 | < Previous Page | 233 234 235 236 237 238 239 240 241 242 243 244  | Next Page >

• Help with SQL server stack dump

  - by edosoft

  Hi guru's We're running SQL 2005 standard SP2 on a 4cpu box. Suddenly it crashdumps, after which all pooled connections are invalid and it goes into admin-only mode (only sa can connect) The short stackdump is below. After the dump a number of errors show up like '2008-09-16 10:49:34.48 Server Resource Monitor (0xec4) Worker 0x03D1C0E8 appears to be non-yielding on Node 0. Memory freed: 232408 KB. Approx CPU Used: kernel 203 ms, user 140 ms, Interval: 250250.' Have Googled around but couldn't find a definate answer. Anyone? 2008-09-16 10:46:24.98 Server Using 'dbghelp.dll' version '4.0.5' 2008-09-16 10:46:25.40 Server **Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000 2008-09-16 10:46:25.40 Server ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0009.txt 2008-09-16 10:46:25.40 Server * ******************************************************************************* 2008-09-16 10:46:25.40 Server * 2008-09-16 10:46:25.40 Server * BEGIN STACK DUMP: 2008-09-16 10:46:25.40 Server * 09/16/08 10:46:25 spid 0 2008-09-16 10:46:25.42 Server * 2008-09-16 10:46:25.42 Server * Non-yielding Resource Monitor 2008-09-16 10:46:25.42 Server * 2008-09-16 10:46:25.42 Server * ******************************************************************************* 2008-09-16 10:46:25.42 Server * ------------------------------------------------------------------------------- 2008-09-16 10:46:25.42 Server * Short Stack Dump 2008-09-16 10:46:25.76 Server Stack Signature for the dump is 0x00000352 2008-09-16 10:46:32.70 Server External dump process return code 0x20000001.

  Read the article

• SQL Server 2005 and 'General network error'

  - by Mariusz

  I know there is a lot of information in the Internet about solving this problem, but it didn't help me. My Delphi application uses dbExpress controls to access the database and execute SQL queries. Once every couple of days, however, it stops working because the database connection fails. This happens on several different computers with different versions of Windows. MSSQL Server 2005 (version 9.0.4035) is installed on each of them. The above mentioned application executes queries every couple of seconds, and they are mainly insert commands. Every couple of days I get a series of exceptions like the following one: [DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation. And then the SQL server becomes inaccessible until I restart it manually. The information I found in the Internet say that I should install some service packs, change some registry entries etc., but believe me, none of these helps and I don't know what else to do now. Could you please help me solve this problem? Any clues or ideas? I can give you some more information about the server or the application if necessary. Thank you very much in advance.

  Read the article

• MS SQL server and Trees

  - by Julian

  Im looking for some way of extrating data form a tree table as defined below. Table Tree Defined as :- TreeID uniqueidentifier TreeParent uniqueidentifier TreeCode varchar(50) TreeDesc varchar(100) Data some (23k rows), Parent Refs back into ID in table The following SQL renders the whole tree (takes arround 2 mins 30) I need to do the following. 1) Render each Tree Node with its LVL 1 parent 2) Render all nodes that have a Description that matches a TreeDesc like 'SomeText%' 3) Render all parent nodes that are for a single tree id. Items 2 and 3 take 2mins30 so this needs to be a lot faster! Item 1, just cant work out how to do it with out killing SQL or taking forever any sugestions would be helpfull Thanks Julian WITH TreeCTE(TreeCode, TreeDesc, depth, TreeParent, TreeID) AS ( -- anchor member SELECT cast('' as varchar(50)) as TreeCode , cast('Trees' as varchar(100)) as TreeDesc, cast('0' as Integer) as depth, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) as TreeID UNION ALL -- recursive member SELECT s.TreeCode, s.TreeDesc, cte.depth+1, isnull(s.TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)), isnull(s.TreeID, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) FROM pdTrees AS S JOIN TreeCTE AS cte ON isnull(s.TreeParent, cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) = isnull( cte.TreeID , cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)) ) -- outer query SELECT s.TreeID, s.TreeCode, s.TreeDesc, s.depth, s.TreeParent FROM TreeCTE s

  Read the article

• Nature of Lock is child table while deletion(sql server)

  - by Mubashar Ahmad

  Dear Devs From couple of days i am thinking of a following scenario Consider I have 2 tables with parent child relationship of kind one-to-many. On removal of parent row i have to delete the rows in child those are related to parents. simple right? i have to make a transaction scope to do above operation i can do this as following; (its psuedo code but i am doing this in c# code using odbc connection and database is sql server) begin transaction(read committed) Read all child where child.fk = p1 foreach(child) delete child where child.pk = cx delete parent where parent.pk = p1 commit trans OR begin transaction(read committed) delete all child where child.fk = p1 delete parent where parent.pk = p1 commit trans Now there are couple of questions in my mind Which one of above is better to use specially considering a scenario of real time system where thousands of other operations(select/update/delete/insert) are being performed within a span of seconds. does it ensure that no new child with child.fk = p1 will be added until transaction completes? If yes for 2nd question then how it ensures? do it take the table level locks or what. Is there any kind of Index locking supported by sql server if yes what it does and how it can be used. Regards Mubashar

  Read the article

• SQL 2000 (MSDE) Hangs When It Receives an Erroneous Query from a Classic ASP Web Application

  - by Jimbo

  I have a SQL interface page in my classic ASP web app that allows admin users to run queries against the app's database (MSDE 2000) - it simply consists of a textarea that the user submits and the app returns the resulting list of records as below Dim oRS Set oRS = Server.CreateObject("ADODB.Recordset") oRS.ActiveConnection = sConnectionString // run the query - this is for the admin only so doesnt check for sql safe commands etc. oRS.Open Request.Form("txtSQL") If Not oRS.EOF Then // list the field names from the recordset For i = 0 to oRS.Fields.Count - 1 Response.Write oRS.Fields(i).name & "&nbsp;" Next // show the data for each record in the recordset While Not oRS.EOF For i = 0 to oRS.Fields.Count - 1 Response.Write oRS.Fields(i).value & "&nbsp;" Next Response.Write "<br />" oRS.Movenext() Wend End If The problem with this is that if you send it an invalid query (with a spelling mistake, invalid join etc.) instead of throwing back an error immediately, it hangs IIS (you can see this by trying to browse the app from another computer, it fails) for a number of minutes and THEN returns the error. I have NO idea why! Can anyone help?

  Read the article

• "Executing SQL directly; no cursor" error when using SCOPE_IDENTITY

  - by Chris

  There wasn't much on google about this error, so I'm askin here. I'm switching a PHP web application from using MySQL to SQL Server 2008 (using ODBC, not php_mssql). Running queries or anything else isn't a problem, but when I try to do scope_identity (or any similar functions), I get the error "Executing SQL directly; no cursor". I'm doing this immediately after an insert, so it should still be in scope. Running the same insert statement then query for the insert ID works fine in SQL Server Management Studio. Here's my code right now (everything else in the database wrapper class works fine for other queries, so I'll assume it isn't relevant right now): function insert_id(){ $x = $this->query_first("SELECT SCOPE_IDENTITY('session_log') as insert_id"); echo "($x)"; return $x; } query_first being a function that returns the first result from the first field of a query (basically the equivalent of execute_scalar() on .net). The full error message: Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor., SQL state 01000 in SQLExecDirect in C:[...]\Database_MSSQL.php on line 110

  Read the article

• LEFT OUTER JOIN in Linq - How to Force

  - by dodegaard

  I have a LEFT OUTER OUTER join in LINQ that is combining with the outer join condition and not providing the desired results. It is basically limiting my LEFT side result with this combination. Here is the LINQ and resulting SQL. What I'd like is for "AND ([t2].[EligEnd] = @p0" in the LINQ query to not bew part of the join condition but rather a subquery to filter results BEFORE the join. Thanks in advance (samples pulled from LINQPad) - Doug (from l in Users join mr in (from mri in vwMETRemotes where met.EligEnd == Convert.ToDateTime("2009-10-31") select mri) on l.Mahcpid equals mr.Mahcpid into lo from g in lo.DefaultIfEmpty() orderby l.LastName, l.FirstName where l.LastName.StartsWith("smith") && l.DeletedDate == null select g) Here is the resulting SQL -- Region Parameters DECLARE @p0 DateTime = '2009-10-31 00:00:00.000' DECLARE @p1 NVarChar(6) = 'smith%' -- EndRegion SELECT [t2].[test], [t2].[MAHCPID] AS [Mahcpid], [t2].[FirstName], [t2].[LastName], [t2].[Gender], [t2].[Address1], [t2].[Address2], [t2].[City], [t2].[State] AS [State], [t2].[ZipCode], [t2].[Email], [t2].[EligStart], [t2].[EligEnd], [t2].[Dependent], [t2].[DateOfBirth], [t2].[ID], [t2].[MiddleInit], [t2].[Age], [t2].[SSN] AS [Ssn], [t2].[County], [t2].[HomePhone], [t2].[EmpGroupID], [t2].[PopulationIdentifier] FROM [dbo].[User] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[MAHCPID], [t1].[FirstName], [t1].[LastName], [t1].[Gender], [t1].[Address1], [t1].[Address2], [t1].[City], [t1].[State], [t1].[ZipCode], [t1].[Email], [t1].[EligStart], [t1].[EligEnd], [t1].[Dependent], [t1].[DateOfBirth], [t1].[ID], [t1].[MiddleInit], [t1].[Age], [t1].[SSN], [t1].[County], [t1].[HomePhone], [t1].[EmpGroupID], [t1].[PopulationIdentifier] FROM [dbo].[vwMETRemote] AS [t1] ) AS [t2] ON ([t0].[MAHCPID] = [t2].[MAHCPID]) AND ([t2].[EligEnd] = @p0) WHERE ([t0].[LastName] LIKE @p1) AND ([t0].[DeletedDate] IS NULL) ORDER BY [t0].[LastName], [t0].[FirstName]

  Read the article

• Accounting System for Winforms / SQL Server applications

  - by Craig L

  If you were going to write a vertical market C# / WinForms / SQL Server application and needed an accounting "engine" for it, what software package would you chose ? By vertical market, I mean the application is intended to solve a particular set of business problems, not be a generic accounting application. Thus the value add of the program is the 70% of non-accounting related functionality present in the finished product. The 30% of accounting functionality is merely to enable the basic accounting needs of the business. I said all that to lead up to this: The accounting engine needs to be a royalty-free runtime license and not super expensive. I've found a couple C#/SQL Server accounting apps that can be had with source code and a royalty free run time for $150k+ and that would be fine for greenfield development funded by a large bankroll, but for smaller apps, that sort of capital outlay isn't feasible. Something along the lines of $5k to $15k for a royalty-free runtime would be more reasonable. Open-source would be even better. By accounting engine, I mean something that takes care of at a minimum: General Ledger Invoices Statements Accounts Receivable Payments / Credits Basically, an accounting engine should be something that lets the developer concentrate on the value added (industry specific business best practices / processes) part of the solution and not have to worry about how to implement the low level details of a double entry accounting system. Ideally, the accounting engine would be something that is licensed on a royalty free run-time basis. Suggestions, please ?

  Read the article

• SQL select descendants of a row

  - by Joey Adams

  Suppose a tree structure is implemented in SQL like this: CREATE TABLE nodes ( id INTEGER PRIMARY KEY, parent INTEGER -- references nodes(id) ); Although cycles can be created in this representation, let's assume we never let that happen. The table will only store a collection of roots (records where parent is null) and their descendants. The goal is to, given an id of a node on the table, find all nodes that are descendants of it. A is a descendant of B if either A's parent is B or A's parent is a descendant of B. Note the recursive definition. Here is some sample data: INSERT INTO nodes VALUES (1, NULL); INSERT INTO nodes VALUES (2, 1); INSERT INTO nodes VALUES (3, 2); INSERT INTO nodes VALUES (4, 3); INSERT INTO nodes VALUES (5, 3); INSERT INTO nodes VALUES (6, 2); which represents: 1 `-- 2 |-- 3 | |-- 4 | `-- 5 | `-- 6 We can select the (immediate) children of 1 by doing this: SELECT a.* FROM nodes AS a WHERE parent=1; We can select the children and grandchildren of 1 by doing this: SELECT a.* FROM nodes AS a WHERE parent=1 UNION ALL SELECT b.* FROM nodes AS a, nodes AS b WHERE a.parent=1 AND b.parent=a.id; We can select the children, grandchildren, and great grandchildren of 1 by doing this: SELECT a.* FROM nodes AS a WHERE parent=1 UNION ALL SELECT b.* FROM nodes AS a, nodes AS b WHERE a.parent=1 AND b.parent=a.id UNION ALL SELECT c.* FROM nodes AS a, nodes AS b, nodes AS c WHERE a.parent=1 AND b.parent=a.id AND c.parent=b.id; How can a query be constructed that gets all descendants of node 1 rather than those at a finite depth? It seems like I would need to create a recursive query or something. I'd like to know if such a query would be possible using SQLite. However, if this type of query requires features not available in SQLite, I'm curious to know if it can be done in other SQL databases.

  Read the article

• Advice on optimzing speed for a Stored Procedure that uses Views

  - by Belliez

  Based on a previous question and with a lot of help from Damir Sudarevic (thanks) I have the following sql code which works great but is very slow. Can anyone suggest how I can speed this up and optimise for speed. I am now using SQL Server Express 2008 (not 2005 as per my original question). What this code does is retrieves parameters and their associated values from several tables and rotates the table in a form that can be easily compared. Its great for one of two rows of data but now I am testing with 100 rows and to run GetJobParameters takes over 7 minutes to complete? Any advice is gratefully accepted, thank you in advanced. /*********************************************************************************************** ** CREATE A VIEW (VIRTUAL TABLE) TO ALLOW EASIER RETREIVAL OF PARMETERS ************************************************************************************************/ CREATE VIEW dbo.vParameters AS SELECT m.MachineID AS [Machine ID] ,j.JobID AS [Job ID] ,p.ParamID AS [Param ID] ,t.ParamTypeID AS [Param Type ID] ,m.Name AS [Machine Name] ,j.Name AS [Job Name] ,t.Name AS [Param Type Name] ,t.JobDataType AS [Job DataType] ,x.Value AS [Measurement Value] ,x.Unit AS [Unit] ,y.Value AS [JobDataType] FROM dbo.Machines AS m JOIN dbo.JobFiles AS j ON j.MachineID = m.MachineID JOIN dbo.JobParams AS p ON p.JobFileID = j.JobID JOIN dbo.JobParamType AS t ON t.ParamTypeID = p.ParamTypeID LEFT JOIN dbo.JobMeasurement AS x ON x.ParamID = p.ParamID LEFT JOIN dbo.JobTrait AS y ON y.ParamID = p.ParamID GO -- Step 2 CREATE VIEW dbo.vJobValues AS SELECT [Job Name] ,[Param Type Name] ,COALESCE(cast([Measurement Value] AS varchar(50)), [JobDataType]) AS [Val] FROM dbo.vParameters GO /*********************************************************************************************** ** GET JOB PARMETERS FROM THE VIEW JUST CREATED ************************************************************************************************/ CREATE PROCEDURE GetJobParameters AS -- Step 3 DECLARE @Params TABLE ( id int IDENTITY (1,1) ,ParamName varchar(50) ); INSERT INTO @Params (ParamName) SELECT DISTINCT [Name] FROM dbo.JobParamType -- Step 4 DECLARE @qw TABLE( id int IDENTITY (1,1) , txt nchar(300) ) INSERT INTO @qw (txt) SELECT 'SELECT' UNION SELECT '[Job Name]' ; INSERT INTO @qw (txt) SELECT ',MAX(CASE [Param Type Name] WHEN ''' + ParamName + ''' THEN Val ELSE NULL END) AS [' + ParamName + ']' FROM @Params ORDER BY id; INSERT INTO @qw (txt) SELECT 'FROM dbo.vJobValues' UNION SELECT 'GROUP BY [Job Name]' UNION SELECT 'ORDER BY [Job Name]'; -- Step 5 --SELECT txt FROM @qw DECLARE @sql_output VARCHAR (MAX) SET @sql_output = '' -- NULL + '' = NULL, so we need to have a seed SELECT @sql_output = -- string to avoid losing the first line. COALESCE (@sql_output + txt + char (10), '') FROM @qw EXEC (@sql_output) GO

  Read the article

• Complex SQL query... names of returned variables

  - by Andrew P.

  Excuse me for what I'm sure is an elementary question for most of you, but I have an issue with table columns from separate tables having the same name as one another, and trying to select from both tables in the same query. Okay, so this is my code: $q_value = $mdb2->quote($_POST['query']); $field = $_POST['field']; $sql = "SELECT m.*, l.name FROM memberlist m, mail_lists l WHERE m.$field=$q_value AND l.id = m.list ORDER BY m.id"; $l_list = $mdb2->queryAll($sql, '', 'MDB2_FETCHMODE_ASSOC'); The table memberlist has the following columns: id, email, list, sex, name and the table mail_lists has the following columns: id, name After running the query, I later loop through the results with a foreach like so: foreach ($l_list as $l){ //blahblah } The problem is that the column 'name' in mail_lists refers to the names of the list, while the column 'name' in memberlist refers to the name of the member. When I later access $l-name (within the foreach), will I get m.name, or l.name? Furthermore, how do I get access to the other? Or will I just have to do two separate queries?

  Read the article

• Skipping one item in the column

  - by zurna

  I created a simple news website. I store both videos and images in IMAGES table. Videos added have videos and images added have images stored in a column called ImagesType. Images and Videos attached to a news is stored in ImagesID column of the NEWS table. My problem occurs when I need to display the first image of a news. i.e. IMAGES table: ImagesID ImagesLgURL ImagesType 1 /FLPM/media/videos/0H7T9C0F.flv videos 2 /FLPM/media/images/8R5D7M8O.jpg images 3 /FLPM/media/images/0E7Q9Z0C.jpg images NEWS table NewsID ImagesID NewsTitle 1 1;2; Street Chic: Paris ERROR 2 3; Paris Runway NO ERROR The following code give me an error with the 2nd news item because the first ImageID stored in the list is not an image but a video. I need to figure out a way to skip the video item and display the next image. I hope I made sense. SQL = "SELECT NEWSID, CATEGORIESID, IMAGESID, NEWSTITLE, NEWSSHORTDESC, NEWSACTIVE, NEWSDATEENTERED" SQL = SQL & " FROM NEWS N" SQL = SQL & " WHERE NEWSACTIVE = 1" SQL = SQL & " ORDER BY NEWSDATEENTERED DESC" Set objNews = objConn.Execute(SQL) Do While intLooper1 <= 3 And Not objNews.EOF IMAGES = Split(Left(objNews("IMAGESID"),Len(objNews("IMAGESID"))-1), ";") SQL = "SELECT ImagesID, ImagesName, ImagesLgURL, ImagesSmURL, ImagesType" SQL = SQL & " FROM IMAGES I" SQL = SQL & " WHERE ImagesID = " & IMAGES(0) & " AND ImagesType = 'images'" Set objLgImage = objConn.Execute(SQL) <div> <a href="?Section=news&SubSection=redirect&NEWSID=<%=objNews("NEWSID")%>"> <img src="<%=objLgImage("ImagesLgURL")%>" alt="<%=objLgImage("ImagesName")%>" /> </a> </div> <% objLgImage.Close Set objLgImage = Nothing intLooper1 = intLooper1 + 1 objNews.MoveNext Loop %>

  Read the article

• Numeric Order By In Transact SQL (Ordering As String Instead Of Int)

  - by Pyronaut

  I have an issue where I am trying to order a result set by what I believe to be a numberic column in my database. However when I get the result set, It has sorted the column as if it was a string (So alphabetically), instead of sorting it as an int. As an example. I have these numbers, 1 , 2, 3, 4, 5, 10, 11 When I order by in Transact SQL, I get back : 1, 10, 11, 2, 3, 4, 5 I had the same issue with Datagridview's a while back, And the issue was because of the sorting being done as if it was a string. I assume the same thing is happening here. My full SQL code is : SELECT TOP (12) DATEPART(YEAR, [OrderDate]) AS 'Year', DATEPART(MONTH, [OrderDate]) AS 'Month' , COUNT(OrderRef) AS 'OrderCount' FROM [Order] WHERE [Status] LIKE('PaymentReceived') OR [Status] LIKE ('Shipped') GROUP BY DATEPART(MONTH, [OrderDate]), DATEPART(YEAR, [OrderDate]) ORDER BY DATEPART(YEAR, OrderDate) DESC, DATEPART(MONTH, OrderDate) desc DO NOTE The wrong sorting only happens when I cam calling the function from Visual Studio. As in my code is : using (SqlConnection conn = GetConnection()) { string query = @"SELECT TOP (12) DATEPART(YEAR, [OrderDate]) AS 'Year', DATEPART(MONTH, [OrderDate]) AS 'Month' , COUNT(OrderRef) AS 'OrderCount' FROM [Order] WHERE [Status] LIKE('PaymentReceived') OR [Status] LIKE ('Shipped') GROUP BY DATEPART(MONTH, [OrderDate]), DATEPART(YEAR, [OrderDate]) ORDER BY DATEPART(YEAR, OrderDate) DESC, DATEPART(MONTH, OrderDate) desc"; SqlCommand command = new SqlCommand(query, conn); command.CommandType = CommandType.Text; using (SqlDataReader reader = command.ExecuteReader()) etc. When I run the statement in MSSQL server, there is no issues. I am currently using MSSQL 2005 express edition, And Visual Studio 2005. I have tried numerous things that are strewn across the web. Including using Convert() and ABS() to no avail. Any help would be much appreciated.

  Read the article

• Simple, fast SQL queries for flat files.

  - by plinehan

  Does anyone know of any tools to provide simple, fast queries of flat files using a SQL-like declarative query language? I'd rather not pay the overhead of loading the file into a DB since the input data is typically thrown out almost immediately after the query is run. Consider the data file, "animals.txt": dog 15 cat 20 dog 10 cat 30 dog 5 cat 40 Suppose I want to extract the highest value for each unique animal. I would like to write something like: cat animals.txt | foo "select $1, max(convert($2 using decimal)) group by $1" I can get nearly the same result using sort: cat animals.txt | sort -t " " -k1,1 -k2,2nr And I can always drop into awk from there, but this all feels a bit awkward (couldn't resist) when a SQL-like language would seem to solve the problem so cleanly. I've considered writing a wrapper for SQLite that would automatically create a table based on the input data, and I've looked into using Hive in single-processor mode, but I can't help but feel this problem has been solved before. Am I missing something? Is this functionality already implemented by another standard tool? Halp!

  Read the article

• SQL Server Bulk insert of CSV file with inconsistent quotes

  - by mattstuehler

  Is it possible to BULK INSERT (SQL Server) a CSV file in which the fields are only OCCASSIONALLY surrounded by quotes? Specifically, quotes only surround those fields that contain a ",". In other words, I have data that looks like this (the first row contain headers): id, company, rep, employees 729216,INGRAM MICRO INC.,"Stuart, Becky",523 729235,"GREAT PLAINS ENERGY, INC.","Nelson, Beena",114 721177,GEORGE WESTON BAKERIES INC,"Hogan, Meg",253 Because the quotes aren't consistent, I can't use '","' as a delimiter, and I don't know how to create a format file that accounts for this. I tried using ',' as a delimter and loading it into a temporary table where every column is a varchar, then using some kludgy processing to strip out the quotes, but that doesn't work either, because the fields that contain ',' are split into multiple columns. Unfortunately, I don't have the ability to manipulate the CSV file beforehand. Is this hopeless? Many thanks in advance for any advice. By the way, i saw this post SQL bulk import from csv, but in that case, EVERY field was consistently wrapped in quotes. So, in that case, he could use ',' as a delimiter, then strip out the quotes afterwards.

  Read the article

• SQL Stored Queries - use result of query as boolean based on existence of records

  - by Christian Mann

  Just getting into SQL stored queries right now... anyway, here's my database schema (simplified for YOUR convenience): member ------ id INT PK board ------ id INT PK officer ------ id INT PK If you're into OOP, Officer Inherits Board Inherits Member. In other words, if someone is listed on the officer table, s/he is listed on the board table and the member table. I want to find out the highest privilege level someone has. So far my SP looks like this: DELIMITER // CREATE PROCEDURE GetAuthLevel(IN targetID MEDIUMINT) BEGIN IF SELECT `id` FROM `member` WHERE `id` = targetID; THEN IF SELECT `id` FROM `board` WHERE `id` = targetID; THEN IF SELECT `id` FROM `officer` WHERE `id` = targetID; THEN RETURN 3; /*officer*/ ELSE RETURN 2; /*board member*/ ELSE RETURN 1; /*general member*/ ELSE RETURN 0; /*not a member*/ END // DELIMITER ; The exact text of the error is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM member WHERE id = targetID; THEN IF SEL' at line 4 I suspect the issue is in the arguments for the IF blocks. What I want to do is return true if the result-set is at least one -- i.e. the id was found in the table. Do any of you guys see anything to do here, or should I reconsider my database design into this:? person ------ id INT PK level SMALLINT

  Read the article

• Blob in Java/Hibernate/sql-server 2005

  - by Ramy

  Hi, I'm trying to insert an HTML blob into our sql-server2005 database. i've been using the data-type [text] for the field the blob will eventually live in. i've also put a '@Lob' annotation on the field in the domain model. The problem comes in when the HTML blob I'm attempting to store is larger than 65536 characters. Its seems that is the caracter-limit for a text data type when using the @Lob annotation. Ideally I'd like to keep the whole blob in tact rather than chunk it up into multiple rows in the database. I appreciate any help or insight that might be provided. Thanks! _Ramy Allow me to clarify annotation: @Lob @Column(length = Integer. MAX_VALUE) //per an answer on stackoverflow private String htmlBlob; database side (sql-server-2005): CREATE TABLE dbo.IndustrySectorTearSheetBlob( ... htmlBlob text NULL ... ) Still seeing truncation after 65536 characters... EDIT: i've printed out the contents of all possible strings (only 10 right now) that would be inserted into the Database. Each string seems to contain all cahracters, judging by the fact that the close html tag is present at the end of the string....

  Read the article

• Transitive SQL query on same table

  - by MiKu

  Hey. consider d following table and data... in_timestamp | out_timestamp | name | in_id | out_id | in_server | out_server | status timestamp1 | timestamp2 | data1 |id1 | id2 | others-server1 | my-server1 | success timestamp2 | timestamp3 | data1 | id2 | id3 | my-server1 | my-server2 | success timestamp3 | timestamp4 | data1 | id3 | id4 | my-server2 | my-server3 | success timestamp4 | timestamp5 | data1 | id4 | id5 | my-server3 | others-server2 | success the above data represent log of a execution flow of some data across servers. e.g. some data has flowed from some 'outside-server1' to bunch of 'my-servers' and finally to destined 'others-server2'. Question : 1) I need to give this log in representable form to client where he doesn't need to know anything about the bunch of 'my-servers'. All i am supposed to give is timestamp of the data entered my infrastructure and when it left; drilling down to following info. in_timestamp (of 'others_server1' to 'my-server1') out_timestamp (of 'my-server3' to 'others-server2') name status I want to write sql for the same! Can someone help? NOTE : there might not be 3 'my-servers' all the time. It differs from situation to situation. e.g. there might be 4 'my-server' involved for, say, data2! 2) Are there any other alternatives to SQL? I mean stored procs/etc? 3) Optimizations? (The records are huge in number! As of now, it is around 5 million a day. And we are supposed to show records that are upto a week old.) In advance, THANKS FOR THE HELP! :)

  Read the article

• SQL Server Search Proper Names Full Text Index vs LIKE + SOUNDEX

  - by Matthew Talbert

  I have a database of names of people that has (currently) 35 million rows. I need to know what is the best method for quickly searching these names. The current system (not designed by me), simply has the first and last name columns indexed and uses "LIKE" queries with the additional option of using SOUNDEX (though I'm not sure this is actually used much). Performance has always been a problem with this system, and so currently the searches are limited to 200 results (which still takes too long to run). So, I have a few questions: Does full text index work well for proper names? If so, what is the best way to query proper names? (CONTAINS, FREETEXT, etc) Is there some other system (like Lucene.net) that would be better? Just for reference, I'm using Fluent NHibernate for data access, so methods that work will with that will be preferred. I'm using SQL Server 2008 currently. EDIT I want to add that I'm very interested in solutions that will deal with things like commonly misspelled names, eg 'smythe', 'smith', as well as first names, eg 'tomas', 'thomas'. Query Plan |--Parallelism(Gather Streams) |--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[Test].[Id], [Expr1004]) OPTIMIZED WITH UNORDERED PREFETCH) |--Hash Match(Inner Join, HASH:([testdb].[dbo].[Test].[Id])=([testdb].[dbo].[Test].[Id])) | |--Bitmap(HASH:([testdb].[dbo].[Test].[Id]), DEFINE:([Bitmap1003])) | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([testdb].[dbo].[Test].[Id])) | | |--Index Seek(OBJECT:([testdb].[dbo].[Test].[IX_Test_LastName]), SEEK:([testdb].[dbo].[Test].[LastName] >= 'WHITDþ' AND [testdb].[dbo].[Test].[LastName] < 'WHITF'), WHERE:([testdb].[dbo].[Test].[LastName] like 'WHITE%') ORDERED FORWARD) | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([testdb].[dbo].[Test].[Id])) | |--Index Seek(OBJECT:([testdb].[dbo].[Test].[IX_Test_FirstName]), SEEK:([testdb].[dbo].[Test].[FirstName] >= 'THOMARþ' AND [testdb].[dbo].[Test].[FirstName] < 'THOMAT'), WHERE:([testdb].[dbo].[Test].[FirstName] like 'THOMAS%' AND PROBE([Bitmap1003],[testdb].[dbo].[Test].[Id],N'[IN ROW]')) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([testdb].[dbo].[Test].[PK__TEST__3214EC073B95D2F1]), SEEK:([testdb].[dbo].[Test].[Id]=[testdb].[dbo].[Test].[Id]) LOOKUP ORDERED FORWARD) SQL for above: SELECT * FROM testdb.dbo.Test WHERE LastName LIKE 'WHITE%' AND FirstName LIKE 'THOMAS%' Based on advice from Mitch, I created an index like this: CREATE INDEX IX_Test_Name_DOB ON Test (LastName ASC, FirstName ASC, BirthDate ASC) INCLUDE (and here I list the other columns) My searches are now incredibly fast for my typical search (last, first, and birth date).

  Read the article

• Why do people hate SQL cursors so much?

  - by Steven A. Lowe

  I can understand wanting to avoid having to use a cursor due to the overhead and inconvenience, but it looks like there's some serious cursor-phobia-mania going on where people are going to great lengths to avoid having to use one for example, one question asked how to do something obviously trivial with a cursor and the accepted answer proposed using a common table expression (CTE) recursive query with a recursive custom function, even though this limits the number of rows that could be processed to 32 (due to recursive call limit in sql server). This strikes me as a terrible solution for system longevity, not to mention a tremendous effort just to avoid using a simple cursor. what is the reason for this level of insane hatred? has some 'noted authority' issued a fatwa against cursors? does some unspeakable evil lurk in the heart of cursors that corrupts the morals of the children or something? wiki question, more interested in the answer than the rep thanks in advance! Related Info: http://stackoverflow.com/questions/37029/sql-server-fast-forward-cursors EDIT: let me be more precise: I understand that cursors should not be used instead of normal relational operations, that is a no-brainer. What I don't understand is people going waaaaay out of their way to avoid cursors like they have cooties or something, even when a cursor is a simpler and/or more efficient solution. It's the irrational hatred that baffles me, not the obvious technical efficiencies.

  Read the article

• PHP PDO SQL Server Select statement not replacing question marks

  - by Metropolis

  Awhile ago I wrote a database class which uses PDO in order to connect to SQL Server databases and also to MySQL databases. It has always replaced the question marks fine when using it on the MySQL databases, but for the SQL Server database I had to create a work around which basically replaces the question marks manually. Here is the code for that. if($this->getPDODriver() == 'odbc' && !empty($values_a) && substr_count($query_s, "?") > 0) { $query_s = preg_replace(array_fill(0, substr_count($query_s, "?"), '/\?/'), $values_a, $query_s, 1); $values_a = NULL; } Now, I understand that this completely defeats the purpose of the question marks and PDO, but it has been working fine for me. What I would like to do now though, is find out why the question marks are not getting replaced in the first place, and remove this workaround. If I have a select statement like the following SELECT * FROM database WHERE value = ? That is what the query looks like when I go to prepare it, but when I display the query results, it is a blank array. Just remember, this class is working fine with MySQL, and it is working fine with the work around above. So I know it has something to do with the question marks.

  Read the article

• SQL Express under IIS 7.5

  - by fampinheiro

  I´m developing a web service that access a SQL Express database, it works very well in the Visual Studio host but when i deploy it to IIS 7.5 i get this exception. Please help me. Stack Trace: System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) --- End of inner exception stack trace --- at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at System.Data.Objects.ObjectContext.EnsureConnection() at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source) at WSCinema.CinemaService.Movie() in D:\Documents\My Dropbox\Projects\sd.v0910\trab3\code\WSCinema\CinemaService.asmx.cs:line 46

  Read the article

• SQL GUID Vs Integer

  - by Dal

  Hi I have recently started a new job and noticed that all the SQL tables use the GUID data type for the primary key. In my previous job we used integers (Auto-Increment) for the primary key and it was a lot more easier to work with in my opinion. For example, say you had two related tables; Product and ProductType - I could easily cross check the 'ProductTypeID' column of both tables for a particular row to quickly map the data in my head because its easy to store the number (2,4,45 etc) as opposed to (E75B92A3-3299-4407-A913-C5CA196B3CAB). The extra frustration comes from me wanting to understand how the tables are related, sadly there is no Database diagram :( A lot of people say that GUID's are better because you can define the unique identifer in your C# code for example using NewID() without requiring SQL SERVER to do it - this also allows you to know provisionally what the ID will be.... but I've seen that it is possible to still retrieve the 'next auto-incremented integer' too. A DBA contractor reported that our queries could be up to 30% faster if we used the Integer type instead of GUIDS... Why does the GUID data type exist, what advantages does it really provide?... Even if its a choice by some professional there must be some good reasons as to why its implemented?

  Read the article

• Sql Server Maintenance Plan Tasks & Completion

  - by Ben

  Hi All, I have a maintenance plan that looks like this... Client 1 Import Data (Success) -> Process Data (Success) -> Post Process (Completion) -> Next Client Client 2 Import Data (Success) -> Process Data (Success) -> Post Process (Completion) -> Next Client Client N ... Import Data and Process Data are calling jobs and Post Process is an Execute Sql task. If Import Data or Process Data Fail, it goes to the next client Import Data... Both Import Data and Process Data are jobs that contain SSIS packages that are using the built-in SQL logging provider. My expectation with the configuration as it stands is: Client 1 Import Data Runs: Failure - Client 2 Import Data | Success Process Data Process Data Runs: Failure - Client 2 Import Data | Success Post Process Post Process Runs: Completion - Success or Failure - Next Client Import Data This isn't what I'm seeing in my logs though... I see several Client Import Data SSIS log entries, then several Post Process log entries, then back to Client Import Data! Arg!! What am I doing wrong? I didn't think the "success" piece of Client 1 Import Data would kick off until it... well... succeeded aka finished! The logs seem to indicate otherwise though... I really need these tasks to be consecutive not concurrent. Is this possible? Thanks!

  Read the article

• Images in database vs file system

  - by Jesse

  We have a project coming up where we will be building a whole backend CMS system that will power our entire extranet and intranet with one package. The question I have been trying to find an answer to is which is better: storing images in the database (SQL Server 2005) so we may have integrity, single replication plan, etc OR storing on the file system? One issue we have is that we have multiple servers load balanced that require to have the same data at all times. As of now we have SQL replication taking care of that but file replication seems to be a little tougher. Another concern we have is that we would like to have multiple resolutions of the same image, we are not sure if creating and storing each version on the file system would be best or maybe dynamically pulling and creating the resolution image we would like upon request. Our concerns are the with the following: Data integrity Data replication Multiple resolutions Speed of database vs file system Overhead load of database vs file system Data management and backup Does anyone have a similar situation or have any input on what would be recommended? Thanks in advance for the help!

  Read the article

< Previous Page | 233 234 235 236 237 238 239 240 241 242 243 244  | Next Page >