OK, so, from what people tell me I should be using @@Idnetity. What Im trying to do is insert data into a table, than revrieve the id from the new row in that table, than use that id in another sql insert statment later down the road. Currnetly the way im doing it is with a sql insert, than executte the scalar, than execute a reader, which is causeing me much grief.
This is my sql statment here:
Dim sqlInsert As New SqlCommand("INSERT INTO Author (Lastname, FirstName, FullName) VALUES (@LName, @FName, @FullName)", sqlConn)
From what I understand, using @@Identity, I can retrive data from my insert statment without using a sperate select statment. Can you guys point me in the right direction, ive looked alot in the forms and such, most I found is either assuming you know what todo, or is security related.
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
This is my first time posting in this forum, so forgive me if my post is out of place. If so, let me know.
I am trying to get started setting up a development server on Windows 2000. These are the componants I have installed so far:
- IIS 5 - MSDE Database Server - .NET Framework 1.1 - MDAC (Microsoft Data Access Componants) - ASP.NET Web Matrix
I have also downloaded a quickstart example, TimeTracker, to learn from.
Here is my problem: When I try to install TimeTracker, it does not detect a database connection. Also, when I try to connect to a database in Web Matrix, it says I need to have client tools installed. What are these and how to I get them?
Is there anyone that can give me a hand with this? Is it a matter of running the MSDE service? If so, how do you do this?
My apologies for not being more knowledgable, but thanks for helping me get started.
I just installed SQL server 2005 with SP2 on Vista business.
When I open Management Studio, and try to Register a new server, I get this message:
Cannot connect to MyServer. Additional information: An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could nor open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)
When I open SQL Server 2005 Surface Area configuration and click on Add New Administrator, there are no Available privileges. Not sure if this is relevant.
Don't know what to do, any help will be appreciated.
I have just installed SQL Server at home. Had a bit of experience at work, but at home I have to get started from scratch.
Enterprise manager says 'Connection could not be established'. In this error it talkes about 'registration properties'. I am (by default ?) a member of SQL Server group, but I have no sub items, like databases. And how do I get to where I can edit stored procedures ?
As you can see I'm quite a novice, but with a little nudge I can would a lot out myself. But not all. If you can help me now, I'll be back soon.
i have roughly 10-15 access databases that i am trying to run through sql server express, i installed this thing thinking it would would "just work minus a little configuration", what a misconception.
so do tell, what do i need to do. i have sql server express running, it is configured to allow remote connections. i know the path's of these access databases. how do i attach them. i saw something about the command line interface but im not interested in that because of the number of databases i will need to manage, not to mention trying to add an additional one 6 months down the road and i will be lost all over again. i thought about downloading SQL Server Management Studio thinking it would be a "gui side" to the CLI version. while it was downloading, it said i need XX program, i go to that page, it says i need XX and XX first, it is turning into a pyramid scheme to install a billion programs.
so point blank, i want to share some access databases to my local lan, i have sql server express installed and running. what do i need to finish this out. the simplest and smallest footprint solution is needed.
all this because the program im using cant do remote databases. ugh.
I am trying to make a small vb.net program in visual studio 2005 edition. I would like to have a SQL database with it.
I have the Microsoft Visual Studio and SQL Server 2005 installed on my PC. I am running XP but will be moving it all to a new pc running Vista next week.
How to I get the SQL database started ? I am not using the express edition.
This is a repost of my post on the VB Express board.
I would like your opinions.
I am a network guy. I learned basic years ago. I can do some html edits as needed to change sites.
Today I would like to start the long process of learning VB with .net applications. I thought I could start with Office developer xp to learn VB. I have that package.
I also would like to be able to build SQL quaries and integrations or automate and manipulate data exports and imports later down the road.
Example: Export various order and customer information from a shopping cart and import it in to Quickbooks or MS Accounting.
First off I need to be able to build web sites and I know I want to grow into VB and SQL.
What should I do?
Learn HTML from web monkey?
Start with Office xp developer Tutorials?
Start with VB Express?
Buy a book?
Can someone tell me where to start and when to move to the next language?
What is the best resource to get to it, without the bla bla bla and a commnd/syntax refference?
How do I mix Html, CSS and .Net (VB)...in the learning process?
I would like to get the fast track for the long haul.
I am looking to learn about reporting with SQL Server for my company
Currently we have an applicaiton running on SQL Server 2000 and have SQL Server 2000 Reporting Services installed. However, I have VB Express and Web Development Express 2005.
How do I get started here? What versions "work" with what? Do I need to either upgrade the DB to SQL 2005 or find VB.net 2003, or can I use what I have to get started?
hello, i own about a dozen interactive forums (see example at http://ConcealedCarryForum.com for one of them) that i host on a windows 2000 server dedicated server out of my home on a commercial cable backbone. the forums are ASP using Access DBs. we have outgrown the capabilities of Access and are getting complaints from DB lag during peak usage, and i must upgrade my DB to ensure the QOS end users get from my forums. i have no intention of switching over to php/MySQL/linux formats.
i tried unsuccessfully to get MySQL to play nicely in a windows environment, and recently learned that MSSQL offers free versions, the compact and the express. trouble is, i know absolutely nothing about DB management. Access is idiot proof so i have used it as long as i could. now i find myself in a scramble to learn how to create, employ, and use a more powerful DB.
for my intended usage (running about a dozen DBs on a dedicated server), will compact or express be the appropriate version to start learning? for my intended usage, is there any benefit to downloading/installing/learning the additional tools that are also available? if so, which tools? where do i learn how to create new DBs, and what tools do i need to do this? most tech papers ive seen on MSSQL assume a prior working knowledge of MSSQL DB management and im coming into this completely ignorant but willing to dedicate myself to learning.
I'm studying some articles about DMX and i have a question. DMX statements is only for prediction?
Can i create a model and run a classification/clustering/decision tree algorithm through DMX instead of prediction?Because all the examples i found are talking about prediction.
Are there any other sources i can study, so i can have a better understanding of what DMX does?
I am using this program for the first time but as it has no direct interface on its own, I would like to ask if anyone could help me get a database up and running.Nothing fancy but simple and functional for my staff to use for basic csv searches and so forth. skywalkerza@highveldmail.co.za
Hi folks... this is part complaint, part request for help. I have an Access db that I'd rather were an sql db. Partly to learn sql and partly because all of the asp.net examples use sql and don't seem to work with access.
I installed VS2005 which includes sqlexpress, but there's no front end to it.. there's no way to "talk to" this sql2005. Can't seem to create tables, etc.
So, I installed SQL 2005 Development server... still no front end. Just some config tools that I presume I may care about some day but don't mean anything to me just yet.
I'm on XP Pro so can't install SQL 2005 Enterprise, but I discover a CTP called Management Studio Express. Well, we all know how robust CTPs are, nevertheless I install it and boy is it friendly.
At this point I feel as though I've missed The Big Thing that had I not missed I would be a lot further along.
Maybe the best way to express my question is to ask for what the heck I should have done in order to be learning sql at this point instead of having run out of time again.
Thanks for any insight.
Best,
Eric
PS... hmm, seems like this is mostly complaint... sorry.
I have a job for run DTS packages, My Job got failed due some reason,I corrected the problem a started Job again.Here problem is I for got to note the start time.Now I want find out from how long it is running or it what time it started.
Hi - I need to start from the beginning with SQL Server 7 2005 Express. I want to get financial data and perform analysis in conjunction with Excel. I have downloaded the file from Microsoft and would like a resource for getting going. What is the best way to go?
While I have been programming .Net for four years and T-SQL for about 12, I am a newbie to the whole SQL CLR thing. I have VS.NET 2003 on my machine, and I installed the client tools for SQL 2005 which also installs VS 2005 for SQL. Yet when I open VS 2005, it only has Data Analysis projects, but no Database projects. If I go into VS 2003 and try to import some of the assemblies I have found by googling 'SQL CLR' - VS 2003 won't let me add them.
Exactly what do I need to install so that I can get started? Do I need an instance of SQL server running on my machine? (I currently do not have a server running, only the client tools are installed)
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
Hello all. I have some questions that are probably pretty stupid but Ill ask anyway :)
It is important that I start down a path of understanding mssql 2000. I wanted some advice from experts as to what the best way to start would be. Is there a great book out there? Can I run a version locally that I can play with? Also, when using the database, are the commands the same for mssql and mysql? I ask because I searched for tutorials and a lot more mysql tutorials popped up.
Obviously I dont expect to become an expert in a few weeks but if I could start down the path I would be happy. Maybe learn how to do simple maintenance and management functions.
I have never used MS SQL database. I usually create my database with phpMyadmin. But my clinet doesn't have this tool. And they don't have MySQL instead they have MS SQL.
Could I create the data base with phpMyadmin and output that? Then import the data into MS SQL? There is no GUI tool on the server to create tables and data entry.
I have this simple example from which to start learning to use cursors:
USE ar GO DECLARE @mortgage INT DECLARE @getMortgage CURSOR
SET @getMortgage = CURSOR FOR SELECT Converted_Mortgage_Number FROM format_mortgage_history OPEN @getMortgage FETCH NEXT FROM @getMortgage INTO @mortgage WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @mortgage FETCH NEXT FROM @getMortgage INTO @mortgage END CLOSE @getMortgage DEALLOCATE @getMortgage GO
Although it runs, I dont see the Converted_Mortage_Number printed. What have I done wrong please?
It cause both instances on the server get restarted itself, did a search on the net and not much luck with it. Got some people saying its because of the clsuter admin can't query SQL and it just fail itself. And its using Name pipe rather then TCP/IP, so by putting an alias there will get the problem solved. But I would like to be sure. Does anyone have that issue before? And found a solution? Thanks
What is the best online documentation either on the Web or installedwithSQL Server that I could use to get started? I want to jump in andstartcreating tables, etc for applications.Also, what is the most recommended text for converting my DBAexperience from other database to SQL Server.ThanksBill.
I created a snapshot publication (the engine is SQL2005 and the compatibility is 2000). I got this message:
"The agent could not be started. An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent Error: Request to run job REPLSM-6 (from User DOMcustuser) refused because the job has been suspended. Changed database context to SM. (Microsoft SQL Server, Error: 22022)"
And when i'm trying to reinitialize the publication, I get this:
"Replication Monitor could not start the Snapshot Agent to generate a new snapshot. You must start the Snapshot Agent manually before the subscription(s) can be reinitialized..."
I need some help please. My MSDE database won't start after a reboot. This is a SurfControl MSDE database. It wasn't too healthy yesterday when the log on the SurfControl MSDE database filled so I truncated it and shrunk the file. Everything was cool until I rebooted the server today:-(
Trying to start the service it says "the system cannot find the file specified" - ok to that and it says "An error 2 (the system cannot find the file specified" occured while performing this service operation on the mssqlserver service "
sqlserver.exe -c -f gets this result
2005-11-26 20:00:03.28 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
2005-11-26 20:00:03.30 server Copyright (C) 1988-2002 Microsoft Corporation. 2005-11-26 20:00:03.32 server All rights reserved. 2005-11-26 20:00:03.32 server Server Process ID is 3636. 2005-11-26 20:00:03.32 server Logging SQL Server messages in file 'D:Program FilesMicrosoft SQL ServerMSSQLLOGERRORLOG'. 2005-11-26 20:00:03.35 server Warning: -f command line flag used...minimal server configured. 2005-11-26 20:00:03.35 server SQL Server is starting at priority class 'normal'(2 CPUs detected). 2005-11-26 20:00:03.46 server SQL Server configured for thread mode processing. 2005-11-26 20:00:03.46 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks. 2005-11-26 20:00:03.60 server MS DTC initialization skipped because of start up option. 2005-11-26 20:00:03.60 server ksconsole: Could not initialize console operation. 2005-11-26 20:00:03.61 server Error: 17822, Severity: 16, State: 1 2005-11-26 20:00:03.61 server Could not load Net-Library 'SSNETLIB'.. 2005-11-26 20:00:03.61 spid4 Warning ****************** 2005-11-26 20:00:03.61 server Error: 17059, Severity: 18, State: 0 2005-11-26 20:00:03.61 server Operating system error 0: The operation completed successfully... 2005-11-26 20:00:03.61 spid4 SQL Server started in single user mode. Updates allowed to system catalogs. 2005-11-26 20:00:03.63 server Unable to load any NETLIBs. Server shutting down.2005-11-26 20:00:03.63 spid4 Starting up database 'master'.
Any ideas? I reall need to get it working. I don't think there are any backups
I've downloaded and installed SQL Compact Edition. When I go to Access to set up a project (or to use the upsizing wizard) the software doesn't see the Server on my computer. No doubt I'm missing some very simple step. Although the compact server is installed, there's no program file to run, so I don't see how to get into the server software in order to set up a database or project that I can link to from Access.
I've also downloaded and installed Microsoft.net. As with server, the files are in place but there's no program file to execute.
OK. I am new to SSIS. I do not have control over the servers, so I have to provide a list of what features we need installed and such.
In order to use SSIS to import data files (from multiple flat file sources), what do I need installed?
I have SQL Server 2005 installed on a server. But the Business Intelligence Development Studio isn't present. Do I need to install that on the server? How about any services?
Also, can I/Should I install Business Intelligence Development Studio on my local development machine? What about licensing requirements for it?
Thank you for your help in this matter. Usually I have full control over the server and I can just poke through the install to get what I need.
How do you get started? I downloaded the SQL 7 software and the sample databases, and did the start program to run the SQL Server Configuration Manager (Local), but what do you do now?
I am trying to show my employer the benefits of using SQL SERVER (2005) vs. MS Access. My problem is I can't get past a something I believe to be simple.
I created a project in Business Solutions (I tried multiple times) - The idea of the project is to download a flat file from FTP, then pull the records into a table. I can get this to work without issue *IF* I execute this process by hand.
However, I would like to execute the project via a stored procedure. I am using
My last version of the package just has the FTP download in it (I was trying to break it down to small steps) - when executing I get an error
" Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that..."
I'm not sure if that error is important or not (I guess every error is important). However, the one that bothers me the most is
" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed"
I know the password I set in the FTP function is correct, because I can run it by hand. Can someone please offer me some direction?
