How do I get a multi user table created without DBA involved?

Question

How do I get a multi user table created without DBA involved?

nikhil_kapoor1712

SSC Rookie

Points: 47
More actions
April 14, 2016 at 1:01 pm

#315526

Hello All,
I have a situation where I have to pull data from many tables creating & merging multiple temp tables to create a final temp table in Sql Server 2008. The data is then copied to excel for analysis & reporting.
I want a solution using which I can create a table that can be used by multiple users for reporting and analytics on running the query everyday without actually having a DBA involved to create a permanent table in database?
Is this possible?
Also is it possible to extract the table from SQL server without having to copy in Excel as it is difficult to copy records greater than a million rows into excel?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the public role has access to tempdb, so you could create a semi-permanent table that would exist until the sql service was stopped and started/server rebooted, by creating the table without the # sign.

CREATE TABLE tempdb.dbo.MyResults (ResultsId int,ResultsText varchar(30))

as far as extracting data, excel is a great tool, but even if it would handle ten million rows, you logically cannot review a million or more rows in it anyway, so leave it on the server and query/group the data instead.

don't export details at that level, would be the first thing i'd look at.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nikhil_kapoor1712 SSC Rookie Points: 47 More actions · Answer 2

nikhil_kapoor1712

SSC Rookie

Points: 47

April 14, 2016 at 1:14 pm

#1872151

Thank you. Appreciate your help.

nikhil_kapoor1712 SSC Rookie Points: 47 More actions · Answer 3

I do not have the permissions to use tempdb.dbo.* for table name but # works. ANy work around it so that multiple sessions can use the temp table created.

Nikhil.

matak SSCrazy Points: 2674 More actions · Answer 4

Global temp tables will help ## instead of #.

Just keep in mind they will disappear with every sql service restart.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

matak (4/14/2016)
Global temp tables will help ## instead of #.
Just keep in mind they will disappear with every sql service restart.

And when the connection which created them closes and no one else is accessing the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Because of what GilaMonster brought up, get the DBA involved. Make a permanent table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

And out of curiosity, why are you trying to back door the DBA? Not a good way to influence or make friends with a DBA.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Lynn Pettis (4/15/2016)
And out of curiosity, why are you trying to back door the DBA? Not a good way to influence or make friends with a DBA.

Further on Lynn's question, why on earth would you do this knowing that any DBA would drop your login if caught in the act?

😎

Alternatively you could install SQL Express on your PC/Laptop/Tablet, load the data into it and work from there...

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 9

Eirikur Eiriksson (4/15/2016)
Lynn Pettis (4/15/2016)
And out of curiosity, why are you trying to back door the DBA? Not a good way to influence or make friends with a DBA.
Further on Lynn's question, why on earth would you do this knowing that any DBA would drop your login if caught in the act?

Where I work, this is the fastest way to a pink slip and a potential lawsuit. My employer is a big believer in database security and would fire anyone who didn't follow procedure, involve the DBAs, and get the job done correctly.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 10

Ask your DBA if she can create a special database for which you are the owner, so you can use it to create your tables and stage data for reporting purposes. Also, you could install and instance of SQL Server Express Edition either locally on your PC or somehwere on the network, and then coordinate with the DBA to grant other uers access to it. Express Edition is free and can support multiple users and up to 10 GB per database, which should be more than enough for staging small to medium sized datasets for reporting.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho