Hi to all,
Is It possible to use dynamic qyery inside a function in sql server.
For Example:
Create function fn_Test
Returns Table
As
Return sp_ExecuteSql 'SELECT * FROM EMP'
Like this.
With regards
Amjath
When I run the query below on my DB, it gives the following error:
Server: Msg 8114, Level 16, State 5, Procedure partstlist ...Error converting data type varchar to float.
Only after I replace the " + @d + " section with a float numnber such as 39020.5 does it work fineIt's reported that my temporary table named #tbl doesn't exist within the scope of the environment that the EXEC(UTE) command operates.Someone said that I couldn't combine dynamic SQL and temporary tables or table variables...and that I would have to use a global temp table "##tbl" or a permanent table.
So here I have appearantly a "DYNAMiC SQL & TEMPORARY TABLE" problem. That's sure.
But I don't have much knowledge and experience with SQL tables and have difficulty forming a global temp table "##tbl" or a permanent table.and I have to employ the datetime variable dynamically @d inside the Dynamic SQL string.
So what kind of a code should I use here to properly combine Dynamic SQL string with my dynamic datetime variable? I'd be grateful if you could post your code suggestions so that I can try them on my DB ..
alter PROCEDURE partslist@no INT,@dt DATETiMEASDECLARE @s VARCHAR(5000)DECLARE @d floatSET @d = CONVERT (float, @dt2)create TABLE #tbl(pageindex int IDENTITY(1,1) PRIMARY KEY , prt_name varchar(50), prt_country varchar(50) , prt_date smalldatetime, partID int , prt_cat varchar(50),prt_product_type nvarchar(10), producer_company nvarchar(50), producer_city nvarchar(50), prt_released DATETIME, dvalue float, datevalue varchar(26) )BEGiNSET ANSI_WARNINGS OFFSET ROWCOUNT @noSELECT @s = 'INSERT INTO #tbl SELECT prt_name, Countries.Val AS ''prt_country'' , prt_date, partID , partsCategories.Val AS ''prt_cat'' , partsProductTypes.Val AS ''prt_product_type'' , producers.producer_company AS ''producer_company'' , producers.producer_city AS ''producer_city'' , prt_released , CONVERT (float, prt_released) , CONVERT (varchar(26) , prt_released, 109) FROM producer_parts JOIN partsProductTypes ON (producer_parts.prt_product_type = partsProductTypes.ID) JOIN partsCategories ON (producer_parts.prt_cat = partsCategories.ID)JOIN Countries ON (producer_parts.prt_country = Countries.ID) JOIN producers ON (producer_parts.producerID = producers.producerID) WHERE prt_visible = 1 AND CONVERT (float, prt_released) > ' + @d + ' AND CONVERT (float, prt_released) < 39025.5ORDER BY prt_released DESC SELECT * FROM #tbl'EXEC (@s)SET ROWCOUNT 0ENDGO partslist 10 , '10.10.2006 10:10:10' -- This is just to test the SP.
This code may not be syntatically correct, it's a re-edited version of my code which works fine in my DB.
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working...
Thanks
______________________________________________________________________
set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey
from rt_'+@platform+'_CallLegs
where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5))
exec @sql
return @CallLegKey
Hello,I need to have a stored procedure, which performs sorting. Something likethis:CREATE PROCEDURE procname@sortby varchar(30)ASBEGINSELECT some, columnsFROM some_tableORDER BY @sortbyEND(of course, i know this won't work, but it gives the idea of what i mean)Is there a possibility to write a procedure which behaves like that? It isimportant for me not to have multiple procedures just for different sortingcriteria...Thanks,Mike
View 3 Replies View RelatedJust 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.
it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.
Any Help appreciated.
Hi All!
I have a specific requirement.
I have to generate a report on the fly.
The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more.
From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly.
So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time.
I hope i have explained very clearly.
I would be grateful for your contributions.
Thanks
hello,..
please take a look and tell me what's wrong.
*** declare @myfine money
select @myfine=sum(
select fp.total_fine from final_payment fp,inserted i
where fp.member_no=i.member_no and fp.return_complete=0)***
i have final_payment table and everything.I'm creating this syntax in an insert trigger on my final_payment table. will @myfine value be the sum of total_fine where the conditions are true?please help
truly,
JC
Hey Guys, I am creating a scalar function for the first time. I have googled, ck'd your FAQ's, looked 2 text books but cannot find the answer to my questions.
Is the IF statement used in the Scalar function like it is in the stored procedure. In other words does it have to return a Boolean? I want to perform a loop after I call the function, do I have to use a Cursor? Or can I code it like a VB function with a FOR/DO While? Also I can call this UDF from my Stored Proc, right?
Here is a quick synopsis of what I have been assigned to do:
--- Calculate Run Dates
--(start from Previous Saturday subtract 41 days
-- if the 41st day does not fall on a Saturday (day = 7)
-- keep subtracting days until Day=Saturday. Add 6 days to make
-- END date the following Friday)
Thanx,
Trudye
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
I use the following code to fill the missing leading zero from datepart sys function. Is there another way simple to get the leading zero if month < 10 and/or if day < 9?
-D
--++++++++++++++++++++++++++++++++
declare @currentYYYYMMDD char(8)
declare @currentYYYY char(4), @currentMM varchar(2), @currentDD varchar(2)
select @currentYYYY = cast(datepart(yyyy, getdate()) as char(4))
select @currentMM = cast(datepart(mm, getdate()) as varchar(2))
select @currentDD = cast(datepart(dd, getdate()) as varchar(2))
print '@currentYYYY: ' + @currentYYYY + ' @currentMM:' + @currentMM + ' @currentDD:' + @currentDD
select @currentMM = case when @currentMM < 10 then '0' + @currentMM
else @currentMM end
select @currentDD = case when @currentDD < 10 then '0' + @currentDD
else @currentDD end
print '@currentYYYY: ' + @currentYYYY + ' @currentMM:' + @currentMM + ' @currentDD:' + @currentDD
select @currentYYYYMMDD = @currentYYYY + @currentMM + @currentDD
print '@currentYYYYMMDD: ' + @currentYYYYMMDD
All,
I need to use ISNULL function in a Conditional split transformation. Data will be split based on the ISNULL function. ISNULL( col) can get all the null records, How to get the not null records? ISNULL(col) = ��false�? doesn��t work.
Thanks in Advance
Hi all!
I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:
SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )
But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):
CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
GO
INSERT INTO tUser VALUES (111, 'Me')
INSERT INTO tAcctGroup VALUES (1, 'NY')
INSERT INTO tAcct VALUES (11, 1, 'New York City')
INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
INSERT INTO tAcctGroup VALUES (2, 'GA')
INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
INSERT INTO tAcct VALUES (22, 2, 'Savannah')
INSERT INTO tAcct VALUES (23, 2, 'Augusta')
INSERT INTO tAcctGroup VALUES (3, 'TX')
INSERT INTO tAcct VALUES (31, 3, 'Dallas')
INSERT INTO tAcct VALUES (32, 3, 'Houston')
INSERT INTO tAcct VALUES (33, 3, 'El Paso')
INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
INSERT INTO tAcctGroup VALUES (4, 'CA')
INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
INSERT INTO tMapUserToGroup VALUES (111,2)
INSERT INTO tMapUserToGroup VALUES (111,4)
GO
CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50))
AS
BEGIN
INSERT INTO @tAcct
SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID
RETURN
END
GO
I know that I can do:
SELECT * FROM TestDB.dbo.ufnGetAcctList(4)
But I want the equivalent of:
SELECT AcctID, AcctName FROM tAcct
WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)
Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be:
AcctID AcctName
-----------------------------
21 Atlanta
22 Savannah
23 Augusta
41 Los Angeles
42 San Francisco
Any thoughts?
Thanks in advance for your help.
Cat
Hi all
As following I show my sql server query.Please just look at the blue code.How can I add a statement to do not read the code if value received is null, i.e., do not add INNER JOIN stm.Thanks a lot
string strCmd = "SELECT "; strCmd += " Codigo_cotacao as 'Cód. Proposta', "; strCmd += " Cod_empresa as 'Cód. Cliente', "; strCmd += " Nome_empresa as 'Nome Cliente', "; strCmd += " Negocios_atividades_propostas.Id_atividade_proposta as 'Cód. Atividade', "; strCmd += " Nome_atividade_proposta as 'Atividade', "; // add something here if Ramo_cotacao is null and not read the next line strCmd += " Negocios_ramos.Cod_ramo as 'Cód. Ramo', "; strCmd += " convert(varchar,Data_cotacao,103) as 'Data Proposta', "; strCmd += " convert(varchar,Vigencia_cotacao_inic,103) as 'Iníc. Vigência', "; strCmd += " convert(varchar,Vigencia_cotacao_fim,103) as 'Térm. Vigência', "; strCmd += " Nome_status as 'Status', "; strCmd += " NVIdas_cotacao as 'Núm. de Vidas', "; strCmd += " Premio_cotacao as 'Prêmio Estimado', "; strCmd += " Nome_canal as 'Canal', "; strCmd += " Nome_corretor as 'Corretor', "; strCmd += " Nome_pac as 'PAC', "; strCmd += " Negocios_gerentes_canais.Nome_gerente as 'Gerente Canal', "; strCmd += " Negocios_gerente_beneficios.Nome_gerente as 'Gerente Benefícios', "; strCmd += " Nome_filial as 'Filial', "; strCmd += " Nome_regiao as 'Região', "; strCmd += " Nome_consultor as 'Consultor' "; strCmd += " FROM Negocios_cotacoes "; strCmd += " INNER JOIN Negocios_empresas ON Cod_empresa = Empresa_cotacao "; strCmd += " INNER JOIN Negocios_atividades_propostas ON AtivProp_cotacao = Negocios_atividades_propostas.Id_atividade_proposta "; // add something here if Ramo_cotacao is null and not read the next line strCmd += " INNER JOIN Negocios_ramos ON Negocios_ramos.Cod_ramo = Ramo_cotacao "; strCmd += " INNER JOIN Negocios_status ON Id_status = Status_cotacao "; strCmd += " INNER JOIN Negocios_canais ON Cod_canal = Canal_cotacao "; strCmd += " INNER JOIN Negocios_corretores ON Cod_corretor = Corretor_cotacao "; strCmd += " INNER JOIN Negocios_pacs ON Cod_pac = Pac_cotacao "; strCmd += " INNER JOIN Negocios_gerentes_canais ON Negocios_gerentes_canais.Cod_gerente = GerenteCanal_cotacao "; strCmd += " INNER JOIN Negocios_gerente_beneficios ON Negocios_gerente_beneficios.Cod_gerente = GerenteBeneficios_cotacao "; strCmd += " INNER JOIN Negocios_filiais ON Negocios_filiais.Cod_filial = Filial_cotacao "; strCmd += " INNER JOIN Negocios_regioes ON Cod_regiao = Regiao_cotacao "; strCmd += " INNER JOIN Negocios_consultores ON Cod_consultor = Consultor_cotacao "; strCmd += " INNER JOIN Negocios_produtos ON Produto_cotacao = Id_produto "; strCmd += " WHERE Codigo_cotacao <> -1 "; if (hiddenddlEmpresa.Text != "Todas") strCmd += " AND Negocios_empresas.Cod_empresa = " + hiddenddlEmpresa.Text; if (hiddenddlCategoria.Text != "Todas") strCmd += " AND Negocios_categorias.Id_categoria = " + hiddenddlCategoria.Text; if (hiddenddlProduto.Text != "Todos") strCmd += " AND Negocios_produtos.Id_produto = " + hiddenddlProduto.Text; if (hiddenddlRamo.Text != "Todos") strCmd += " AND Negocios_ramos.Cod_ramo = " + hiddenddlRamo.Text; if (hiddenddlCorretor.Text != "Todos") strCmd += " AND Negocios_corretores.Cod_corretor = " + hiddenddlCorretor.Text; if (hiddenddlConsultor.Text != "Todos") strCmd += " AND Negocios_consultores.Cod_consultor = " + hiddenddlConsultor.Text; if (hiddenddlCanal.Text != "Todos") strCmd += " AND Negocios_canais.Cod_canal = " + hiddenddlCanal.Text; if (hiddenddlStatus.Text != "Todos") strCmd += " AND Negocios_status.Id_status = " + hiddenddlStatus.Text; if (hiddenddlRegiao.Text != "Todas") strCmd += " AND Negocios_regioes.Cod_regiao = " + hiddenddlRegiao.Text; if (hiddenddlGerenteCanal.Text != "Todos") strCmd += " AND Negocios_gerentes_canais.Cod_gerente = " + hiddenddlGerenteCanal.Text; if (hiddenddlFilial.Text != "Todas") strCmd += " AND Negocios_filiais.Nome_filial = '" + hiddenddlFilial.Text + "'"; if (hiddenddlAtividadeProposta.Text != "Todas") strCmd += " AND Negocios_atividades_propostas.Id_atividade_proposta = " + hiddenddlAtividadeProposta.Text; if (hiddenddlPAC.Text != "Todos") strCmd += " AND Negocios_pacs.Cod_pac = " + hiddenddlPAC.Text; if (hiddenddlGerenteBenef.Text != "Todos") strCmd += " AND Negocios_gerente_beneficios.Cod_gerente = " + hiddenddlGerenteBenef.Text; if (hiddentxtDataPropostaInic.Text != "" && hiddentxtDataPropostaFim.Text != "") strCmd += " AND Data_cotacao BETWEEN '" + hiddentxtDataPropostaInic.Text + "' AND '" + hiddentxtDataPropostaFim.Text + "'"; if (hiddentxtInicioVigenciaInic.Text != "") strCmd += " AND Vigencia_cotacao_inic BETWEEN '" + hiddentxtInicioVigenciaInic.Text + "' AND '" + hiddentxtInicioVigenciaFim.Text + "'"; if (hiddentxtDataPropostaFim.Text != "") strCmd += " AND Vigencia_cotacao_fim BETWEEN '" + hiddentxtFinalVigenciaInic.Text + "' AND '" + hiddentxtFinalVigenciaFim.Text + "'";
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
View 4 Replies View RelatedI have code
Function GetDealCount(reportItems)
return iif(IsNothing(reportItems!txtDetailCountRows.Value), 0, reportItems!txtDetailCountRows.Value)
End Function
Function GetSumNotionalAmount(reportItems)
return iif(IsNothing(reportItems!txtDealSumNotionalAmount.Value), 0, reportItems!txtDealSumNotionalAmount.Value)
End Function
That I am calling from a textboxes in the page header
= Code.GetDealCount(ReportItems) & " Deal(s)"
also
= Parameters!BaseCurrency.Value + " " + Format(Code.GetSumNotionalAmount(ReportItems),"N2").ToString().Replace(",","'")
When I preview the report in VS.NET I get values showing.
When I deploy the report I just get #Error showing.
Also this report used to work fine in RS2000
Does anyone know the cause of this issue?
Hello,
I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?
Hello,
I have an if statement for one of my columns in my query.
I want to write the if statement as a part of my select statement.
How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this:
if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work.
Any ideas?
Hi,
I'm wondering if it is possible to use IF statements in a query, for example if this was my query:
SELECT Asset, Source, Val1, Val2, Val3
FROM tableA
Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative.
Could I do something like this:
SELECT Asset, Source, (IIF Source = null, Val1*-1, Val1), (IIF Source = null, Val2*-1, Val2), (IIF Source = null, Val3*-1, Val3)
FROM tableA
When I try something like this it doesn't work, is there a way to do this in a query?
Thanks.
Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.
View 4 Replies View RelatedFor 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.
Hi all,
I have a website under development. when trying to load page for the first time, I am getting sometimes a timeout error messege.
My guess is that one of the queries is problematic and takes too long. I need to track the problematic query. one way to do that is using the ASP trace option.
So this is what I did and I got the below error. the error as I can see is inside a BEGIN_INIT function, but I dont have a fnction like this in my code.
Do you have any suggestion how to track? here is the trace code:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
span.tracecontent b { color:white }
span.tracecontent { background-color:white; color:black;font: 10pt verdana, arial; }
span.tracecontent table { clear:left; font: 10pt verdana, arial; cellspacing:0; cellpadding:0; margin-bottom:25}
span.tracecontent tr.subhead { background-color:#cccccc;}
span.tracecontent th { padding:0,3,0,3 }
span.tracecontent th.alt { background-color:black; color:white; padding:3,3,2,3; }
span.tracecontent td { color: black; padding:0,3,0,3; text-align: left }
span.tracecontent td.err { color: red; }
span.tracecontent tr.alt { background-color:#eeeeee }
span.tracecontent h1 { font: 24pt verdana, arial; margin:0,0,0,0}
span.tracecontent h2 { font: 18pt verdana, arial; margin:0,0,0,0}
span.tracecontent h3 { font: 12pt verdana, arial; margin:0,0,0,0}
span.tracecontent th a { color:darkblue; font: 8pt verdana, arial; }
span.tracecontent a { color:darkblue;text-decoration:none }
span.tracecontent a:hover { color:darkblue;text-decoration:underline; }
span.tracecontent div.outer { width:90%; margin:15,15,15,15}
span.tracecontent table.viewmenu td { background-color:#006699; color:white; padding:0,5,0,5; }
span.tracecontent table.viewmenu td.end { padding:0,0,0,0; }
span.tracecontent table.viewmenu a {color:white; font: 8pt verdana, arial; }
span.tracecontent table.viewmenu a:hover {color:white; font: 8pt verdana, arial; }
span.tracecontent a.tinylink {color:darkblue; background-color:black; font: 8pt verdana, arial;text-decoration:underline;}
span.tracecontent a.link {color:darkblue; text-decoration:underline;}
span.tracecontent div.buffer {padding-top:7; padding-bottom:17;}
span.tracecontent .small { font: 8pt verdana, arial }
span.tracecontent table td { padding-right:20 }
span.tracecontent table td.nopad { padding-right:5 }
Request Details
Session Id:
sesqltrnnbq35g45fxkyqk45
Request Type:
GET
Time of Request:
6/9/2007 12:36:41 AM
Status Code:
500
Request Encoding:
Unicode (UTF-8)
Response Encoding:
Unicode (UTF-8)
Trace Information
Category
Message
From First(s)
From Last(s)
aspx.page
Begin PreInit
aspx.page
End PreInit
4.97502699318581
4.975027
aspx.page
Begin Init
5.03576665047642
0.060740
Unhandled Execution Error
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.GetConnectionHolder() at System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider.LoadPersonalizationBlobs(WebPartManager webPartManager, String path, String userName, Byte[]& sharedDataBlob, Byte[]& userDataBlob) at System.Web.UI.WebControls.WebParts.PersonalizationProvider.LoadPersonalizationState(WebPartManager webPartManager, Boolean ignoreCurrentUser) at System.Web.UI.WebControls.WebParts.WebPartPersonalization.Load() at System.Web.UI.WebControls.WebParts.WebPartManager.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
8.31843797517178
3.282671
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
View 5 Replies View Relatedhi there,
I saw a similar thread before but i've tried it and no luck. I want run a query off a database (stawebdata.branchinfo) then a join into bluepages.phonelist
i've tired
JOIN bluepages.phonelist p
Incorrect syntax near the keyword 'JOIN'.
any ideas... is this possible?
complete code below:
select DISTINCT b.name, b.tel1, b.id, b.telprivate,b.sc, b.manager,b.branchEmail
from branchInfo b
inner
JOIN bluepages.phonelist p on b.name = p.subdepartment
where b.name like 'Sarah' or b.id LIKE '%@keyword%' OR b.county LIKE '%@keyword%' OR b.Area LIKE '%@keyword%' OR b.Manager LIKE '%@keyword%' OR b.AssistantManager LIKE '%@keyword%' OR b.Postcode LIKE '%@keyword%' OR b.PseudoCity LIKE '%@keyword%' OR b.TicketingTA LIKE '%@keyword%' OR b.SLC LIKE '%@keyword%' OR b.ItineraryTA LIKE '%@keyword%' OR b.TIDS LIKE '%@keyword%' OR b.ABTA LIKE '%@keyword%' and
p.department = 'UK Branch' and
b.isbranch <> 'False' or
(p.lastName = '@keyword' or p.firstname = '@keyword')
order by b.name asc
GO
Inside a query how can I decide which application role is presentlty active?
-- declared variables
declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100)
declare @list varchar(8000)
-- set values to variables
set @list = ''
set @database_name = 'data200802_dan'
set @table_name = 'other02'
set @primary_key_field = 'callid'
use database
select @list = @list + column_name + ', '
from information_schema.columns
where table_name = @table_name --table name
and column_name != @primary_key_field --unique identifier
select @list = substring(@list, 1, len(rtrim(@list)) - 1)
--above 5 lines btw came from a helper in the msdn forum. thanks
SELECT DISTINCT @list
INTO '#' + @table_name
FROM @table_name
@table_name + ':'
IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0
BEGIN
INSERT INTO
@database_name + '.dbo.' + @table_name + '(' + @list + ')'
SELECT
@list
FROM
'#' + @table_name
END
ELSE
BEGIN
DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')'
GOTO @table_name
END
DROP TABLE '#' + @table_name
the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table.
is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query..
this is for removing duplicates btw. thanks!
Is it possible for me to do something like
update table1 SET var1=something,var2=something2
from table1
(SELECT * from table2) as newtable
where newtable.field1=acondition
what is the logic in multiple "FROM" statements inside a single query?
View 1 Replies View RelatedI have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code:
SELECT Jobs_Table.JobNum,
[Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces,
The_Big_One1.HT_Mail_STId,
THE_Big_One1.STC_SCAN,
[Code] ...
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!
View 5 Replies View Related/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
View 1 Replies View RelatedHi,
I have to write a stored procedure what repair a table.
It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID').
I do not found the way to write this procedure to can create statement from parameter of the procedure.
I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'.
If the parameter is in the 'WHERE', nothing problem.
My procedure is:
CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @OrphanRowCount int
select @OrphanRowCount = 1
while(@OrphanRowCount > 0)
begin
DECLARE KWCursor CURSOR FOR
SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);
OPEN KWCursor;
FETCH NEXT FROM KWCursor INTO @OrphanRowCount
CLOSE KWCursor
DEALLOCATE KWCursor
IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0)
BEGIN
exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)')
END
end
END
GO
How can I run fully parameterized queries from an SP.
I can make it only like exec('DELETE FROM' + @TableName + ...)
Thank you for any idea,
Imre
Hello,
when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ?
GROUP BY Panel_Position)
i get this error returned:
0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.
I don't know where inside the sql string a conversion is necessary/fails.
Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ?
GROUP BY Panel_Position)
The only difference between the 2 statements is the ABS() function inside the sub query.
More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.
I would appreciate it very much if someone out there knows an answer or a hint and could tell me.
With kind regards,
Andre