Getting a SQL Database Error...any ideas SQL experts?

I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer...

Here is the code I am using:

<%@. Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.Configuration" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server">

<script runat="server">

Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)

Dim CWZ As CreateUserWizard
CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard)

CreateUserProfile(CWZ.UserName)

Private Sub CreateUserProfile(ByVal UserName As String)

Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@.UserID)", con)
cmd.Parameters.AddWithValue("@.UserID", UserName)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using

End Sub

</script>

...and here is the error and stack trace (the offending Line 49 is in bold):

Server Error in '/Site_Dev' Application.
------------------------

An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 47: cmd.Parameters.AddWithValue("@.UserID", UserName)
Line 48: Using con
Line 49: con.Open()
Line 50: cmd.ExecuteNonQuery()
Line 51: End Using

Source File: C:\Users\mdcragg\Documents\Website\Site_Dev\User_Create.aspx Line: 49

Stack Trace:

[SqlException (0x80131904): An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ASP.user_create_aspx.CreateUserProfile(String UserName) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:49
ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:30
System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

Looking around potential solutions seem to be:

1. Open SQL Server Management Studio and attach the .mdf file manually to the SQL Server. (Select the 'Databases' node, Right click and select 'Attach')

Now change your web.config and change the connection string to point to the pre attached database instead of auto attach.

2. SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again.

3. The windows account "ASPNET" must have write permission on the folder. If it is a Windows 2003 machine, provide write access to the account "NETWORK SERVICE" on the folder, instead of account ASPNET.

4. You may want to disable the "User Instance=true" and try.

5. Are you using beta versions of VWD, Visual Studio 2005 or SQL Server 2005 Express? Uninstall them andinstall the released versions.

6. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode. (Select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select the node 'Security' to view/change the authentication mode)

7. Make sure your connection string is correct. It must be something like below:

"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"

When you useIntegrated Secuiryt=SSPI in the connection string, make sure you are using<identity impersonate="true" /> in the<system.web> section of the web.config and also make sure to specify a valid windows account for the "Anonymous User" access section in IIS.

Getting a field from subtotal

hi all,

i have a problem in my report; some fields grouped by matrix and include a subtotal. In column group there is 2 field and one of them contain integer values. I want to sum all of the this fields and minus from a rowgroup field. Is there a way for to sum fields in coloumn group or getting total field from subtotal?

I'm having trouble understanding your problem and your English. Could you try to rephrase your question and add a little more detail?

Thanks

getting a deleted ids froma field

hi i need to get deleted dis froma table say customers id where in we have 1000 records and we have randomly deleted 100 records i want the ids of those randomly deleted recodsTry http://www.lumigent.com Log explorer
or if it was numeric column, you can guess IDs by this query
select
FreeRange=
case when (h.ID+1)=(min(l.Id)-1)
then convert(varchar(20),h.ID+1)
else isnull(convert(varchar(20),h.ID+1),'?')+'..'+isnul l(convert(varchar(20),min(l.Id)-1),'?')
end
,"Count"=isnull(convert(varchar(20),min(l.Id)-1-(h.ID+1)+1),'?')
from
(
select yt.ID
from YourTable yt
where not exists(select 'x' from YourTable ytH where yt.ID=ytH.ID-1)
) h
full join
(
select yt.ID
from YourTable yt
where not exists(select 'x' from YourTable ytL where yt.ID=ytL.ID+1)
) l on h.ID<l.Id
group by h.ID
order by h.ID|||see www.nigelrivett.com
Find gaps in sequence numbers|||see www.nigelrivett.com
Find gaps in sequence numbers|||nigelrivett's query

select convert(varchar(10),imin.ID) + ' - ' + convert(varchar(10)
,
(
select min(ID)
from
(
select ID
from YourTable
where not exists
(
select *
from YourTable a2
where YourTable.ID-1 = a2.ID
)
and YourTable.ID <> (select min(ID) from YourTable)) as imax where imax.ID > imin.ID)
)
from
(
select ID
from YourTable
where not exists
(
select *
from YourTable a2
where YourTable.ID+1 = a2.ID
)
and YourTable.ID <> (select max(ID) from YourTable)
) as imin

But it was a little slow (7s/4000 rows), I added index
create unique clustered index icx_YourTable on YourTable(ID ASC)
but it is still about 3s/4000 rows (By the way Index tuning wizard did not recomend any index !)
My query is about 1s/4000 rows with no index and I expect higher difference for larger dataset.

You said, you have 1000 rows, so time under 10 s is excelent for you.
I am speaking to terabyters :)

