I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.
My reuierement is to execute a bcp to write the query output to a flat file. I am using the following syntax...
exec xp_cmdshell 'bcp "SELECT top 50 * tbl_xyz" queryout tbl_xyz_output.txt -c -S abc -U x -P x'
But I am getting the following error....
output --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CTLIB Message: - L6/O8/S5/N3/5/0: ct_connect(): directory service layer: internal directory control layer error: Requested server name not found. Establishing connection failed. NULL
This bcp executes perfect if I am going to call from command prmomt.
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors. Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
( @pageSize int, @previousMaxId int
)
AS /* SET NOCOUNT ON */ SELECT Top @pageSize ProductId, ProductName FROM Products WHERE (ProductID > @previousMaxId) order by ProductId RETURN
You have a store procedure and inside you make Updates or Inserts against another Server. But that name can change and I dont want to change it manually in everyplace.
Per example:
SELECT * FROM Himalaya.DBName.dbo.tblUserGroup WHERE fldUserID = 7300
I have several Selects, Updates and Inserts pointing to that server.
What I can do if I want to change from Himalaya server to another server?
CREATE PROCEDURE SelectCostumers @name varchar(100) Declare @SQL = "SELECT Id, Name FROM Costumers" AS IF (@name IS NULL) @SQL ELSE @SQL += "WHERE Name LIKE @name"
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
Hi I have a problem executing a SELECT inside a TRAN against other computer
For example: IN THE SQL Query Analizer of the COMPUTER2 1) this runs OK BEGIN TRAN SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE COMMIT TRAN 2) this runs OK SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE 3) this runs OK SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE 4) this runs bad BEGIN TRAN SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE COMMIT TRAN
The problem is that TABLE locks and it does not finish.
I've been looking for similar ERRORS in Microsoft Support but I found nothing I've uninstall and install de SQL server 2000 SP4 and the problems continues the same
 I have a range bar chart Inside a table of row group and the chart is repeatedly generate according to category ,i want to increase chart height dynamically based on category.
