Hi all,
I am trying to write a stored procedure, which has two insert statements.
the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.
Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?
thanks for your help!
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
Hi plz read below is my scenarioI have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.Thanks in advance.bye bye.happy coding!!!
I've few questions regarding SQL Server. Appreciated, If any one out there help me with it.
I would like to do T-SQL Programing and would like to know some good book. I'll mostly be doing Stored procedures,Triggers and views programming. Any advice?
On my sql server I've 10 logins. But, I cannot view these logins from the Enterprise GUI. But, When i run a query against the syslogin table, those all 10 logins shows up. Why is that?
I'm trying to construct the following SQL statement but am missing something somewhere.
I get a syntax error with the 1st FROM highlighted.
If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.
I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.
SELECT Type, Sum(Volume) AS Volume, TotalVolume FROM >>>>>>>>>>this following bit works............ (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Type, VotingUniverse_TopFirmsVolumeByType.Volume FROM VotingUniverse_TopFirmsVolumeByType WHERE (((VotingUniverse_TopFirmsVolumeByType.Volume) In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume FROM VotingUniverse_TopFirmsVolumeByType ORDER BY Volume DESC))) ORDER BY VotingUniverse_TopFirmsVolumeByType.Volume;) >>>>>>>>>>>end of bit that works INNER JOIN VotingUniverse_TotalVolumeByType ON Type = VotingUniverse_TotalVolumeByType.Type GROUP BY Type, TotalVolume;
I have a xxx.exe running on SQL 2000 server as a sql job every hour. For some reason this job sometime just hung in the middle and never finish. But if I stop it and rerun it, it will be down within 10 seconds. I am wondering if we can set up something so that the job aborts automatically if it ran more than 10 minutes.
We are running Sql 6.5 sp5 on a Pentium II 350 Mhz with 512 Mb. RAM, 1 ScSi 4 Gb hd, one 8Gb ScSi hd and 2 IDE hd (13,6 Gb and 8Gb) computer running windows NT 4.0 sp 6 without any problems. Now have bought a new computer. The new one is a Pentium III 733 Mhz 512 Mb Ram with 2 ScSi ultra wide 2 18Gb each and one 40 Gb Ide Hd. We have installed windows NT 4.0 sp 6 and Sql 6.5 sp 5. We have restored our database to make some tests to it. The database is 7 Gb big. The results of our test are that the old computer is between 20% and 30% faster than the new one when doing sql instructions and using the database even knowing that the hd´s of the new computers are 4 times faster than the old ones.
Would you please give us any help or clue that would make the new computer go faster? We know that upgrading to sql 7 will work but we need first to make this computer work better with the 6.5. Thank you.
I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”. Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
I have a client requirement. We are integrating two applications. So the problem is that, whenever some updates (meaning to say "create" , "delete" , "update") happens in the SQL DB Tables, i needed to track that data and send it to a different system.
What I have thought abt is that if SQL could generate a file with all the values deleted/updated/created in the DB, then I could take those values and do the needful. Please help me..
Hello guys, I'm new to the forum and to MS SQL 2K. I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:- This is my input table TableA ID A B C ------------------------ 1 jk kl bj 2 sd we op 3 io po kl 1 ui gh ew 2 kl re op 1 qw kj nn
My output table should look like this TableB ID A1 B1 C1 A2 B2 C2 A3 B3 C3 ----------------------------------------------------- 1 jk kl bj ui gh ew qw kj nn 2 sd we op kl re op 3 io po kl
Please help me on how to create my output. Thanks in advance,
Columns PartnerId: int NativeId: int FirstName: nvarchar(50) LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId, 2) if duplicates exists in the above condition i should select top 1 (first record)
Hi, I have been SQL developer for past 2 yrs, want to get into Administration(DBA) can any one suggest me some good articles on net and good books for this.
Hey all, I need some help to build some data bases and maybe some morestuff. Please reply only if you can do it volunteerly or very cheap please;but there is incentives in time..TIA
I have a tableCreate Table Payments {paymentid int,customerid int,amount int,date datetime}What I want is the sum of the amounts of the last payments of all customers.Now the last payment of a customer is not necessarily the one with thehighest paymentid for that customer BUT it is the one with the highestpaymentid on the MOST RECENT date. We dont keep the time part just the dateso if there are more than 1 payments of a customer on a date ( and there aremany such cases ) only then the paymentid decides which is the last payment.Further the last payment may be the last as of today but I may want to findthe sum of all the last payments upto say March 1, 2003or any date. My own solution is too slow even it is correct.SELECT SUM( AMOUNT )FROM PAYMENTS AS P1WHERE PAYMENTID =( SELECT MAX( PAYMENTID ) FROM PAYMENTS AS P2 WHERE P1.CUSTOMERID =P2.CUSTOMERID AND DATE =( SELECT MAX(DATE) FROM PAYMENS AS P3 WHERE P3.CUSTOMERID = P2.CUSTOMERIDAND DATE < #9/8/03# ))What would be the most efficient solution to this.Both in SQL Server and in Access 2000thx in advance
I am using the following T-SQL to pull records from a given table that have a start time between 6:59:59PM and 7:00:00AM. However, it's not working. The SQL I'm using is listed here. What am I doing wrong?
select RecNum, convert(varchar(8), StartTime, 8) as Start_Time from TableA
where convert(varchar(8), StartTime, 8) between '18:59:59' and '07:00:00'
By the way, there are a multitude of records that I'm looking for in the table.
I need some Help with VSA in SSIS . Whenever i open VSA for scripting , it shows the following validation errors.
Warning 1 The dependency 'EnvDTE' could not be found. Warning 3 The dependency 'Microsoft.SqlServer.DTSRuntimeWrap' could not be found. Warning 5 The dependency 'Microsoft.SqlServer.msxml6_interop' could not be found. Warning 4 The dependency 'Microsoft.SqlServer.PipelineHost' could not be found. Warning 6 The dependency 'Microsoft.SqlServer.SqlTDiagM' could not be found. Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found. Warning 7 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
I am able to locate these dependencies in the c: programfilesmicrosoftsqlserver90SDKAssemblies.
APPLICATION SUMMARY * Online only application. * Trust url parameter is set. ERROR SUMMARY Below is a summary of the errors, details of these errors are listed later in the log. * Activation of C:UserspmohanDesktopReportBuilder(2).application resulted in exception. Following failure messages were detected: + Downloading file:///C:/Users/pmohan/Desktop/ReportBuilder.exe.manifest did not succeed. + Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. + Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. + Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
COMPONENT STORE TRANSACTION FAILURE SUMMARY No transaction error was detected.
WARNINGS There were no warnings during this operation.
OPERATION PROGRESS STATUS * [4/3/2008 3:25:51 PM] : Activation of C:UserspmohanDesktopReportBuilder(2).application has started. * [4/3/2008 3:25:51 PM] : Processing of deployment manifest has successfully completed. * [4/3/2008 3:25:51 PM] : Installation of the application has started.
ERROR DETAILS Following errors were detected during this operation. * [4/3/2008 3:25:51 PM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype) - Downloading file:///C:/Users/pmohan/Desktop/ReportBuilder.exe.manifest did not succeed. - Source: System.Deployment - Stack trace: at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles() at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState) at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation) at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, IDownloadNotification notification, DownloadOptions options, Uri& appSourceUri, String& appManifestPath) at System.Deployment.Application.ApplicationActivator.DownloadApplication(SubscriptionState subState, ActivationDescription actDesc, Int64 transactionId, TempDirectory& downloadTemp) at System.Deployment.Application.ApplicationActivator.InstallApplication(SubscriptionState& subState, ActivationDescription actDesc) at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut, String textualSubId, String deploymentProviderUrlFromExtension) at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state) --- Inner Exception --- System.Net.WebException - Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. - Source: System - Stack trace: at System.Net.FileWebRequest.EndGetResponse(IAsyncResult asyncResult) at System.Net.FileWebRequest.GetResponse() at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next) --- Inner Exception --- System.Net.WebException - Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. - Source: System - Stack trace: at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint) at System.Net.FileWebRequest.GetResponseCallback(Object state) --- Inner Exception --- System.IO.FileNotFoundException - Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'. - Source: mscorlib - Stack trace: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync) at System.Net.FileWebStream..ctor(FileWebRequest request, String path, FileMode mode, FileAccess access, FileShare sharing, Int32 length, Boolean async) at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
COMPONENT STORE TRANSACTION DETAILS No transaction information is available.
into a 1-column recordset containing 2 rows with the values 'Martin' and 'Cyril'
Here's the nearest I have got:
Code Block declare @pElementListXML XML set @pElementListXML = ' <ElementList> <Element>Martin</Element> <Element>Cyril</Element> </ElementList>
' ;WITH nodes_cte as ( SELECT [c].[value]('/', 'NVARCHAR(40)') Element FROM @pElementListXML.nodes('/ElementList/Element') T(c) ) SELECT * FROM nodes_cte
Hi everyone, I€™m pursuing a fast deployment for SSIS. I mean, we€™ve got ten ASP/VB6 developers which often doing some DTS stuff by mean, of course, Enterprise Manager.
Now, they€™d have to handle the same but with SSIS. I don€™t have very clear whether only is necessary install them SSIS component or it€™d requires something else. Framework 2.0 too??
We want to create an Internet sealing system. The web server where the site is located isn€™t owned by our company however the SQL server that contains reservation data is in our Local Area Network. In the beginning, due to security reasons, we considered to set another SQL Server in our DMZ and update our internal SQL Server through Replication. We€™ve been highly recommended to use Web Services instead of Replication. Replication has been let out; however, I€™m thinking of setting a SQL Server in the DMZ which only has stored procedures that update our Internal database (The database which contains reservation data). What€™s your opinion about this? I know that Web Services is the best choice but I would like to know if it€™s a good alternative.
I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user?
The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.
Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.
We want to create an Internet sealing system. The web server where the site is located isn€™t owned by our company however the SQL server that contains reservation data is in our Local Area Network. In the beginning, due to security reasons, we considered to set another SQL Server 2000 in our DMZ and update our internal SQL Server through Replication. We€™ve been highly recommended to use Web Services instead of Replication. Replication has been let out; however, I€™m thinking of setting a SQL Server in the DMZ which only has stored procedures that update our Internal database (The database which contains reservation data). What€™s your opinion about this? I know that Web Services is the best choice but I would like to know if it€™s a good alternative.
Hello geeks, I am new to database thing, wanted to know that does MS reporting services 2005 works with SQL 2000..? if yes than can any one please give me reference link. I appreciate for the efforts to be taken by you people.
Hi I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user? What about the case where I reaches let's say 1000 users in the one user per database case, it would be extremly difficult to have an overview of the databases or what? The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom. Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P. Regards Joachim
Hi, I have a simple problem that I need a quick solution for. I hope someone can help.I have a list of email addresses that contain many different email providers (hotmail, yahoo, gmail, etc)What I'm trying to do, it select all emails that are not from a select list of providers...Here is the code I have so far... (this isnt giving me what I want)SELECT EmailAddress as Email FROM TBL_EmailAddresses WHERE EmailAddress <> '%aim.com%' OR EmailAddress <> '%hotmail.com%'OR EmailAddress <> '%msn.com%'OR EmailAddress <> '%yahoo.com%'OR EmailAddress <> '%gmail.com%'OR EmailAddress <> '%aol.com%'I want to select all emails that are not of the above types. Thanks in advance.
Hi everyone, My hoster hosts asp.net but does not yet support sql 2005 only sql 2000 and access I want to use either of the starter kits. I am confused on what is needed to make the changes to make either sql 2000 or access work. I know enough that the connection strings will need to change my concern is code. Is there strings that I will need to find and change in the application. I sure would appreciate any and all advice. Thank you for your help. DKB
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hello Dear Forum! I am new to this web. I am now days working with .NET Tecs especailly ASP.NET. I have developed a small school system in which i used SQL MDF file. I want to know about its approach if they want to make it online. and one more question, is MDF file is best for more than 10,000 Students? Can i convert the MDF into normal table format like SQL Server 2005 , i am using SQL Server Express. Take Care.Wating for reply. Developer
hi everyone, i've added 2 tables one is on adding, the other one is on returning. but when i return, the dropdownlist keeps displaying those which are returned already. it shouldnt display because i've already returned. here are the codes that i add, "INSERT INTO record VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + toName + "', '" + toRemarks + "', '" + purpose + "', '" + timeEntered + "', '" + dateEntered + "', 'Out')" here are the codes that i return "INSERT INTO returnRecord VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + timeEntered + "', 'In')" here are the codes that i wanna display in the dropdownlist which i have set the status = IN. means they have returned already so it should display. this is wad i tried what its wrong ----> "select a.manifestNo from record a, returnRecord r where a.status = 'Out' And r.status = 'In'"
my dropdownlist cant retrieve those records that are already returned. i suspect is the select statement that is wrong. but i cant figure out how. please help me!
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'. This is my sproc ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT nvarchar(max) If @ClientId IS NULL Begin Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT)
