Script to Export SQL Tables to Access

Mello

I am not sure if this is eaven possible but I need a script to quickly
export selected tables from a SQL database into Access.

The script should preferably run from an ASP page on demand or perhaps
be triggered by an update of one of the selected tables.

Any help will be greatly appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

You could either use DTS and run the package or create a linked server and
use regular insert statements.

Dor DTS this may help:
http://www.sqldts.com/default.aspx?288

John

"Mello" <anonymous@.devdex.com> wrote in message
news:41383ecb$0$26170$c397aba@.news.newsgroups.ws.. .
> Mello
> I am not sure if this is eaven possible but I need a script to quickly
> export selected tables from a SQL database into Access.
> The script should preferably run from an ASP page on demand or perhaps
> be triggered by an update of one of the selected tables.
> Any help will be greatly appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Nic?

DTS should work just fine, or just write an aspx to fill a dataset, then
use a component to create an MDB. Fill the MDB from the dataset and
you're good.

Weird that this was posted under my account, I never posted the original
message in this thread.

Good Luck,
Joel

--
Joel Mello
Mello Software Consulting
www.MelloSoftwareConsulting.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

SCRIPT TO ALTER for functions in a SQL 2000

I installed SQL Server Management Express and try to access to a SQL Server 2000 remotly.

I connected correctly but when I try to alter an existing function, I get the following error message:

TITLE: Microsoft SQL Server Management Studio Express

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[functionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

Note that functionName contain the correct function name.

Can someone confirm me that SQL Server Managmt Express can not alter function from previous SQL version or may I need to set some parameters on Server 2000.

Thanks

I'm moving this to the Tools forum.

Mike

sql

script to add users

Hi I have to add a lot of users to sql server with datareader access I have
created the script below but this does not work do you know what I would hav
e
to cahnge to get it working?
thanks for any help
Sammy
DECLARE @.user varchar (8)
DECLARE @.SQLString NVARCHAR(500)
DECLARE @.pwd NVARCHAR(8)
DECLARE @.db NVARCHAR(10)
SET @.SQLString =
N'EXEC sp_addlogin ''@.user'', ''@.pwd'', ''@.db''
use ''@.db''
EXEC sp_grantdbaccess ''@.user'',''@.user''
EXEC sp_addrolemember ''db_datareader'',''@.user''
'
SET @.user = 'clairep'
set @.pwd = 'london'
set @.db= 'pubs'Try this
DECLARE @.user varchar (8)
DECLARE @.SQLString NVARCHAR(500)
DECLARE @.pwd NVARCHAR(8)
DECLARE @.db NVARCHAR(10)
SET @.user = 'clairep'
set @.pwd = 'london'
set @.db= 'pubs'
SET @.SQLString = 'EXEC sp_addlogin ' + @.user + ', ' + @.pwd + ', ' + @.db
SET @.SQLString = @.SQLString + ' USE ' + @.db
SET @.SQLString = @.SQLString + ' EXEC sp_grantdbaccess ' + @.user + ', ' + @.us
er
SET @.SQLString = @.SQLString + ' EXEC sp_addrolemember db_datareader, ' + @.us
er
EXEC (@.SQLString)
Andy
"Sammy" wrote:

> Hi I have to add a lot of users to sql server with datareader access I hav
e
> created the script below but this does not work do you know what I would h
ave
> to cahnge to get it working?
> thanks for any help
> Sammy
>
> DECLARE @.user varchar (8)
> DECLARE @.SQLString NVARCHAR(500)
> DECLARE @.pwd NVARCHAR(8)
> DECLARE @.db NVARCHAR(10)
>
> SET @.SQLString =
> N'EXEC sp_addlogin ''@.user'', ''@.pwd'', ''@.db''
> use ''@.db''
> EXEC sp_grantdbaccess ''@.user'',''@.user''
> EXEC sp_addrolemember ''db_datareader'',''@.user''
> '
> SET @.user = 'clairep'
> set @.pwd = 'london'
> set @.db= 'pubs'|||Try,
DECLARE @.user varchar (8)
DECLARE @.SQLString NVARCHAR(500)
DECLARE @.pwd NVARCHAR(8)
DECLARE @.db NVARCHAR(10)
SET @.user = N'clairep'
set @.pwd = N'london'
set @.db= N'pubs'
SET @.SQLString = N'use master' + char(13)
SET @.SQLString = @.SQLString + N'EXEC sp_addlogin ''' + @.user + N''', ''' +
@.pwd + N''', ''' + @.db + N'''' + char(13)
SET @.SQLString = @.SQLString + N'use ' + quotename(@.db) + char(13)
SET @.SQLString = @.SQLString + N'EXEC sp_grantdbaccess ''' + @.user + N''','''
+ @.user + N'''' + char(13)
SET @.SQLString = @.SQLString + N'EXEC sp_addrolemember ''db_datareader'','''
+ @.user + N''''
execute sp_executesql @.SQLString
go
AMB
"Sammy" wrote:

> Hi I have to add a lot of users to sql server with datareader access I hav
e
> created the script below but this does not work do you know what I would h
ave
> to cahnge to get it working?
> thanks for any help
> Sammy
>
> DECLARE @.user varchar (8)
> DECLARE @.SQLString NVARCHAR(500)
> DECLARE @.pwd NVARCHAR(8)
> DECLARE @.db NVARCHAR(10)
>
> SET @.SQLString =
> N'EXEC sp_addlogin ''@.user'', ''@.pwd'', ''@.db''
> use ''@.db''
> EXEC sp_grantdbaccess ''@.user'',''@.user''
> EXEC sp_addrolemember ''db_datareader'',''@.user''
> '
> SET @.user = 'clairep'
> set @.pwd = 'london'
> set @.db= 'pubs'

Scrambling Data in a table

We have a bunch of data that includes things like DNS names and host names. For testing purposes, we want to give people access to our data but we do not want them to see the real names of the items I mentioned prior. What I would like to do is a letter replacement for each character in the name, thus A could become T, B could become U, etc. It's not a big deal if the end user can unscrable these, they probably won't anyways and it's not a big deal if they do, our client just wants something to make it not so obvious.

Is this possible to do through a SQL query and if so, can you give my pointers on how to accomplish this?

Otherwise I will probably use a VB solution, but it would be similar if I could just write a SQL query that I could send to anyone that needs to do this.

you can use SQL to replace things in select statements

read this :http://www.java2s.com/Code/SQL/Function/CallREPLACEfunctioninselectclause.htm

hth,
mcm

|||

U can use Replace function

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

You can create an SP which applies some encoding logic over the supplied value and one SP which does exactly the reverse of it. The encoding and decoding logic can absolutely be your ( which ever way you like it to be ). Generally you can play with the ascii value of the character. One caution that should be exercised is that your encode and decode logic must be strong and tested enough for you can't bare wrong values when encoding or decoding.

Hope this will help.

|||

I see how I can use replace to replace one letter, but how would I go about replacing all the letters? Some of my tables have thousands of records, if I have to run a select and then update query for every letter, it's going to take a LONG time.

|||

Hi,

but how would I go about replacing all the letters? Some of my tables have thousands of records, if I have to run a select and then update query for every letter, it's going to take a LONG time.

It seems that you have misunderstood the community members' suggestions. What you want to do is just to hide the real information since you don't want the information to be seen by the users. So we replacing all the records in database would not be a good practice, and actually it's not necessary. As mentioned by others, you have two ways to achieve that.

First, write the "replace" logic in your application level, not in the database level. When you have retrieve a real data from the database, you should replace the specific value against the data before displaying.

Another way is, you can create a stored procedure, after you have select the result set, just use SELECT REPLACE (Ep1,Ep2,Ep3) to replace the char in the result.

As for the speed, the replace operation against a variable would not take much time.

Thanks.

|||

I could do this in the application level, but i've been asked to do it in the database, so that's what I'm trying to do.

And I want to replace the data in the database... I see how the replace function works to replace one character at a time, but when you run your select query, you seem to only have the option to replace one letter at a time. I need to replace 26 letters and 10 numbers, which is what I'm not sure how to do.

SELECT REPLACE(name, "a", "x")

That would replace one character... I have to do it 35 more times for all the different letters and numbers, and then I have to acctually update the database with the new values. And then I have to do it on multiple fields too.

|||

UPDATE YourTable

SET YourField=REPLACE(REPLACE(REPLACE(YourField,"a","x"),"b","y"),"c","z")

Of course, that'll get really nasty with 36 of them, and you will run into problems because if you first replace a with x, when you get to replacing x with whatever, now you are replacing the old x and the new x with something. I would suggest writing a user defined function (UDF) to do the conversion for you. Then you can just:

UPDATE YourTable

SET YourField=dbo.Scramble(YourField)

|||

Kriswd40:

It's not a big deal if the end user can unscrable these, they probably won't anyways and it's not a big deal if they do, our client just wants something to make it not so obvious.

With due respect to your client, that makes no sense at all. Either you hide the information or you don't. Making it "not obvious" is ridiculous. And what's the point of making it available in develpment if they can't read it, anyway?

|||

It's not all fields that I'm changing, just IP Addresses, DNS entries, serial numbers, ect so the data is still very usable. It's not live data, periodically we are given a backup of the data we can use for developement but the client no longer wants us to have access to some of the sensative information in this data. The best thing to do would be to completely encrypt/randomize these fields so they cannot be recovered, but at this time we do nothing so at least this is a step in the right direction.