suppose for if category =A THEN CHART HEIGHT=10CM category =B THEN CHART HEIGHT=7CM
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value OR to do System.DateTime.Now in the application and then insert it in the table? I figure even small differences would be magnified if there is moderate traffic, so every little bit helps. Thanks.
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL )
INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I have a statement which might need a while inside of a while. The start date - end date creates one record for a record insert. I have that working. But along with producing a record for every day there might be an instance where something is dispersed 3 times a day for five days. I then need to create 3 records for every day for 15 records. This only happens on records if the daily dispersal is greater than 1. The code below works fine, but should I add a second while inside of the existing one for the @freq and increment it by one. Would an If or case inside of the while be better? Thanks
set @freq = freq in table set @nodays = datediff(day, @sdate - 1, @edate) select @nodays while @cnter < @nodays and begin --insert values
insert into PATIENT_MEDICATION_dispersal2_
values (@account_id,@caseid, @entcid, @ndcid, @sdate) Set @cnter = @cnter + 1 set @sdate = @sdate + 1
Is it possible to use IF inside a query, in the WHERE statement? I started with the query right below, but I onlye got error. After testing and rewriting a lot I ended up with the last query. But there hast to be a better, smarter, more elegant way to write this query? Any hint? ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all links from the requested category.-- ============================================= (@CategoryId int, @AdminFilter bit)AS SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId IF (@AdminFilter = 1) print 'AND Link.Hidden = @AdminFilter' ORDER BY Link.Title ALTER PROCEDURE [dbo].[LinksInCategory]-- =============================================-- Description: Return all NOT hidden links from the requested category.-- If in Administrators role the return ALL links (the hidden ones also).-- ============================================= (@CategoryId int, @AdminFilter bit)AS IF (@AdminFilter = 1) BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId ORDER BY Link.Title END ELSE BEGIN SELECT Link.Id, Link.Title, Link.Url, Link.ShortText, Link.Hidden FROM Link WHERE Link.Parent = @CategoryId AND Link.Hidden = @AdminFilter ORDER BY Link.Title END Regards, Sigurd
I have a sql statement that has several OR statements in it which work fine. It looks like bottom below.
What I need to know is can you put a IF statement in a where clause like this. Such as WHERE convert(datetime, patient_.df_admit_date, 101) > = @tdate or if patient_.dru = "yes" convert(datetime, patinet_.df_admit_date, 101) > = @tdate - 8 or
WORKIN STATEMENT select PATIENT.ACCOUNT_ID,patient_.DF_PPD_POS_NEG, PATIENT.LAST_NAME, PATIENT.FIRST_NAME, PATIENT.MIDDLE_INIT, PATIENT.OTHER_ID_NUMBER, PATIENT_.DF_ADMIT_DATE, PATIENT_.DF_PPD, PATIENT_.DF_PPD_POS_NEG, PATIENT_.DF_PPDB_DATE, PATIENT_.DF_XRAY_DATE, PATIENT_.df_ppd_read, FROM { oj development.dbo.PATIENT PATIENT INNER JOIN development.dbo.PATIENT_ PATIENT_ ON PATIENT.COMPANY_ID = PATIENT_.COMPANY_ID AND PATIENT.DEPARTMENT_ID = PATIENT_.DEPARTMENT_ID AND PATIENT.ACCOUNT_ID = PATIENT_.ACCOUNT_ID} where convert(datetime, patient_.df_admit_date, 101) > = @tdate or convert(datetime,patient_.df_ppd, 101) >= @tdate - 2 or continued!!!
I want to open a new session/connection inside the execution of a stored procedure. Is this possible ? I ask this because I need a new sesssion with its own transaction.
Hi, everyone. I was using ODBC everywhere in my code and now I'm considering using ADO in a new project. However, I don't want to throw all the old ODBC code away. Is that possible that I can use some wrapper to use ADO underneath while having a ODBC interface?
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND
I have created in my sqlserver 2005 database a stored procedure with the following code. /////////////////////////////////////////////////////////////// SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[d_sty_print_menu_per_role_per_app2] @menu_name VARCHAR(255) = NULL , @is_user VARCHAR(255) = NULL , @is_appl VARCHAR(255) = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled) AS (--Anchor. select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item , (SELECT 'N' FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name --and --PP.MENU_ITEM=tb1.menu_item ) as enabled
From sys_menu_item as tb1 where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help') And tb1.item_parent= @menu_name --Members UNION ALL select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item , (SELECT 'N' FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name -- and -- PP.MENU_ITEM=tb1.menu_item ) as enabled
from sys_menu_item as tb2 , GetHierarchy where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help') And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name )
select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled From GetHierarchy
END /////////////////////////////////////////////////////////////// So far so good. The problem is in a specific part of the sql statement (which is also part of my business logic). the following statement has a little problem.
(SELECT 'N' FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name --and --PP.MENU_ITEM=tb1.menu_item ) as enabled When I'm executing, it tells me that the Subquerry is returning more than one rows. I have tried to use TOP 1 but Sqlserver 2005 doesn't allow you to do that because you are inside a recursion. I have tried to do this
(SELECT TOP 1 'N' FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name --and --PP.MENU_ITEM=tb1.menu_item ) as enabled
But the system prevents me from doing that.
Any ideas ? How can I return only one row (I don't care which one) ?
Can I put this Sql statement in a function and then call it inside this recursion ? Is it permitted ?
I would mostly appreciated any help you can give me.
If I use an OR statement inside the WHERE clause of a SELECT, should SQL Server evaluate both side of the OR or just the left hand side if it returns TRUE?
The reason I'm asking is that I have an SP the accepts a string parameter, this param is a search condition, say a name. The param is a nvarchar and can be null. In my SP I do this:
SELECT * FROM Customer WHERE CustomerDeleted = 0 AND ( @searchText IS NULL OR CustomerID IN (SELECT ID FROM fn_GetSearchResults(@searchText)) )
The idea is that if the @searchText param is NULL then all Customers are return, otherwise the @searchText is used in a function to determine which customers match the criteria.
This only works if SQL stops evaluating the OR condition as soon as it comes accross a TRUE statement.
Hello, inside of my SP i want to execute another SP, something like: EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns). Columns it returns:QResult , Threads , Answers SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID ? or how do you get it? Patrick
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
I am using the createuserwizard, and have set up my own profile table which hold first name, last etc. In my CreateUserWizard, the field for Vendor is a dropdownlist that is to be populated from a list in a database. I set up the form without the createuserwizard, and everything went skippy, but when I placed it into the wizard, I now cannot access the control of the dropdownlist. I have tried doing.. Using SqlConnection As New SqlConnection(WebConfigurationManager.ConnectionStrings("Personal").ConnectionString)Dim VendorID As DropDownListVendorID = CType(CreateUserWizard1.FindControl("VendorName"), DropDownList) Dim MyReader As SqlDataReaderDim sel As String = String.Format("SELECT VendorID, Name FROM Vendors")Dim MyCommand As SqlCommandMyCommand = New SqlCommandMyCommand.CommandText = selMyCommand.CommandType = CommandType.TextMyCommand.Connection = SqlConnectionMyCommand.Connection.Open()MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)But when I go to databind, it says:Object reference not set to an instance of an object. VendorID.DataSource = MyReaderLine 30: VendorID.DataValueField = "VendorID"Line 31: VendorID.DataTextField = "Name"
I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated
declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid while (@@FETCH_STATUS <> -1) begin select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate) while @cnter < @nodays begin while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid ) set @fcnter = @fcnter + 1 set @erdate = @sdate
END if @hfreq = 9 begin set @fcnter = 1 set @sdate = @sdate + 2 Set @cnter = @cnter + 2 end else begin set @fcnter = 1 set @sdate = @sdate + 1 Set @cnter = @cnter + 1 end end end close dbcursor deallocate dbcursor
begin if @counter=2 update t_import_main set program2=@prog, g2=@gno,yr2=@yr, Program_code2=@pcode where uno=@oldu case when @pcode is null then Program_code2=@gno End
I get this error message Incorrect syntax near the keyword 'case'. What am i doing wrong??
