Search Results

Search found 36949 results on 1478 pages for 'sql handle dbc'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 319/1478 | < Previous Page | 315 316 317 318 319 320 321 322 323 324 325 326  | Next Page >

  • Adjust SQL query to DB2

    - by Guy Roth
    Part of a complex query that our app is running contains the lines: ...(inner query) SELECT ... NULL as column_A, NULL as column_B, ... FROM ... This syntax of creating columns with null values is not allowed in DB2 altough it is totally OK in MSSQL and Oracle DBs. Technically I can change it to: '' as column_A, '' as column_B, But this doesn't have exactly the same meaning and can damage our calculation results. How can I create columns with null values in DB2 using other syntax??

    Read the article

  • Alter View not recognizing column

    - by Chris
    I have scripts for adding columns to tables which worked. When I run scripts to alter views with the new column the script fails because the columns are not recognized Msg 207, Level 16, State 1, Procedure UniqueTempDispositions, Line 76 Invalid column name 'servicerequestid'. Msg 207, Level 16, State 1, Procedure UniqueTempDispositions, Line 47 Invalid column name 'servicerequestid'. Msg 207, Level 16, State 1, Procedure MergeDispositions, Line 54 Invalid column name 'ServiceRequestID'. Msg 207, Level 16, State 1, Procedure NonPIICachedDispositions, Line 18 Invalid column name 'ServiceRequestID'. Any reason why? Am I missing something? I've started and stopped the server, I've relogged in to no avail.

    Read the article

  • Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

    - by ctrlShiftBryan
    Using MSSQL2005, Can I truncate a table with a foreign key constraint if I first truncate the child table(the table with the primary key of the FK relationship)? I know I can use a DELETE without a where clause and then RESEED the identity OR Remove the FK, truncate and recreate but I thought as long as you truncate the child table you'll be OK however I'm getting a "Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint." error.

    Read the article

  • TSQL Shred XML - Is this right or is there a better way (newbie @ shredding XML)

    - by drachenstern
    Ok, I'm a C# ASP.NET dev following orders: The orders are to take a given dataset, shred the XML and return columns. I've argued that it's easier to do the shredding on the ASP.NET side where we already have access to things like deserializers, etc, and the entire complex of known types, but no, the boss says "shred it on the server, return a dataset, bind the dataset to the columns of the gridview" so for now, I'm doing what I was told. This is all to head off the folks who will come along and say "bad requirements". Task at hand: Here's my code that works and does what I want it to: DECLARE @table1 AS TABLE ( ProductID VARCHAR(10) , Name VARCHAR(20) , Color VARCHAR(20) , UserEntered VARCHAR(20) , XmlField XML ) INSERT INTO @table1 SELECT '12345','ball','red','john','<sizes><size name="medium"><price>10</price></size><size name="large"><price>20</price></size></sizes>' INSERT INTO @table1 SELECT '12346','ball','blue','adam','<sizes><size name="medium"><price>12</price></size><size name="large"><price>25</price></size></sizes>' INSERT INTO @table1 SELECT '12347','ring','red','john','<sizes><size name="medium"><price>5</price></size><size name="large"><price>8</price></size></sizes>' INSERT INTO @table1 SELECT '12348','ring','blue','adam','<sizes><size name="medium"><price>8</price></size><size name="large"><price>10</price></size></sizes>' INSERT INTO @table1 SELECT '23456','auto','black','ann','<auto><type>car</type><wheels>4</wheels><doors>4</doors><cylinders>3</cylinders></auto>' INSERT INTO @table1 SELECT '23457','auto','black','ann','<auto><type>truck</type><wheels>4</wheels><doors>2</doors><cylinders>8</cylinders></auto><auto><type>car</type><wheels>4</wheels><doors>4</doors><cylinders>6</cylinders></auto>' DECLARE @x XML SELECT @x = ( SELECT ProductID , Name , Color , UserEntered , XmlField.query(' for $vehicle in //auto return <auto type = "{$vehicle/type}" wheels = "{$vehicle/wheels}" doors = "{$vehicle/doors}" cylinders = "{$vehicle/cylinders}" />') FROM @table1 table1 WHERE Name = 'auto' FOR XML AUTO ) SELECT @x SELECT ProductID = T.Item.value('../@ProductID', 'varchar(10)') , Name = T.Item.value('../@Name', 'varchar(20)') , Color = T.Item.value('../@Color', 'varchar(20)') , UserEntered = T.Item.value('../@UserEntered', 'varchar(20)') , VType = T.Item.value('@type' , 'varchar(10)') , Wheels = T.Item.value('@wheels', 'varchar(2)') , Doors = T.Item.value('@doors', 'varchar(2)') , Cylinders = T.Item.value('@cylinders', 'varchar(2)') FROM @x.nodes('//table1/auto') AS T(Item) SELECT @x = ( SELECT ProductID , Name , Color , UserEntered , XmlField.query(' for $object in //sizes/size return <size name = "{$object/@name}" price = "{$object/price}" />') FROM @table1 table1 WHERE Name IN ('ring', 'ball') FOR XML AUTO ) SELECT @x SELECT ProductID = T.Item.value('../@ProductID', 'varchar(10)') , Name = T.Item.value('../@Name', 'varchar(20)') , Color = T.Item.value('../@Color', 'varchar(20)') , UserEntered = T.Item.value('../@UserEntered', 'varchar(20)') , SubName = T.Item.value('@name' , 'varchar(10)') , Price = T.Item.value('@price', 'varchar(2)') FROM @x.nodes('//table1/size') AS T(Item) So for now, I'm trying to figure out if there's a better way to write the code than what I'm doing now... (I have a part 2 I'm about to go key in)

    Read the article

  • pl/sql object types "ORA-06530: Reference to uninitialized composite" error

    - by mutoss
    hi, i have a type as follows: CREATE OR REPLACE TYPE tbusiness_inter_item_bag AS OBJECT ( item_id NUMBER, system_event_cd VARCHAR2 (20), CONSTRUCTOR FUNCTION tbusiness_inter_item_bag RETURN SELF AS RESULT ); CREATE OR REPLACE TYPE BODY tbusiness_inter_item_bag AS CONSTRUCTOR FUNCTION tbusiness_inter_item_bag RETURN SELF AS RESULT AS BEGIN RETURN; END; END; when i execute the following script, i got a "Reference to uninitialized composite" error, which is imho quite suitable. DECLARE item tbusiness_inter_item_bag; BEGIN item.system_event_cd := 'ABC'; END; This also raises the same error: item.item_id := 3; But if i change my object type into: CREATE OR REPLACE TYPE tbusiness_inter_item_bag AS OBJECT ( item_id NUMBER(1), system_event_cd VARCHAR2 (20), CONSTRUCTOR FUNCTION tbusiness_inter_item_bag RETURN SELF AS RESULT ); then the last statement raises no more error (where my "item" is still uninitialized): item.item_id := 3; Shouldn't i get the same ORA-06530 error? ps: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

    Read the article

  • SQL Join Problem

    - by Harley
    Table one contains ID|Name 1 Mary 2 John Table two contains ID|Color 1 Red 1 Blue 2 Green 2 Blue 2 Black I want to end up with is ID|Name|Red|Blue|Green|Black 1 Mary    Y      Y 2 John            Y        Y       Y Thanks for and help.

    Read the article

  • How to refactor this database to allow sync. with smart clients?

    - by Anton Zvgny
    Howdy, I have inherited the following database: http://i46.tinypic.com/einvjr.png (EDIT: I cannot post images, so here goes the link) This currently runs 100% online thru an Asp.Net web app , but some employees will need to have offline access to this database to either get documents or to insert new documents for later synchronization when back to the office. What changes need to be made to this database scheme to support such scenario (sync smart clients with central web database)? Thanks! ps. I'm not an developer/dba, i'm just an mechanic engineer tasked with changing this app, so, take it easy on me :D ps1. We're using MSSQL Server for the online central database and MSSQL Compact for the smart client. ps2. the ImageGuid field of the images table is used to save the images to the file system. The web app takes the guid and create folders according to the first 3 initial letters to persist the image to the file system. ps3. The users of the smart client not always get server data first and then go modifying to sync later the changes. Most of the time they start working completely offline (with a blank local database) and later sync the data to the server. ps4. All the users of the smart client app have an account at the web app.

    Read the article

  • SQL ConnectionString in global.asax overridden by web.config

    - by rlb.usa
    This is going to sound very odd, but I have a web.config like this: <connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=BACKUPDB;..." providerName="System.Data.SqlClient"/> </connectionStrings> And a global.asax like this: void Session_Start(object sender, EventArgs e) { // Code that runs when a new session is started if (Application["con"] == null || Application["con"] == "") { Application["con"] = "Data Source=PRODUCTIONDB;..."; } } And EVERYWHERE in my code, I reference my ConnectionStrings like this: SqlConnection con = new SqlConnection(Convert.ToString(HttpContext.Current.Application["con"])); However, I see that everything I do inside this application goes to BACKUP db instead of PRODUCTIONDB. What is going on, how could this happen, and why? It doesn't make any sense to me, and it got me into a lot of trouble. We use LocalSqlServer string for FormsAuthentication.

    Read the article

  • .NET and Closing SQL Server connections

    - by user307076
    I am having a hard time figuring out why the following constructor will not close connnections. When I view the active connections. Here is the code that I have. Public Sub New(ByVal UserID As Integer) Dim oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ToString()) Dim cmd As New SqlCommand("stored proc", oConn) Dim sdr As SqlDataReader cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@userID", UserID) oConn.Open() sdr = cmd.ExecuteReader() Try If Not sdr.HasRows Then sdr.Close() If Not oConn Is Nothing Then If oConn.State <> ConnectionState.Closed Then oConn.Close() End If End If cmd.Dispose() Exit Sub End If 'User has account in WATS, proceed to load account information While sdr.Read _firstname = Convert.ToString(sdr("First Name")) _lastname = Convert.ToString(sdr("Last Name")) End While Catch ex As Exception 'Throw New Exception("User Error: " + ex.Message) Finally sdr.Close() If Not oConn Is Nothing Then If oConn.State <> ConnectionState.Closed Then oConn.Close() End If End If cmd.Dispose() End Try End Sub

    Read the article

  • Access COM object through a windows process handle.

    - by Sivvy
    I'm currently automating an application at work using COM, and have an issue where anyone using my application has a problem if the original application is already open when my application runs. I know how to locate the process if it's open, but instead of having to worry about closing it, or working around it, etc., I want to try to use the existing application instead of opening a new one. This is how I normally start the application in my automation program: Designer.Application desApp = new Designer.Application(); Now I'm attempting to try and use the handle from an existing application: Designer.Application desApp = (Designer.Application)((System.Diagnostics.Process.GetProcessesByName("Designer.exe")[0]).Handle) (I know this doesn't work, since .Handle returns an IntPtr, but I'm using it as an example.) Is there any way to accomplish this? How do I return a usable object if I know the handle/process?

    Read the article

  • How do I average the difference between specific values in TSQL?

    - by jvenema
    Hey folks, sorry this is a bit of a longer question... I have a table with the following columns: [ChatID] [User] [LogID] [CreatedOn] [Text] What I need to find is the average response time for a given user id, to another specific user id. So, if my data looks like: [1] [john] [20] [1/1/11 3:00:00] [Hello] [1] [john] [21] [1/1/11 3:00:23] [Anyone there?] [1] [susan] [22] [1/1/11 3:00:43] [Hello!] [1] [susan] [23] [1/1/11 3:00:53] [What's up?] [1] [john] [24] [1/1/11 3:01:02] [Not much] [1] [susan] [25] [1/1/11 3:01:08] [Cool] ...then I need to see that Susan has an average response time of (20 + 6) / 2 = 13 seconds to John, and John has an average of (9 / 1) = 9 seconds to Susan. I'm not even sure this can be done in set-based logic, but if anyone has any ideas, they'd be much appreciated!

    Read the article

  • Linq sql Attach, Update Check set to Never, but still Concurrency conflicts

    - by remdao
    In the dbml designer I've set Update Check to Never on all properties. But i still get an exception when doing Attach: "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported." This approach seems to have worked for others on here, but there must be something I've missed. using(TheDataContext dc = new TheDataContext()) { test = dc.Members.FirstOrDefault(m => m.fltId == 1); } test.Name = "test2"; using(TheDataContext dc = new TheDataContext()) { dc.Members.Attach(test, true); dc.SubmitChanges(); }

    Read the article

  • Select dynamic string has a different value when referenced in Where clause

    - by David
    I dynamically select a string built using another string. So, if string1='David Banner', then MyDynamicString should be 'DBanne' Select ... , Left( left((select top 1 strval from dbo.SPLIT(string1,' ')) //first word ,1) //first character + (select top 1 strval from dbo.SPLIT(string1,' ') //second word where strval not in (select top 1 strval from dbo.SPLIT(string1,' '))) ,6) //1st character of 1st word, followed by up to 5 characters of second word [MyDynamicString] ,... From table1 Join table2 on table1pkey=table2fkey Where MyDynamicString <> table2.someotherfield I know table2.someotherfield is not equal to the dynamic string. However, when I replace MyDynamicString in the Where clause with the full left(left(etc.. function, it works as expected. Can I not reference this string later in the query? Do I have to build it using the left(left(etc.. function each time in the where clause?

    Read the article

  • SQL housekeeping tasks on Herkoku

    - by Chris Beeson
    Hi, I'm not sure how to do this... I have a database which contains a messages and categories tables. The categories table has a field which has a count of the number of messages related to it. Sometimes however I need to deactivate (active = 0) a message, at the moment this doesn't then update the category table... I will implement this is in the end but for the time I would just like to run a script perhaps daily that goes through all the categories, counts up the messages and updates the field. What the best way of doing this? Thanks in advance Chris

    Read the article

  • sql server optional parameters: syntax for between clause

    - by Aseem Gautam
    @FromDate datetime = null @ToDate datetime = null SELECT * FROM TABLE WHERE .... AND [PI].Date BETWEEN @FromDate AND @ToDate When any date is null, the records are not displayed. What is the correct syntax so that I can get all records if any of the dates are null. I have thought of this: @FromDate datetime = '01/01/1901', @ToDate datetime = '12/31/9999' Thanks.

    Read the article

  • Repair bad character due to encoding problem

    - by remi bourgarel
    Hi all, Recently we had an encoding problem in our system : If we had the string "æ" in our db ,it became "æ" on our web pages. Now this problem is solved, but the problem is that now we have a lot of "æ" in our database : users didn't see and validate pre-filled form with these characters. I found that If you read in utf 8 C3A6 you'll get "æ", if you read it in ascii you'll get "æ". It's strange because if I execute "select convert(varbinary(40),N'æ'),convert(varbinary(40),'æ')" I don't have the same result... Do you have any idea on how I can fix my database (ie change all "æ" to "æ") ? thx

    Read the article

  • DB2 SQL count in join or where clause

    - by Pat
    This is probably an amateur question but I'm an amateur ! ;o) I have several tables: Account table, Officer table, Location table, Web table... The Officer table doesn't give me the number of officers per account. I need to retrieve only the accounts that have more than 20 officers. Should I use the COUNT ? If so, how ? Your help is greatly appreciated. Thanks. Pat

    Read the article

  • sql-server: how to insert to temporary table?

    - by RedsDevils
    I have one Temporary Table CREATE TABLE #TEMP (TEMP_ID INT IDENTITY(1,1)) And I would like to insert records to that table, How can I?I do as follow: INSERT INTO #TEMP DEFAULT VALUES But sometimes it doesn't work. What it might be?And I would like to know lifetime of temptable in MSSQL. Please Help me! Thanks all!

    Read the article

  • Linq to SQL ordered child collection

    - by Matt Connolly
    Is there a way to define a default order column from a child collection? In my case, I have a Form entity that has a collection of FormItem entities called FormItems. FormItem has a property called DisplayOrder (int). I want to make sure that any Form entities I return from a method have that collection properly ordered. Is there a way to do this before returning the result? For example, I tried this but the list is not actually sorted: var form = context.Forms.FirstOrDefault(x => x.IsDeleted == false && x.FormName == formName); if (form != null) { form.FormItems.OrderBy(x => x.DisplayOrder); // I can't even figure out a way to cast this next line so that it will compile // form.FormItems = form.FormItems.OrderBy(x => x.DisplayOrder); } return form; Is there a way to do this without using DataLoadOptions?

    Read the article

  • Finding duplicate values in a SQL table - ADVANCED

    - by Alex
    It's easy to find duplicates with one field SELECT name, COUNT(email) FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) So if we have a table ID NAME EMAIL 1 John [email protected] 2 Sam [email protected] 3 Tom [email protected] 4 Bob [email protected] 5 Tom [email protected] This query will give us John, Sam, Tom, Tom because they all have the same e-mails. But what I want, is to get duplicates with the same e-mails and names. I want to get Tom, Tom. I made a mistake, and allowed to insert duplicate name and e-mail values. Now I need to remove/change the duplicates. But I need to find them first.

    Read the article

  • Linq to SQL generates StackOverflowException in tight Insert loop

    - by ChrisW
    I'm parsing an XML file and inserting the rows into a table (and related tables) using LinqToSQL. I parse the XML file using LinqToXml into IEnumerable. Then, I create a foreach loop, where I build my LinqToSQL objects and call InsertOnSubmit and SubmitChanges at the end of each loop. Nothing special here. Usually, I make it through around 4,100 records before receiving a StackOverflowException from LinqToSql, right as I call SubmitChanges. It's not always on 4,100... sometimes it's 4102, sometimes, less, etc. I've tried inserting the records that generate the failure individually, but putting them in their own Xml file, but that inserts fine... so it's not the data. I'm running the whole process from an MVC2 app that is uploading the Xml file to the server. I've adjusted my WebRequest timeouts to appropriate values, and again, I'm not getting timeout errors, just StackOverflowExceptions. So is there some pattern that I should follow for times when I have to do many insertions into the database? I never encounter this exception on smaller Xml files, just larger ones.

    Read the article

  • Use a certain select statement in a stored procedure depending on the Exec statement

    - by MyHeadHurts
    Alright so i am not even sure if this is possible I have a q_00 and q_01 and q_02 which are all in my stored procedure. then on the bottom i have 3 select statements that select a certain catagory for example Sales,Net Sales and INS sales What i want to be able to do is if the user types exec (name of my sp) (sales) (and a year which is the @yearparameter) it will run the sales select statement If they type Exec (name of my SP) netsales (@Yeartoget) it will show the net sales is this possible or do i need multiple stored procedures ALTER PROCEDURE [dbo].[casof] @YearToGet int as ; with q_00 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as asofSales , sum(PARTY) as asofPAX , sum(NetAmount) as asofNetSales , sum(InsAmount) as asofInsSales , sum(CancelRevenue) as asofCXSales , sum(OtherAmount) as asofOtherSales , sum(CXVALUE) as asofCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = @YearToGet group by DIVISION, SDESCR, DYYYY ), q_01 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as YESales , sum(PARTY) as YEPAX , sum(NetAmount) as YENetSales , sum(InsAmount) as YEInsSales , sum(CancelRevenue) as YECXSales , sum(OtherAmount) as YEOtherSales , sum(CXVALUE) as YECXValue from dbo.B101BookingsDetails where DYYYY=@YearToGet group by DIVISION, SDESCR, DYYYY ), q_02 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as CurrentSales , sum(PARTY) as CurrentPAX , sum(NetAmount) as CurrentNetSales , sum(InsAmount) as CurrentInsSales , sum(CancelRevenue) as CurrentCXSales , sum(OtherAmount) as CurrentOtherSales , sum(CXVALUE) as CurrentCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = (year( getdate() )) group by DIVISION, SDESCR, DYYYY ) select a.DIVISION , a.SDESCR , a.DYYYY , asofSales , asofPAX , YESales , YEPAX , CurrentSales , CurrentPAX , asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales , asofpax/yepax as percentpax ,currentsales/ISNULL(NULLIF((asofsales/ISNULL(NULLIF(yesales,0),1)),0),1) as projectedsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofNetSales , YEPAX , YENetSales , CurrentPAX , CurrentNetSales , asofnetsales/ ISNULL(NULLIF(yenetsales,0),1) as percentnetsales , asofpax/yepax as percentpax ,currentnetsales/ISNULL(NULLIF((asofnetsales/ISNULL(NULLIF(yenetsales,0),1)),0),1) as projectednetsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofInsSales , YEPAX , YEInsSales , CurrentPAX , CurrentInsSales , asofinssales/ ISNULL(NULLIF(yeinssales,0),1) as percentsales , asofpax/yepax as percentpax ,currentinssales/ISNULL(NULLIF((asofinssales/ISNULL(NULLIF(yeinssales,0),1)),0),1) as projectedinssales from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ;

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 315 316 317 318 319 320 321 322 323 324 325 326  | Next Page >