Data Storing Problem......

Hi everyone,

I have some data which is in korean Language and i want to store that data in sql server 2000 table's row.But i am not able to store it.

When i try to store it then it display me square box in table.

Does anybody have any idea about this matter?

Plz reply me back as soon as possible.

Thanks

---Nimesh Patel

Please check out Books On Line for SQL Collations. Setting the collation level allows you to store data in different languagues.|||

Collations have nothing to do with what you can or can't store. Collations are the rules that define the order of alphabets, which is important for sorting.

Are the columns that you are storing the data into defined as nvarchar, nchar, or ntext?

After pulling out the data from the database, are you displaying it in a font that actually has those characters?

||| I think I need to check out the BOL first. thanks for the correction Motley and good to see you back. Its been kinda dull here lately..|||Good to see you are still around as well ;-)|||Yes.. been fighting the battle all alone|||You've never been alone, you've just been doing such a great job that I rarely have to say anything anymore

Data Source passwords in Config Files

Anybody find a sound approach to being able to store DataSource connection information in a configuration and be able to access a Data Source that requires a password for login?

Storing it as a configuraiton in the DB is not an option, as you require the pw to access the DB that would store it.

Can security be applied to an environment variable? Registry?

Anyone successfully use the registry to store configuration values?I'm not sure what your criterion for sound is, so I don't know if this meets it :), but you can store credentials (username & password) in a configuration file, and then make an environment variable which gives its path, and then configure a Package Configuration in the SSIS package to use the environment variable.
Of course you have to configure the environment variable for each machine (and for each user, if two users on one machine will use different credentials -- therefore, different configuration files).
Then, the credentials being now in one or more files, secure access to these files via traditional NTFS ACLs (file pemissions).
|||

I guess by sound, I meant, any way that works... when i store the pw in the config the .xml file represents it like the following:

- <Configuration ConfiguredType="Property" Path="\Package.Connections [MDB].Properties[Password]" ValueType="String">

<ConfiguredValue />

</Configuration>

This results in the error:
Error 1 Validation error. Data Flow Task: OLE DB Source [1]: The AcquireConnection method call to the connection manager "MDB" failed with error code 0xC0202009. Package2.dtsx 0 0

Have you been able to do this?.. am I missing something? I am also storing the connection string, initial catalog, and username in the config file.

|||When the file is generated by Visual Studio, it will not write the pw. I was able to get it to work by modifying the file directly.. duh.|||I'm assuming that you're not on a domain and so cannot use integrated authentication?
K|||Yes, Joe, We've been using the solution I explained above, with different configurations for different developers on the same machine (so they can use different login names, so the processes are distinguishable in SQL Server Enterprise Manager).
But, we've been using SQL Server logons (not integrated), because we're using a SQL Server 2000 backend. What you quoted sounds like you're using an mdb backend, which we've not tried.
To set up a new configuration with a new set of credentials, I first add the environment variable for that user, pointing it to where the config file is going to be. Then I put a config file there -- I take an existing one, and using a text editor, change the logon name & password (stored in plaintext in the file).
The very first time we did this, SSIS generated the config file for us; since then, we've been hand editing it for each machine and/or credential combination.

|||Here is an edited copy of our config file. Everything beginning with Zz is something that I edited before posting (along with the nifty DEADBEEF guid).
Also I added the carriage returns and whitespaces -- the raw config file is a whitespaceless mess.
******************

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="ZzMachineName\ZzUserName" GeneratedFromPackageName="Zz Our Nifty Package" GeneratedFromPackageID="{DEADBEEF-AAAA-BBBB-CCCC-DEADBEEFBEEF}" GeneratedDate="7/26/2005 4:25:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data security

My boss would like to store sensitive information in our company SQL Server
database. At the momnt everyone has write access to all data in the
database. in addition the developers can basically do anything with the
database.
I am using Windows security and need to deny acces to the data in the table
to eeryone apart from a couple of groups. In some cases i need to deny
access to data just in one column. I know you can do this, but how reliable
is it?
I am trying to figure out where to start on this.
Should the sensitive data only live in the live database? Should we release
the database and revoke rights at tat moment in time? Presumably i could
write a script to do this.
Is there any way to have the sensitive data in a database that the
developers are developing and be comfortable that the data is secure?
Is the only really safe harbour encryption?
Also, if anyone out there knows a good book on security architecture for SQL
Server, a recommendation would be greatly appreciated.
TIA
SarahSQL Server security is reliable and it works... Regarding DEV... Some
companies treat highly sensitive data by jumbling up anything that
uniquelly identifies the individual prior to making a copy of the data for
devs.
I worked in Healthcare Development, and we all tested with copies of live
sensitive data most of the time... However there were occasions when we
would get copies of data from hospitals that came in with all identifying
data scrambled or removed.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Why not put it in a seperate database or table, linked to your original
database, and then you have complete control on permissions, without as much
chance of making an 'error' and granting access to the wrong peeps ?
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||look into using Stored procedures. these can be used with logins. and grant
rights.
it is a lot of work, but will give you the best protection.
Next is to use the encrypt table. I don't like that option my self.
the quickest way to isolate a DB is to user another DB to make the calls to
the stored procedures.
Note: if you SQL server, can be physically accessed by anyone, then all
these procedures are useless.
"Sarah" <Sarah@.TT.com> wrote in message
news:#mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Use a copy of the database and run a qry on User & Company identifying
details and Swap all the identifying records around.
Did this when I pass on a production db to another developer.
Don Grover
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>

Data security

My boss would like to store sensitive information in our company SQL Server
database. At the momnt everyone has write access to all data in the
database. in addition the developers can basically do anything with the
database.
I am using Windows security and need to deny acces to the data in the table
to eeryone apart from a couple of groups. In some cases i need to deny
access to data just in one column. I know you can do this, but how reliable
is it?
I am trying to figure out where to start on this.
Should the sensitive data only live in the live database? Should we release
the database and revoke rights at tat moment in time? Presumably i could
write a script to do this.
Is there any way to have the sensitive data in a database that the
developers are developing and be comfortable that the data is secure?
Is the only really safe harbour encryption?
Also, if anyone out there knows a good book on security architecture for SQL
Server, a recommendation would be greatly appreciated.
TIA
SarahSQL Server security is reliable and it works... Regarding DEV... Some
companies treat highly sensitive data by jumbling up anything that
uniquelly identifies the individual prior to making a copy of the data for
devs.
I worked in Healthcare Development, and we all tested with copies of live
sensitive data most of the time... However there were occasions when we
would get copies of data from hospitals that came in with all identifying
data scrambled or removed.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Why not put it in a seperate database or table, linked to your original
database, and then you have complete control on permissions, without as much
chance of making an 'error' and granting access to the wrong peeps ?
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||look into using Stored procedures. these can be used with logins. and grant
rights.
it is a lot of work, but will give you the best protection.
Next is to use the encrypt table. I don't like that option my self.
the quickest way to isolate a DB is to user another DB to make the calls to
the stored procedures.
Note: if you SQL server, can be physically accessed by anyone, then all
these procedures are useless.
"Sarah" <Sarah@.TT.com> wrote in message
news:#mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>|||Use a copy of the database and run a qry on User & Company identifying
details and Swap all the identifying records around.
Did this when I pass on a production db to another developer.
Don Grover
"Sarah" <Sarah@.TT.com> wrote in message
news:%23mrTozL8DHA.2044@.TK2MSFTNGP10.phx.gbl...
> My boss would like to store sensitive information in our company SQL
Server
> database. At the momnt everyone has write access to all data in the
> database. in addition the developers can basically do anything with the
> database.
> I am using Windows security and need to deny acces to the data in the
table
> to eeryone apart from a couple of groups. In some cases i need to deny
> access to data just in one column. I know you can do this, but how
reliable
> is it?
> I am trying to figure out where to start on this.
> Should the sensitive data only live in the live database? Should we
release
> the database and revoke rights at tat moment in time? Presumably i could
> write a script to do this.
> Is there any way to have the sensitive data in a database that the
> developers are developing and be comfortable that the data is secure?
> Is the only really safe harbour encryption?
> Also, if anyone out there knows a good book on security architecture for
SQL
> Server, a recommendation would be greatly appreciated.
> TIA
> Sarah
>

data reader ?

is it possible to use a data reader to read from 2 tables with 1 store procedure(sp)?

---------------

ex:

create sp1

as

select * from tbl1

select * from tbl2

---------------

how can i use a data reader to read the items from tbl2?

If you have SqlServer 2005 (inc Express), you can use MARS -MultipleActiveResultSets. It's only available with SqlClient, so won't work with Oracle, or MySQL etc.|||

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

|||

Use a DataSet in this instance to retrieve your results. Then, you can retrieve the items out of the two tables that will be returned.

|||

It looks like you can support mulitple resultsets with a DataReader as well although I've always used a DataSet in this instance. See the following article.

http://msdn.microsoft.com/msdnmag/issues/04/06/DataPoints/#S2

|||

TATWORTH:

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

As I understand it, that's only the case when using the SqlDataSource control, although I can't remember whether it can only hold the first resultset, or the last....

sql

Data Pump

My client is using a sql server 7.0 to store realtime data like heat,temp,pressure etc inserted every second.He wants me to provide a solution to transfer the summarised data to oracle server on a regular basis..say once on every 5 min..
what approach i should take...help me out plsSet up a DTS package to send the data from SQL Server to Oracle.
Create a scheduled job to run the package every five minutes.|||In SQL Server 2000 replication can solve your problem but I do not know about SQL Server 7.0.

Blindman's idea is easy & will be work for you...|||I can't recommend that anybody include DTS packages as part of any long-term solution now that SQL Server 2005 no longer supports them.|||I can't recommend that anybody include DTS packages as part of any long-term solution now that SQL Server 2005 no longer supports them.I suppose that by the strictest definition that is true, but I tend to think of SSIS (http://msdn.microsoft.com/SQL/bi/integration/default.aspx) as being the descendant of DTS.

-PatP|||Descendent, bastard child, unholy spawn, however you want to call it...|||Descendent, bastard child, unholy spawn, however you want to call it...So, how do you really feel about SSIS? You don't have to hold back, we understand. ;)

-PatP|||select @.Functionality/@.Documentation

----
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Data Manipulation

HI everyone,

I have a column STATUS of type bit(1,0) , i would like to manipulate it(without editing the database) in the Store Procedure in such a way that

if
STATUS =1 // return result STATUS= 'Active '
Else if
STATUS = 0 // return result STATUS = 'Inactive'
End if

My Stored Procedure Is As Follow

SELECT
STATUS + '--' + NAME AS STATUSNAME
FROM M_USER
END

Desired Result When Populated Into a Listbox Should Be As Follow

Active--Tester
Inactive--Tester1

Thanks In Advance for the Help Provided!

SELECT
 CASE When Status = 1 Then 'Active'
 ELSE 'Inactive'
 END As STATUSNAME
FROM M_USER

:)

JB|||Thanks JBelthoff for the reply,

I follow ur example but its still not working , the code are as follow,

IF (@.MASTER = 'NRICSEARCH')
BEGIN
SELECT
CASE WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS ACTIVE,
USER_ID,
NRIC + '--' + NAME+ '(' + CONVERT(VARCHAR, ACTIVE) + ')' AS NRICNAME
FROM M_USER
ORDER BY NRIC

--
Column ACTIVE is of type bit.
NRICNAME is used in the listbox as datatextfield

TheCurrent Output from the current code in the listbox is as follow,

123--Ben(0)
124--Andy(1)

TheDesired Output in the listbox is as follow,

123--Ben(ACTIVE)
124--Andy(INACTIVE)

Apparantly, The CASE is not working.|||To get a 1 column result set in your desired output.......

SELECT CAST(USER_ID As Varchar) + '--' + NAME + '(' +
CASE
WHEN ACTIVE= 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END
+ ')' As NRICNAME
FROM M_USER

:-D

Enjoy!

JB|||Hi JB,

Its working now, thanks a lot.

data in uppercase.

Hi,
Is there any way to store all data in tables in uppercase?This may be helpful...

print Upper(convert(datetime, '09/21/2002'))

The Function Upper() convert all characters to uppercase!

Data for Report Services

Our developers approached me with this problem of formatting output for a
report. Hereâ's the record set
Store No SubDepartment
100 Clothes
200 Clothes
200 Shoes
200 Bunnies
The want to use SQL Syntax to return the following:
Store No SubDepartments
100 Clothes
200 Clothes, Shoes, Bunnies
Any suggestions? Thank you.Concatenating row values in Transact-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"brymer28303" wrote:
> Our developers approached me with this problem of formatting output for a
> report. Hereâ's the record set
> Store No SubDepartment
> 100 Clothes
> 200 Clothes
> 200 Shoes
> 200 Bunnies
> The want to use SQL Syntax to return the following:
> Store No SubDepartments
> 100 Clothes
> 200 Clothes, Shoes, Bunnies
> Any suggestions? Thank you.
>