Does SQL Server Take Advantage Of 'bind Variables'
Jul 20, 2005Using prepared statements like Oracle does? This way in a high transaction
system you do not have to recompile queries every time?
ADVERTISEMENT
Bind Variables / Parameter Queries
Jan 27, 2005Hi,
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.
Profiler Trace Bind Variables
Aug 2, 2007Hi,
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.
Tuning An Application Statement With Bind Variables
Jul 20, 2005Hi gurus,I just started to look at a very slow-running SQL statementgenerated by an application (Siebel). I spooled the SQL from theapplication, replaced the bind variables by their values, and tunedfrom the Query Analyser. But after awhile, I realized that thestatement using bind variables and the same statement using the valuesinstead of the bind variables often have completely differentexecution plans! Is that normal? Can someone tell me how the SQLServer treats bind variables. Don't worry about being too technical,I'm an Oracle DBA/developer.ThanxDaniel
View 1 Replies View RelatedUrgent! Report Server Bind Dataset
May 22, 2008Hi Everyone,
I have used the StoredProcedure as shown below:
Begin
Select a.Field1,b.Field from table a, table b where a.patientid=@patID
if(@select=1)
select a.Field1, a.Field2 from table a
else if(@select=2)
select b.Field1, b.Field2 from table b
end
When I bind this storedprocedure with Dataset, all the Fields Corresponding to table a and table b are displayed in the Dataset.Finally, when i bind this Dataset with my chart, the fields corresponding to table b are not displayed in the Chart.
The reason for that may be because Report Server cannot bind with multiple Datasets? Please Clarify.
If the Report Server cannot bind with multiple datasets, then how to call different StoredProcedures (according to Criteria) in a Single Report in Report Server project?
Thanks
Navdeep
SSAS Linked Server Error: PFDBReaderOledb::Bind
Apr 29, 2008Hi,
I have set up a linked server and I am succesfully running MDX queries using OPENQUERY against an SSAS cube. However, occasionally I am getting the error:
OLE DB provider "MSOLAP" for linked server "MyLinkedServer" returned message "Internal error: An unexpected error occurred (file 'pfoledbreader.cpp', line 796, function 'PFDBReaderOledb::Bind').".
Does anyone know what might be causing this?
Advantage 6.5
Aug 11, 1998Suggest some sites to check the advantages of version 6.5 over 6.0
View 1 Replies View RelatedAdvantage Of Views
Jun 21, 2005Hi,
anyone can provide a quick link or summary why I should be using Views in MS SQL Server rather than just using SQL in my vbscript pages
Thanks!
Advantage Of Temp Table
Jul 23, 2005When I import data I first import it from a text file into a table ofit's own, then using some logic insert some of the records into apermanent table.I am considering having the table that the data from the text file isplaced in being there all the time and just clearing it out after I dothe import, or creating it and after using it then drop it, or using atemporary table.What are the advantages of a temporary table as opposed to creating aregular table and dropping it after use?
View 3 Replies View RelatedOpenDataSource Trouble With Advantage SQL
Jun 17, 2006Hi folks.I'm trying to connect to a Medisoft Advantage SQL db through SQL Serverusing OpenDataSource or OpenRowSet. I have general connections to the dbworking fine, but not with OpenDataSource or OpenRowSet.I've tried variations on:select * fromOpenDataSource('Advantage OLE DB Provider','Data Source=C:MediDataTutormwddf.add;User ID=user;Password=password;Advantage Server Type=ADS_LOCAL_SERVER;Initial Catalog=mwddf.add;')...MWPATWhich gives:OLE DB error trace [OLE/DB Provider 'Advantage OLE DB Provider'IColumnsInfo::MapColumnIDs returned 0x80040e21: [COLUMN_NAME=TABLE_CATALOGORDINAL=-1], [COLUMN_NAME=TABLE_SCHEMA ORDINAL=-1], [COLUMN_NAME=TABLE_NAMEORDINAL=-1], [COLUMN_NAME=TABLE_TYPE ORDINAL=-1], [COLUMN_NAME=TABLE_GUIDORDINAL=-1]].I've also tried:select * fromOpenrowset('Advantage OLE DB Provider','Data Source=C:MediDataTutormwddf.add;UserID=user;Password=password;Initial Catalog=mwddf.add;Advantage ServerType=ADS_REMOTE_SERVER',MWPAT)and 'Select * from MWPAT'.These last yield:[OLE/DB provider returned message: No Data Source specified]which seems closer.Can anyone help? I think this is all Advantage-specific. I've posted ontheir boards, but their not very active...Thanks.David
View 1 Replies View RelatedAdvantage Of Using Catalog Views
Apr 17, 2007Hi,
Can anyone tell the advantage of using the new catalog views over the traditional system tables which were given as compatiblity views in sql server 2005
Which Is Advantage To Develope A Report.
May 14, 2008hi all,
i need to create a report in reporting services, in reporting services there are two ways to develope a report by using "Report Server Project" and "Report Model Project". i need to know the purpose of each thing and which one will be advantage.
Difference/advantage Of Varchar Vs Nvarchar
Oct 22, 1998View 2 Replies View Related
Any Advantage Executing Packages Out Of Process On X64?
May 12, 2007On 64 bit SSIS platforms (win2k3 and vista), where the amount of virtual memory is practically unlimited, is there any advantage to running child Integration Services Execute Package Tasks out of process? The question pertains to 64 bit processes on a 64 platform, not 32 on 32, or 32 bit emulation processes on 64.
Would the answer be different based on the amount of physical memory on the machine, supposing one had a "reasonable" amount of memory on the machine (e.g. 4Gb or more )
Advantage Of Using A Data Source In A Project
Jun 18, 2007
I am new to SSIS.
I came across the following article:
http://technet.microsoft.com/en-us/library/ms139848(SQL.90).aspx
It states that:
"A data source can be defined one time and then referenced by connection managers in multiple packages. You use a data source object in a package by adding a connection manager that references the data source object to the package. There is no dependency between a data source and the connection managers that reference it."
I have created a data source (DS1) and set it to point to a database say DB1. In the connection managers area, I create a connection manager CM1 using the datasource DS1.Now I edit DS1 to point to a different database DB2. When I open CM1 however it€™s still pointing to DS1.
I guess this is because it€™s said that there is no dependency between the connection manager and data source. My question is what exactly is the advantage of using a data source?
Priya
Advantage Of Using Money Data Types
Feb 9, 2008what are the advantages of using the money data types over decimal etc?
View 6 Replies View RelatedTransact SQL :: What Is Advantage Of Foreign Keys In Database
Apr 22, 2015suppose if we do not have FK then what kind of advantage we could not avail. we can fetch data from two table by creating a relation in sql.....then why FK is required.
View 10 Replies View RelatedHow To Bind SQL Express To A Particular CPU?
Mar 15, 2006Since 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?
Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)
Sep 4, 2006 Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?
Jan 24, 2006Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
Bind A Label To An SqlDataSource
Nov 22, 2006Hi 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 1 Replies View RelatedHow Can I Bind To An SqlDataSource From Code
Feb 18, 2007My 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()
SQL2K Bind Failed
Jun 11, 2004I 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 14 Replies View RelatedHow To Pass Value To Bind Variable
Jun 19, 2008select 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
Bind Variable In CURSOR
Mar 30, 2006SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--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 OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd 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 1 Replies View RelatedSQLDataSource No Bind On Page Load?
Aug 29, 2006I 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 11 Replies View RelatedHow To Bind Data Retrived From Sql Db To DropDownLists?
Jun 20, 2007Hello 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 3 Replies View RelatedWhat All I Can Bind To My Select Query Results?
Nov 7, 2007my 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 5 Replies View RelatedHow Do I Bind A Value Returned From Sqldatasource To A Session?
Apr 22, 2008I 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>
Checkbox Changed Value Doesn't Bind
May 21, 2008The 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)
Select Statements To Bind Datagrid
Apr 24, 2004I 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?
Bind The Asp:SqlDataSource To A Label Control
Nov 12, 2005how can i display the result of an asp:SqlDataSource into a lable control.the sqldatasource returns the count for some thing ie "select count(*) as total from tbl"please help
View 1 Replies View RelatedBind 2 Tables From Different Database File.
Apr 23, 2006Hi!
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.