Search Results

Search found 84007 results on 3361 pages for 'sql system table'.

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 57/3361 | < Previous Page | 53 54 55 56 57 58 59 60 61 62 63 64  | Next Page >

  • SQL Server Transactions how can I commit my transaction

    - by codingguy3000
    I have SQL Server 2005 stored procedure. Someone one is calling my stored procedure within a transaction. In my stored proc I'm logging some information (insert into a table). When the higher level transaction rolls back it removes my insert. Is there anyway I can commit my insert and prevent the higher level rollback from removing my insert? Thanks

    Read the article

  • SQL Server: How to iterate over function arguments

    - by atricapilla
    I'm trying to learn SQL and for demonstration purposes I would like to create a loop which iterates over function arguments. E.g. I would like to iterate over SERVERPROPERTY function arguments (propertynames). I can do single select like this: SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition; GO but how to iterate over all these propertynames? Thanks in advance.

    Read the article

  • SQL Server: "Mostly-unique" index

    - by Ian Boyd
    In a table i want to ensure that only unique vales exist over the five-column key: Timestamp Account RatingDate TripHistoryKey EventAction ========= ======= ========== ============== =========== 2010511 1234 2010511 1 INSERT 2010511 1234 2010511 4 INSERT 2010511 1234 2010511 7 INSERT 2010511 1234 2010511 1 INSERT <---duplicate But i only want the unique constraint to apply between rows when EventAction is INSERT: Timestamp Account RatingDate TripHistoryKey EventAction ========= ======= ========== ============== =========== 2010511 1234 2010511 1 INSERT 2010511 1234 2010511 1 UPDATE 2010511 1234 2010511 1 UPDATE <---not duplicate 2010511 1234 2010511 1 UPDATE <---not duplicate 2010511 1234 2010511 1 DELETE <---not duplicate 2010511 1234 2010511 1 DELETE <---not duplicate 2010511 1234 2010511 1 INSERT <---DUPLICATE Possible?

    Read the article

  • Passing multiple parameters of same column to SQL Server select SP

    - by Bill
    I have a string value in the web.config — for example 2 guids seperated by a ",". I need to query the database dynamically (i.e i have no idea how many values could be seperated by a comma in the web.config) and run a select statement on the table passing these values and getting all that is relevant for example: select * from tablename where columnname = string1 string2 string3 etc etc some strings may only contain 1 guid some may contain 10

    Read the article

  • How do you unit test your T-SQL

    - by AlexKuznetsov
    How do you unit test your T-SQL? Which libraries/tools do you use? What percentage of your code is covered by unit tests and how do you measure it? Do you think the time and effort which you invested in your unit testing harness has paid off or not? If you do not use unit testing, can you explain why not?

    Read the article

  • Ordering sql query results

    - by user343409
    My sql query gives the columns: product_id (which is an integer) pnl (which is float - can be negative) I get more than 100 rows. I want to filter out the top 40 rows based on abs(pnl). But the results should be ordered by pnl column only and not by abs(pnl). I want to do this for MSSQL 2005. Is there a way to do this?

    Read the article

  • help with t-sql data aggregation

    - by stackoverflowuser
    Based on the following table Area S1 S2 S3 S4 -------------------- A1 5 10 20 0 A2 11 19 15 20 A3 0 0 0 20 I want to generate an output that will give the number of columns not having "0". So the output would be Area S1 S2 S3 S4 Count ------------------------- A1 5 10 20 0 3 A2 11 19 15 20 4 A3 0 0 0 20 1

    Read the article

  • Oracle Open World 2012: SQL Developer Recap

    - by thatjeffsmith
    Last week was the ‘big show’ in San Francisco. I was very happy to meet many of you in person. And many of you had questions – lots of questions! We had full or overflowing rooms for our sessions and hands-on-labs. The SQL Developer ‘booths’ were also slammed several times. So exciting to see so many of YOU excited about SQL Developer. It’s very cool to hear the stories of our tools saving you and your organizations so much time (and money!) Instead of doing a Day 0 – Day 9 recap, I thought I’d share with you the questions that I heard more than once. And just for giggles, I’ll throw in some answers as well So in no particular order… What’s the difference between Oracle SQL Developer & Oracle SQL Developer Data Modeler? Mathematically speaking – two words. But as far as the actual modeling features go, there’s no difference between the two applications. The same ‘code’ or features as it pertains to data modeling and design are in both tools. However, in SQL Developer you have all of the OTHER features fighting for real estate in the UI. So I have a general rule of thumb – if you spend MOST of your time in the database, use SQL Developer. And if you spend most of your time in the data model, run the separate and dedicated program, Oracle SQL Developer Data Modeler. Here’s a couple of screenshots to drive home the UI point: Oracle SQL Developer Oracle SQL Developer Data Modeler running INSIDE of SQL Developer. Notice how the Modeler menu items fold under the file menu? Oracle SQL Developer Data Modeler Easier to navigate and manipulate your models with the stand alone modeler. Just no worksheet to run your ad-hoc queries, etc. Don’t forget you can disable the Data Modeler inside of SQL Developer via the Extensions preference page. How can I model my table partitions? Partitioning is defined via the Physical model. So after you have finished your relational model, you need to generate a physical model. Oracle SQL Developer Data Modeler Physical Model and Partitioning Open the properties for your physical model table. Enable the ‘partitioned’ property. Once you do so, the ‘Partitioning’ page will activate. Lots and lots of partitioning support and options here But what about Interval Partitioning? An extension of range partitioning in 11gR2, we don’t currently support this partitioning scheme in SQL Developer. But we’re working on it! Can SQL Developer ignore column order when comparing models? Yes! After you start a model compare, one of your options is to disregard the order of an attribute or column definition. Tell SQL Developer you don’t care when your column shows up, just as long as it DOES show up. Wow, you got a lot of questions around modeling! Is that normal? Yes! While we appreciate that many folks inherit their applications and associated designs, new applications are being ‘born’ every day. Since both of our tools are free for anyone to design their new Oracle applications with, we attract a fair amount of attention I want to do a Hands On Lab. How do I get your software and instructional guides? Go here. Download VirtualBox. Then download the VB image. Import the appliance. Start it. Connect oracle/oracle on the OEL VM. Click on ‘Start Here’ in the desktop. Follow the instructions. If you need help, ask away! You went too fast in your Tips & Tricks session. Do you have cliff notes? Yes! And you’re SO close to finding them! Just go to my SQL Developer resources page. All of my tips are documented on this blog somewhere. I’ve indexed the most popular ones on the resource page. You can use the Search dialog on the right to find the rest. Or just send me a comment or question, and I’ll do my best to answer them as they come in.

    Read the article

  • SQL server deadlock between INSERT and SELECT statement

    - by dtroy
    Hi! I've got a problem with multiple deadlocks on SQL server 2005. This one is between an INSERT and a SELECT statement. There are two tables. Table 1 and Table2. Table2 has Table1's PK (table1_id) as foreign key. Index on table1_id is clustered. The INSERT inserts a single row into table2 at a time. The SELCET joins the 2 tables. (it's a long query which might take up to 12 secs to run) According to my understanding (and experiments) the INSERT should acquire an IS lock on table1 to check referential integrity (which should not cause a deadlock). But, in this case it acquired an IX page lock The deadlock report: <deadlock-list> <deadlock victim="process968898"> <process-list> <process id="process8db1f8" taskpriority="0" logused="2424" waitresource="OBJECT: 5:789577851:0 " waittime="12390" ownerId="61831512" transactionname="user_transaction" lasttranstarted="2010-04-16T07:10:13.347" XDES="0x222a8250" lockMode="IX" schedulerid="1" kpid="3764" status="suspended" spid="52" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:13.350" lastbatchcompleted="2010-04-16T07:10:13.347" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read uncommitted (1)" xactid="61831512" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="DatabaseName.dbo.prcTable2_Insert" line="18" stmtstart="576" stmtend="1148" sqlhandle="0x0300050079e62d06e9307f000b9d00000100000000000000"> INSERT INTO dbo.Table2 ( f1, table1_id, f2 ) VALUES ( @p1, @p_DocumentVersionID, @p1 ) </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 103671417] </inputbuf> </process> <process id="process968898" taskpriority="0" logused="0" waitresource="PAGE: 5:1:46510" waittime="7625" ownerId="61831406" transactionname="INSERT" lasttranstarted="2010-04-16T07:10:12.717" XDES="0x418ec00" lockMode="S" schedulerid="2" kpid="1724" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:12.713" lastbatchcompleted="2010-04-16T07:10:12.713" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read committed (2)" xactid="61831406" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="DatabaseName.dbo.prcGetList" line="64" stmtstart="3548" stmtend="11570" sqlhandle="0x03000500dbcec17e8d267f000b9d00000100000000000000"> <!-- XXXXXXXXXXXXXX...SELECT STATEMENT WITH Multiple joins including both Table2 table 1 and .... XXXXXXXXXXXXXXX --> </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 2126630619] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="46510" dbid="5" objectname="DatabaseName.dbo.table1" id="lock6236bc0" mode="IX" associatedObjectId="72057594042908672"> <owner-list> <owner id="process8db1f8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process968898" mode="S" requestType="wait"/> </waiter-list> </pagelock> <objectlock lockPartition="0" objid="789577851" subresource="FULL" dbid="5" objectname="DatabaseName.dbo.Table2" id="lock970a240" mode="S" associatedObjectId="789577851"> <owner-list> <owner id="process968898" mode="S"/> </owner-list> <waiter-list> <waiter id="process8db1f8" mode="IX" requestType="wait"/> </waiter-list> </objectlock> </resource-list> </deadlock> </deadlock-list> Can anyone explain why the INSERT gets the IX page lock ? Am I not reading the deadlock report properly? BTW, I have not managed to reproduce this issue. Thanks!

    Read the article

  • SQL Server 2008: Using Multiple dts Ranges to Build a Set of Dates

    - by raoulcousins
    I'm trying to build a query for a medical database that counts the number of patients that were on at least one medication from a class of medications (the medications listed below in the FAST_MEDS CTE) and had either: 1) A diagnosis of myopathy (the list of diagnoses in the FAST_DX CTE) 2) A CPK lab value above 1000 (the lab value in the FAST_LABS CTE) and this diagnosis or lab happened AFTER a patient was on a statin. The query I've included below does that under the assumption that once a patient is on a statin, they're on a statin forever. The first CTE collects the ids of patients that were on a statin along with the first date of their diagnosis, the second those with a diagnosis, and the third those with a high lab value. After this I count those that match the above criteria. What I would like to do is drop the assumption that once a patient is on a statin, they're on it for life. The table edw_dm.patient_medications has a column called start_dts and end_dts. This table has one row for each prescription written, with start_dts and end_dts denoting the start and end date of the prescription. End_dts could be null, which I'll take to assume that the patient is currently on this medication (it could be a missing record, but I can't do anything about this). If a patient is on two different statins, the start and ends dates can overlap, and there may be multiple records of the same medication for a patient, as in a record showing 3-11-2000 to 4-5-2003 and another for the same patient showing 5-6-2007 to 7-8-2009. I would like to use these two columns to build a query where I'm only counting the patients that had a lab value or diagnosis done during a time when they were already on a statin, or in the first n (say 3) months after they stopped taking a statin. I'm really not sure how to go about rewriting the first CTE to get this information and how to do the comparison after the CTEs are built. I know this is a vague question, but I'm really stumped. Any ideas? As always, thank you in advance. Here's the current query: WITH FAST_MEDS AS ( select distinct statins.mrd_pt_id, min(year(statins.order_dts)) as statin_yr from edw_dm.patient_medications as statins inner join mrd.medications as mrd on statins.mrd_med_id = mrd.mrd_med_id WHERE mrd.generic_nm in ( 'Lovastatin (9664708500)', 'lovastatin-niacin', 'Lovastatin/Niacin', 'Lovastatin', 'Simvastatin (9678583966)', 'ezetimibe-simvastatin', 'niacin-simvastatin', 'ezetimibe/Simvastatin', 'Niacin/Simvastatin', 'Simvastatin', 'Aspirin Buffered-Pravastatin', 'aspirin-pravastatin', 'Aspirin/Pravastatin', 'Pravastatin', 'amlodipine-atorvastatin', 'Amlodipine/atorvastatin', 'atorvastatin', 'fluvastatin', 'rosuvastatin' ) and YEAR(statins.order_dts) IS NOT NULL and statins.mrd_pt_id IS NOT NULL group by statins.mrd_pt_id ) select * into #meds from FAST_MEDS ; --return patients who had a diagnosis in the list and the year that --diagnosis was given with FAST_DX AS ( SELECT pd.mrd_pt_id, YEAR(pd.init_noted_dts) as init_yr FROM edw_dm.patient_diagnoses as pd inner join mrd.diagnoses as mrd on pd.mrd_dx_id = mrd.mrd_dx_id and mrd.icd9_cd in ('728.89','729.1','710.4','728.3','729.0','728.81','781.0','791.3') ) select * into #dx from FAST_DX; --return patients who had a high cpk value along with the year the cpk --value was taken with FAST_LABS AS ( SELECT pl.mrd_pt_id, YEAR(pl.order_dts) as lab_yr FROM edw_dm.patient_labs as pl inner join mrd.labs as mrd on pl.mrd_lab_id = mrd.mrd_lab_id and mrd.lab_nm = 'CK (CPK)' WHERE pl.lab_val between 1000 AND 999998 ) select * into #labs from FAST_LABS; -- count the number of patients who had a lab value or a medication -- value taken sometime AFTER their initial statin diagnosis select count(distinct p.mrd_pt_id) as ct from mrd.patient_demographics as p join #meds as m on p.mrd_pt_id = m.mrd_pt_id AND ( EXISTS ( SELECT 'A' FROM #labs l WHERE p.mrd_pt_id = l.mrd_pt_id and l.lab_yr >= m.statin_yr ) OR EXISTS( SELECT 'A' FROM #dx d WHERE p.mrd_pt_id = d.mrd_pt_id AND d.init_yr >= m.statin_yr ) )

    Read the article

  • SQL Server 2008 : Standard or SQL Express

    - by dr
    Which is a better choice on a development box if you primarily develop Asp.Net applications and SSRS reports. I have never had to use the Express editions, so I don't really know the pros or cons. The cons I have listed for Standard+ editions are: toll it takes on system resources pain to attach database for projects pain to detach unused databases $$$ Pros: You have everything you need Management Studio features Easy move to production

    Read the article

  • SQL Query Theory Question...

    - by Keng
    I have a large historical transaction table (15-20 million rows MANY columns) and a table with one row one column. The table with one row contains a date (last processing date) which will be used to pull the data in the trasaction table ('process_date'). Question: Should I inner join the 'process_date' table to the transaction table or the transaction table to the 'process_date' table?

    Read the article

  • SQL Server 2005 - Syncing development/production databases

    - by hamlin11
    I've got a rather large SQL Server 2005 database that is under constant development. Every so often, I either get a new developer or need to deploy wide-scale schema changes to the production server. My main concern is deploying schema + data updates to developer machines from the "master" development copy. Is there some built-in functionality or tools for publishing schema + data in such a fashion? I'd like it to take as little time as possible. Can it be done from within SSMS? Thanks in advance for your time

    Read the article

  • Conditional Operator in SQL Where Clause

    - by Marc
    I'm wishing I could do something like the following in SQl Server 2005 (which I know isnt valid) for my where clause. Sometimes @teamID (passed into a stored procedure) will be the value of an existing teamID, otherwise it will always be zero and I want all rows from the Team table. I researched using Case and the operator needs to come before or after the entire statement which prevents me from having a different operator based on the value of @teamid. Any suggestions other than duplicating my select statements. declare @teamid int set @teamid = 0 Select Team.teamID From Team case @teamid when 0 then WHERE Team.teamID > 0 else WHERE Team.teamID = @teamid end

    Read the article

  • SQL Server 2008 pivot without aggregate

    - by Bryan Lewis
    I have table to test score data that I need to pivot and I am stuck on how to do it. I have the data as this: gradelistening speaking reading writing 0 0.0 0.0 0.0 0.0 1 399.4 423.8 0.0 0.0 2 461.6 508.4 424.2 431.5 3 501.0 525.9 492.8 491.3 4 521.9 517.4 488.7 486.7 5 555.1 581.1 547.2 538.2 6 562.7 545.5 498.2 530.2 7 560.5 525.8 545.3 562.0 8 580.9 548.7 551.4 560.3 9 602.4 550.2 586.8 564.1 10 623.4 581.1 589.9 568.5 11 633.3 578.3 598.1 568.2 12 626.0 588.8 600.5 564.8 But I need it like this: gr0 gr1 gr2 gr3 gr4 gr5 gr6 gr7 ... listening 0.0 399.4 461.6 501.0 521.9 555.1 562.7 560.5 580.9... speaking 0.0 423.8... reading 0.0 0.0 424.2... writing 0.0 0.0 431.5... I don't need to aggregate anything, just pivot the data.

    Read the article

  • Actual table Vs. Div table

    - by omfgroflmao
    This <table> <tr> <td>Hello</td> <td>World</td> </tr> </table> Can be done with this: <div> <div style="display: table-row;"> <div style="display: table-cell;">Hello</div> <div style="display: table-cell;">World</div> </div> </div> Now, is there any difference between these two in terms of performance and/or render speed or they're just the same?

    Read the article

  • MS SQL Server: how to optimize "like" queries?

    - by duke84
    I have a query that searches for clients using "like" with wildcard. For example: SELECT TOP (10) [t0].[CLIENTNUMBER], [t0].[FIRSTNAME], [t0].[LASTNAME], [t0].[MI], [t0].[MDOCNUMBER] FROM [dbo].[CLIENT] AS [t0] WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND ([t0].[FIRSTNAME] LIKE '%John%') AND ([t0].[LASTNAME] LIKE '%Smith%') AND ([t0].[SSN] LIKE '%123%') AND ([t0].[CLIENTNUMBER] LIKE '%123%') AND ([t0].[MDOCNUMBER] LIKE '%123%') AND ([t0].[CLIENTINDICATOR] = 'ON') It can also use less parameters in "where" clause, for example: SELECT TOP (10) [t0].[CLIENTNUMBER], [t0].[FIRSTNAME], [t0].[LASTNAME], [t0].[MI], [t0].[MDOCNUMBER] FROM [dbo].[CLIENT] AS [t0] WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND ([t0].[FIRSTNAME] LIKE '%John%') AND ([t0].[CLIENTINDICATOR] = 'ON') Can anybody tell what is the best way to optimize performance of such query? Maybe I need to create an index? This table can have up to 1000K records in production.

    Read the article

  • SQL Server: how to optimize "like" queries?

    - by duke84
    I have a query that searches for clients using "like" with wildcard. For example: SELECT TOP (10) [t0].[CLIENTNUMBER], [t0].[FIRSTNAME], [t0].[LASTNAME], [t0].[MI], [t0].[MDOCNUMBER] FROM [dbo].[CLIENT] AS [t0] WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND ([t0].[FIRSTNAME] LIKE '%John%') AND ([t0].[LASTNAME] LIKE '%Smith%') AND ([t0].[SSN] LIKE '%123%') AND ([t0].[CLIENTNUMBER] LIKE '%123%') AND ([t0].[MDOCNUMBER] LIKE '%123%') AND ([t0].[CLIENTINDICATOR] = 'ON') It can also use less parameters in "where" clause, for example: SELECT TOP (10) [t0].[CLIENTNUMBER], [t0].[FIRSTNAME], [t0].[LASTNAME], [t0].[MI], [t0].[MDOCNUMBER] FROM [dbo].[CLIENT] AS [t0] WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') AND ([t0].[FIRSTNAME] LIKE '%John%') AND ([t0].[CLIENTINDICATOR] = 'ON') Can anybody tell what is the best way to optimize performance of such query? Maybe I need to create an index? This table can have up to 1000K records in production.

    Read the article

  • Changing SQL Server Database sorting

    - by plaisthos
    I have a request to change the collation of a SQL Server Database: ALTER DATABASE solarwind95 collate SQL_Latin1_General_CP1_CI_AS but I get this strange error: Meldung 5075, Ebene 16, Status 1, Zeile 1 Das 'Spalte'-Objekt 'CustomPollerAssignment.PollerID' ist von 'Datenbanksortierung' abhängig. Die Datenbanksortierung kann nicht geändert werden, wenn ein schemagebundenes Objekt von ihr abhängig ist. Entfernen Sie die Anhängigkeiten der Datenbanksortierung, und wiederholen Sie den Vorgang. Sorry for the german errror message. I do not know how to switch the language to english, but here is a translation: Translation: Message 5075, Layer 16, Status 1, Row 1 The 'column' object 'CustomPollerAssignment.PollerID' depends on 'Database sorting. The database sorting cannot be changed if a schema bound object depends on it. Remove the dependency of the database sortieren and retry. I got a ton more of the errors like that.

    Read the article

  • SQL server 2005 - user rights

    - by Paresh
    I have created one user named "tuser" with create database rights in SQL server 2005. and given the 'db_owner' database role of master and msdb database to "tuser". From this user login when I run the script for create database then it will create new database. But "tuser" don't have access that newly created database generated from script. Any one have any idea?, I want to write the script so "tuser" have access that new created database after creation and can have add user permission of newly created database. I want to give 'db_owner' database roles to "tuser" on that newly created database in the same script which create new database. The script run under 'tuser'.

    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 | 53 54 55 56 57 58 59 60 61 62 63 64  | Next Page >