Can anyone answer ths question please.
If your page is running slow what sould u check specifically with the store procedure. What is it tht you might think is giving a problem. or how would u go about see what making your stored procdure run slow.??
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
Good fortune came my way this morning I was invited to an interview for a Database Administrator/IT Support position with a local charity.Although my 2 years experience with IT has been mainly supporting desktops Win 98,2K ,XP in a workgroup together with training users on MS Office Professional I decided to go for the database admin/IT support because I want to broaden my experience with IT applications. The objective being to become more marketable to employers.While I definately meet most of the IT support/training requirements for the post a few requirements are making me nervous because I have no experience in them since I believe I may need programming and or other skills which I don't have. The accountabilities that make me nervous are the following :
1. Devise and implement solutions to corrupted data and software inconsistencies or failures of the housing database.
2. Devise and implement solutiions to corrupted data and software inconsistences or failures of the management information system.
3. Devise and deliver reports from the housing database or MIS in order to satisfy the requirements of managers and external regulatory bodies.
4. Test and validate new versions of software.
Do I need database programming skills for these requirement?If not what skiils do I need and what would be the best way of answering quetions about these requiremnts during interview since I have no database admin experience ?
Looking forward to your thoughts on this.
Eastfield
Hello everybody,
I'm new to SQL Server,I have a interview tomor,could anybody give me a website from where i can get questions on SSRS.
Thanks in advance
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
Help please? Does anyone have any suggestions for me in regards to my first interview for a SQL DBA position. I will be meeting with the technical team including developers and IT professionals as well as the hiring manager. I want to make sure I as the right questions and have the correct answers. This is my first interview for this type of position. What are good questions? Any help or advice would be greatly appreciated.
Jerry
Hi,
I was wondering if some one would be able to help me?
I'm interviewing a SQL Admin soon and I'd like to ask him some
SQL 7 questions to test his knowledge. He has less than a year
of experience. Does anyone know any good questions I could ask him?
Please e-mail them if possible. I appreciate any help that could be provided.
Thanks,
Jim
here is a question asked in a interview:
u created the sales database with the initial size of hte transaction log of 50 mb with autogrow, but no autoshrink
u do not make any backup and donot set the truncate log on check point option to on.
what will be the transaction log size after the next check point has passed?
How many tables can be updated with a single update ?
View 14 Replies View Relatedwhen does sql use update lock?
View 1 Replies View RelatedHello
Can any one help me for below question....
" write a query using FOR UPDATE syntax "
Thanks
Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
Hi,
I know sql and Dataware housing.I got a job offering for sybase sql but I don't anything about sybase.Will it be in programming in Actually what we will do in sybase? What will be the nature of work? and I just want to know what type of question will be asked ? Please do reply.
Regards,
Raji
hi,
i have been attending interviews and jotting down a couple of questions that were asked to me. could someone please answer to these..it would be of gr8 help..
Q 1> give example of situation when & why u would use clustered and unclustered index. not the definition a practical example.
Q 2> a website has a lot of users using it and the pages are loading very slowly what would you check..
i said would look for deadlock , the next question how would you look for dead lock?
i answered would check on the stored procedure on that page, question what if there are multiple stored procs then what would you do?
Q 3> what if u have a dts package and execute SQL task and there is a stored proc that is doing inserts on a table and that table has triggers on
it for insert, delete etc.so would the triggers also be executed with the dts package run?
thanks.
Dear All,
Could anyone guide me to prepare for the Interview in SQL SERVER 2005.
I have studied the relevant things from background knowledge from the SQL Server books. But is there any specific areas in which I have to concentrate for the sake of Interview?
Thanks in Advance.
My boss asked me to interview 2 people on Monday for a programmer/developer position. They say they are gurus, but my boss wants me to find out for sure. I thought a question about bitwise operations would be good, maybe one about semi-joins. Are there any questions you can think of that will spot the phony pretty quick?
Thanks,
Jim
Hi to all. Recently i attended an interview .I have so many doubts ,could any one tell me about these questions.
1.) How to identify a query which is used by end user with out profiler. (the exact query)
2.) my tempdb is full what to do.
Then i said i will restart the server
then he asked My company is not allowing to restart the server.then what we can do?
3.) Transaction log is full what to do ?
4..How can we improve the performance of database.
5..how to rebuild the master database if it is corrupted.
6.. what new features are introduced in log shipping in 2005.
7..how can we give a permission to a userto use only profiler. he is not allowed to do any operations
8..after finding long running queries the next stop what we can do?
9..what i can do if my secondary database is no in sync with primary database in log shipping.?.
10..if the quereader agent fails will the transactional replication completely fails or partially fails.
11..how to replicate identity column in merge replication?
Waiting for ur replies,
Sripathi
DBA Interview Questionshttp://quickdba.blogspot.com/2006/0...-questions.htmlVisit for more Articles
View 3 Replies View Related
I've met an interview question. How to do when you need to add an normal column to a very large table? what should you do carefully?
Thanks.
I have been asked by a person who is going to interview me to supply this informationl;
'Come prepared to be able to talk me through how to set up an SQL driven .NET website on a server.
You are given a webroot folder containing root files such as the web.config and all the files for a .NET website and a data folder which contains an .mdf and .ldf files.
Talk me through what each of these folders contain and what you would do with them in order to set up the website on a server such that you would be able to open a browser type the URL you have configured and visit that website.'
I come from a Cisco background and no nothing about what she is asking. Is it unreasonable of me to expect to be able to gain this information in 2 days? Can you tell me the best place to look for this knowledge?
Thanks for any help.
hi,
pls help me ,i am going to attend the interview for SQL DBA pls give me a some tips.
Hi,
I will be going for an interview and I heard that the interviewer usually ask one question which is "if a table contains only one field in which normal form it lies.". What I wanted to know is if it is normalized or not if normalized then in which normalized form it will lie and if not lie in any normal form then why?
I would also appriciate if someone can give me some good source for that as well.
Please treat it as urgent as my interview will be day after tomorrow.
Thanks and best regards,
Suggest me for SQL Server 2000/2005 Interview & Practical Preparation... 1) FAQ2) Interview Questions.3) Books.4) Tutorials.5) Others.
View 2 Replies View RelatedAny suggestions for some really juicy questions to ask contractors in an interview scenario?? What are your best questions any further ideas would be nice to hear. Cheers.
View 1 Replies View RelatedAnyone know the answer to this? Looks an awfull lot like a trick question. How long will it take to create a non-clustered index comprised of 5 columns with each column being 50 bytes wide? The table has 6 million rows and an average row size of 2000 bytes. The hard drive system is comprised of 5 disks, which spin at 15,000RPMs and are setup in a RAID 5 configuration
View 7 Replies View Relatedi need some help. im going back for second interview for dba job and l am been told that there's going to be a test. Not writing but just task to perform. what should i expect? i want to be ready?
thanks for hint