Getting #Error details from deployed report

I have a report that displays #Error for some field values when deployed on the server. Is there any way to get details on the error?

Thanks.

Most likely the #Error comes from making a call into a custom assembly and insufficient security permissions for that custom assembly to be invoked.

You can partially simulate the server environment, if you run the report in the stand-alone preview of report designer. I.e. instead of clicking on the Preview tab, hit F5 which should open a new window for the stand-alone preview. For the #Error you should get warning messages with more details in the output window.

-- Robert

Getdate() with no time associated

Is there a command that will let me set getdate() a in a smalldatetime field so that the there is no time associated with it?

For example, I have a table that I want to load the date a user does an action. If I use getdate() I'll get a value such as 5/25/2006 08:26:56.340, whereas I would just like a value 5/25/2006.

I can work it out by doing the following: select (datename(month,getdate())+'-'+datename(day,getdate())+'-'
+datename(year,getdate()))

However it seems to me that there should be a simpler way.well, i dunno if it's simpler, but this is a lot more efficient --

dateadd(d,datediff(d,0,getdate()),0)|||Towards the bottom of this article is an explanation on the why and how :)

EDIT - how about I post the article link eh?
http://www.sql-server-performance.com/fk_datetime.asp|||That does seem more efficient (I knew there had to be a better approach). And thanks for the link to the article.|||fabulous link, pootle, thanks

GETDATE() to insert into a datetime field

Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:

> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:

> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql

GetDate() in Constraint using user's system time, Stored Proc using Server's.

All,

I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().

I performed the following test:

1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.

2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.

*note: These servers are on separate domains and therefore could have different system times.

This causes my select stored procedure to NOT return the values I just entered.

Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?

If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||

Todd:

Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.

Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.

sql

Getdate() default value does not get created

I have a table in which a field named DateCreated has the default value
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:

