Learning SQL: Rather Difficult Query Needed: And If You Know This With Explanation What You're Doing If Possible :)

Jan 7, 2008

If you know the answer please explain what you're doing if possible, that'll help me :)

I have the following tables:

CREATE TABLE [dbo].[tblUserData](
    [UserCode] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY]

CREATE TABLE [dbo].[tblFriends](
    [UserCodeOwner] [int] NOT NULL,
    [UserCodeFriend] [int] NOT NULL,
    [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate]  DEFAULT (getdate())
) ON [PRIMARY]
in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5

Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.


Example:
tblUserdata
5 peter Petertje
6 john Johnny
11 simon SimonSays
15 monique Montje

tblFriends
5 6 'createdate 30 days ago'
5 11 'createdate 5 days ago'
6 5 'createdate 30 days ago'
6 11 'createdate 3 days ago'
6 15 'createdate 7 days ago'
11 5 'createdate 5 days ago'
11 6 'createdate 3 days ago'
15 6 'createdate 7 days ago'

The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):

6 john Johnny 11 simon SimonSays 'createdate 3 days ago'
6 john Johnny 15 monique Montje 'createdate 7 days ago'

As you can see each relation is only returned twice even though there are always two entries

What would be the SQL statement, if possible without temp table..

Thanks!

View 21 Replies


ADVERTISEMENT

ReportBuilder / Report Model Explanation Needed

May 1, 2008

(I wish I was able to attach pictures....anyhow I'll try my best without them)

The customer's goal is to via ReportBuilder to select fields from multiple
many-to-many relationships. They are able to see the fields that exists
within the various many-to-many relationships but IS ONLY able to select
fields from ONE many-to-many relationship. Example using AdventureWorks:

DataSourceView (relationships automatically generated by wizard)
Contact ---< VendorContact >--- Vendor
Contact ---< StoreContact >--- Store
Contact ---< ContactCreditCard >--- CreditCard

So the goal in the ReportBuilder is to be able select fields from
Contact/Vendor/Store/ and CreditCard.
PROBLEM is that the ReportBuilder ONLY allows user to select fields from
Contact/Vendor OR Contact/Store OR Contact/CreditCard (exclusively)

The only workaround I've found is to reverse the relationships in the
DataSourceView to the following

Contact >--- VendorContact ---< Vendor
Contact >--- StoreContact ---< Store
Contact >--- ContactCreditCard ---< CreditCard

This works however we need an explanation as to why it only works this way?

Thanks in advance

View 1 Replies View Related

Query To Find Nth Value Explanation

Jun 20, 2007

can anyone explain how this works:

USE NORTHWIND

GO

SELECT freight
FROM orders E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))

replace N by a number. To find that Nth value from the table.

Ashley Rhodes

View 2 Replies View Related

Difficult Query

May 29, 2008

Hey i have a query i need help with.

I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child.
Example of the data is below. I think i need to do this in a sub query

ID Ind Parent Child Cost
P110041012705921.8000
W11004101270595.4500
A110041012705921.8000
B110041012705916.3500
R110041012705916.3500
B0100420043.3000
P0100420043.3000
W0100420021.6500

View 2 Replies View Related

Difficult Query Help

Jul 20, 2005

I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Difficult Query: Is This Possible In SQL?

Jul 20, 2005

suppose I have the following table:CREATE TABLE (int level, color varchar, length int, width int, heightint)It has the following rows1, "RED", 8, 10, 122, NULL, NULL, NULL, 203, NULL, 9, 82, 254, "BLUE", NULL, 67, NULL5, "GRAY", NULL NULL, NULLI want to write a query that will return me a view collapsed from"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)So I want a query that will returnGRAY, 9, 67, 25The principle is that looking from the bottom level up in each columnwe first see GRAY for color, 9 for length, 67 for width, 25 forheight. In other words, any non-NULL row in a lower level overridesthe value set at a higher level.Is this possible in SQL without using stored procedures?Thanks!- Robert

View 22 Replies View Related

Difficult T-SQL Query (for Me Anyways)

Nov 13, 2006

Hi.

I am developing for a system that receives an input from an external modem.

The Transaction is split into 2 sections,

Section 1 = grants the transaction ID,

Section 2 = deliver the transaction Data.

I have 2 corresponding tables,

One called tblremoteunitrequestID (Where the transaction ID is granted)

The other called tblremoteunitrequests (Where the transaction is completed, about 1 second later)

I am writing a diagnostic report that determines if the first part of the transaction completes but the second part fails.

I am having difficulties designing the SQL for this.

Here is some sample data for tblremoteunitrequestID: (The first stage of the transaction)

RecordDate | Serial

13/11/2006 14:00:36 0000-0000-0000-0006
13/11/2006 14:00:30 0000-0000-0000-0004
13/11/2006 13:59:04 0000-0000-0000-0092 (This didtn transaction didnt complete)
13/11/2006 12:15:22 0000-0000-0000-0092 (nor did this one)
13/11/2006 10:31:54 0000-0000-0000-0092
13/11/2006 10:00:29 0000-0000-0000-0006

Here is some sample data for tblremoteunitrequests: (The second stage of transaction, 1st stage has to be completed beforehand)

DateReceived | Serial

13/11/2006 14:00:37 0000-0000-0000-0006
13/11/2006 14:00:31 0000-0000-0000-0004
13/11/2006 10:31:56 0000-0000-0000-0092
13/11/2006 10:00:31 0000-0000-0000-0006
13/11/2006 10:00:25 0000-0000-0000-0004
13/11/2006 07:19:13 0000-0000-0000-0020

From this data I can see that serial number 0000-0000-0000-0006 Successfully completed part 1 and part 2 of the transaction, as did serial number 0000-0000-0000-0004.

Serial number 0000-0000-0000-0092 had trouble, it connected at 13:59:04 (tblremoteunitrequestID) but part 2 didnt complete, so it wasent saved in tblremoteunitrequests. The same happened at 12:15:22 but at 10:31:54 it was successful so it was saved.

I Only want to display the transactions that didnt complete, sounds easy huh?

This is what I hope to get in my Results table:

DateReceived | Serial

13/11/2006 13:59:04 0000-0000-0000-0092
13/11/2006 12:15:22 0000-0000-0000-0092

I was experimenting with T-SQL today, this is what I have done so far:

SELECT DISTINCT
TBLRemoteFeildUnitRequestID.Serial, TBLRemoteFeildUnitRequestID.RecordDate,

CASE WHEN TBLRemoteUnitRequests.DateReceived BETWEEN DATEADD(SECOND,-1,TBLRemoteFeildUnitRequestID.RecordDate) AND DATEADD(SECOND,10,TBLRemoteFeildUnitRequestID.RecordDate)

THEN ' Ok'

ELSE ' Not ok'

END AS PROBLEM

FROM TBLRemoteFeildUnitRequestID LEFT OUTER JOIN
TBLRemoteUnitRequests ON TBLRemoteFeildUnitRequestID.Serial = TBLRemoteUnitRequests.Serial
WHERE TBLRemoteFeildUnitRequestID.RecordDate BETWEEN DATEADD(WEEK, - 2, GetDate()) AND GetDate()

ORDER BY RecordDate DESC

This kinda worked, but it caused records that satisfied the between condition to be displayed twice, once as "Ok" and once as "Not ok".

Heres a sample of the result I got:

Serial | RecordDate (1st part of transaction) | Status

