Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Wednesday, March 28, 2012

How to use variables as a Counter?

While inserting data into a target table, I'm trying to populate a primary key ID field sequentially. For each record, the value of the primary key field needs to be incremented by one (a counter).

I've tried to use the RowCount transformation to store the values in a variable. I'm able to successfully do that; however, I don't know how to read or update the variable incrementally.

If someone knows how to perform this task, please let me know. I would greatly appreciate it.

I think this: http://www.sqlis.com/default.aspx?37 should do what you need.


As will this: http://www.sqlis.com/default.aspx?93

They both do the same thing. One uses script, one is a custom component.

-Jamie

|||

I used the script component and it does exactly what I need it to do.

Thanks, Jamie.

Monday, March 26, 2012

How to use value of a variable in defining data type

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :

DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER

--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.

Please suggest

Thanks in Advance

Quote:

Originally Posted by aviansh

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :


DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER


--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.


Please suggest

Thanks in Advance



Examine this line more closely...

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE

at what point are you declaring either of these variable datatypes?

Jim :)

How to use two keys in subquery

I can use a key in subquery, like
Select * from Employees where key1 in (Select key1,from SalesPerson)
But if I have two keys like
Select * from Employees where key1 and key2 in (Select key1, key2 from
SalesPerson..)
The above is wrong, but how can I do it?
Use EXISTS:
SELECT *
FROM employees AS e
WHERE EXISTS
(SELECT *
FROM salesperson AS P
WHERE P.key1 = E.key1
AND P.key2 = E.key2) ;
Or possibly using a join:
SELECT *
FROM employees AS e
JOIN salesperson AS P
ON P.key1 = E.key1
AND P.key2 = E.key2 ;
David Portas
SQL Server MVP

How to use two keys in subquery

I can use a key in subquery, like
Select * from Employees where key1 in (Select key1,from SalesPerson)
But if I have two keys like
Select * from Employees where key1 and key2 in (Select key1, key2 from
SalesPerson..)
The above is wrong, but how can I do it?Use EXISTS:
SELECT *
FROM employees AS e
WHERE EXISTS
(SELECT *
FROM salesperson AS P
WHERE P.key1 = E.key1
AND P.key2 = E.key2) ;
Or possibly using a join:
SELECT *
FROM employees AS e
JOIN salesperson AS P
ON P.key1 = E.key1
AND P.key2 = E.key2 ;
David Portas
SQL Server MVP
--

How to use two keys in subquery

I can use a key in subquery, like
Select * from Employees where key1 in (Select key1,from SalesPerson)
But if I have two keys like
Select * from Employees where key1 and key2 in (Select key1, key2 from
SalesPerson..)
The above is wrong, but how can I do it?Use EXISTS:
SELECT *
FROM employees AS e
WHERE EXISTS
(SELECT *
FROM salesperson AS P
WHERE P.key1 = E.key1
AND P.key2 = E.key2) ;
Or possibly using a join:
SELECT *
FROM employees AS e
JOIN salesperson AS P
ON P.key1 = E.key1
AND P.key2 = E.key2 ;
--
David Portas
SQL Server MVP
--

Wednesday, March 21, 2012

how to use sql encryption via an asp site. is this correct?

ive been playing around with encryption, just encrypting a column with a symmetric key, and having that symmetric key encrypted with a cert, and all that , and it works well through the SSMS, but i want to know how that will be used if im using sql server as a back end database for an asp web site, or an application. does the sql server have to be protected with an SSL cert, and once a user has logged in, what would the steps be to view encrypted data?

lets say i have an asp.net site wtih forms authentication, where the username and password are checked against values stored in a table, all done via https so the password data cant fall into the wrong hands. once the user is authenticate this is my idea:

each user has a status, stored in the status column of the users table : Admin or RegularUser.

if the user status is an Admin, and clicks on something that returns encrypted data, a sproc checks the user has admin status and uses impersonation to impersonate a login that has control to use the encryption key that encrypted the data. so the data is returned unencrypted.

is this a valid way of using encryption via asp.net, or are their security issues?

|||

SSL protects the communication with the server; the encryption you experimented with is for protecting the data within the server. For the steps required to view the encrypted data, it is up to you to determine those in your database application - you can have the user manually decrypt or you can have him access the data via views that automatically decrypt.

Thanks
Laurentiu

|||

You can provide access to data via a view that automatically decrypts using decryptbykeyautocert, for example. You can then group your Admin users in a role and grant permissions to that role to use the key. Admin users would then be able to decrypt the data and see it; regular users would not be able to.

Thanks
Laurentiu

|||that makes sense. thanks. one thing though. as the only account to access the sql server is the account the website is running under, how can i grant different groups of users different security levels? could it be as simple as once they log into the site ( which checks they are in the users table and checks a col called SecurityLevel) and if they have a level of "Admin User", the site will call sprocs for them that uses impersonation of a login that has admin access, where as if they only have "standard User" security level, the site will call sprocs that uses a login with low priviledges via impersonation to get their data. is this a viable solution or is it a round about way of doing something that could be much easier?

How to Use SMO to Create Primary Key

All,
I am using Microsoft Management Objects (SMO) to create a database with it's
associated tables.
All works well until I try to define more than one index as a primary key.
See code below:
// Add two primary key indices
Index pkIndex = new Index(myTable, "PK_MyTable");
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "Column1"));
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex2, "Column2"));
// Add index to the table
myTable.Indexes.Add(pkIndex);
// Create table in database
myTable.Create(); // Throws exception!
Also tried it this way:
// Add two primary key indices
Index pkIndex = new Index(myTable, "PK_MyTable1");
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "Column1"));
Index pkIndex2 = new Index(myTable, "PK_MyTable2");
pkIndex2.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex2.IndexedColumns.Add(new IndexedColumn(pkIndex2, "Column2"));
// Add index to the table
myTable.Indexes.Add(pkIndex);
myTable.Indexes.Add(pkIndex2);
// Create table in database
myTable.Create(); // Throws exception!
Does anyone know how to define more than one primary key for a table using
SMO?
Thanks in advance.All,
Disregard this question.
Problem was with a column definition, not with the primary key index creatio
n.
Note that the following code works:
// Add two primary key indices
Index pkIndex = new Index(myTable, "PK_MyTable");
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "Column1"));
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex2, "Column2"));
// Add index to the table
myTable.Indexes.Add(pkIndex);
// Create table in database
myTable.Create();

Monday, March 12, 2012

How to use ODBC to find a record by key in a larger result set

I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
working fine in a report style program and it's very fast. In Maintenance
programs, I need to find a specific record and/or scroll from there. I can
get the scrolling next and prior working ok and I can get the calling up a
single account ok but scrolling immediately after a single record rowset fin
d
only works if I issue another select statement for the entire file. Of cours
e
the first sqlfetch gets the first record and not the first record after the
one that was just on screen. In other words I need to know how to retain the
absolute row position in the file some way so my scrolling can start from
there.Briefly, you need to bind column 0 (the bookmark column). There are some
set up steps you need to take (Setting 'use bookmarks' values to ON, etc.)
I'm not sure how much experience you have, but you should be able to find
some useful information on http://msdn.microsoft.com by searching for "ODBC
BOOKMARK". For example, I found an interesting article at
http://msdn.microsoft.com/library/d...-us/odbc/htm/od
bcscrolling_by_bookmark.asp
| Thread-Topic: How to use ODBC to find a record by key in a larger result
set
| thread-index: AcW5Qb9KPSrQ97YTT32NMc+1qaZ1Iw==
| X-WBNR-Posting-Host: 70.61.11.50
| From: "examnotes" <BobMTenn@.discussions.microsoft.com>
| Subject: How to use ODBC to find a record by key in a larger result set
| Date: Wed, 14 Sep 2005 08:34:08 -0700
| Lines: 10
| Message-ID: <BFB597E8-F6B0-4541-8345-52B400B58E86@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2708
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
| working fine in a report style program and it's very fast. In Maintenance
| programs, I need to find a specific record and/or scroll from there. I
can
| get the scrolling next and prior working ok and I can get the calling up
a
| single account ok but scrolling immediately after a single record rowset
find
| only works if I issue another select statement for the entire file. Of
course
| the first sqlfetch gets the first record and not the first record after
the
| one that was just on screen. In other words I need to know how to retain
the
| absolute row position in the file some way so my scrolling can start from
| there.
|

How to use ODBC to find a record by key in a larger result set

I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
working fine in a report style program and it's very fast. In Maintenance
programs, I need to find a specific record and/or scroll from there. I can
get the scrolling next and prior working ok and I can get the calling up a
single account ok but scrolling immediately after a single record rowset find
only works if I issue another select statement for the entire file. Of course
the first sqlfetch gets the first record and not the first record after the
one that was just on screen. In other words I need to know how to retain the
absolute row position in the file some way so my scrolling can start from
there.
Briefly, you need to bind column 0 (the bookmark column). There are some
set up steps you need to take (Setting 'use bookmarks' values to ON, etc.)
I'm not sure how much experience you have, but you should be able to find
some useful information on http://msdn.microsoft.com by searching for "ODBC
BOOKMARK". For example, I found an interesting article at
http://msdn.microsoft.com/library/de...us/odbc/htm/od
bcscrolling_by_bookmark.asp
| Thread-Topic: How to use ODBC to find a record by key in a larger result
set
| thread-index: AcW5Qb9KPSrQ97YTT32NMc+1qaZ1Iw==
| X-WBNR-Posting-Host: 70.61.11.50
| From: "=?Utf-8?B?Qm9iTVRlbm4=?=" <BobMTenn@.discussions.microsoft.com>
| Subject: How to use ODBC to find a record by key in a larger result set
| Date: Wed, 14 Sep 2005 08:34:08 -0700
| Lines: 10
| Message-ID: <BFB597E8-F6B0-4541-8345-52B400B58E86@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2708
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| I'm using SQL32.INC,SQLEXT32.INC etc for the first time. I have some code
| working fine in a report style program and it's very fast. In Maintenance
| programs, I need to find a specific record and/or scroll from there. I
can
| get the scrolling next and prior working ok and I can get the calling up
a
| single account ok but scrolling immediately after a single record rowset
find
| only works if I issue another select statement for the entire file. Of
course
| the first sqlfetch gets the first record and not the first record after
the
| one that was just on screen. In other words I need to know how to retain
the
| absolute row position in the file some way so my scrolling can start from
| there.
|

Wednesday, March 7, 2012

How to use Clustering Algorytm is SQL Server 2005

Hi,

Pl clarify how to use SQL server 2005 clustering Algorythm , I want to use the case and nested table concept. Pl let me know what is key column , Input and product with an example

I have table in the following structure

Cust_id

Age

Product

Location

Income

the above mentioned columns are in my tableand i want to perdict which product is mostly likely to sold with other based on age , location and income

Pl clarify and mail me on rajesh.ladda@.lntinfotech.com

Thanks in advance

Rajesh Ladda

Essentially to use a case/nested table you need two tables. The first would have

Cust_id, Age, Location, Income (CustID becomes the mining key)

The second

Cust_id, Product (Product becomes the nested table key)

In general if you have one table, you don't need nested tables, but if you have lots of products you still may want to. In this case, you would use the same table as the case and nested table and mark the columns appropriately.

For prediction, Clustering is not likely to give you a good response. I would use Trees or Association Rules.

Friday, February 24, 2012

How to use BETWEEN with custom-ordered values

We have a 10 digit primary key value in this format: M000123456. The
order for this key is first determined by positions 3 and 4 in this
example, then positions 1 and 2. So a brief sample of correct ordering
would look like this:
M000001501
M000011501
M000021501
M000001601
M000011601
M000021601

Now my question: how can I use a BETWEEN (or > and <) in my WHERE
clause to get a range of values for this column? I use the following
ORDER BY clause to control how the results are sorted, but I can't get
the same logic to work with BETWEEN in a WHERE clause.

ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
How do I return values between M000011501 and M000011601 for example?On 19 Dec 2004 16:36:14 -0800, ian.proffer@.gmail.com wrote:

>We have a 10 digit primary key value in this format: M000123456. The
>order for this key is first determined by positions 3 and 4 in this
>example, then positions 1 and 2. So a brief sample of correct ordering
>would look like this:
>M000001501
>M000011501
>M000021501
>M000001601
>M000011601
>M000021601
>Now my question: how can I use a BETWEEN (or > and <) in my WHERE
>clause to get a range of values for this column? I use the following
>ORDER BY clause to control how the results are sorted, but I can't get
>the same logic to work with BETWEEN in a WHERE clause.
>ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
>How do I return values between M000011501 and M000011601 for example?

Hi Ian,

Usually, this kind of requirement is a sign of a bad table design. I have
the suspicion that both the two digits in position 3 and 4 and the two
digits in position 1 and 2 have a specific meaning in your business. If
that is the case, you should probably store these as seperate columns. You
can always paste the different values together for outputting as one
column.

Anyway, based on your ORDER BY clause, the BETWEEN predicate would read

WHERE SUBSTRING(<columnname>, 7, 2) + SUBSTRING (<columnname>, 5, 2)
BETWEEN SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2)
AND SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi

It may be easier if you stored the correctly ordered string rather than the
one used for display, and have the display string in a view/computed column
or part of the select statement.

You don't say how the rest of the string is arranged, but something like:

SELECT SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2),
* FROM <tablename>
WHERE SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2) between
SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2) + SUBSTRING
('M000011501', 1, 4) + SUBSTRING ('M000011501', 9, 2)
and SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2) +
SUBSTRING ('M000011601', 1, 4) + SUBSTRING ('M000011601', 9, 2)
ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)

will give your result.

John

<ian.proffer@.gmail.com> wrote in message
news:1103502974.453433.144600@.f14g2000cwb.googlegr oups.com...
> We have a 10 digit primary key value in this format: M000123456. The
> order for this key is first determined by positions 3 and 4 in this
> example, then positions 1 and 2. So a brief sample of correct ordering
> would look like this:
> M000001501
> M000011501
> M000021501
> M000001601
> M000011601
> M000021601
> Now my question: how can I use a BETWEEN (or > and <) in my WHERE
> clause to get a range of values for this column? I use the following
> ORDER BY clause to control how the results are sorted, but I can't get
> the same logic to work with BETWEEN in a WHERE clause.
> ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
> How do I return values between M000011501 and M000011601 for example?|||Thanks for the reply Hugo. (I don't usually multi-post, btw,
but...sorry.) Your solution works great, even though I didn't
accurately post my sample data (where M000251501 is followed by
M000001601).

And oh, if only I could redesign the table! Out of my control however
with this application <sigh>.

Thanks again,
-- Ian

how to use appsettings to connect to the SQL server

Can you tell me how to use Appsettings to connect to the SQL server to retrieve data?

i use:

<appSettings>
<add key="ITR_DW_ConnectionString" value="server=wangjin ; user id=sa ;password=?password?;database=data" />
</appSettings>
But that doesn't work.
how can i get the user id and password?And how should i set the database in SQL server?
thanks a lot !

then i use:
<appSettings>
<add key="ITR_DW_ConnectionString" value="Server=wangjin;Database=data;Trusted_Connection=True;" />
</appSettings>
But during the connection the system reply:
can't log in 'wangjin\ASPNET'
:( what can i do please help.First create your database in SQL Server, lets says DB1 as a name.
Create a login that will have access to that database, let's say log1 and pwd as password.
Your connection string should be something like this:

<add key="ITR_DW_ConnectionString" value="server=servername;user id=log1;password=pwd;database=DB1" /
If you now retrieve the key in your code and put that in the constructor of your connection object (or pass it to the connectionstring property) it should work|||:::( what can i do please help.

RTFM and provide full error descriptions

Btw - for connectionstrings http://www.connectionstrings.com/ has GREAT examples of ALL parameters for basically all databases. Nice shortcut fto answer questions like:

::And how should i set the database in SQL Server

and much faster than trying to find this in the documentation.

::<add key="ITR_DW_ConnectionString" value="server=wangjin ; user id=sa ;password=?
::password?;database=data" /
This SHOULD work, IF you provide a valid password and username. AND if your sql server is actually configured to allow mixed mode or sql server type logins, not just trusted connections / computer accounts.

I suggest you read up on SQL Server security setup in - the sql server documentation. The different modes are quite interesting.

Here:

::<add key="ITR_DW_ConnectionString"
::value="Server=wangjin;Database=data;Trusted_Connection=True;" /
The error message is natural. Trusted connections use the identity of the process making the connection. and wanjin\ASPNET has no rights on the database server, I assume. Pretty standard. You need to change asp.net worker process or use impersonation (lok up the term inthe documentation) if you want to use trusted connections.