>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?
Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegro ups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>
|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession@.nField1 int = 0,
@.nField2int = 0,
@.nField3int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking(fldField1,
fldField2,
fldField3,
fldField4)
VALUES (@.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction@.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.
|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:

> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession @.nField1 int = 0,
@.nField2 int = 0,
@.nField3 int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking( fldField1,
fldField2,
fldField3,
fldField4)
VALUES ( @.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction @.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql

Get zodic sign in sql server

I have a field in table A named tbl_dob.It is date of birth of member. I want to find zodic sign from this tbl_dob.How I get it.Please help me.

You'll have to create a table to hold the zodiac signs and their date ranges. You can then join to this table to find which sign the date falls into.

sql

Get value from Report

There is a textbox property where I can set the Jump to URL value equal to
any field from the report. I currently have a windows application with an IE
web control embedded in the windows form. How can I get the value of a
selected field in the report outside of reporting services? Is it possible to
create a web service and pass the value over to it and retrieve it that way
or is there a better way to do this? Thanks for any input.I am not sure what you want to do here. If you want the value of a
selected field from your app to be passed to a report you can pass it
as a parameter to the report.
if you want a value from the report to be passed into your app then you
can pass the value from your report into a custom assembly, which you
can use in your app.|||I would like the value passed from the report to the calling app. Can you
point me to any examples (preferably c#) re. passing values from report into
a custom assembly? Thanks.
"RN" wrote:
> I am not sure what you want to do here. If you want the value of a
> selected field from your app to be passed to a report you can pass it
> as a parameter to the report.
> if you want a value from the report to be passed into your app then you
> can pass the value from your report into a custom assembly, which you
> can use in your app.
>|||The custom assembly would be a C# project with a method in it that
accepts the value you want to pass to your app, which you can save in a
global variable or collection and use in your app. Please refer to
Custom Assemblies in books online for information about how to
reference this assembly in your report and how to call methods in it.
El Presidente wrote:
> I would like the value passed from the report to the calling app.
Can you
> point me to any examples (preferably c#) re. passing values from
report into
> a custom assembly? Thanks.
> "RN" wrote:
> > I am not sure what you want to do here. If you want the value of a
> > selected field from your app to be passed to a report you can pass
it
> > as a parameter to the report.
> > if you want a value from the report to be passed into your app then
you
> > can pass the value from your report into a custom assembly, which
you
> > can use in your app.
> >
> >

Get value from query to update another another table

I have a query that simply slelcts the min value of a specified field
from one table, I want to take that value to update a field in
annother table, just can not figure it out.(rzito@.si.rr.com) writes:

Quote:

Originally Posted by

I have a query that simply slelcts the min value of a specified field
from one table, I want to take that value to update a field in
annother table, just can not figure it out.

UPDATE tbl
SET col = (SELECT MIN(somecol) FROM othertbl)
WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Get Today's Records

Hello, I have a query that needs to run daily and only collect records for
that day's activity. The table has a date/time field called TranDateSold.
Could someone please advise the proper syntax for the following logic:
SELECT * FROM tablename
WHERE TranDateSold (is today only)
I experimented with GETDATE() but it seemed to want to match today's date
and time to the hour and minute. Any ideas would be most appreciated.Hi,
Strip the time portion from the date returned by getdate() function before
comparison. Something like this:
select *
from tablename
where trandatesold = convert(vachar(8), getdate(), 112)
This assumes that the time is already zero-ed in trandatesold column.
hth,
Dean
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0BA6C334-70D4-4637-8528-1B838763F5DD@.microsoft.com...
> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
> Could someone please advise the proper syntax for the following logic:
> SELECT * FROM tablename
> WHERE TranDateSold (is today only)
> I experimented with GETDATE() but it seemed to want to match today's date
> and time to the hour and minute. Any ideas would be most appreciated.|||Try this
SELECT * FROM tablename
WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
If there can be nothing in the future:
DECLARE @.dt SMALLDATETIME
SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt;
If there may be future-dated rows:
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt
AND TranDateSold < @.dt + 1;
A|||Oops, I goofed the = should be >=|||This will only work if TranDateSold is intentionally stored with no time
value. Otherwise you will be asking for rows where '2006-04-21 13:26' =
'2006-04-21 00:00';
If you have to do the convert on the left-hand side to get rid of the time
component, you've just wiped out any chance of using an index.
http://www.aspfaq.com/2280
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649232.038005.231380@.g10g2000cwb.googlegroups.com...
> Try this
> SELECT * FROM tablename
> WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks to everyone for your posts. The convert function is what I needed fo
r
my vendor, and the DECLARE stmt gave me all records after midnight today.
Have a nice wend, Pancho.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If there can be nothing in the future:
> DECLARE @.dt SMALLDATETIME
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt;
> If there may be future-dated rows:
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt
> AND TranDateSold < @.dt + 1;
> A
>
>

get the search term from a URL for reporting purpose

Hi guys,

I have a field called URL in my table. I want to get the SEARCH TERM from a given URL and create a report based on that information. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.

Examples of the URLs from google :

http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables

Does anybody have a sql query or used a CLR functions to get the SEARCH TERM from different search engine (URL).

Thanks in advance.

Here is a blog entry from Chrissy LeMaire that seems to do what you want in both C# and straight SQL:

http://blog.netnerds.net/2007/02/mssql-parse-search-engine-querystrings-for-search-terms/

Larry

|||

10Q Larry, that's what exactly I was looking for.

get the search term from a URL

Hi guys,

I have a field called URL in my table. I want to get the SEARCH TERM from a given URL. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.

Examples of the URLs from google :

http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables

Does anybody have a sql query to get the SEARCH TERM from different search engine (URL).

Thanks in advance.

Sounds like a fairly tricky piece of work that.

All i can really add is that as it's essentially string parsing, you'd be best doing this outside of T-SQL either in your app code or in a CLR function.

|||

RegX (Regular Expressions) is your FRIEND in situations like this.

You really, really want to do this in application code using RegX. It is so much more efficient!!!

If absolutely necessary, you could create a CLR function (in VB or C#), using RegX, and add that CLR function to your server. You could then call it from SQL code.

get the rows where the info from one table is not contained in the

hello,
i have 2 tables which have 2 fields.
Common in the 2 tables is the id, the other field is a varchar(256)
example
Table1
Id UserInfo
1 Pc A.Julien-3400
2 Soft V.Noris-2800
3 Liz Barbara -2345
Table2
Id Username
1 Julien
2 Jack
3 Barbara
I want to get the id value where the username is not contained in the UserIn
fo
In the example
for id=1 Julien is contained in Pc A.Julien-3400
for id=2 Jack !!! is not contained .....
for id=3 Barbara is contained in Liz Barbara -2345
For this case i want to get only id=2
thanks
best regardsYou can do something like:
SELECT
<your column list>
FROM
table1
JOIN table2 ON table1.id = table2.id AND CHARINDEX(table2.col,
table1.col) > 0
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:1DB6FF37-9D4A-4A9A-A486-6E0C382F0070@.microsoft.com...
> hello,
> i have 2 tables which have 2 fields.
> Common in the 2 tables is the id, the other field is a varchar(256)
> example
> Table1
> Id UserInfo
> 1 Pc A.Julien-3400
> 2 Soft V.Noris-2800
> 3 Liz Barbara -2345
> Table2
> Id Username
> 1 Julien
> 2 Jack
> 3 Barbara
> I want to get the id value where the username is not contained in the
> UserInfo
> In the example
> for id=1 Julien is contained in Pc A.Julien-3400
> for id=2 Jack !!! is not contained .....
> for id=3 Barbara is contained in Liz Barbara -2345
>
> For this case i want to get only id=2
> thanks
> best regards|||On Wed, 1 Feb 2006 06:52:27 -0800, Xavier wrote:

>hello,
>i have 2 tables which have 2 fields.
>Common in the 2 tables is the id, the other field is a varchar(256)
>example
>Table1
>Id UserInfo
>1 Pc A.Julien-3400
>2 Soft V.Noris-2800
>3 Liz Barbara -2345
>Table2
>Id Username
>1 Julien
>2 Jack
>3 Barbara
>I want to get the id value where the username is not contained in the UserI
nfo
>In the example
>for id=1 Julien is contained in Pc A.Julien-3400
>for id=2 Jack !!! is not contained .....
>for id=3 Barbara is contained in Liz Barbara -2345
>
>For this case i want to get only id=2
Hi Xavier,
SELECT Table1.Id, Table1.UserInfo, Table2.UserName
FROM Table1
INNER JOIN Table2
ON Table2.Id = Table1.Id
WHERE Table1.UserInfo NOT LIKE '%' + Table2.Username + '%'
Hugo Kornelis, SQL Server MVP

Get the Row which has Max value of the field

I want to get a field value in the row, which has max value of another field in the same table

I have done with the below query. I want to know is there any other better way of doing it becuase it is taking longer time.

Thanks for your help

SELECT Field1
FROM TableName
WHERE Field2 = ( SELECT MAX(field2)
FROM TableName)That shouldn't take too long to process. How many rows, and do you have an index on Field2?|||I have about 200k records. I have index (non-clustered )on field2. More over it is taking about 7 sec to run the above query

Thanks|||...and of those 200K records, how many are typically being returned in the result set? It may take several seconds to display the result set.

Have you tried viewing the query plan?

Get the recent records

i have a datetime field in the post tables.

I would like to get the records within the latest 7 days.

Are there any functions for doing something like this?

my current query is something like

select * from post where creation_time ...

Thank you

try something like this:

create

table #test(datedatetime)

insert

into #test

values

('01/01/2007')

insert

into #test

values

('02/01/2007')

insert

into #test

values

('02/04/2007')

select

*from #test

where

date>dateadd(day,-7,getdate())

drop

table #test

I think that it will point you in correct direction, or maybe it is your solution?

|||

Could you not just do...

select

*from post

where

creation_time >dateadd(day,-7,getdate())

jpazgier, i am not following the reason for creating the additional table.

|||

I just try to provide working example in my answer so I created temporary table with my test data to show that it works and for future testing.

But in this case my example only points your how you can try to solve problem, you maybe would like to take care about not only day but also minutes?
This example if you run it at 12:31 today will show records inserted after 12:31 7 days ago so records inserted at 12:30 will be not visible and maybe author of the post would like to take care about this himself, I do not know if time of the day is important for him or not.

Thanks

|||Both answers are great!|||

Both answers are great!

Thank you