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 Query Parameter from Elsewhere

I have a query on a table. This table has a datetime column.
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?

Getting a Package's ExecutionID

Hello everyone

I'm logging the execution of my package into a SQL SERVER table. Everything works right. I'm also executing this package using a web service, I'd like to retrive the package's executionId after it is executed. By the way, i'm using a Microsft.SqlServer.Dts.Runtime.Package object but i haven't found any property that could give this.

any clue?

thanks a lotHave you searched this forum for executionID? There has been many threads around this topic and the current version of SSIS' shortcomings with respect to logging the execution ID.|||There is a system variable ExecutionId. Try that for now, not sure if the value is available post execution, but would seem sensible.|||I mean there is a system variable called ExecutionInstanceGUID which is the same value as you get logged in the sysdtslog90 table.

getting a list of user created tables ONLY

sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>

Getting a final version of a person into a DW

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first?

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

Fuzzy could be of help here. The fuzzy grouping can be used for deduplicating, the fuzzy lookup to check whether you already have a record that resembles your new one.

You will need to spend some time (by testing) to figure out a similarity value that suits your situation. In a DW environment, I think this should be a business decision.

From what I understand, ssno probably needs to be involved. In the lookup (haven't used the grouping yet) you can set similarity values for ssno addressno and name. For example, ssno (or birthdate?) needs a similarity of 1, and the name needs to have a similarity > 0.6

Regards,

Pipo

Getting a error in DTS Active x script

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function

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 a date in the past

Hi,
Does anyone knows the select syntax for getting a date in the past but
close the current date.
For example: i have a table of addresses with an id, startdate, street,
etc. Now what i would like to do, is get the date that is close to the
current date. The outcome of it, is the current address of a person.
Is this possible with use of the columns id and startdate or just startdate?Please post DDL if you are refering to columns in your tables
http://www.aspfaq.com/5006
Select TOP 1 <columnlist>
>From SomeTable
Where id = <Someid>
Order by Startdate desc
HTH, Jens Suessmeyer.|||select DATEDIFF(dd, StartDate, getdate()), * from YourTable
order by DATEDIFF(dd, StartDate, getdate())
dd = Days. This can be substitued for hours, minutes, seconds etc. Have a
look at DATEDIFF function in SQL Books Online
HTH. Ryan
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:%23Spc$gZIGHA.1876@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone knows the select syntax for getting a date in the past but
> close the current date.
> For example: i have a table of addresses with an id, startdate, street,
> etc. Now what i would like to do, is get the date that is close to the
> current date. The outcome of it, is the current address of a person.
> Is this possible with use of the columns id and startdate or just
> startdate?

getting a constraint value from query in a trigger?

say i have a query like:

UPDATE table SET status = 1 WHERE id = 1000

if i have a trigger on that table, is there anyway i can get the id ?

i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?

thankshmm, looks like i can't even get the id if i try to update since it's an identity column|||How about:SELECT id FROM inserted-PatP|||inserted AND deleted are full blown copies of the table that's affected for the rows that are be modified or added.

You [Id] should be there

What are you trying to do?|||oops, actually i just realized SELECT id from inserted works fine even though id isn't updated

thanks

Getting 2 SUMs from the same table

Hi All

I'm really stuck on this one so would appreciate any help you can give.

In essence, I have 1 SQL 2000 table with rows of data logging stock
movement. To differenciate between a stock sale and a stock receipt the
table has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals a
receipt.

I've been asked to report on this data by suming the total qty used on
invoices and the total qty recvd for each stock item, but I can't figure out
how I sum the same rows twice in the one query.

For example, my query is as follows:

select st.stockid as 'STYLE',
s.picture as 'COLOUR',
'' as 'IN FIRST IN LAST WEEK',
'' as 'THIS WEEK IN',
'' as 'TOTAL IN',
'' as 'OUT FIRST OUT LAST WEEK',
SUM(st.quantity) as 'THIS WEEK OUT',
'' as 'TOTAL OUT',
'' as 'REMAINING',
'' as 'TOTAL DIGESTION %'
from stocktransactions st, stock s
where st.stockid = s.stockid and
st.transactiontype in (8,7) and
st.transactiondate >= '2005-07-12 00:00:00' and
st.transactiondate <= '2005-07-12 23:59:59'
group by st.stockid,s.picture
order by st.stockid

Apart from the 'THIS WEEK OUT' column SUMing all of the stock sales by
transactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of the
transactions by transactiontype 3, so that I get the following results:

STYLE COLOUR ... THIS WEEK IN ... THIS WEEK OUT ......
IVP Red 12 23
STP Blue 4 15
etc etc

My problem is that I don't want to exclude a stock item if it hasn't got a
row/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking too
much of SQL?

My table schemas are as follows:

create table STOCKTRANSACTIONS
(
STOCKTRANSACTIONID T_STOCKTRANSACTIONSDOMAIN not null
identity(1,1),
TRANSACTIONTYPE smallint not null,
TRANSACTIONDATE datetime null ,
REFERENCE varchar(40) null ,
Comment varchar(255) null ,
STOCKID T_STOCKDOMAIN null ,
DESCRIPTION varchar(255) null ,
UNITOFSALE varchar(20) null ,
WAREHOUSEID T_WAREHOUSESDOMAIN null ,
PEOPLEID T_PEOPLEDOMAIN null ,
AccountID T_AccountsDomain null ,
AgentID T_AgentsDomain null ,
PLRate float null ,
CONTACTID T_CONTACTDETAILSDOMAIN null ,
JOBID T_JOBSDOMAIN null ,
QUANTITY float null ,
CURRENCYID T_CURRENCIESDOMAIN null ,
SELLINGPRICE float null ,
DISCOUNTPERCENT float null ,
COSTPRICE float null ,
MINIMUMPRICE float null ,
TILLID T_TILLSDOMAIN null ,
UserID T_UsersDomain null ,
ClockDate DateTime null ,
TimeStamp TimeStamp ,
constraint pk_stocktransactions primary key (STOCKTRANSACTIONID)
)
go

create table STOCK
(
STOCKID T_STOCKDOMAIN not null,
NAME varchar(40) not null,
PICTURE varchar(40) null ,
WEIGHT float null ,
VOLUME float null ,
BARCODE smallint null ,
NumberOfPriceBreaks SmallInt not null default 1,
STOCKCATEGORYID T_STOCKCATEGORIESDOMAIN null ,
SALESNOMINALID T_NOMINALACCOUNTSDOMAIN null ,
PURCHASENOMINALID T_NOMINALACCOUNTSDOMAIN null ,
SELLINGCOMMENT varchar(255) null ,
INCLUDESELLINGCOMMENT TinyInt null ,
DISPLAYSELLINGCOMMENT TinyInt null ,
COSTCOMMENT varchar(255) null ,
DISPLAYCOSTCOMMENT TinyInt null ,
PRODUCTTRACKING smallint null ,
ITEMTYPE smallint null ,
VALUATIONPRICE float not null default
0.00 ,
INCLUDEINCUSTOMERSTURNOVER TinyInt null ,
INCLUDEINAGENTSTURNOVER TinyInt null ,
SUPERCEDED TinyInt null ,
SUPERCEDEDBY T_STOCKDOMAIN null ,
SUPPLIERID T_PEOPLEDOMAIN null ,
SUPPLIERSTOCKID varchar(40) null ,
SUPPLIERCOMMENT varchar(255) null ,
NEXTSERIALNUMBER int null ,
SERIALNUMBERLENGTH smallint null ,
SERIALNUMBERPREFIX varchar(10) null ,
SERIALNUMBERSUFFIX varchar(10) null ,
SERIALNUMBERPREFIXLENGTH smallint null ,
SERIALNUMBERSUFFIXLENGTH smallint null ,
TIMESTAMP timestamp not null,
constraint pk_stock primary key (STOCKID)
)
go

Thanks

RobbieDont repeat the question
http://groups-beta.google.com/group...e663f7fc429d1a3

Madhivanan|||Robbie,

You have some data types in your schema that aren't really data types.
You have STOCKTRANSACTIONID as a data type of
T_STOCKTRANSACTIONSDOMAIN. Are you using SQL Server? How about
posting with good data types and some inserts so people can help you
better.

Thanks,
Jennifer|||(jennifer1970@.hotmail.com) writes:
> You have some data types in your schema that aren't really data types.
> You have STOCKTRANSACTIONID as a data type of
> T_STOCKTRANSACTIONSDOMAIN. Are you using SQL Server? How about
> posting with good data types and some inserts so people can help you
> better.

I assume that these are so-called user-defined data types created with
sp_addtype.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Hello!
I have a stored procedure that takes a file name as a parameter and
imports the data from there into the appropriate table using the
correct format file. I would like to save the number of rows inserted
at the end of my stored procedure. Since the file name is variable I
use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
generated.
Since some of these files are extremely large I have set the BATCHSIZE
for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
show the number of rows inserted in the last batch. Since the table
name is variable I don't have an easy way of getting the count(*) from
it.
Any suggestions?
Thanks!
-Tom.Aardvark (tom_hummel@.hotmail.com) writes:
> I have a stored procedure that takes a file name as a parameter and
> imports the data from there into the appropriate table using the
> correct format file. I would like to save the number of rows inserted
> at the end of my stored procedure. Since the file name is variable I
> use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
> generated.
> Since some of these files are extremely large I have set the BATCHSIZE
> for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
> show the number of rows inserted in the last batch. Since the table
> name is variable I don't have an easy way of getting the count(*) from
> it.
> Any suggestions?
You could run SELKCT COUNT(*) on the table before and after, but that's
of course ont very appealing on a large table. (The fact that the
table is dynamic should not be a problem. You do know sp_executesql,
don't you? Else read
http://www.sommarskog.se/dynamic_sq...#sp_executesql.
The other option would be to have the client to catch those rows
affected messages for each batch committed.
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|||Erland Sommarskog wrote:
> You could run SELKCT COUNT(*) on the table before and after, but that's
> of course ont very appealing on a large table. (The fact that the
> table is dynamic should not be a problem. You do know sp_executesql,
> don't you? Else read
> http://www.sommarskog.se/dynamic_sq...#sp_executesql.
> The other option would be to have the client to catch those rows
> affected messages for each batch committed.
Thanks for the advice Erland. I had forgotten that sp_executesql
allowed for output parameters, so that should give me what I need using
SELECT COUNT(*). Not the most elegant way and it will suffer a
performance hit since some of the tables can be quite large, but it's a
process that runs for up to 12 hours once a month, so a couple minutes
to get a count from a table shouldn't be a problem.
Thanks!
-Tom.

getting left _

how can I can update a table ::
I must get all the left part of a columns part1_part2
I dont know how many characters

something like :

UPDATE Users SET Users.Info = left(Users.name, _ ? or split('_'))

in that case Users.name = part1

i must update only the one with a '_' and do nothing for the others

thank youTry variations of these to get what you want:
declare @.Name varchar(50)
set @.Name = 'Part1Part2'
select left(@.Name, charindex('_', @.Name + '_')-1)
select substring(@.Name, charindex('_', @.Name + '_')+1, 50)
...or if the names are more complex (such as including middle names, initials, or titles), I have a name splitting function I can give you.|||thank you BlindMan

but the name of the column is Users.Name and part1_part2 was an exemple of value
in that exemple i must get part1

it could be werfewgf fff jjj_rgfregreg
and in that case i must get werfewgf fff jjj

allways the left part of '_'|||with select substring(Users.Name, charindex('_', Users.Name + '_')+1, 50) FROM Users

I get exactly the right part

the left will be great :-)

but I get all even if there is no '_'|||Can you supply some sample data and what the expect result is suppose to be?

My esp usb port is clogged|||sample data >> expect result

wefwefqrwf_hhhh >> wefwefqrwf
fff jj ff_rgdefrhbg >> fff jj ff
acacac145 i4_kk >> acacac145 i4

dddd >> no result, I dont take it there is no _ in the sample data

thanks Brett

getting in textbox while retrieving

hi,
I am using sql server database. In my table i am having three columns namely Name, age and salary. I am having three textboxes in my webform to retrieve data from the database.The column salary has no value in the database. When i retrieve the values in to textbox controls, i am getting in my textbox for the salary field.

How to overcome this.?

pls help

Are you HTML Encoding the values that appear in the TextBox?|||

(1) It is a good idea to default 0 in the column.(2) You could do a convert to double in the front end.

If you still have problems please post some code as to how you are populating the textbox.

getteing table name

hi,

i have list of database name in drop down list, when i choose database i want the related table to be display in grid.?
how can i do this?how to set connection string for this?

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

using this code i can display all tables in a particular databse in SQL quey analyser, it displays all tables from selected database in toolbar

but in my coding part

server=localhost;Trusted_Connection=true;database=Reports...

this is my connection string ...but it refers to only reports database,so the query will dispaly all table in reports DB.but i need to dispaly table for selected database in DDL.how cxan i set connection str then?or how can i write query to pass database name also in where clause?

Thanks for any help

You can set the value of the drop down list items to be the connection string to be used itself for the respective database. When the user changes the selection in the drop down list, you can put the code in the selected index change event of the drop down list and you can get the value of the currently selected item which will be the connection string to be used. Pass the value (i.e., the connection string) in another method and at the time of creating the connection object use that variable as the connection string. Feel free to ask about any queries for this approach.

Hope this helps.

Getstring question

how can i add two columns in each row (both strings) in a table only using getstring?

i'm getting an error from this statement... thanks

dim row as string

while dr.read()

problem with this statment

--->> row = dr.getstring(1) + dr.getstring(2)

end whileExactly what is the error message?|||the error message is

Index was outside the bounds of the array.|||Index was outside the bounds of the array.|||Can you verify that your query is in fact returning 2 fields?|||select lastname,firstname from employees

i'm using the northwind db in sql server 2000|||I am not familiar with VB.NET syntax, but in C#, GetString() is zero based, so you'd have:

row = dr.getstring(0) + dr.getstring(1)

Not sure if its the same in VB.NET, but you might want to give that a try.

GetSchema

Should the compact edition connection support the GetSchema method? When I call it gives me a not supported exception. I am after the table names in my database. I could do a query on Information_schema.tables, but I thought using GetSchema("Tables") would be a more generic way to get the information.

Thanks,

Juan Foegen

It is a bug, see http://support.microsoft.com/kb/912435. Use INFORMATION_SCHEMA.TABLES instead (it is a standard way of getting schema information)

geting timeout error in application when inserting record

i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Thanks in advanceis it a single record insert or an insert select? how many indices do you have on the table? have you run a trace and looked at the duration column for the steps in the process? Do you want to post your code?sql

Geting the last inserted row for each CLIENT. How?

Hi,

I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).

Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.

Does anyone knows how to do this? Is it possible with T-SQL only?

Thanks.

Antonio:

Is one of this close to what you are looking for?

Dave

-- --
-- I have used a LEFT JOIN in this case because I think that it
-- is probably important that a client that has not been called
-- be listed.
--
-- I went ahead and added the "recordsToPull" column to provide
-- for the future change when you want to be able to vary the
-- number of conversations returned on a client-by-client
-- basis
-- --
set nocount on

declare @.client table
( client_id integer not null,
recordsToPull integer not null
)

declare @.conversations table
( client_id integer not null,
conv_id integer not null,
conversation_date datetime not null
)

insert into @.client values (1, 4)
insert into @.client values (2, 2)
insert into @.client values (3, 4)
insert into @.client values (4, 4)
--select * from @.client

insert into @.conversations values (1, 1, '1/14/2006')
insert into @.conversations values (1, 2, '10/31/2006')
insert into @.conversations values (1, 3, '3/17/6')

insert into @.conversations values (2, 4, '2/2/6')
insert into @.conversations values (2, 5, '2/2/6')
insert into @.conversations values (2, 6, '2/4/6')
insert into @.conversations values (2, 7, '2/5/6')
insert into @.conversations values (2, 8, '2/8/6')

insert into @.conversations values (3, 9, '7/1/5')
insert into @.conversations values (3, 10, '11/23/5')
insert into @.conversations values (3, 11, '1/17/6')
insert into @.conversations values (3, 12, '4/1/6')
insert into @.conversations values (3, 13, '10/31/6')
--select * from @.conversations

--
-- SQL Server 2005 Version
--
/*
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select client_id,
conv_id,
conversation_date,
row_number ()
over ( partition by client_id
order by conversation_date desc, conv_id desc
)
as seq
from @.conversations
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq
*/

--
-- SQL Server 2000 Version
--
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select x.client_id,
x.conv_id,
x.conversation_date,
count(*) as seq
from @.conversations x
inner join @.conversations y
on x.client_id = y.client_id
and ( x.conversation_date < y.conversation_date or
x.conversation_date = y.conversation_date and
x.conv_id <= y.conv_id
)
group by x.client_id,
x.conversation_date,
x.conv_id
-- order by x.client_id,
-- x.conversation_date desc,
-- x.conv_id desc
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq

--
-- Sample Output:
--

-- client_id recordsToPull conv_id conversation_date
-- -- - -- --
-- 1 4 2 2006-10-31 00:00:00.000
-- 1 4 3 2006-03-17 00:00:00.000
-- 1 4 1 2006-01-14 00:00:00.000
-- 2 2 8 2006-02-08 00:00:00.000
-- 2 2 7 2006-02-05 00:00:00.000
-- 3 4 13 2006-10-31 00:00:00.000
-- 3 4 12 2006-04-01 00:00:00.000
-- 3 4 11 2006-01-17 00:00:00.000
-- 3 4 10 2005-11-23 00:00:00.000
-- 4 4 NULL NULL

|||

It would help if you post the version of SQL Server you are using. In SQL Server 2005, you can do below:

select c.*, t.*

from CLIENTS as c

cross apply (

select top(@.n) *

from CONVERSATIONS as cn

where cn.CLIENT_ID = c.CLIENT_ID

order by cn.CONVERSATION_DATE desc

) as t

|||

Thanks

this is exactly what I needed, worked perfectly 2005 version, didn't try 2000, but I'll need it too...

|||

Hi,

I don't know if you tryed it but didn't work for me. Thanks anyway.

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.
See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.
|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegr oups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

Geting the last inserted row for each CLIENT. How?

Hi,
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Thanks.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> Hi,
> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
> CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
> where the conversation data is registered (and other columns).
> Now I need to retrieve, for each client, the last N (for some clients,
> eventually, less then N) conversations with one T-SQL statement.
> Does anyone knows how to do this? Is it possible with T-SQL only?
> Thanks.|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>> Hi,
>> I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table
>> where
>> CONV_ID and CLIENT_ID form the pk, there is another column
>> CONVERSATION_DATE
>> where the conversation data is registered (and other columns).
>> Now I need to retrieve, for each client, the last N (for some clients,
>> eventually, less then N) conversations with one T-SQL statement.
>> Does anyone knows how to do this? Is it possible with T-SQL only?
>> Thanks.
>sql