0000-0000-0000-0006 2006-11-13 14:00:36.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 14:00:36.000 Not ok
0000-0000-0000-0004 2006-11-13 14:00:30.000 Not ok (Duplicated)
0000-0000-0000-0004 2006-11-13 14:00:30.000 Ok
0000-0000-0000-0092 2006-11-13 13:59:04.000 Not ok (Correct) (Not duplicated)
0000-0000-0000-0092 2006-11-13 12:15:22.000 Not ok (Correct) (Not Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Not ok (Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Ok
0000-0000-0000-0006 2006-11-13 10:00:29.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 10:00:29.000 Not ok


I have just about had enough, I have wasted an entire day on this

Someone please Help

Dan

View 4 Replies View Related

Simple Or Difficult Query

Jul 6, 2005

hello,

I could need some help with a little query.

table "acme"

name1 varchar(128)
name2 varchar(128)
idate datetime

content

A,H,1/1/2005
A,H,2/1/2005
A,I,2/1/2005
A,J,3/1/2005
B,K,4/1/2005
B,L,5/1/2005

I want the following result (for 'A'):

1/1/2005,1
2/1/2005,3
3/1/2005,4

I want to filter for Column "Name1" and cumulative count the entries grouped by date.

what's the simplest solution?

best regards, thilo.

View 4 Replies View Related

Difficult Query With Many-To-Many Relationship

Sep 20, 2007

I'm writing a workflow management application for my work, and its somewhat complicated, here's a general idea of how it works:

- Anything that a company does is defined by a workflow.
- A workflow consists of tasks.
- Some tasks in a workflow can't be started until other tasks have been completed. If task A can't be started until tasks B and C are finished, then task A depends on B and C.

You might imagine that a bank has a workflow for handling a house loan. Before a bank could sign a contract with an applicant, they'd need proof of house ownership, but before they could get proof of house ownership they need an applicant's proof of identity like a driver's license or military ID.

Here's an oversimplified visual:

Each arrow points to its dependency. Each task can have multiple dependencies.

The setup above is represented in the database by a Tasks and a Dependencies table. Tasks has an ID field, and Dependencies has a TaskID and DependencyID field which are both foreign keys to Tasks.ID.

Code:

[Tasks]
ID Status Name
-- ------ ----
1 Done Start Processing Loan Application
2 Done Photocopy applicant's driver's license
3 NotDone Photocopy proof of house ownership
4 NotDone Get a copy of applicant's W-2 forms
5 NotDone Perform credit check on applicant
6 NotDone Sign loan contract


[Dependencies]
TaskID DependencyID
------ ------------
1 0
2 1
3 1
4 2
5 2
5 3
6 4
6 5


Tasks has a many-to-many relationship with itself.

Here's the hard part:
- A task can't be started until all of its dependencies have been completed.
- after a task is completed (meanings its status is marked "done"), I need to return a list of all the new tasks that are ready to be started.

When TaskID 2 is marked "Done", then TaskID 4 is ready to begin; however, TaskID 5 is not ready to begin since it depends on 2 and 3, and 3 hasn't been completed yet.

The requirements of the query are very simple, but the implementation is difficult.


I'll post a prelimenary solution in the next post:

View 1 Replies View Related

Difficult Summing Query

Nov 23, 2005

Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.

View 5 Replies View Related

A Challenge: Need To Write A Difficult Query

Aug 18, 2007

 GO
 CREATE TABLE [dbo].[Product]
 (
  [ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY,
  [Name] [varchar](52) NOT NULL,
  [Type] [smallint] NOT NULL,
 )
For this table
I have to write the querywhich willget the TOP 1 Row of each Type.
I know the alternate way of doing this by union.
But this is not professional.
Can anyone resolve this issue?

View 2 Replies View Related

Difficult Select Distinct Query

Jul 20, 2005

Hi,I have a table as followingaa Text1 aa, Join Bytes!, 15267aa Text1 aa, Join Bytes!, 16598aa Text1 aa, Join Bytes!, 17568aa Text2 aa, Join Bytes!, 25698aa Text3 aa, Join Bytes!, 12258I have to write a query as follows ...SELECT DISTINCT TOP 500 fldText, fldContact, fldItemidFROM tableWHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')In the example you can see the table has rows in which text and contact ordouble but with different itemid's. Now my employer wants me to show only 1row when text and contact or the same. He doesn't mind which itemid I show.... but I have to show one.I've an idea of how to do this using a cursor and a temporary table but Iguess that will be fatal for the performance because then I have to loopthrough all selected rows, check each row with all other rows and store theprimary key in the temporary table if dedected it isn't double. AfterwardsI can execute ... SELECT ... FROM TABLE where primary key in (selecttemp_primarykey from #temptable).I hoped I could do everything in 1 "easy" SELECT but I should not know how?Any ideas are much appreciated.Thanks a lot.Perre Van Wilrijk.

View 1 Replies View Related

Difficult Query: Return Recordset From Concatenated Strings?

Jul 20, 2005

Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray

View 7 Replies View Related

Learning Microsoft Query Analyzer

Nov 2, 2005

Hi -I'm completely new to Microsoft Query Analyzer and I need to learn itfor work to do data mining as a Product Manager. Does anyone have anyrecommendations on how to learn this and where? Which books? Whatclasses? What links to online tutorials?thanks

View 1 Replies View Related

Just An Explanation Of What This Means Please

Jan 22, 2008

Hi,
I have a project that has been given to me and need help please. The complete class is as follows
Public Class CarAccessData
    Public Function Getcarinfo() As List(Of CarInfo)
        Dim AllCarInfo As New List(Of CarInfo)
        Dim SQL As String        SQL = "SELECT [SKU], [CarMake], [CarModel], [Carprice]"
        Dim MyConnection As SqlConnection        MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("AntConnectionString1").ConnectionString)
        Dim aCmd As SqlCommand        aCmd = MyConnection.CreateCommand
        aCmd.CommandText = SQL        aCmd.CommandType = CommandType.Text
        Dim aDataReader As SqlDataReader
        Try
            MyConnection.Open()
            aDataReader = aCmd.ExecuteReader
            While aDataReader.Read()
                AllCarInfo.Add(New CarInfo(aDataReader))            End While
        Catch ex As Exception            Throw ex        Finally
            aDataReader.Close()
            MyConnection.Close()        End Try
        Return AllCarInfo    End Function
End Class
 
The bit I dont quite understand is the following snippet from above
 
 
Dim aCmd As SqlCommand        aCmd = MyConnection.CreateCommand
        aCmd.CommandText = SQL        aCmd.CommandType = CommandType.Text
        Dim aDataReader As SqlDataReader
        Try
            MyConnection.Open()
            aDataReader = aCmd.ExecuteReader
            While aDataReader.Read()
                AllCarInfo.Add(New CarInfo(aDataReader))            End While
        Catch ex As Exception            Throw ex        Finally
 
Can anyone explain this in real ABC style step by step please, just so I can start to understand this, ( I am quite new to this)
many thanks,
Anteater

View 8 Replies View Related

Explanation Of Columns_updated()

Jun 1, 2004

the statement
if ((columns_updated() & 2 + 4 + 8)) > 0) is supposed to tell me if the 2nd, 3rd or 5th columns were updated. My question is, what desginates the column 2,3,5, when 2,4,8 are in the statement

View 1 Replies View Related

Need Some Explanation On Transactions

Apr 28, 2005

I am having some problems with transactions, although it seems that the problem exists within the vb code making the db call it may be that sql is the source of the problem.

1.
There is a number of stored procs that contain transactions most of which are inserts followed by a select statement to retrieve the most recently added ID. So to start with is a select the best way or is the @@Identity. I have read the @@Identity is global, so for a external server that is running a number of databases I stayed away from it. Did I take the right actions or is there a better way?

2.
Is having sql transactions within a call from vb.net ok? I know that the sqlClient class doesn't support nested transactions, but does that include transactions within sql?

3.
if a stored proc is called, from vb contained in a sqlClient transaction, are ALL the calls from within the sproc able to be rolled back?

If there is a reliable way to obtain the Identity without containing it in a transaction, and having the internal sql transactions in the problem, then I am home free, so I am hoping this is the case.

Thanks for any help

View 11 Replies View Related

Simple Explanation Please

Jul 23, 2005

PREDICATESUsed as a clause.A. What does PREDICATES mean?B. What does it mean when used in a where clause?I checked BOL (Glossary) but get no explanation there.ThanksJay

View 5 Replies View Related

SQL Queries - Explanation Of Od

Oct 31, 2007

I am wondering what are od1 and od2 used for in SQL Queries? Are they used for joining... can anyone explain their significance in the queries below... (especially the commands in red)


USE Northwind
SELECT OrderID, CustomerID
FROM Orders o
WHERE 20 < (SELECT Quantity
FROM [Order Details] od
WHERE o.OrderID = od.OrderID AND od.ProductID = 23)USE Northwind
SELECT DISTINCT ProductName, Quantity
FROM [Order Details] od1 JOIN Products p
ON od1.ProductID = p.ProductID
WHERE Quantity = (SELECT MAX(Quantity)
FROM [Order Details] od2
WHERE od1.ProductID = od2.ProductID)

View 3 Replies View Related

Basic Syntax Explanation

Jul 23, 2005

In T sql for sql server, what is the technical difference between thecomparisons "is" and "="for example:set @test = nullprint @test is null -> trueprint @test = null -> false

View 3 Replies View Related

Very Chalanging Question - Explanation

May 31, 2006

Ok, here is a asample table representing the problem more clearlyA | B | C | D-----------------a1 b1 c1 d1a1 b2 c2 d2a3 b3 c1 d3a4 b4 c4 d3a5 b5 c5 d5a6 b6 c6 d3Tha duplications are:row 1+2 in param Arow 1+3 in param Crow 3+4+6 in param Donly row 5 is unique in all parameters.conclusion: row 1+2+3+4+6 are the same usergoal: to find all duplicated rows & to delete them all accept oneinstance to leave.Note:Finding that row 1similar to 2 in A & deleting it will loose databecause we won't know that row 1 is ALSO similar to 3 on C & later onfinding that 3 is similar to 4 & 6 on D & so onThe simple time consuming (about 2 weaks) query to acomplish the taskis:SELECT count(*),A.B,C,DFROM tblGROUP BY A,B,C,DHAVING count(*)>1I THANK YOU ALL

View 3 Replies View Related

Explanation Of SQL Server Database Files

Feb 22, 2000

When a database is created in SQL Server, 4 files are made:

(example)
DB_Data.DAT
DB_data.mdf
DB_Log.DAT
DB_log.ldf


What do each of these files contain? I can figure out that the main database is the DB_Data.DAT, but why is the transaction log a .DAT and why is there four files instead of two? etceterea.

Llyal

View 3 Replies View Related

Instr (vbscript) Function Explanation ???

Jan 15, 2001

I went to Microsoft to find some info about the function Instr. I need to
perform a search with a string similar to their example I found below. Can
anyone explain to me Microsoft's example?? I am little confused by the
parameters used and the explanation it gives back to me??

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at
position 1. Returns 0 ("W" is not found).

My problem is this:

I need to scan within SearchString for blanks/spaces characters. When I
find one, then place the values to the left and right of it in seperate
columns. For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
column.

I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?

my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' ' (note I am searching for a
space/blank character)

So would then my code be like:

Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)

