Insert Identity value with SqlCeResultSet

SQL Server Compact Edition

Index ‹ SQL Server

- Previous
  - 1
    - How to dynamically map the coloumn to a flat file destination? Metadata cannot change. Will it change with your dynamic query Tag: Insert Identity value with SqlCeResultSet
  - 2
    - SQL 2005 Licensing Question http://support.microsoft.com/kb/913967 and have you restarted after installation of SQL Server (just checking(). Tag: Insert Identity value with SqlCeResultSet
  - 3
    - OLE DB Connection Manager will not save password Please search the forums. SSIS does not save sensitive information in the packages. You'll need to look at using the /SET command line option to pass in a password. Tag: Insert Identity value with SqlCeResultSet
  - 4
    - Cube and aggregation design Let's start with the second item. Each measure can have a single aggregation function applied to it. However, there is nothing preventing you from using a single field in a fact table as the source for multiple measures. The aggregation functions available to you depend on the edition of Analysis Services you have installed but it sounds like you need simply COUNT and SUM which are available in all editions. When you define aggregations, what you are asking SSAS to do is use the aggregation functions you previously assigned to individual measures to pre-calculate the measure values at different levels of your dimensional hierarchies. In other words, if you have a SUM defined for Measure A and a COUNT defined for Measure B and then you get an aggregation at the Detector Site level of your dimension, SSAS will pre-calculate the SUM of Measure A and the COUNT of Measure B for each value of Detector Site. The only control you have with aggregations is over which dimension levels are pre-calculated and I would suggest letting SSAS actually determine what's best in that situation. Regarding smoothing, maybe you could help us explain what you intend to do there. Detector readings are a little scatter shot, right If you SUM those readings at the detector site level, does the variation in the readings naturally smooth out If so, what additional smooting is needed Thanks, Bryan Smith Tag: Insert Identity value with SqlCeResultSet
  - 5
    - accessing static methods of a .net dll, but from sql2000? Thats ugly, you would have to write a com wrapper to call the method via sp_OA*, if you really want to do this, write a console application and consume the output or let the console application manipulate your database (You did not write what you want to achieve) HTH, Jens K. Suessmeyer. ---- http://www.sqlserver2005.de ---- Tag: Insert Identity value with SqlCeResultSet
  - 6
    - A call to SQL Server Reconciler failed. SQL Server 2005, SQL Server Mobile merge replication If looks like you have not configured the read permissions on the snapshot folder. The error code 80045017 seems to indicate a permission problem. Can you try the sync after you give read permissions to the snapshot folder/subfolder and all files. And note that if you using absolute paths (instead of share) you will need to give NTFS permissions too. 80045017 The SQL Server CE Replication Provider must have read permissions to the snapshot folder. Read permission is needed so the SQL Server CE Replication Provider can download the initial subscription to the Windows CE-based device. The identity under which the SQL Server CE Replication Provider runs depends upon how IIS authentication is configured. Tag: Insert Identity value with SqlCeResultSet
  - 7
    - Data restore help Do you get the same error when you try to attach the database files (mdf & ldf) Tag: Insert Identity value with SqlCeResultSet
  - 8
    - categories table Selfreferencing tables Code Snippet DROP TABLE Categories GO CREATE TABLE Categories ( CategoryId INT NOT NULL, ParentCategoryID INT , Description VARCHAR ( 4000 ) ) GO ALTER TABLE Categories ADD CONSTRAINT PK_Categories PRIMARY KEY ( CategoryId ) GO ALTER TABLE Categories ADD CONSTRAINT FK_Categories_Cetegories FOREIGN KEY ( ParentCategoryId ) REFERENCES Categories ( CategoryId ) GO Jens K. Suessmeyer. --- http://www.sqlserver2008.de --- Tag: Insert Identity value with SqlCeResultSet
  - 9
    - Could not find stored procedure the proc name doesn't look right, it should be 'sp_ins...', not 'bp_ins...'. Regardless, did you or someone else drop the proc along the way by accident You may want to try reinitializing the subscription. Tag: Insert Identity value with SqlCeResultSet
  - 10
    - How to deploy an report and connection string from some xml file 1. Shared data sources are used to define a connection once, and share it among multiple reports. That way, if the connection information changes, you only have to update the shared data source, and not each report. You don't have to use shared data sources, though. Data sources can be defined at a report level as well. 2. You can create a connection string from an expression, which would allow you to change the connect string at runtime based on parameters passed to the report. However, as far as I am aware, you cannot use an XML file directly to set the connect string. Tag: Insert Identity value with SqlCeResultSet
  - 11
    - Help sorting a Recursive Query you forget the order by clause, Code Snippet WITH tree ( data , id , level , pathstr , numSortID , memContent ) AS ( SELECT txtTitle , txtid , 0 , CAST ( '' AS NVARCHAR ( MAX )), numSortID , memContent FROM CTLG WHERE txtParentID IS NULL UNION ALL SELECT txtTitle , V . txtid , t . level + 1 , t . pathstr + '>' + V . txtTitle , V . numSortID , v . memContent FROM CTLG V INNER JOIN tree t ON t . id = V . txtParentID ) SELECT SPACE ( level ) + data as data , id , level , pathstr , numSortID , memContent FROM tree Order By numSortID Tag: Insert Identity value with SqlCeResultSet
  - 12
    - "Select * from Table" is processed much faster than "Select MyField from Table" ???? Whenever you see the difference in performance, thing thing you need to do is check the execution plans to see how optimizer is trying to service that requests. And to also give fair chance to both the queries, run dbcc dropcleanbuffers after running each one. Also if you hide some information that may be crucial part of puzzle, that makes it harder to guess from this end. Tag: Insert Identity value with SqlCeResultSet
  - 13
    - Usage Based Optimization - Problem Are there any rows in the Query Log table Chris Tag: Insert Identity value with SqlCeResultSet
  - 14
    - DTS problem on 64-bit installation I don't see "the one below" in your post. Could you clarify what exact problem you're looking for an answer to Mike Tag: Insert Identity value with SqlCeResultSet
  - 15
    - Can I change from Integrated Security? The Authetication method is global to the server and not specific to the database. When you change your authentication to mixed mode The SA account becomes the most powerful account in the Sql server box and can login in to all the databases in the server by default. there are no data loss in the change. reset the password of your SA account and makeuse of it. Tag: Insert Identity value with SqlCeResultSet
  - 16
    - Remove SP1 or install SP2 Dec. CTP Unfortunately the Service Packs are not uninstallable. As a result, my suggestion would be to wait a short while longer until SP2 is publicly released. If you need an immediate solution, the ugly workaround is to uninstall your SQL instances and reinstall RTM again. If you can wait a little bit, I'd suggest holding off until SP2. Thanks, Sam Lester (MSFT) Tag: Insert Identity value with SqlCeResultSet
- Next
  - 1
    - Add Reference to Excel in Scripting tool in SSIS 2005 Adding refereneces to external assembleis is documented in Books Online - Referencing Other Assemblies in Scripting Solutions http://technet.microsoft.com/en-us/library/ms136007.aspx For Office applications like Excel that are COM, you will need to download the PIAs if you have not already.You will need to reference the PIA. Download - http://www.microsoft.com/downloads/details.aspx FamilyID=3C9A983A-AC14-4125-8BA0-D36D67E0F4AD&displaylang=en General page of on PIAs if you are not familar already - http://msdn2.microsoft.com/en-us/library/aax7sdch.aspx Tag: Insert Identity value with SqlCeResultSet
  - 2
    - How to change the target table name in SSIS package(not designed from scratch but was migrated) Can you tell us what tasks the package has in it If it has a data flow, there should be a destination component in it. One of the properties of the destination component will be either the table name or a variable that holds the table name. Tag: Insert Identity value with SqlCeResultSet
  - 3
    - Inconsistency between Data Source Designer and underlying code Can you recreate them Tag: Insert Identity value with SqlCeResultSet
  - 4
    - SQL Server 2005 Express hi, no, there' not.. SSMSE does not include DTS or SSIS features to perform ETL (extract-transform-load) tasks... you have to "move" (copying it or backing it up and restoring it) your db to the other server, or, via Linked Server (if the 2 machines are reachable on the net) you can perform INSERT... SELECT across database and server boundaries.. regards Tag: Insert Identity value with SqlCeResultSet
  - 5
    - Increase Transaction Log size ALTER DATABASE YourDatabaseName MODIFY FILE ( NAME = YourDatbase_log , SIZE = 1500MB ); Or you can do it from SQL Server Management Studio -- Database Property WIndow Madhu Tag: Insert Identity value with SqlCeResultSet
  - 6
    - top 10 accounts for every day with summary This query helps explain what's going on with your query and maybe gives you some ideas on how to address the problem. Before digging in, let me break it down. First, I need to know the top products for a given day. Because Adventure Works is so sparsely populated, I limit myself to the top 2 products on a given day as determined by Internet Sales Amount. This is the TOPCOUNT() stuff you see all over the place. To keep my cellset small, I just grab two days. This is the HEAD() stuff. I use NONEMPTY in the HEAD function just to make sure the days I get have sales associated with them. The GENERATE function in the middle of all this takes a set of days and then does the TOPCOUNT on products for each day in that set. This generates a set of Day-Product combinations. I union this set with another set that is those same days coupled with the ALL Products member of the Product attribute hierarchy. I also union this set with another set that is those same days coupled with a calculated member defined at the top. The calculated member is the aggregation of the top 2 products for a day. This is different from the ALL Products member that represents all products regardless of whether or not they are in my top 2. In your Excel results, I believe you are getting the ALL member for your dimension. This would have all members of the dimension regardless of what you are displaying below. Like my calculated member, you need to aggregate just the set of members you are returning on your axis to give you the results you are looking for. Not sure the best approach for your situation, but hopefully this gets you on the right path. Good luck, Bryan Code Snippet with member [Product].[Product].[All Top Products] as AGGREGATE( TOPCOUNT( ([Ship Date].[Date].CurrentMember,[Product].[Product].[Product].Members), 2, [Measures].[Internet Sales Amount] )) select [Internet Sales Amount] on 0, { GENERATE( HEAD(NONEMPTY([Ship Date].[Date].[Date].Members,[Measures].[Internet Sales Amount]),2), TOPCOUNT( ([Ship Date].[Date].CurrentMember,[Product].[Product].[Product].Members), 2, [Measures].[Internet Sales Amount]) ), ( HEAD(NONEMPTY([Ship Date].[Date].[Date].Members,[Measures].[Internet Sales Amount]),2) * [Product].[Product].[All Top Products] ), HEAD(NONEMPTY([Ship Date].[Date].[Date].Members,[Measures].[Internet Sales Amount]),2) * [Product].[Product].[All Products] } on 1 from [Adventure Works] ; Tag: Insert Identity value with SqlCeResultSet
  - 7
    - Improving Performance 2 and a half hours is a little bit excessive to insert just 12K rows. I would try to insert 12K rows from a csv file using bcp or DTS. How much time that will take Also how do you insert Do you use parameterized statements or are you issuing statements with constants, such as INSERT INTO MyTable( columns list) VALUES(1, 'bla', bla bla', bla' ...) Are there any triggers on your table Tag: Insert Identity value with SqlCeResultSet
  - 8
    - Copy objects with DTS Import/Export Wizard? DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects. Tag: Insert Identity value with SqlCeResultSet
  - 9
    - Problem with the SQL Insert with .CommandText() and .ExecuteNonQuery() Hi, Your thread seems to be more appropriate here and hope you find satisfactory answers. Thank you for understanding Tag: Insert Identity value with SqlCeResultSet
  - 10
    - Select Statement In Table (Valid Expressions Error). I can't tell from your post what is a single quote and what is a double quote. But, it appears that there is an imbalance in this statement SET @Statement = N'INSERT INTO [dbo].[New_Cust_Table] VALUES ( '''+@UserID+'1'','''+@UserID+'1#'','''+@SelectStmt+''')' And the @SelectStmt variable is being read as code and not as a literal. Tag: Insert Identity value with SqlCeResultSet
  - 11
    - Why does using the "in" clause cause an error in my "Having" condition in 2000, but not 2005? Aslom Rob, 1 - Can you tell us what is the error 2 - I do not see this table in the statement, may be the error is related to it or are we missing something here . "Business_Grp"."BusinessGrpAbbrev" 3 - Seems that you want the result just for rows that match the criteria: "table1"."UnitCode" in ('012201', '09C202', '10C010') so, I would recommend to put that filter in the "where" clause. select "table1"."LevelKey" AS "level0key", sum("table2"."Total") AS "Total" from "t_mlcdm_d_businessGrp" "table1" inner join "t_mlcdm_f_facilitySnapshot" "table2" on "Table1"."levelKey" = "Table2"."GrpKey" where "table1"."UnitCode" in ('012201', '09C202', '10C010') Group by "table1"."LevelKey" AMB Tag: Insert Identity value with SqlCeResultSet
  - 12
    - Dose anybody knows how to use MergeJoin component programitcally? I'm building a package programitcally and I can't find any document which shows how to work with MergeJoin component in code. Please help, Thanks Tag: Insert Identity value with SqlCeResultSet
  - 13
    - SQL Server 2005 SP2 cannot be installed because the Database Engine setup remains in suspended installation mode I got help from Amy Lewis, Microsoft. The problem occurred because the Remote Registry service had been disabled on the computer. Always make sure it is started before you attempt to install a service pack. Tag: Insert Identity value with SqlCeResultSet
  - 14
    - patindex with nvarchar You have to put N prefix on Nvarchar String values See the sample here.. Code Snippet SELECT PATINDEX ( '% %' , N ' ' ) --Output : 0 SELECT PATINDEX ( N '% %' , N ' ' ) --Output : 9 --InYour Query SELECT PATINDEX ( N '% %' , keyword ) FROM tblKeywords where keywordID = 68 Tag: Insert Identity value with SqlCeResultSet
  - 15
    - MDX Ranking Question Hi Michael, Here's a sample Adventure Works ranking query - could you give more details, with examples, of your scenario With Set [OrderedProducts] as Order ([Product].[Category].[Category] * {[Measures].[Order Quantity]}, [Measures].[Order Quantity], BDESC ) + Order ([Product].[Category].[Category] * {[Measures].[Sales Amount]}, [Measures].[Sales Amount], BDESC ) + Order ([Product].[Category].[Category] * {[Measures].[Total Product Cost]}, [Measures].[Total Product Cost], BDESC ) Member [Measures].[QuantityRank] as Rank (([Product].[Category]. CurrentMember , [Measures].[Order Quantity]), Exists ([OrderedProducts], {[Measures].[Order Quantity]})) Member [Measures].[SalesRank] as Rank (([Product].[Category]. CurrentMember , [Measures].[Sales Amount]), Exists ([OrderedProducts], {[Measures].[Sales Amount]})) Member [Measures].[CostRank] as Rank (([Product].[Category]. CurrentMember , [Measures].[Total Product Cost]), Exists ([OrderedProducts], {[Measures].[Total Product Cost]})) select {[Measures].[Order Quantity], [Measures].[QuantityRank], [Measures].[Sales Amount], [Measures].[SalesRank], [Measures].[Total Product Cost], [Measures].[CostRank]} on 0, Extract ( Exists ([OrderedProducts], {[Measures].[Order Quantity]}), [Product].[Category]) on 1 from [Adventure Works] ----------------------------------------------------------------------------------------------------------- Order Quantity QuantityRank Sales Amount SalesRank Total Product Cost CostRank Bikes 90,220 1 $94,620,526.21 1 $84,105,429.60 1 Clothing 73,598 2 $2,117,613.45 3 $1,748,777.45 3 Accessories 61,931 3 $1,272,057.89 4 $637,590.73 4 Components 49,027 4 $11,799,076.66 2 $10,766,110.18 2 Tag: Insert Identity value with SqlCeResultSet
  - 16
    - BCP loading failing on timeout error Hi Paul, did you use a remote connection What about using sqlcmd sqlcmd -S <machine name>\SQLEXPRESS ... CU tosc Tag: Insert Identity value with SqlCeResultSet

Rik78

I have a table with an identity column. This is set to increase by 1 and start on 1, and is the first column of the table.

When I insert a record into the resultset, I get an error stating that it can't be DbNull.

Th record is created from a resultset with the query "Select * from table", so the id should be included

But as I understand it, it should be increasing automatically, right Becuase that's the behaviour of the normal sql Insert command. I have seen an article that just left the id column in the record alone before inserting it, but it didn't help me.

How can I sidestep this issue

Top

Re: Insert Identity value with SqlCeResultSet

ErikEJ

Use tabledirect instead of select * from table, see this sample from Joao:

http://www.pocketpcdn.com/articles/articles.php &atb.set(c_id)=74&atb.set(a_id)=11078&atb.perform(details)=&

Top