OK, I create a table called Customer, I imported date from my as400, when i
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on that
problem I have no more Idea.
Thanks!
JF
Seem like the primary key should be the combination of CustId and SubCustId.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF
|||Is the primary key defined on both columns? If it is, try to import the data
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF
|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustId.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
>
>
|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
|||I first delete the table and start over every time.
"Jack" wrote:
[vbcol=seagreen]
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I first delete the table and start over every time.
> "Jack" wrote:
|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
[vbcol=seagreen]
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> DTS?
> "Jean-Francois" wrote:
|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did an connection with client access driver to connect to the as400 and a
> query the as400 to retreive the data.
> "Jack" wrote:
|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...[vbcol=seagreen]
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts
Wednesday, March 21, 2012
Primary Key Problem
OK, I create a table called Customer, I imported date from my as400, when i
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on that
problem I have no more Idea.
Thanks!
JFSeem like the primary key should be the combination of CustId and SubCustId.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||Is the primary key defined on both columns? If it is, try to import the data
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustId.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > OK, I create a table called Customer, I imported date from my as400, when i
> > want to set a primary key it telling me that i have duplicate key.
> > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > have more then one same CustId, but SubCustID have not the same SubCustID.
> >
> > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > can be the same but the sub is always different. Could anyone help me on that
> > problem I have no more Idea.
> >
> > Thanks!
> > JF
>
>|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
> > Seem like the primary key should be the combination of CustId and SubCustId.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > OK, I create a table called Customer, I imported date from my as400, when i
> > > want to set a primary key it telling me that i have duplicate key.
> > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > >
> > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > can be the same but the sub is always different. Could anyone help me on that
> > > problem I have no more Idea.
> > >
> > > Thanks!
> > > JF
> >
> >
> >|||I first delete the table and start over every time.
"Jack" wrote:
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
> > I did
> > SELECT CustId, SubCustId FROM table
> > GROUP BY CustId, SubCustId
> > HAVING COUNT(*) > 1
> >
> > But it give me all my data. That seem to be that all my data is duplicate.
> > I try to import the data before put the primary key and after put the
> > primary key and get the same result.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seem like the primary key should be the combination of CustId and SubCustId.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > http://www.sqlug.se/
> > >
> > >
> > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > want to set a primary key it telling me that i have duplicate key.
> > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > >
> > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > can be the same but the sub is always different. Could anyone help me on that
> > > > problem I have no more Idea.
> > > >
> > > > Thanks!
> > > > JF
> > >
> > >
> > >|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
> I first delete the table and start over every time.
> "Jack" wrote:
> > How many times have you try to import the data into SQL table? Did you
> > delete all records in the table before you import the data if you did it
> > multiple times?
> >
> > "Jean-Francois" wrote:
> >
> > > I did
> > > SELECT CustId, SubCustId FROM table
> > > GROUP BY CustId, SubCustId
> > > HAVING COUNT(*) > 1
> > >
> > > But it give me all my data. That seem to be that all my data is duplicate.
> > > I try to import the data before put the primary key and after put the
> > > primary key and get the same result.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > http://www.sqlug.se/
> > > >
> > > >
> > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > >
> > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > problem I have no more Idea.
> > > > >
> > > > > Thanks!
> > > > > JF
> > > >
> > > >
> > > >|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> DTS?
> "Jean-Francois" wrote:
> > I first delete the table and start over every time.
> >
> > "Jack" wrote:
> >
> > > How many times have you try to import the data into SQL table? Did you
> > > delete all records in the table before you import the data if you did it
> > > multiple times?
> > >
> > > "Jean-Francois" wrote:
> > >
> > > > I did
> > > > SELECT CustId, SubCustId FROM table
> > > > GROUP BY CustId, SubCustId
> > > > HAVING COUNT(*) > 1
> > > >
> > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > I try to import the data before put the primary key and after put the
> > > > primary key and get the same result.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > > http://www.sqlug.se/
> > > > >
> > > > >
> > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > >
> > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > problem I have no more Idea.
> > > > > >
> > > > > > Thanks!
> > > > > > JF
> > > > >
> > > > >
> > > > >|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
> I did an connection with client access driver to connect to the as400 and a
> query the as400 to retreive the data.
> "Jack" wrote:
> > Does the total row returned from the "check duplicate" query match SELECT
> > COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> > DTS?
> >
> > "Jean-Francois" wrote:
> >
> > > I first delete the table and start over every time.
> > >
> > > "Jack" wrote:
> > >
> > > > How many times have you try to import the data into SQL table? Did you
> > > > delete all records in the table before you import the data if you did it
> > > > multiple times?
> > > >
> > > > "Jean-Francois" wrote:
> > > >
> > > > > I did
> > > > > SELECT CustId, SubCustId FROM table
> > > > > GROUP BY CustId, SubCustId
> > > > > HAVING COUNT(*) > 1
> > > > >
> > > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > > I try to import the data before put the primary key and after put the
> > > > > primary key and get the same result.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > http://www.solidqualitylearning.com/
> > > > > > http://www.sqlug.se/
> > > > > >
> > > > > >
> > > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > > >
> > > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > > problem I have no more Idea.
> > > > > > >
> > > > > > > Thanks!
> > > > > > > JF
> > > > > >
> > > > > >
> > > > > >|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
>> OK, I create a table called Customer, I imported date from my as400, when
>> i
>> want to set a primary key it telling me that i have duplicate key.
>> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
>> have more then one same CustId, but SubCustID have not the same
>> SubCustID.
>> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
>> can be the same but the sub is always different. Could anyone help me on
>> that
>> problem I have no more Idea.
>> Thanks!
>> JF
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on that
problem I have no more Idea.
Thanks!
JFSeem like the primary key should be the combination of CustId and SubCustId.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||Is the primary key defined on both columns? If it is, try to import the data
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustId.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > OK, I create a table called Customer, I imported date from my as400, when i
> > want to set a primary key it telling me that i have duplicate key.
> > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > have more then one same CustId, but SubCustID have not the same SubCustID.
> >
> > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > can be the same but the sub is always different. Could anyone help me on that
> > problem I have no more Idea.
> >
> > Thanks!
> > JF
>
>|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
> > Seem like the primary key should be the combination of CustId and SubCustId.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > OK, I create a table called Customer, I imported date from my as400, when i
> > > want to set a primary key it telling me that i have duplicate key.
> > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > >
> > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > can be the same but the sub is always different. Could anyone help me on that
> > > problem I have no more Idea.
> > >
> > > Thanks!
> > > JF
> >
> >
> >|||I first delete the table and start over every time.
"Jack" wrote:
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
> > I did
> > SELECT CustId, SubCustId FROM table
> > GROUP BY CustId, SubCustId
> > HAVING COUNT(*) > 1
> >
> > But it give me all my data. That seem to be that all my data is duplicate.
> > I try to import the data before put the primary key and after put the
> > primary key and get the same result.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Seem like the primary key should be the combination of CustId and SubCustId.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > > http://www.sqlug.se/
> > >
> > >
> > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > want to set a primary key it telling me that i have duplicate key.
> > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > >
> > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > can be the same but the sub is always different. Could anyone help me on that
> > > > problem I have no more Idea.
> > > >
> > > > Thanks!
> > > > JF
> > >
> > >
> > >|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
> I first delete the table and start over every time.
> "Jack" wrote:
> > How many times have you try to import the data into SQL table? Did you
> > delete all records in the table before you import the data if you did it
> > multiple times?
> >
> > "Jean-Francois" wrote:
> >
> > > I did
> > > SELECT CustId, SubCustId FROM table
> > > GROUP BY CustId, SubCustId
> > > HAVING COUNT(*) > 1
> > >
> > > But it give me all my data. That seem to be that all my data is duplicate.
> > > I try to import the data before put the primary key and after put the
> > > primary key and get the same result.
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > > http://www.sqlug.se/
> > > >
> > > >
> > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > >
> > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > problem I have no more Idea.
> > > > >
> > > > > Thanks!
> > > > > JF
> > > >
> > > >
> > > >|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> DTS?
> "Jean-Francois" wrote:
> > I first delete the table and start over every time.
> >
> > "Jack" wrote:
> >
> > > How many times have you try to import the data into SQL table? Did you
> > > delete all records in the table before you import the data if you did it
> > > multiple times?
> > >
> > > "Jean-Francois" wrote:
> > >
> > > > I did
> > > > SELECT CustId, SubCustId FROM table
> > > > GROUP BY CustId, SubCustId
> > > > HAVING COUNT(*) > 1
> > > >
> > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > I try to import the data before put the primary key and after put the
> > > > primary key and get the same result.
> > > >
> > > >
> > > > "Tibor Karaszi" wrote:
> > > >
> > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > > http://www.sqlug.se/
> > > > >
> > > > >
> > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > >
> > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > problem I have no more Idea.
> > > > > >
> > > > > > Thanks!
> > > > > > JF
> > > > >
> > > > >
> > > > >|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
> I did an connection with client access driver to connect to the as400 and a
> query the as400 to retreive the data.
> "Jack" wrote:
> > Does the total row returned from the "check duplicate" query match SELECT
> > COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> > DTS?
> >
> > "Jean-Francois" wrote:
> >
> > > I first delete the table and start over every time.
> > >
> > > "Jack" wrote:
> > >
> > > > How many times have you try to import the data into SQL table? Did you
> > > > delete all records in the table before you import the data if you did it
> > > > multiple times?
> > > >
> > > > "Jean-Francois" wrote:
> > > >
> > > > > I did
> > > > > SELECT CustId, SubCustId FROM table
> > > > > GROUP BY CustId, SubCustId
> > > > > HAVING COUNT(*) > 1
> > > > >
> > > > > But it give me all my data. That seem to be that all my data is duplicate.
> > > > > I try to import the data before put the primary key and after put the
> > > > > primary key and get the same result.
> > > > >
> > > > >
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > > > Seem like the primary key should be the combination of CustId and SubCustId.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > http://www.solidqualitylearning.com/
> > > > > > http://www.sqlug.se/
> > > > > >
> > > > > >
> > > > > > "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> > > > > > news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> > > > > > > OK, I create a table called Customer, I imported date from my as400, when i
> > > > > > > want to set a primary key it telling me that i have duplicate key.
> > > > > > > Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> > > > > > > have more then one same CustId, but SubCustID have not the same SubCustID.
> > > > > > >
> > > > > > > example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> > > > > > > can be the same but the sub is always different. Could anyone help me on that
> > > > > > > problem I have no more Idea.
> > > > > > >
> > > > > > > Thanks!
> > > > > > > JF
> > > > > >
> > > > > >
> > > > > >|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
>> OK, I create a table called Customer, I imported date from my as400, when
>> i
>> want to set a primary key it telling me that i have duplicate key.
>> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
>> have more then one same CustId, but SubCustID have not the same
>> SubCustID.
>> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
>> can be the same but the sub is always different. Could anyone help me on
>> that
>> problem I have no more Idea.
>> Thanks!
>> JF
Primary Key Problem
OK, I create a table called Customer, I imported date from my as400, when i
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on tha
t
problem I have no more Idea.
Thanks!
JFSeem like the primary key should be the combination of CustId and SubCustId.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when
i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on t
hat
> problem I have no more Idea.
> Thanks!
> JF|||Is the primary key defined on both columns? If it is, try to import the dat
a
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when
i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on t
hat
> problem I have no more Idea.
> Thanks!
> JF|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustI
d.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
>
>|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
>|||I first delete the table and start over every time.
"Jack" wrote:
[vbcol=seagreen]
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
>|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I first delete the table and start over every time.
> "Jack" wrote:
>|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
[vbcol=seagreen]
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file
?
> DTS?
> "Jean-Francois" wrote:
>|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did an connection with client access driver to connect to the as400 and
a
> query the as400 to retreive the data.
> "Jack" wrote:
>|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...[vbcol=seagreen]
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
>
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on tha
t
problem I have no more Idea.
Thanks!
JFSeem like the primary key should be the combination of CustId and SubCustId.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when
i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on t
hat
> problem I have no more Idea.
> Thanks!
> JF|||Is the primary key defined on both columns? If it is, try to import the dat
a
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:
> OK, I create a table called Customer, I imported date from my as400, when
i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on t
hat
> problem I have no more Idea.
> Thanks!
> JF|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:
> Seem like the primary key should be the combination of CustId and SubCustI
d.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
>
>|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
>|||I first delete the table and start over every time.
"Jack" wrote:
[vbcol=seagreen]
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
>|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I first delete the table and start over every time.
> "Jack" wrote:
>|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
[vbcol=seagreen]
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file
?
> DTS?
> "Jean-Francois" wrote:
>|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did an connection with client access driver to connect to the as400 and
a
> query the as400 to retreive the data.
> "Jack" wrote:
>|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...[vbcol=seagreen]
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote:
>
Saturday, February 25, 2012
Preview only returning one record of report
I'm new to reporting services so I assume this is a stupid question however here it goes.
I created a report that will produce a simple customer invoice. I use a stored procedure to return the data. When I run the dataset in the data section it returns 5 records. When I run the report in preview mode it only returns the first record of data in the report. I did notice that when I drag and drop the dataset fields into a report it appends the record with "First(Fields!..." I assume that this will only return the first record which it appears to do. When I remove the "First" it only returns the last record in the report. How do I return all 5 of the records in the preview pane. The paging section in the preview pane in all scenerios always says 1of1 with the next page arrow grayed out.
Thanks in advance..
Hi,
use tables in stead of text boxes to visualize tabular data.
Cheers,
Yani
|||I don't want tabular data.
I want Name, Address, City State Zip of customer #1 on Page 1 Followed by detail (I use tabular data here) for that customer
Name, Address, City State Zip of customer #2 on Page 2 Followed by detail for that customer
All I get is Customet #1 (Or #5 if I remove the first stated in the original question..)
Etc..
|||Okay,
did u try setting up all controls related to a customer into aListControl.
The contained info by a List Control is repeated for reach data row, this way you could achieve your need.
Cheers
|||I'm totally lost at your suggestion??
I want to produce 5 invoices.
The Stored procedure returns data for those 5 invoices.( Name, address, city, state, zip etc..)
The report should return 5 pages with a customer invoice on each page so I can print them.
I'm not sure where a list control come into play here.
|||If You don't want ur report in a tabular form then you will have to use list control.Drop textboxes into
the list control .
|||Thank you,
I think I got it now.
One additional question I have with Formatting and Printing. Is there any type of formatting control for printing. In my research so far It appears that printing directly from the control is not an option. For now I will print to a PDF and then print. The problem I have is the PDF is creating what appears to be about a 1" margin on the page. I need the reports to print with about a .25" margin. Is there any place to control this. I will be doing checks next and the positiong will become more critical.
Thanks in advance.
|||
Go to Report Menu->Report properties -> Layout Tab.
I think this will help.
May
Monday, February 20, 2012
Preventing Remote Access to SQL Server
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.
JLLR
MCDBA MCSD
Database DeveloperThere is "a way" to prevent logging into the server unless sitting in front of it, but how do you picture those apps to continue functioning? Can you clarify your question?|||Here's the deal. SQL Server is running a machine we will call DBserver. There are several Web apps, and MTS apps running on another machine which is acting as an MTS Server and a Web Server. We will call this machine WBServer. There are also some VB exe's running on some client machines in the Network. WBServer is separated from DBServer by a firewall, (with WB server being outside the firewall). The servers are located in the server room. Inside the firewall, the are several machines which could connect to SQL Server using client tools (enterprise manager, Query analyzer, etc.) In addition, there were some machines linking to the data using ODBC connections in MS Access. Due to the fact there were alterations to the data in the tables which caused major complications to the National Defense readiness, the customer now requires all ad hoc connections to SQL Server be done on DBServer after the person making the change lsigns a logbook recording their entry into the server room and which machine on to which they will be logging. Due to the fact that individuals are violating the rules they wish to set up SQL Server to make it impossible to make an ad hoc connection other than from DBServer. However they do not want affect the way the various applications work. The obvious answer was to provide a limited number of logins and restrict their distribution. However, the passwords did not remain secret, and persons were able to get them and login. I have some thoughts bof a work around but I would like something better.
What do you think.|||If you wish to prevent users from connecting to SQL Server using Microsoft SQL Client Tools, if the are not sitting at the keyboard of the server. You could look at creating a script (job) that runs every 10 seconds let's say and looks for records in SYSPROCESSES for connections where program_name is in a list example :
SQL Query Analyzer
SQL Query Analyzer - Object Browser
SQL Profiler
ISQL-32
etc. and hostname is not equal to DBserver (using your name). Any matches would have there process terminated.
PS
What about users that create a link to SQL Server via MS Access and then perform ad-hoc queries via MS Access?|||Also Forbidden.|||Very interesting. There is the problem of server resources, and how often to run this procedure.. A user can do a lot of damage in 10 seconds. I will think about it.|||Actually a KILL is not forbidden, if you thought I meant to "DELETE" the record from sysprocesses your mistaking. As far as the 10 seconds goes that is an arbitrary number.
To correctly do what you want, you would not allow direct access to the database tables in the first place.
At our site all data queries and modifications are done via stored procedures which is in a controlled environment. Any ad-hoc queries are done on a separate server which is refreshed via database backup from production on a nightly bases.|||You misunderstand. MS Access ODBC links to the server are also forbidden.
It sounds like DBA's have some authority in your shop. Here they are just glorified data entry people until something goes wrong, then they do not listen to the advice anyway, or they ask you to build the impossible. If it were up to me, all apps would connect through Application roles., and no one would be able to connect to the DB if they were not in the an administrators role Then only certain NT Logins would be in that role. (Can't make that Admin group of Windows NT because everyone is in Admin group!!) But what do I know I am only an MCDBA.|||Tell them they can't screen their doors with chicken wire and then complain about flies in the house.
blindman|||There is a way to allow only certain applications to access a database on a SQLServer. It's called APPLICATION ROLE. This is the article in the Books on Line for it:
Establishing Application Security and Application Roles
The security system in Microsoft SQL Server is implemented at the lowest level: the database itself. This is the best method for controlling user activities regardless of the application used to communicate with SQL Server. However, sometimes security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.
Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.
SQL Server accommodates these needs through the use of application roles. Application roles are different than standard roles in that:
Application roles contain no members.
Microsoft Windows NT 4.0 or Windows 2000 groups, users, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application or applications. A user's association with an application role is due to his ability to run an application that activates the role, rather than his being a member of the role.
Application roles are inactive by default and require a password to be activated.
Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.
To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.
Application roles allow the application, rather than SQL Server, to take over the responsibility of user authentication. However, because SQL Server still must authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.
If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure.
There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.
When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.
If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to an instance of SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to an instance of SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.
Note Application roles work with both authentication modes.
Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Query Analyzer or any other tool. To ensure this, create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. Then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to an instance of SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.
To create an application role
Transact-SQL
Enterprise Manager
How to create an application role (Enterprise Manager)
To create an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which to create a role.
Right-click Roles, and then click New Database Role.
In the Name box, enter the name of the new application role.
Under Database role type, click Application role, and then enter a password.
See Also
Establishing Application Security and Application Roles
SQL-DMO
To set an application role
Transact-SQL
To change the password of an application role
Transact-SQL
SQL-DMO
To remove an application role
Transact-SQL
Enterprise Manager
How to remove an application role (Enterprise Manager)
To remove an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which the application role exists.
Click Roles.
In the details pane, right-click the application role to remove, and then click Delete.
Confirm the deletion.
See Also
Establishing Application Security and Application Roles
SQL-DMO
For more information search the web with google and ask for application role sql server
Good luck
ionut
Originally posted by Joeller
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.
JLLR
MCDBA MCSD
Database Developer|||to inut: As stated earlier that is what I would do if I were able to do what I want. But I can't and that's all there is to that.
To blindman: It would be nice to tell these people what I think of their office politics, their so-called back up plans, their horrific excuse for a database etc., but that would probably cost our company it major customer and put 3/4 of the people here out of work. So I smile and tell them I will investigate ways to do what they want. It appears that the suggestion earlier re Killing the process after it starts will end up ing the only way to do this, as that is the third time I have gotten this suggestion. Then maybe I can put forward my plan for role based security. sigh.|||...but make sure it isn't YOUR head that rolls when (not if) the house of cards collapses.
blindman
JLLR
MCDBA MCSD
Database DeveloperThere is "a way" to prevent logging into the server unless sitting in front of it, but how do you picture those apps to continue functioning? Can you clarify your question?|||Here's the deal. SQL Server is running a machine we will call DBserver. There are several Web apps, and MTS apps running on another machine which is acting as an MTS Server and a Web Server. We will call this machine WBServer. There are also some VB exe's running on some client machines in the Network. WBServer is separated from DBServer by a firewall, (with WB server being outside the firewall). The servers are located in the server room. Inside the firewall, the are several machines which could connect to SQL Server using client tools (enterprise manager, Query analyzer, etc.) In addition, there were some machines linking to the data using ODBC connections in MS Access. Due to the fact there were alterations to the data in the tables which caused major complications to the National Defense readiness, the customer now requires all ad hoc connections to SQL Server be done on DBServer after the person making the change lsigns a logbook recording their entry into the server room and which machine on to which they will be logging. Due to the fact that individuals are violating the rules they wish to set up SQL Server to make it impossible to make an ad hoc connection other than from DBServer. However they do not want affect the way the various applications work. The obvious answer was to provide a limited number of logins and restrict their distribution. However, the passwords did not remain secret, and persons were able to get them and login. I have some thoughts bof a work around but I would like something better.
What do you think.|||If you wish to prevent users from connecting to SQL Server using Microsoft SQL Client Tools, if the are not sitting at the keyboard of the server. You could look at creating a script (job) that runs every 10 seconds let's say and looks for records in SYSPROCESSES for connections where program_name is in a list example :
SQL Query Analyzer
SQL Query Analyzer - Object Browser
SQL Profiler
ISQL-32
etc. and hostname is not equal to DBserver (using your name). Any matches would have there process terminated.
PS
What about users that create a link to SQL Server via MS Access and then perform ad-hoc queries via MS Access?|||Also Forbidden.|||Very interesting. There is the problem of server resources, and how often to run this procedure.. A user can do a lot of damage in 10 seconds. I will think about it.|||Actually a KILL is not forbidden, if you thought I meant to "DELETE" the record from sysprocesses your mistaking. As far as the 10 seconds goes that is an arbitrary number.
To correctly do what you want, you would not allow direct access to the database tables in the first place.
At our site all data queries and modifications are done via stored procedures which is in a controlled environment. Any ad-hoc queries are done on a separate server which is refreshed via database backup from production on a nightly bases.|||You misunderstand. MS Access ODBC links to the server are also forbidden.
It sounds like DBA's have some authority in your shop. Here they are just glorified data entry people until something goes wrong, then they do not listen to the advice anyway, or they ask you to build the impossible. If it were up to me, all apps would connect through Application roles., and no one would be able to connect to the DB if they were not in the an administrators role Then only certain NT Logins would be in that role. (Can't make that Admin group of Windows NT because everyone is in Admin group!!) But what do I know I am only an MCDBA.|||Tell them they can't screen their doors with chicken wire and then complain about flies in the house.
blindman|||There is a way to allow only certain applications to access a database on a SQLServer. It's called APPLICATION ROLE. This is the article in the Books on Line for it:
Establishing Application Security and Application Roles
The security system in Microsoft SQL Server is implemented at the lowest level: the database itself. This is the best method for controlling user activities regardless of the application used to communicate with SQL Server. However, sometimes security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.
Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.
SQL Server accommodates these needs through the use of application roles. Application roles are different than standard roles in that:
Application roles contain no members.
Microsoft Windows NT 4.0 or Windows 2000 groups, users, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user's connection through a specific application or applications. A user's association with an application role is due to his ability to run an application that activates the role, rather than his being a member of the role.
Application roles are inactive by default and require a password to be activated.
Application roles bypass standard permissions.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of an instance of SQL Server.
To ensure that all the functions of the application can be performed, a connection must lose default permissions applied to the login and user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked so the user can use the application successfully. Application roles overcome any conflicts with user's default permissions by temporarily suspending the user's default permissions and assigning them only the permissions of the application role.
Application roles allow the application, rather than SQL Server, to take over the responsibility of user authentication. However, because SQL Server still must authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.
If ad hoc access to a database is not required, users and Windows NT 4.0 or Windows 2000 groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, standardizing on one system-wide password assigned to an application role is possible, assuming access to the applications is secure.
There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or a SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to an instance of SQL Server, when the role is activated.
When an application user connects to an instance of SQL Server using Windows Authentication Mode, an application role can be used to set the permissions the Windows NT 4.0 or Windows 2000 user has in a database when using the application. This method allows Windows NT 4.0 or Windows 2000 auditing of the user account and control over user permissions, while she uses the application, to be easily maintained.
If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to an instance of SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to an instance of SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.
Note Application roles work with both authentication modes.
Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Query Analyzer or any other tool. To ensure this, create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. Then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to an instance of SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.
To create an application role
Transact-SQL
Enterprise Manager
How to create an application role (Enterprise Manager)
To create an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which to create a role.
Right-click Roles, and then click New Database Role.
In the Name box, enter the name of the new application role.
Under Database role type, click Application role, and then enter a password.
See Also
Establishing Application Security and Application Roles
SQL-DMO
To set an application role
Transact-SQL
To change the password of an application role
Transact-SQL
SQL-DMO
To remove an application role
Transact-SQL
Enterprise Manager
How to remove an application role (Enterprise Manager)
To remove an application role
Expand a server group, and then expand a server.
Expand Databases, and then expand the database in which the application role exists.
Click Roles.
In the details pane, right-click the application role to remove, and then click Delete.
Confirm the deletion.
See Also
Establishing Application Security and Application Roles
SQL-DMO
For more information search the web with google and ask for application role sql server
Good luck
ionut
Originally posted by Joeller
Our company's customer wants to prevent anyone from logging into SQL Server unless they are actually logged on the server machine. (In other words they do not want a user to be able to connect unless he or she is sitting at the server's keyboard). They do not wish to utilize NT authenication. In addition, there are a number of ASP and MTS apps that are required to connect using ADO connections. These apps are located on another server which is acting as the Web Server and the MTS Server. This server is on the other side of a firewall. I have been searching the Web for weeks now and have been unable to find any reference to anything similar. Any advice would be most appreciated.
JLLR
MCDBA MCSD
Database Developer|||to inut: As stated earlier that is what I would do if I were able to do what I want. But I can't and that's all there is to that.
To blindman: It would be nice to tell these people what I think of their office politics, their so-called back up plans, their horrific excuse for a database etc., but that would probably cost our company it major customer and put 3/4 of the people here out of work. So I smile and tell them I will investigate ways to do what they want. It appears that the suggestion earlier re Killing the process after it starts will end up ing the only way to do this, as that is the third time I have gotten this suggestion. Then maybe I can put forward my plan for role based security. sigh.|||...but make sure it isn't YOUR head that rolls when (not if) the house of cards collapses.
blindman
Subscribe to:
Posts (Atom)