How do I get whatever is returned from the Instr function to a column in a
table??

Any help would be great.

Rey

View 1 Replies View Related

Need An Explanation Of Msdtssrvr.ini.xml And Named Instances

Apr 30, 2008

We've read about a single installation of SSIS applies to all SQL instances on a server. We've also read how by default SQL Server stores SSIS packages in the MSDB database on the default instance and if you want to have packages stored on a named instance you must modify msdtssrvr.ini.xml. Here are our questions.

1 - If msdtssrvr is modified to reference a named instance, such as <ServerName>SQLTESTINST1<ServerName>, when I connect to Integration Services through Management Studio do I still specify the default instance name in the login window? That's the only way we have been able to connect and despite specifying the default instance name, the packages displayed are those of the named instance.

2 - If I am correct that you must always specify the default instance name when connecting to Integration Services, then in order to eliminate confusion regarding what SQL instance's SSIS packages I am viewing under the folder structure I can create SQL Server folders by modifying msdtssrvr.ini.xml. For example:


<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST1</Name>
<ServerName>SQLTESTINST1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST2</Name>
<ServerName>SQLTESTINST2</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>


I then restart the SSIS service and can now see the following folder structure.

Running Packages
Stored Packages

+ File System
+ MSDB
+ MSDB-INST1
+ MSDB-INST2

