Converting Values To YesNo Inside Query

Mar 19, 2015

I 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] ...

View 5 Replies


ADVERTISEMENT

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Converting INT Values To HEX

Apr 29, 2006

Is there any function in SQL Server to convert INT Values to HEX

Ex IF Int Value = 33948700 ,then I shud get HEX Value = 206041C


Thx All for any help

Venu

View 7 Replies View Related

Converting Hex Values To Int.

Aug 10, 2007

I have to convert values like 0x01A4 to integer and I just can not get it right!

Select Convert(int, 0x01A4) works OK but is not useful.

But
Declare @hex varchar(20)
Select Convert(int, @b) yields:
Server: Msg 245, Level 16, State 1, Line 13
Syntax error converting the varchar value '0x01A4' to a column of data type int.

What's the trick??

View 8 Replies View Related

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

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.

View 1 Replies View Related

Converting Column Values

Aug 5, 2004

I have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.

Can anyone suggest an approach.

View 1 Replies View Related

Converting Values In Column To A Date Value

Apr 15, 2015

I have a columns with datatype varchar(50) which has date values in it.

I am trying to convert the values in the column to a date value,but i am getting conversion error.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

select last_update from importtest where convert(datetime,last_update)<convert(datetime,'24 Dec 2014')

View 3 Replies View Related

Converting Varchar To Numeric Values

Oct 31, 2005

Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?

View 5 Replies View Related

Converting NULL Int Values To Blanks

Feb 5, 2008

Hi,

I've an int field and when this field has a null, I want to show empty/blanks instead of 0. How can this be achieved?

I've tried the below:


declare @a as int

set @a= null

select isnull(@a,'')
o/p: 0


select cast(isnull(@a,'') as varchar)
o/p: 0


select case convert(varchar,@a)
when null then ''
else @a
end
o/p: null


Thanks for your help!

Subha

View 14 Replies View Related

Problem Converting Decimal Values

Apr 25, 2007

Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.

Any help on this ? tnks in advance

View 5 Replies View Related

Converting Integer Values To Datetimes

May 23, 2006

We have inherited an appointments database that has a table tblAppointments

Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.

My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins

Eg: 0 = 0:00

12 = 1.00

24 = 2.00

36 = 3.00

€Š

...

288 = 24:00

What I need is a piece of T_SQL that will translate these into times that can be read by a normal user

So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30

Any help is greatly appreciated.

Cheers,
Craig

View 6 Replies View Related

Converting String Parameter Values To Date

Mar 11, 2008



Hi,

I have a calendar parameter in one of my reports. The values of the calendar are of the form 'yyyy-mm-dd hh:mms'. And they are string values.

Now when I generate the report I have a textbox that takes the parameter label (e.g., parameter!calendar.label). However, I would like to format (or trim) the label as just 'yyyy-mm-dd'. Does anyone know how I can do this? I tried to change the parameter value format to datetime but this just throws an invalid datatype error...

cheers,
Al

View 10 Replies View Related

T-SQL (SS2K8) :: Converting Integer Values To Date-time?

Jul 15, 2014

As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.

I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.

Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.

It leads to massive implicit conversions and I am trying to change this.

To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.

Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.

View 4 Replies View Related

Transact SQL :: Null Values After Converting To Right Outer Joins

Sep 25, 2015

I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.

SELECT c.juris_id, b.jrnl_mo_yr 
FROM a_trueup a,
#t_mths b, r_rj c   
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
AND 
a.juris_id
=* c.juris_id

[code]....

I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..

View 15 Replies View Related

Converting UTC Datetime Values To Local Time Zones

May 20, 2008



I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:


SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4


-- =============================================

-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO

View 6 Replies View Related

SSIS Converting To Date Empty Fields (no Values)

Jan 8, 2008



Hi, how do I convert dates with SSIS, when I receive dates I have no problem, with empty fields I have to go through a transformation as:


trim(field_name) == "" ? "999999" : substring....... (derive transform)

after this I have to update each field holding '999999' to NULL. (using sql task)

Is there an easier way to doing this?

Thanks

View 1 Replies View Related

Stm Inside Sql Query

Jul 10, 2006

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 + "'";   

View 3 Replies View Related

Can I Use IF Inside A Query?

Jan 7, 2008

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 Related

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

If Statement Inside Sql Query??

Jun 7, 2008

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?

View 1 Replies View Related

If Statement Inside Sql Query??

Jun 7, 2008

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?

View 1 Replies View Related

Using IIF Statements Inside A Query?

Jan 30, 2008



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.

View 3 Replies View Related

Getting Result Of Query Inside Another Query

May 22, 2007

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 Related

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?

Sep 12, 2007

For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.

View 9 Replies View Related

Timeout For Query Inside BEGIN_INIT

Jun 8, 2007

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

View 3 Replies View Related

Query To Each Item Inside A Datalist

Dec 25, 2007

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>            &nbsp;&nbsp;</td>          </tr>        </table>      </ItemTemplate>  </ASP:DataList>  </body></asp:Content> Thanks a lot. 

View 5 Replies View Related

Connecting To 2 Databases Inside One Query

Sep 16, 2004

hi 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

View 2 Replies View Related

Is It Possible To Use Dynamic Query Inside A Func

Jun 17, 2006

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

View 10 Replies View Related

Deciding Approle Inside A Query?

Jan 9, 2008

Inside a query how can I decide which application role is presentlty active?

View 7 Replies View Related

Hi, Pls See The Query Inside, Reg. Removing Duplicates..

Mar 17, 2008

-- 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!

View 3 Replies View Related

Select Inside Update Query?

Feb 21, 2007

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

View 5 Replies View Related

Update SQL 2000 Query (converting An Old Access 2k Query To SQL)

Mar 30, 2006

Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR

View 2 Replies View Related

Multiple FROM Statements Inside A Single Query?

Mar 21, 2012

what is the logic in multiple "FROM" statements inside a single query?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved