Search Results

Search found 53054 results on 2123 pages for 'sql sample database'.

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 171/2123 | < Previous Page | 167 168 169 170 171 172 173 174 175 176 177 178  | Next Page >

  • Don't Understand Sql Server Error

    - by Jonathan Wood
    I have a table of users (User), and need to create a new table to track which users have referred other users. So, basically, I'm creating a many-to-many relation between rows in the same table. So I'm trying to create table UserReferrals with the columns UserId and UserReferredId. I made both columns a compound primary key. And both columns are foreign keys that link to User.UserID. Since deleting a user should also delete the relationship, I set both foreign keys to cascade deletes. When the user is deleted, any related rows in UserReferrals should also delete. But this gives me the message: 'User' table saved successfully 'UserReferrals' table Unable to create relationship 'FK_UserReferrals_User'. Introducing FOREIGN KEY constraint 'FK_UserReferrals_User' on table 'UserReferrals' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors. I don't get this error. A cascading delete only deletes the row with the foreign key, right? So how can it cause "cycling cascade paths"? Thanks for any tips.

    Read the article

  • Linked servers SQLNCLI problem. "No transaction is active"

    - by Felipe Fiali
    Im trying to execute a stored procedure and simply insert its results in a temporary table, and I'm getting the following message: The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MyServerName" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "MyServerName" returned message "No transaction is active.". My query looks like this: INSERT INTO #TABLE EXEC MyServerName.MyDatabase.dbo.MyStoredProcedure Param1, Param2, Param3 Exact column number, names, the problem is not the result. MSDTC is allowed and started in both computers, Remote procedure calling too. The machines are not in the same domain, but I can execute remote queries from my machine and get the result. I can even execute the stored procedure and see its results, I just can't insert it in another table. Help, please? :)

    Read the article

  • Parralelization in Microsoft SQL Server 2008 R2

    - by stan31337
    We have a specific accounting and production software, called 1C, which uses single user connection to the MS SQL 2008 R2 database. And there are about 500 users connecting to 1C server to perform their tasks. 1C and SQL 2008 are on separate servers. How to configure MS SQL 2008 R2 to effectively use parallelization in this configuration? We have 24 cores, and only one is loaded at 100% at MS SQL 2008 R2 server. We have already configured MS SQL max parallelizm from this MSDN article: http://msdn.microsoft.com/en-us/library/ms181007(v=sql.105).aspx Thank you!

    Read the article

  • SQL Max Group By Query Help

    - by Hcabnettek
    Hi All, I have a quick question. How do I select the two values I need in one query? Currently I'm doing this, which works fine, but it's obviously running two queries when one should do the trick. I tried MAX(columnA) and GROUP BY ColumnB, but that returns multiple row. I only want one row returned. DECLARE @biID bigint , @dtThreshold DateTime SELECT @biID = MAX(biID) FROM tbPricingCalculationCount WITH (NOLOCK) SELECT @dtThreshold = dtDateTime FROM tbPricingCalculationCount WITH (NOLOCK) WHERE biID = @biID I would like both those variables to be set correctly in one query. How can I do that? Thanks, ~ck

    Read the article

  • Query Optimizing Request

    - by mithilatw
    I am very sorry if this question is structured in not a very helpful manner or the question itself is not a very good one! I need to update a MSSQL table call component every 10 minutes based on information from another table call materials_progress I have nearly 60000 records in component and more than 10000 records in materials_progress I wrote an update query to do the job, but it takes longer than 4 minutes to complete execution! Here is the query : UPDATE component SET stage_id = CASE WHEN t.required_quantity <= t.total_received THEN 27 WHEN t.total_ordered < t.total_received THEN 18 ELSE 18 END FROM ( SELECT mp.job_id, mp.line_no, mp.component, l.quantity AS line_quantity, CASE WHEN mp.component_name_id = 2 THEN l.quantity*2 ELSE l.quantity END AS required_quantity, SUM(ordered) AS total_ordered, SUM(received) AS total_received , c.component_id FROM line l LEFT JOIN component c ON c.line_id = l.line_id LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND l.line_no = mp.line_no AND c.component_name_id = mp.component_name_id WHERE mp.job_id IS NOT NULL AND (mp.cancelled IS NULL OR mp.cancelled = 0) AND (mp.manual_override IS NULL OR mp.manual_override = 0) AND c.stage_id = 18 GROUP BY mp.job_id, mp.line_no, mp.component, l.quantity, mp.component_name_id, component_id ) AS t WHERE component.component_id = t.component_id I am not going to explain the scenario as it too complex.. could somebody please please tell me what makes this query this much expensive and a way to get around it? Thank you very very much in advance!!!

    Read the article

  • optimizing an sql query using inner join and order by

    - by Sergio B
    I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort? EXPLAIN SELECT SQL_NO_CACHE `groups`.* FROM `groups` INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id WHERE ((`memberships`.user_id = 1) AND (`memberships`.`status_code` = 1 AND `memberships`.`manager` = 0)) ORDER BY groups.created_at DESC LIMIT 5;` +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | memberships | ref | grp_usr,grp,usr,grp_mngr | usr | 5 | const | 5 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | groups | eq_ref | PRIMARY | PRIMARY | 4 | sportspool_development.memberships.group_id | 1 | | +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | groups | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | groups | 1 | index_groups_on_name | 1 | name | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_privacy_setting | 1 | privacy_setting | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_created_at | 1 | created_at | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_id_and_created_at | 1 | id | A | 6 | NULL | NULL | | BTREE | | | groups | 1 | index_groups_on_id_and_created_at | 2 | created_at | A | 6 | NULL | NULL | YES | BTREE | | +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | memberships | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | memberships | 0 | grp_usr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 0 | grp_usr | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | usr | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp_mngr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp_mngr | 2 | manager | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 3 | status_code | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 4 | manager | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 2 | status_code | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 3 | manager | A | 2 | NULL | NULL | YES | BTREE | | +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

    Read the article

  • Storing a set of criteria in another table

    - by bendataclear
    I have a large table with sales data, useful data below: RowID Date Customer Salesperson Product_Type Manufacturer Quantity Value 1 01-06-2004 James Ian Taps Tap Ltd 200 £850 2 02-06-2004 Apple Fran Hats Hats Inc 30 £350 3 04-06-2004 James Lawrence Pencils ABC Ltd 2000 £980 ... Many rows later... ... 185352 03-09-2012 Apple Ian Washers Tap Ltd 600 £80 I need to calculate a large set of targets from table containing values different types, target table is under my control and so far is like: TargetID Year Month Salesperson Target_Type Quantity 1 2012 7 Ian 1 6000 2 2012 8 James 2 2000 3 2012 9 Ian 2 6500 At present I am working out target types using a view of the first table which has a lot of extra columns: SELECT YEAR(Date) , MONTH(Date) , Salesperson , Quantity , CASE WHEN Manufacturer IN ('Tap Ltd','Hats Inc') AND Product_Type = 'Hats' THEN True ELSE False END AS IsType1 , CASE WHEN Manufacturer = 'Hats Inc' AND Product_Type IN ('Hats','Coats') THEN True ELSE False END AS IsType2 ... ... , CASE WHEN Manufacturer IN ('Tap Ltd','Hats Inc') AND Product_Type = 'Hats' THEN True ELSE False END AS IsType24 , CASE WHEN Manufacturer IN ('Tap Ltd','Hats Inc') AND Product_Type = 'Hats' THEN True ELSE False END AS IsType25 FROM SalesTable WHERE [some stuff here] This is horrible to read/debug and I hate it!! I've tried a few different ways of simplifying this but have been unable to get it to work. The closest I have come is to have a third table holding the definition of the types with the values for each field and the type number, this can be joined to the tables to give me the full values but I can't work out a way to cope with multiple values for each field. Finally the question: Is there a standard way this can be done or an easier/neater method other than one column for each type of target? I know this is a complex problem so if anything is unclear please let me know. Edit - What I need to get: At the very end of the process I need to have targets displayed with actual sales: Type Year Month Salesperson TargetQty ActualQty 2 2012 8 James 2000 2809 2 2012 9 Ian 6500 6251 Each row of the sales table could potentially satisfy 8 of the types. Some more points: I have 5 different columns that need to be defined against the targets (or set to NULL to include any value) I have between 30 and 40 different types that need to be defined, several of the columns could contain as many as 10 different values For point 2, if I am using a row for each permutation of values, 2 columns with 10 values each would give me 100 rows for each sales person for each month which is a lot but if this is the only way to define multiple values I will have to do this. Sorry if this makes no sense!

    Read the article

  • Cannot render images locally with Sql Server 2005 Report Services SOAP service calls

    - by user289146
    I am trying to render images from SSRS 2005 via the SOAP call. When the reports service is installed on the same server as my website, the images generated do not render in the page. However, when I connect to the reporting service URL from the same web code on a separate machine the images render just fine. Is there a dependency that I am missing to allow me to run the web and SSRS on the same box?

    Read the article

  • Multi-base conversion - using all combinations for URL shortener

    - by Guffa
    I am making an URL shortener, and I am struggling with the optimal way of encoding a number (id) into a character string. I am using the characters 0-9,A-Z,a-z so it will basically be a base-62 encoding. That is pretty basic, but it doesn't make use of all possible codes. The codes that it would produce would be: 0, 1, ... y, z, 10, 11, ... zy, zz, 100, 101, ... Notice that the codes 00 to 0z is not used, the same for 000 to 0zz, and so on. I would like to use all the codes, like this: 0, 1, ... y, z, 00, 01, ... zy, zz, 000, 001, ... It would be some combination of base-62 and base-63, with different bases depending on the position... Using base-62 is easy, for example: create procedure tiny_GetCode @UrlId int as set nocount on declare @Code varchar(10) set @Code = '' while (@UrlId > 0 or len(@Code) = 0) begin set @Code = substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', @UrlId % 62 + 1, 1) + @Code set @UrlId = @UrlId / 62 end select @Code But I haven't yet managed to make a multi-base conversion out of it, to make use of all the codes.

    Read the article

  • Counting number of children in hierarchical SQL data

    - by moontear
    Hello, for a simple data structure such as so: ID parentID Text Price 1 Root 2 1 Flowers 3 1 Electro 4 2 Rose 10 5 2 Violet 5 6 4 Red Rose 12 7 3 Television 100 8 3 Radio 70 9 8 Webradio 90 For reference, the hierarchy tree looks like this: ID Text Price 1 Root |2 Flowers |-4 Rose 10 | |-6 Red Rose 12 |-5 Violet 5 |3 Electro |-7 Television 100 |-8 Radio 70 |-9 Webradio 90 I'd like to count the number of children per level. So I would get a new column "NoOfChildren" like so: ID parentID Text Price NoOfChildren 1 Root 8 2 1 Flowers 3 3 1 Electro 3 4 2 Rose 10 1 5 2 Violet 5 0 6 4 Red Rose 12 0 7 3 Television 100 0 8 3 Radio 70 1 9 8 Webradio 90 0 I read a few things about hierarchical data, but I somehow get stuck on the multiple inner joins on the parentIDs. Maybe someone could help me out here. moon

    Read the article

  • Datatype to save excel file in sql server?

    - by gowri-ganapathy
    Hi, I have a table in which there are two columns : 1. import type, 2. Excel import template. The second column - "Excel import template" should store the whole excel file. How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ? Thanks in advance !

    Read the article

  • SQL Server JDBC Exception

    - by user267581
    When using ANT to build my Java application I keep getting this error. I have tried multiple times to use SQLJDBC.JAR and SQLJDBC4.JAR but continually receive this error message. I am completely stumpped why this error is received even after upgrading to sqljdbc4.jar. [javadoc] java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

    Read the article

  • How to find level of employee position using RECURSIVE COMMON TABLE EXPRESSION

    - by user309381
    ;with Ranked(Empid,Mngrid,Empnm,RN,level) As ( select Empid,Mngrid ,Empnm ,row_number() over (order by Empid)AS RN , 0 as level from dbo.EmpMngr ), AnchorRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN ,level from Ranked ), RecurRanked(Empid,Mngrid,Empnm,RN,level) AS(select Empid,Mngrid,Empnm,RN,level from AnchorRanked Union All select Ranked.Empid,Ranked.Mngrid,Ranked.Empnm,Ranked.RN,Ranked.level + 1 from Ranked inner join RecurRanked on Ranked.Empid = RecurRanked.Empid AND Ranked.RN = RecurRanked.RN+1) select Empid,Empnm,level from RecurRanked

    Read the article

  • Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAV

    - by brz dot net
    I have to find the indentid from the status table based on below two conditions: 1. If there are more than one record having same indentid in status table and the same indentID has count1 in feasibilitystatus table then I don't want to display the record. 2. If there is only one record of indentid in status table and the same indentID has count0 in feasibilitystatus table then I don't want to display the record. Query: select distinct s.indentid from status s where s.status='true' and s.indentid not in( select case when count(s.indentid)>1 then (select indentid from feasibilitystatus group by indentid having count(indentid)>1) else (select indentid from feasibilitystatus group by indentid having count(indentid)>0) end as indentid from status) Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

    Read the article

  • Just installed Sql Server 2008 and cannot access it

    - by Mendy
    I just install Sql Server 2008 on a new server and I cannot connect to it. I'm trying to connect using Windows Authentication to the localhost but I get the following error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

    Read the article

  • autoincrement in access sql is not working

    - by Thunder
    how can i create a table with autoincrement in access.Here is what i have been doing but not working. CREATE TABLE People_User_Master( Id INTEGER primary key AUTOINCREMENT , Name varchar(50) , LastName varchar(50) , Userid varchar(50) unique , Email varchar(50) , Phone varchar(50) , Pw varchar(50) , fk_Group int , Address varchar(150) )

    Read the article

  • Counting number of children in hierarchical SQL data

    - by moontear
    for a simple data structure such as so: ID parentID Text Price 1 Root 2 1 Flowers 3 1 Electro 4 2 Rose 10 5 2 Violet 5 6 4 Red Rose 12 7 3 Television 100 8 3 Radio 70 9 8 Webradio 90 For reference, the hierarchy tree looks like this: ID Text Price 1 Root |2 Flowers |-4 Rose 10 | |-6 Red Rose 12 |-5 Violet 5 |3 Electro |-7 Television 100 |-8 Radio 70 |-9 Webradio 90 I'd like to count the number of children per level. So I would get a new column "NoOfChildren" like so: ID parentID Text Price NoOfChildren 1 Root 8 2 1 Flowers 3 3 1 Electro 3 4 2 Rose 10 1 5 2 Violet 5 0 6 4 Red Rose 12 0 7 3 Television 100 0 8 3 Radio 70 1 9 8 Webradio 90 0 I read a few things about hierarchical data, but I somehow get stuck on the multiple inner joins on the parentIDs. Maybe someone could help me out here. moon

    Read the article

  • sql inValid Object Name how to solve it?

    - by Phsika
    error returns to me in below codes:Msg 208, Level 16, State 1, Line 1 Invalid object name 'ENG_PREP'. insert into ENG_PREP VALUES('572012-01-1,572012-01-2,572012-01-3,572013-01-1,572013-01-2', '', '500', '', 'A320 P.001-A', 'Removal of the LH Wing Safety Rope', '', '', '', '0', '', 'AF', '12-00-00-081-001', '', '', '', '', '', '', '' )

    Read the article

  • Why decimal behave differently?

    - by haansi
    Hello, I am doing this small exercise. declare @No decimal(38,5); set @No=12345678910111213.14151; select @No*1000/1000,@No/1000*1000,@No; Results are: 12345678910111213.141510 12345678910111213.141000 12345678910111213.14151 Why are the results of first 2 selects different when mathematically it should be same?

    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 | 167 168 169 170 171 172 173 174 175 176 177 178  | Next Page >