If I expand MSDB-INST1 I will only see packages saved on the instance named INST1. The same is true for INST2. If I expand MSDB I will only see packages saved on the default instance.

Does this sound like I am doing things correctly?

Thanks, Dave

View 3 Replies View Related

Explanation Wanted About The Service Principal Name ( SPN )

Jun 26, 2007

Hello,



In the ErrorLog of my Sql Server , i found this line :

2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.


Operating System XP Home SP2

SQL Server 2005 Express Edition with Advanced SP1 )



( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express



My problem is :

i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )

i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN

How can i do it ? ( activating NTLM ? but how ?)

I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.

As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.

I don't know whether i'm querying with the "correct" forum.

Sorry for my poor english.

I shall appreciate any help about this problem

Have a nice day

View 1 Replies View Related

Simple Explanation Of Mining Algorithms

Jun 9, 2007

Hello Everyone,



I am reading DataMining Tutorial and right now I am at the Mining Algorithms section. I cannot understand any of the algorithms. For example take the following text... what a bunch of mouthful bla bla bla it is ....


"The Microsoft Decision Trees algorithm supports both classification and regression and it works well for predictive modeling. Using the algorithm, you can predict both discrete and continuous attributes.
In building a model, the algorithm examines how each input attribute in the dataset affects the result of the predicted attribute, and then it uses the input attributes with the strongest relationship to create a series of splits, called nodes. As new nodes are added to the model, a tree structure begins to form. The top node of the tree describes the breakdown of the predicted attribute over the overall population. Each additional node is created based on the distribution of states of the predicted attribute as compared to the input attributes. If an input attribute is seen to cause the predicted attribute to favor one state over another, a new node is added to the model. The model continues to grow until none of the remaining attributes create a split that provides an improved prediction over the existing node. The model seeks to find a combination of attributes and their states that creates a disproportionate distribution of states in the predicted attribute, therefore allowing you to predict the outcome of the predicted attribute"



