Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.
I have a stored procedure which is not working the way I think it
should be.
I have a CURSOR which has a variable in the WHERE clause:
DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @dbname
ORDER BY tablename
It won't return anything, even when I verify that @dbname has a value
and if I run the query in Query Analyzer with the value, it returns
rows:
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = 'Archive'
ORDER BY tablename
DB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-29
09:52:11.823
DtsAdtStdArchive_DataSourceType5175768822006-03-29
09:52:11.8702006-03-29 09:52:11.887
DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-29
09:52:12.103
I've taken out most of the guts for simplicity, but here's what I've
got:
--CREATE TABLE dbo.db_ind
--(
--db_ind_tkintIDENTITY,
-- id int NULL,
-- tablename sysname NOT NULL,
-- indid int NULL,
-- indexname sysname NOT NULL,
-- shcontig1dt datetime NULL,
-- defragdt datetime NULL,
-- shcontig2dt datetime NULL,
-- reindexdt datetime NULL
--)
ALTER PROCEDURE IDR
(@hours int
)
AS
--SET NOCOUNT ON
--SET ANSI_WARNINGS OFF
DECLARE @tabname varchar(100),
@indname varchar(100),
@dbname varchar(50),
@vsql varchar(1000),
@v_hours varchar(4),
@shcontig1dtdatetime,
@shcontig2dtdatetime,
@defragdtdatetime,
@reindexdtdatetime,
@idint,
@indidint,
@rundbcursorint,
@runtabcursorint,
@runindcursorint
DECLARE get_dbs CURSOR local fast_forward FOR
SELECT dbname
FROM db_jobs
WHERE idrdate < getdate() - 4
or idrdate is null
ORDER BY dbname
DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @dbname
ORDER BY tablename
DECLARE get_inds CURSOR local fast_forward FOR
SELECT indid, indexname, defragdt, reindexdt
FROM db_ind
WHERE dbname = @dbname
AND tablename = @tabname
ORDER BY indexname
OPEN get_dbs
FETCH NEXT FROM get_dbs
INTO @dbname
IF @@FETCH_STATUS = 0
SELECT @rundbcursor = 1
ELSE
SELECT @rundbcursor = 0
SELECT @v_hours = CONVERT(varchar,@hours)
--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====
WHILE @rundbcursor = 1
BEGIN -- db while
PRINT '============================='
PRINT @dbname
PRINT '============================='
--================================================== ================================================== =====
--================================================== ================================================== =====
OPEN get_tabs
FETCH NEXT FROM get_tabs
INTO @tabname, @id, @shcontig1dt, @shcontig2dt
IF @@FETCH_STATUS = 0
BEGIN
PRINT 'table: ' + @tabname
SELECT @runtabcursor = 1
end
ELSE
BEGIN
PRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITS
SELECT @runtabcursor = 0
end
WHILE @runtabcursor = 1
BEGIN
PRINT @dbname
PRINT @tabname
--================================================== ================================================== =====
OPEN get_inds
FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt
IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0
WHILE @runindcursor = 1
BEGIN
PRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indname
FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt
IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0
END-- 1st loop through indexes
CLOSE get_inds
--================================================== ================================================== =====
--==========
PRINT 'db.tab: ' + @dbname + '.' + @tabname
--==========
--================================================== ================================================== =====
OPEN get_inds
FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt
IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0
WHILE @runindcursor = 1
BEGIN
PRINT 'dbname: ' + @dbname
PRINT 'tabname: ' + @tabname
PRINT 'indname: ' + @indname
FETCH NEXT FROM get_inds
INTO @indid, @indname, @defragdt, @reindexdt
IF @@FETCH_STATUS = 0
SELECT @runindcursor = 1
ELSE
SELECT @runindcursor = 0
END -- 2nd loop through indexes
CLOSE get_inds
--================================================== ================================================== =====
FETCH NEXT FROM get_tabs
INTO @tabname, @id, @shcontig1dt, @shcontig2dt
IF @@FETCH_STATUS = 0
SELECT @runtabcursor = 1
ELSE
SELECT @runtabcursor = 0
END-- loop through tables
CLOSE get_tabs
--================================================== ================================================== =====
--================================================== ================================================== =====
PRINT 'Index Maintenence complete. Job report in
[DB_Rpt_Fragmentation]'
PRINT ''
FETCH NEXT FROM get_dbs
INTO @dbname
IF @@FETCH_STATUS = 0
SELECT @rundbcursor = 1
ELSE
SELECT @rundbcursor = 0
END -- loop through databases
CLOSE get_dbs
deallocate get_dbs
deallocate get_tabs
deallocate get_inds
--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====
GO
And this is what I'm getting:
=============================
Archive
=============================
(0 row(s) affected)
not getting any tables!
Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]
..
..
..
etc.
Am I missing something obvious?
Thank you for any help you can provide!!
View Complete Forum Thread with Replies
Related Forum Messages:
How To Pass Value To Bind Variable
select field1 from table1 where field1 = @p_field1 Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@p_field1". Hi, i'm getting this error, could anybody please explain, how the syntax should be.........thanks
View Replies !
Bind A Variable To A SQL Data Source For Labels, Textboxes, Etc.
I am trying to set a variable to be passed to my asp page but the value is not carrying over. I don't even know if this is supposed to work or not below: <td colspan="3" style="font-size: 10pt; vertical-align: top; color: <%=Div7fontcolor%>; background-color: <%=Div7bgcolor%>; text-align: center; width: 0px;" id="TopBox" runat="server"> The above Div7fontcolor and Div7bgcolor are variables that I set in the VB file as shown below: Dim obConnection As SqlConnection = New SqlConnection("Data Source=localhostsqlexpress;Initial Catalog=OrgBoard;Integrated Security=True")Dim obCommand As SqlCommand = New SqlCommand("SELECT Divisions.*, Dept19.*, Dept20.*, Dept21.*, ConfigDisplay.* FROM Divisions CROSS JOIN Dept19 CROSS JOIN Dept20 CROSS JOIN Dept21 CROSS JOIN ConfigDisplay", obConnection) obConnection.Open()Dim dr As SqlDataReader = obCommand.ExecuteReader() dr.Read() Dim Div7bgcolor As String = dr("Div7color").ToString().Trim()Dim Div7fontcolor As String = dr("Div7textcolor").ToString().Trim() dr.Close() obConnection.Close() The web page runs fine with no errors but the value does not carry over and change the property correctly. I am able to set the bgColor value of the TopBox within the VB file if I use "TopBox.BgColor = Div7bgcolor" but I can't set other values on my web page like the font color. If I can do this from the VB file then please correct me. Using variables within the page will allow me to set almost any value but I don't even know if what I want is possible. Any help is greatly apprectiated. Thank you, Kris
View Replies !
Cursor/variable Help
I'm not sure about this one so if someone could help I'd appreciate this. As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a) and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.
View Replies !
How To Use Variable In A Cursor
if i have the cursor cursor: cursor for select name from sysobjects where type='P' AND category='0' and wants a variable before sysobjects which equals a table name, so itll be: cursor for select name from @variable..sysobjects where type='P' AND category='0' how do i include a variable within a cursor statement
View Replies !
Set Value For Variable In A Declared Cursor
Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent
View Replies !
Using Cursor Variable As A Tablename
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns. I then need to run the following script: declare Detail_Cursor cursor for select @colname, max(len(@colname)) from @table The message I receive is "must declare variable @table". Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated. Can anyone let me know what I'm doing wrong or how I can get the same result. Thanks
View Replies !
Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!! Thanks a lot in advance Here some sample code: DECLARE @Destination char(50) DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR FOR SELECT destination from table1 OPEN AVGCallsToDest FETCH First FROM AVGCallsToDest INTO @Destination WHILE (@@FETCH_STATUS = 0 ) BEGIN exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination) select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls', avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination' from table1 (nolock) whereDATEPART(mm,adetdate) = 5 and DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1) group by DATEPART(mm,adetdate),bdur order by Month") FETCHNext FROM AVGCallsToDest INTO@Destination End CLOSE AVGCallsToDest DEALLOCATE AVGCallsToDest
View Replies !
How To Iterate Through Table Variable Without Using Cursor
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
View Replies !
Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example: declare @col varchar(50) set @col = 'Temperature' declare notifycurs cursor scroll for select @col from Table Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC (ie. exec('select '+@col+' from Table' ) but that gave me an error. Is there a way to pass in a variable for a column name for a curor select statement????
View Replies !
Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like: UPDATE tbl SET @varFieldName = @varValue The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd. I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then. To get the field name, I : SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars' Thanks, Carl
View Replies !
Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse: Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name' Open cursor1 That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
Possible To Fetch Next From Cursor Into Table Variable?
Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx
View Replies !
How To Read The Rows In A Cursor Variable
Hi, I have a dynamic query that returns its values in a cursor variable. How do I read each row from this cursor in a loop ? Eg.: use AdventureWorks go DECLARE @sqlnvarchar(4000), @paramsnvarchar(4000), @tables_cursorcursor, @db_namenvarchar(50), @table_namenvarchar(4000), @schema_namenvarchar(50); set @db_name = 'AdventureWorks'; set @schema_name = 'Production'; set @table_name = 'BillOfMaterials, Product'; set @sql = ' select a.name table_name ' + ' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' + ' on (a.schema_id = b.schema_id) ' + ' where b.name= @schema_name1 ' + ' and @table_name1 is null ' + ' order by 1; ' SELECT @params = N' @table_name1 nvarchar(3000) ,' + N' @schema_name1 nvarchar(100) ,' + N' @cursor cursor output' EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT
View Replies !
Variable Assignment In Cursor Declaration
Hi, here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL) but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help. thanks
View Replies !
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim
View Replies !
Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such: SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2' Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as: EXEC (@BuildDBPageUsed) The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed? *Thanks* for any help in this matter. Brad
View Replies !
In Cursor: Help To Perform Proper Comparison Using Variable
Hi All, I failed to find record when using variable in cursor in WHERE clause: ID is uniqueidentifier field in the table DECLARE @EncounterID uniqueidentifier ........ WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found Tried to CAST(@EncounterID as char(40)) but still no luck. I would greatly appreciate any advise hot to make it working. Thank you very much in advance Roman
View Replies !
Question About Assigning Cursor Variable Data Types...
Hi, I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype. Is there a way to do this in SQL Server 7.0 ? many thanks :)
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all. I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out. So, first things first: let me explain what I need to do. I am designing a web application that will allow users to consult info available in a SQL2000 database. The user will enter the search criterea, and hopefully the web page will show matching results. The problem is the results shown aren't available per se in the DB, I need to process the data a bit. I decided to do so on the SQL Server side, though the use of cursors. So, when a user defines his search criteria, I run a stored procedure that begins by building a dynamic sql query and creating a cursor for it. I used a global cursor in order to do so. It looked something like this: SET @sqlQuery = ... (build the dinamic sql query) SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery OPEN myCursor FETCH NEXT FROM myCursor INTO ... CLOSE myCursor DEALLOCATE myCursor This works fine, if there's only one instance of the stored procedure running at a time. Should another user connect to the site and run a search while someone's at it, it'll fail due to the atempt to create a cursor with the same name. My first thought was to make the cursor name unique, which led me to: ... SET @cursorName = 'myCursor' + @uniqueUserID SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery ... The problem with this is that I can't do a FETCH NEXT FROM @cursorName since @cursorName is a char variable holding the cursor name, and not a cursor variable. So to enforce this unique name method the only option I have is to keep creating dynamic sql queries and exucting them. And this makes the sp a bitch to develop and maintain, and I'm guessing it doesn't make it very performant. So I moved on to my second idea: local cursor variables. The problem with this is that if I create a local cursor variable by executing a dynamic query, I can't extract it from the EXEC (or sp_executesql) context, as it offers no output variable. I guess my concrete questions are: Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How? Anybody sees another way arround this?Thanks in advance, Carlos
View Replies !
Variable In CURSOR Sql Statement (was &"Please Help Me&")
Hi All, What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also). ============================================= SET @Where = '' if IsNull(@Input1,NULL) <> NULL Set @Where = @Where + " AND studentid='" + @input1 +"'" if isnull(@Input2,NULL) <> NULL Set @Where = @Where + " AND teacherid =' " + @Input2 +"'" DECLARE curs1 CURSOR SCROLL FOR SELECT firstname FROM school WHERE school ='ABC' + @where ============================================= Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ? Thanks !
View Replies !
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
Can't Use TCP => Bind Failed.
Hi All, Upon starting SQLServer2000 we get the following errors in the log. 2001-11-13 16:41:51.55 server SuperSocket Info: Bind failed on TCP port 1433. 2001-11-13 16:41:51.58 server SuperSocket Info: Bind failed on TCP port 1020. 2001-11-13 16:41:51.70 server SQL server listening on TCP, Shared Memory, Named Pipes. 2001-11-13 16:41:51.70 server SQL server listening on 127.0.0.1:1433, 127.0.0.1:1020. 2001-11-13 16:41:51.70 server SQL Server is ready for client connections We used to use TCPIP and Port 1433, but for some reason it stopped working, (well, some other app grabs that port before SQLServer gets a chance to use it). So we then changed SQLServer to use 1020. Now only named pipes can connect to the server, and 1433 nor 1020 works. Does anyone know how to stop the 'SuperSocket Info: Bind failed on TCP port XXX' error? Fred.
View Replies !
Bind Paramter
hi: I'm getting this error when trying to insert a row into a table: 999 Bind Parameter value for '?' is too big (155) table trying to insert into: row_ctr numeric(9) not null, load_idno numeric(3) not NULL , err_row char(75) null, the_db_err int NULL , the_db_err_text varchar(100) null, constraint pk_dmf_error primary key (row_ctr,load_idno) I really don't have a clue why I'm getting this. any thoughts? TIA deanna
View Replies !
How To Bind SQL Express To A Particular CPU?
Since SQL Express only runs on one CPU, is there a way to bind SQL Express to a particular CPU, or SQL Express only runs on CPU0? And in case of CPU hyperthreading, I assume that the CPU SQL Express running on will be a logical CPU, not a physical CPU, am I right?
View Replies !
How Can I Bind To An SqlDataSource From Code
My code behind file builds a select statement and I would like to fill an SqlDataSource control with it. Can some show me an example of how I might do that? Maybe something like this? Me.SqlDataSourceSearchResult.ConnectionString = "ConnectStr" Me.SqlDataSourceSearchResult.SelectCommand = "SelectStatement" gvSearchResult.DataSource = Me.SqlDataSourceSearchResult gvSearchResult.DataBind()
View Replies !
Bind A Label To An SqlDataSource
Hi all,I would like to do something I thought was simple but I can't seem to figure it out. I would like to bind the Text properties to two labels to the columns in an SqlDataSource, much the same as you can bind a listbox to a SqlDataSource.Is this possible?The reason why I'm trying it this way is because I originally used an OleDB connection and datareader, but it doesn't seem to work on our service providers server (keeps saying that it can't find the database) even though it works on the four other server's I've tried. It definitely connects to the database when I use the SqlDataSource on a listbox control, but it fails when I use the same connection string with the OleDB connection.Is this the best way to go about it, or should I persist with finding the OleDB/datareader (the service provider has been no help at all). Thanks.
View Replies !
Bind Failed On TCP Port
On SQL Cluster server I changed the IP address for the virtual sql server to diffreent ip segment. Scient then I am getting followin error on eventlog "19012 : SuperSocket Info: Bind failed on TCP port 1433." Users can connect thru named pipe not thru TCP/IP. Any help or suggestion?. Thanks Manesh
View Replies !
SQL2K Bind Failed
I tried to install SQL Server 2K on a W2K server running IIS and SSL. The SQL installation failed until I deleted the certs from my system. Now, the SQL logs say "SuperSocket Info: Bind failed on TCP port 1433". I have tried everything on the MS KB, TechNet, and MSDN sites that I could find. I have also uninstalled IIS and Certificate Services from the server. Since there are no certs left, I suspect that SQL Server is getting in it's own way on startup. Does anyone know of a way to trap/log this kind of conflict?
View Replies !
Checkbox Changed Value Doesn't Bind
The following checkbox in my FormView only records "True" to the database, even after the checkbox is unchecked. After exhausting searches for many weeks, I'm in desperate need of a resolution and would very much appreciate your expertise. From what I understand, the #Bind feature is supposed to be a 2-way binding but I don't know why it doesn't work with my checkbox controls. I know that I should use AutoPostBack="True", but when I do so, I get the error "Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control." <asp:UpdatePanel ID="udp1" UpdateMode="Conditional" runat="server"> <ContentTemplate> <asp:CheckBox ID="chkEmail" Text="Display?" TextAlign="left" Checked='<%#Bind("Email_Disp")%>' runat="server" /><br /> </ContentTemplate> </asp:UpdatePanel> <asp:SqlDataSource ID="srcProfile" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>" SelectCommand="sp_Profile_Get" SelectCommandType="StoredProcedure" DeleteCommand="sp_Profile_Delete" DeleteCommandType="StoredProcedure" UpdateCommand="sp_Profile_Update" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="UserID" SessionField="NewUserID" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="UserID" /> [snip] <asp:Parameter Name="Email_Disp" Type="Boolean" /> [snip] </UpdateParameters> </asp:SqlDataSource> ---------From DB table: Email_Disp bit--------- ALTER PROCEDURE dbo.sp_Profile_Get @UserID uniqueidentifierAS SELECT * FROM [myTable] WHERE UserID = CONVERT(uniqueidentifier,@UserID) --------- ALTER PROCEDURE dbo.sp_Profile_Update @UserID uniqueidentifier,[snip] @Email_Disp bit,[snip] ASUPDATE [myTable] SET [snip], Email_Disp = @Email_Disp, [snip]WHERE UserID = CONVERT(uniqueidentifier,@UserID)
View Replies !
How Do I Bind A Value Returned From Sqldatasource To A Session?
I have a sql data source that runs a query that returns a users data (See below). I want to set the session variable "UserID" equal to the tkinit. How do I "bind" the session to a column from this select command? I tried this Session("UserID") = ("tkinit") but that doesn't work. I can hard code a session value, just can't figure out how to "bind" it from the sqldatasource.. <asp:SqlDataSource ID="getUserID" runat="server" ConnectionString="<%$ ConnectionStrings:xxxConnectionString %>" SelectCommand="SELECT [tkinit], [tklast], [tkfirst], [tkfirst]+' '+[tklast] as fullname FROM [yyy] WHERE ([login] = @login)"><SelectParameters><asp:Parameter Name="login" Type="String" /></SelectParameters></asp:SqlDataSource>
View Replies !
What All I Can Bind To My Select Query Results?
my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader(); can we have something likeHashtable = cmd.ExecuteReader();
View Replies !
How To Bind Data Retrived From Sql Db To DropDownLists?
Hello all,I just have problems to bind the data to dropdownlists, and my code is written as: SqlConnection myConnection; SqlCommand myCommand; SqlDataReader myReader; myConnection = new SqlConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthWindConnectionString"].ConnectionString; myCommand = new SqlCommand(); myCommand.CommandText = "select PostalCode from Customers order by PostalCode asc"; myCommand.CommandType = CommandType.Text; myCommand.Connection = myConnection; myCommand.Connection.Open(); myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); DropDownList1.DataSource = myReader; DropDownList1.DataBind(); myCommand.Dispose(); myConnection.Dispose();Any idea? Thanks
View Replies !
SQLDataSource No Bind On Page Load?
I use SQLDataSource, bind to a GridView and a SelectParameter that gets it's value from a textbox. If the textbox is empty, then it returns all rows.How do I prevent the query and binding when the page first load? Thanks
View Replies !
How To Bind TextBox Controls To DataView ?
Hi, I use a filter on a dataview which has uniq rows. I need to bind several fields of the result filtered row to textboxes. Here the code : protected void Page_Load(object sender, EventArgs e) { string id = Request.QueryString["id"]; if (PreviousPage != null) { SqlDataSource dsPrev = (SqlDataSource)PreviousPage.FindControl("SqlDataSource1"); DataView dv = (DataView)(dsPrev.Select(DataSourceSelectArguments.Empty)); dv.RowFilter = "dr_id='" + id + "' "; dv.RowStateFilter = DataViewRowState.CurrentRows; txtFirstName.Text = // ??? FirstName Field txtLastName.Text= // ??? LastName Field txtAddress.Text= // ??? Address Field } } I don't know which cod to put in those lines. On the help of the dataView there is an example that use DataBindings. This example is in the DataView.RowFilter article: Text1.DataBindings.Add("Text",view,"CompanyName"); The big problem is that I don't have a method or event call 'DataBindings'. I just have 'DataBind' method or dataBinding event and they both don't support the 'Add' method. So how can I bound those controls to my filtered Dataview field value. Thanks, David
View Replies !
Bind 2 Tables From Different Database File.
Hi! I have 2 databases. One is ASPNETDB.MDF and another is PPQ_Data.MDF ASPNETDB.MDF is generated by ASP.net (VWD 2005) when I use Login control. PPQ_Data.MDF is created by me. It contains menu and order information of my website. I would like to connect this 2 databases together so that I can create a GridView table that binds 2 tables, one from ASPNETDB.MDF and another table from PPQ_Data.MDF. So, it is kind of displaying nested data. Is there anybody out there that know how to connect 2 databases together? Again, my purpose of connecting 2 databases together is to pull out a table from each database and bind the 2 tables together. thanks in advanced for any advice or articles submitted.
View Replies !
Select Statements To Bind Datagrid
I want to select 2 fields from two tables inorder to bind my Datagrid... How can I do that using SQL statement? Possible to do that with one statement? or i need to create stored procedure? Can anyone show me sample codes?
View Replies !
Bind Variables / Parameter Queries
Hi, I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached. Does anyone know how I can do this in SQL Server? For instance, I have 20,000 employees and I'm pulling info by SS#: Select * from EmpTable where SS_number = [SSN] Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset? Thanks.
View Replies !
Returning HTML Via SQL To Bind To A Gridview
So I have a stored procedure that returns rows that look similer to this <a href="~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=">DPF-13</a> Unfortunatly when it is bound to the gridview it seems to convert the value to HTML safe. The value it prints on the page is <a href="~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=">DPF-13</a> Hit quote to look at the actual values How can I fix the output to the gridview to print the actual link and the the html safe value? Can I accomplish this in SQL or am I going to have to do it all in the c# code (which will be a less clean solution and alot more work). tia
View Replies !
How To Bind Return Value From Function Call
I have a function that I need to call from an execute sql task. I want to bind the return value from the function to an ssis variable. Can someone please show me an example of what the function syntax needs to look like in order for this to work? I know that with sp's, you need to explicitly state the column names. I have tried many things without success. Thanks
View Replies !
Dynamic Column Bind (Select * From..)
I'm looking for a way to use SSRS to display the contents of a returned dataset with 1-n columns. The number of columns is unknown at design time and the datasetview is using a stored procedure with 1 parameter to return the data. Does any one know if RDL supports wildcard(*) characters for field names or if there is another method. I have this working via a web based custom aspx page but it would be VERY helpful to utilize all the document conversion features the SSRS reports provide via the web. Thanks for any help. IT Publisher
View Replies !
Bind Object To RDLC Report
Hi,all I hava a question about Bind object to RDLC Report,the RDLC like <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="DummyDataSource"> <ConnectionProperties> <ConnectString /> <DataProvider>SQL</DataProvider> </ConnectionProperties> <rd:DataSourceID>47739726-cc8c-4719-b061-c392c2cceb68</rd:DataSourceID> </DataSource> </DataSources> <BottomMargin>2.5cm</BottomMargin> <RightMargin>2.5cm</RightMargin> <PageWidth>21cm</PageWidth> <rd:DrawGrid>true</rd:DrawGrid> <InteractiveWidth>21cm</InteractiveWidth> <rd:GridSpacing>0.25cm</rd:GridSpacing> <rd:SnapToGrid>true</rd:SnapToGrid> <Body> <ColumnSpacing>1cm</ColumnSpacing> <ReportItems> <Table Name="table1"> <Footer> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox7"> <rd:DefaultName>textbox7</rd:DefaultName> <ZIndex>7</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox8"> <rd:DefaultName>textbox8</rd:DefaultName> <ZIndex>6</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox6"> <rd:DefaultName>textbox6</rd:DefaultName> <ZIndex>5</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox9"> <rd:DefaultName>textbox9</rd:DefaultName> <ZIndex>4</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.63492cm</Height> </TableRow> </TableRows> </Footer> <DataSetName>WindowsApplication1_MedcialCase</DataSetName> <Top>0.25cm</Top> <Details> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="ID"> <rd:DefaultName>ID</rd:DefaultName> <ZIndex>3</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>=Fields!ID.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="Name"> <rd:DefaultName>Name</rd:DefaultName> <ZIndex>2</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>=Fields!PatientOfMedcialCase.Value.Name.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="Name_1"> <rd:DefaultName>Name_1</rd:DefaultName> <ZIndex>1</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>=Fields!Name.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="PatientOfMedcialCase"> <rd:DefaultName>PatientOfMedcialCase</rd:DefaultName> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>=Fields!PatientOfMedcialCase.Value</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.63492cm</Height> </TableRow> </TableRows> </Details> <Header> <TableRows> <TableRow> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox1"> <rd:DefaultName>textbox1</rd:DefaultName> <ZIndex>11</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>ID</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox2"> <rd:DefaultName>textbox2</rd:DefaultName> <ZIndex>10</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>Name</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox4"> <rd:DefaultName>textbox4</rd:DefaultName> <ZIndex>9</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>Name</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox3"> <rd:DefaultName>textbox3</rd:DefaultName> <ZIndex>8</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow> <Value>Patient Of Medcial Case</Value> </Textbox> </ReportItems> </TableCell> </TableCells> <Height>0.63492cm</Height> </TableRow> </TableRows> </Header> <TableColumns> <TableColumn> <Width>5.33333cm</Width> </TableColumn> <TableColumn> <Width>8cm</Width> </TableColumn> <TableColumn> <Width>5.33333cm</Width> </TableColumn> <TableColumn> <Width>5.33333cm</Width> </TableColumn> </TableColumns> <Height>1.90476cm</Height> </Table> </ReportItems> <Height>5cm</Height> </Body> <rd:ReportID>a69b2d8e-258a-4212-8d4a-c67c96055732</rd:ReportID> <LeftMargin>2.5cm</LeftMargin> <DataSets> <DataSet Name="WindowsApplication1_MedcialCase"> <rd:DataSetInfo> <rd:DataSetName>WindowsApplication1</rd:DataSetName> <rd:ObjectDataSourceType>WindowsApplication1.MedcialCase, WindowsApplication1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</rd:ObjectDataSourceType> <rd:TableName>MedcialCase</rd:TableName> </rd:DataSetInfo> <Query> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> <CommandText /> <DataSourceName>DummyDataSource</DataSourceName> </Query> <Fields> <Field Name="ID"> <rd:TypeName>System.Guid</rd:TypeName> <DataField>ID</DataField> </Field> <Field Name="PatientOfMedcialCase"> <rd:TypeName>WindowsApplication1.Patient</rd:TypeName> <DataField>PatientOfMedcialCase</DataField> </Field> <Field Name="Name"> <rd:TypeName>System.String</rd:TypeName> <DataField>Name</DataField> </Field> </Fields> </DataSet> </DataSets> <Width>24cm</Width> <InteractiveHeight>29.7cm</InteractiveHeight> <Language>en-US</Language> <TopMargin>2.5cm</TopMargin> <PageHeight>29.7cm</PageHeight> </Report> the object I bind to the Report like public class MedcialCase { private Guid iD; public Guid ID { get { return iD; } set { iD = value; } } private Patient patient; public Patient PatientOfMedcialCase { get { return patient; } set { patient = value; } } } public class Patient { private string name; public string Name { get { return name; } set { name = value; } } } and the code bind object to report like MedcialCase medcialCase = new MedcialCase(); medcialCase.ID = Guid.NewGuid(); Patient patient = new Patient(); patient.Name = "Michael"; medcialCase.PatientOfMedcialCase = patient; this.MedcialCaseBindingSource.DataSource = medcialCase; this.reportViewer1.RefreshReport(); I choose the MedcialCase.ID and MedcialCase.PatientOfMedcialCase.Name to show, but when the report was run ,,just MedcialCase.ID has been shown, the text of MedcialCase.PatientOfMedcialCase.Name is "=Fields!Name.Value" I modify it to "=Fields!PatientOfMedcialCase.Value.Name.Value" and run it ,the "#Error" show in the text of Name anyone can tell me why?and how to bind object like MedcialCase to the Report? thanks!
View Replies !
Profiler Trace Bind Variables
Hi, Could anyne help me on which options in Profiler could be used to trace the bind variables in DML? Given a table, X (col1 number, col2 varchar(10)), where col1 is primary key. Example DML statements: 1) insert into X values (@parameter1, @parameter2) 2) update X set col2 = @parameter2 where col1 = @parameter1 3) delete X where col1 = @parameter1 where @parameter1 and @parameter2 are bind variables. Can Profiler be configured to log the actual values of @parameter1 and @parameter2 in the example statements in trace log? Thanks a lot.
View Replies !
Any One Tell Me The Best Process To Bind The Data To Datagrid?
Hi, Any one tell me the best process to bind the data to datagrid?I am using this method,it is taking lot of time to fill data to datagrid. conn = new SqlCeConnection("Data Source=\sample.sdf; Password =''"); dt = new DataTable(); da = new SqlCeDataAdapter(Quary, conn); da.Fill(dt); DataaGrid1.Datasource=dt; Any one tell the best method? Regards, venkat.
View Replies !
|
TRACKER