In the above text what is meant by discrete and continious attributes? what is regression? what is predicted attributes? what are input attributes? what is distribution of states?



Is there a source which explains these algorighms in a easier way ....

View 4 Replies View Related

Explanation Of Security Groups For 2005

Sep 21, 2005

I've been reconfiguring my Windows service accounts for the SQL Server service and the SQL Agent service to comply with the security best practices for SQL Server 2005.  Specifically, I created two new network accounts.  One account runs the SQL Server service, the other runs the SQL Agent service.

View 1 Replies View Related

Help Needed With This Query

Jul 23, 2007

i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem  , please anyone??im really stumped!   if (Radio_Btn_Book.Checked == true)        {            string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state  = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'";            SqlCommand Cmd_book = new SqlCommand(book_query, con);                        con.Open();            Cmd_book.ExecuteNonQuery();            con.Close();                   } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.  

View 3 Replies View Related

SQL Query Help Needed.

Jan 14, 2008

Hi,
TABLES 
(Association CONTAINS COLUMNS AssociationID),
(Group CONTAINS COLUMNS GroupID, GroupName, AssociationID),
(GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved),
(UserProfiles CONTAINS COLUMNS UserProfileID, UserID), 
(AllUsers CONTAINS COLUMNS UserID, FirstName, LastName)
 I am trying to write a query to collect information about  from the tables. I need to collect all the Users who are not members of Group A in Association I.
Note that Users can belong to more than one group and have more than one profile.
I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance

View 2 Replies View Related

Help Needed With A Query

Mar 20, 2008

Hi
   I have a function which returns the periodId from the period table and the function is as follows:
 ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS int
AS

BEGIN

DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 1 AND 3 THEN 5
WHEN @month BETWEEN 4 AND 6 THEN 6
WHEN @month BETWEEN 7 AND 9 THEN 7
WHEN @month BETWEEN 10 AND 12 THEN 8
END
END
 Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
 
Any help will be appreciated.
Regards,
Karen 

View 5 Replies View Related

Help Needed For This Query..

Apr 10, 2008

I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS.
How do i check if all the three parameters have the same value..
Any help will be appreciated..
Regards,
Karen

View 5 Replies View Related

Help Needed With This Query

Apr 25, 2008

Hi,
  I have this query
 SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId
Inner Join User c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840
16534/25/2008 10:15:42 AM378550
16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045
16624/25/2008 2:30:43 PM4174387130
16634/25/2008 2:47:14 PM154825
16644/25/2008 2:49:10 PM265085
  it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
  
 So it kist

View 3 Replies View Related

SQL Query - Help Needed

Nov 17, 2003

Hi everyone,

I consider myself good at SQL when it comes to SELECTS, UPDATES, INSERTS etc - but now I'm actually wanting to delete a record.

How can I do this? Can I just use:

DELETE * FROM Table WHERE RowID = '23'

??

Can anyone help me out?

Cheers
Andrew

View 4 Replies View Related







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