Hi,
I was using a BULK INSERT statement in a stored procedure.Could any one help me out on one prob.I wanted to let the user select the file he wants to update and then i want to pass this address as a parameter in the stored procedure.
Suppose there is a parameter @.loc,so i want to use this parameter as
Bulk insert TableName from @.loc with(fieldterminator=',')
plz help me out thxI haven't test this but you may take help of Dynamic SQL, http://www.sommarskog.se/dynamic_sql.html fyi.|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task|||Did you even try dynamic sql as suggested to you?
declare @.fn varchar(255)
set @.fn='\\mypc\tmp\tmp1.txt'
exec ('bulk insert mytable from '''+@.fn+''' with(fieldterminator='','')')|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task
u haven't replied to Satya's solution.Then how we know that u got the answer or not?|||Even here u are giving the static location of the file from which u want the bulk insert the data from.What i wanted was to allow the user to define the path of the file and i wanted to pass this path as a parameter .In the example u have given its seems to me the same thing.May be if you could explain me more as i am a newbie.Sorry for the inconvienience.|||also i did try this code-:
@.loc nvarchar (50)
Bulk Insert Tablename From '''@.loc'' with(fieldterminator='','')
but i get an error while saving this stored procedure which says
"Cant find @.loc"|||post ur stored procedure|||Even here u are giving the static location
No, I am using a variable. Here are some code for you to play with
Note: UNC path used for the load as the server is not running on my local PC.
C:\tmp>echo aaa,bbb >tmp.txt
C:\tmp>more tmp.txt
aaa,bbb
C:\tmp>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql" -U sa -S devdb
Password:
1> use tempdb
2> go
1> create proc pdreyer_load
2> @.filename varchar(255)
3> as
4> exec ('bulk insert #t1 from '''+@.filename+''' with(fieldterminator='','')')
5> go
1> create table #t1 (f1 varchar(10),f2 varchar(10))
2> exec pdreyer_load '\\pdreyer\tmp\tmp.txt'
3> select * from #t1
4> go
f1 f2
---- ----
aaa bbb
(1 row affected)
1> drop table #t1
2> drop procedure pdreyer_load
3> go
1> exit
C:\tmp>del tmp.txt|||Is the source file changed all the time?
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
BULK INSERT permissions for non-admin?
I've got a user, "joe", logging into a SqlServer 2005 database using
sqlserver authentication. Joe can create tables, insert, select, drop --
whatever -- but when he tries to do a bulk insert, he gets:
"The current user is not the database or object owner of table 'myTable'.
Cannot perform SET operation."
WARNING: I don't know much about SQL Server permissions administration. I
write queries. :)
When I look at the database in SQL Server Management Studio, I see that the
table is in the schema "dbo". What's the safest way to give Joe permission
to do a bulk insert into this table?
Thanks!"Jesse" <nospam@.nospam.com> wrote in message
news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> I've got a user, "joe", logging into a SqlServer 2005 database using
> sqlserver authentication. Joe can create tables, insert, select, drop --
> whatever -- but when he tries to do a bulk insert, he gets:
> "The current user is not the database or object owner of table 'myTable'.
> Cannot perform SET operation."
> WARNING: I don't know much about SQL Server permissions administration. I
> write queries. :)
> When I look at the database in SQL Server Management Studio, I see that
> the table is in the schema "dbo". What's the safest way to give Joe
> permission to do a bulk insert into this table?
>
The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and perhaps
ALTER TABLE. In addition if the user is connected using SQL Server
authentication then the SQL Server account must be able to read the file.
If the user is authenticated by Windows Integrated Authentication, then the
user, not the SQL Server account, needs to be able to read the file.
It's all in the BOL:
BULK INSERT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
Security Considerations for Using Transact-SQL to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms186286.aspx
David|||David --
Thanks for the quick reply! Unfortunately, I must still be missing
something -- I've gone through and granted everything I can think of, but I
still get the same error.
Specifically, here's what I did:
grant alter to [joe]
grant ADMINISTER BULK OPERATIONS to [joe]
grant insert to [joe]
Then, from Server Management Studio,
add the bulkadmin role to joe
add the sy
min role to joe
add the db_ddladmin role to joe
add the db_owner role to joe
Joe now has the power to destroy the whole database -- but not to bulk
insert. I still get, "The current user is not the database or object owner
of table 'myTable'. Cannot perform SET operation."
What else could I be missing?
Thanks again for your help!
Jesse
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OusnToXZGHA.428@.TK2MSFTNGP02.phx.gbl...
> "Jesse" <nospam@.nospam.com> wrote in message
> news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and
> perhaps ALTER TABLE. In addition if the user is connected using SQL
> Server authentication then the SQL Server account must be able to read the
> file. If the user is authenticated by Windows Integrated Authentication,
> then the user, not the SQL Server account, needs to be able to read the
> file.
> It's all in the BOL:
>
> BULK INSERT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
>
> Security Considerations for Using Transact-SQL to Bulk Import Data
> http://msdn2.microsoft.com/en-us/library/ms186286.aspx
>
>
> David
>
>|||One more detail:
- if I log into the database using Server Management Studio, I can run the
bulk insert OK.
- if I try to run it from an ASP.NET script, I get the permissions error.
The asp.net script is connecting to the database with the same credentials I
use to log in via SMS! Something must be working differently, but what?
This is driving me nuts. Thanks for any help you can give!
Here's how my asp.net script connects:
<add name="production" connectionString="data source=myLocalMachine; initial
catalog=myCatalog; user=joe; password=joe;"
providerName="System.Data.SqlClient"/>
"Jesse" <nospam@.nospam.com> wrote in message
news:e8jp30XZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> David --
> Thanks for the quick reply! Unfortunately, I must still be missing
> something -- I've gone through and granted everything I can think of, but
> I still get the same error.
> Specifically, here's what I did:
> grant alter to [joe]
> grant ADMINISTER BULK OPERATIONS to [joe]
> grant insert to [joe]
> Then, from Server Management Studio,
> add the bulkadmin role to joe
> add the symin role to joe
> add the db_ddladmin role to joe
> add the db_owner role to joe
> Joe now has the power to destroy the whole database -- but not to bulk
> insert. I still get, "The current user is not the database or object owner
> of table 'myTable'. Cannot perform SET operation."
> What else could I be missing?
> Thanks again for your help!
>
> Jesse|||"Jesse" <nospam@.nospam.com> wrote in message
news:eiNZPSYZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> One more detail:
> - if I log into the database using Server Management Studio, I can run the
> bulk insert OK.
> - if I try to run it from an ASP.NET script, I get the permissions error.
> The asp.net script is connecting to the database with the same credentials
> I use to log in via SMS! Something must be working differently, but what?
> This is driving me nuts. Thanks for any help you can give!
>
> Here's how my asp.net script connects:
> <add name="production" connectionString="data source=myLocalMachine;
> initial catalog=myCatalog; user=joe; password=joe;"
> providerName="System.Data.SqlClient"/>
>
Strange.
This works for me.
Run this as a symin. I have a database called test and xp_cmdshell
enabled.
You might run Profiler to see what's actually being sent from the web site.
David
--setup.sql--
use test
master..xp_cmdshell 'dir > c:\data\foo.txt'
master..xp_cmdshell 'type c:\data\foo.txt'
create table LoadTable(data varchar(4000))
bulk insert LoadTable from 'c:\data\foo.txt'
select * from LoadTable
truncate table LoadTable
go
use master
create login Joe with password='Joe'
grant administer bulk operations to Joe
go
use test
create user Joe for login Joe
grant view definition on schema::dbo to Joe
grant select,insert,delete on LoadTable to Joe
--end setup.sql--
then run this .NET program
--Program.cs--
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
namespace csTest
{
public static class Program
{
public static void Main()
{
SqlConnectionStringBuilder cb1 = new SqlConnectionStringBuilder();
cb1.DataSource = "192.168.2.10";
cb1.IntegratedSecurity = false;
cb1.UserID = "Joe";
cb1.Password = "Joe";
cb1.InitialCatalog = "Test";
using (SqlConnection con = new SqlConnection(cb1.ConnectionString))
{
con.Open();
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessa
ge);
new SqlCommand(@."delete from LoadTable", con).ExecuteNonQuery();
new SqlCommand(@."bulk insert LoadTable from
'c:\data\foo.txt'",con).ExecuteNonQuery();
using (SqlDataReader rdr = new SqlCommand("select * from
LoadTable",con).ExecuteReader())
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
Console.ReadLine();
}
static void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}
}
}
--end Program.cs---
sqlserver authentication. Joe can create tables, insert, select, drop --
whatever -- but when he tries to do a bulk insert, he gets:
"The current user is not the database or object owner of table 'myTable'.
Cannot perform SET operation."
WARNING: I don't know much about SQL Server permissions administration. I
write queries. :)
When I look at the database in SQL Server Management Studio, I see that the
table is in the schema "dbo". What's the safest way to give Joe permission
to do a bulk insert into this table?
Thanks!"Jesse" <nospam@.nospam.com> wrote in message
news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> I've got a user, "joe", logging into a SqlServer 2005 database using
> sqlserver authentication. Joe can create tables, insert, select, drop --
> whatever -- but when he tries to do a bulk insert, he gets:
> "The current user is not the database or object owner of table 'myTable'.
> Cannot perform SET operation."
> WARNING: I don't know much about SQL Server permissions administration. I
> write queries. :)
> When I look at the database in SQL Server Management Studio, I see that
> the table is in the schema "dbo". What's the safest way to give Joe
> permission to do a bulk insert into this table?
>
The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and perhaps
ALTER TABLE. In addition if the user is connected using SQL Server
authentication then the SQL Server account must be able to read the file.
If the user is authenticated by Windows Integrated Authentication, then the
user, not the SQL Server account, needs to be able to read the file.
It's all in the BOL:
BULK INSERT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
Security Considerations for Using Transact-SQL to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms186286.aspx
David|||David --
Thanks for the quick reply! Unfortunately, I must still be missing
something -- I've gone through and granted everything I can think of, but I
still get the same error.
Specifically, here's what I did:
grant alter to [joe]
grant ADMINISTER BULK OPERATIONS to [joe]
grant insert to [joe]
Then, from Server Management Studio,
add the bulkadmin role to joe
add the sy
min role to joeadd the db_ddladmin role to joe
add the db_owner role to joe
Joe now has the power to destroy the whole database -- but not to bulk
insert. I still get, "The current user is not the database or object owner
of table 'myTable'. Cannot perform SET operation."
What else could I be missing?
Thanks again for your help!
Jesse
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OusnToXZGHA.428@.TK2MSFTNGP02.phx.gbl...
> "Jesse" <nospam@.nospam.com> wrote in message
> news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and
> perhaps ALTER TABLE. In addition if the user is connected using SQL
> Server authentication then the SQL Server account must be able to read the
> file. If the user is authenticated by Windows Integrated Authentication,
> then the user, not the SQL Server account, needs to be able to read the
> file.
> It's all in the BOL:
>
> BULK INSERT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
>
> Security Considerations for Using Transact-SQL to Bulk Import Data
> http://msdn2.microsoft.com/en-us/library/ms186286.aspx
>
>
> David
>
>|||One more detail:
- if I log into the database using Server Management Studio, I can run the
bulk insert OK.
- if I try to run it from an ASP.NET script, I get the permissions error.
The asp.net script is connecting to the database with the same credentials I
use to log in via SMS! Something must be working differently, but what?
This is driving me nuts. Thanks for any help you can give!
Here's how my asp.net script connects:
<add name="production" connectionString="data source=myLocalMachine; initial
catalog=myCatalog; user=joe; password=joe;"
providerName="System.Data.SqlClient"/>
"Jesse" <nospam@.nospam.com> wrote in message
news:e8jp30XZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> David --
> Thanks for the quick reply! Unfortunately, I must still be missing
> something -- I've gone through and granted everything I can think of, but
> I still get the same error.
> Specifically, here's what I did:
> grant alter to [joe]
> grant ADMINISTER BULK OPERATIONS to [joe]
> grant insert to [joe]
> Then, from Server Management Studio,
> add the bulkadmin role to joe
> add the sy
min role to joe> add the db_ddladmin role to joe
> add the db_owner role to joe
> Joe now has the power to destroy the whole database -- but not to bulk
> insert. I still get, "The current user is not the database or object owner
> of table 'myTable'. Cannot perform SET operation."
> What else could I be missing?
> Thanks again for your help!
>
> Jesse|||"Jesse" <nospam@.nospam.com> wrote in message
news:eiNZPSYZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> One more detail:
> - if I log into the database using Server Management Studio, I can run the
> bulk insert OK.
> - if I try to run it from an ASP.NET script, I get the permissions error.
> The asp.net script is connecting to the database with the same credentials
> I use to log in via SMS! Something must be working differently, but what?
> This is driving me nuts. Thanks for any help you can give!
>
> Here's how my asp.net script connects:
> <add name="production" connectionString="data source=myLocalMachine;
> initial catalog=myCatalog; user=joe; password=joe;"
> providerName="System.Data.SqlClient"/>
>
Strange.
This works for me.
Run this as a sy
min. I have a database called test and xp_cmdshellenabled.
You might run Profiler to see what's actually being sent from the web site.
David
--setup.sql--
use test
master..xp_cmdshell 'dir > c:\data\foo.txt'
master..xp_cmdshell 'type c:\data\foo.txt'
create table LoadTable(data varchar(4000))
bulk insert LoadTable from 'c:\data\foo.txt'
select * from LoadTable
truncate table LoadTable
go
use master
create login Joe with password='Joe'
grant administer bulk operations to Joe
go
use test
create user Joe for login Joe
grant view definition on schema::dbo to Joe
grant select,insert,delete on LoadTable to Joe
--end setup.sql--
then run this .NET program
--Program.cs--
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
namespace csTest
{
public static class Program
{
public static void Main()
{
SqlConnectionStringBuilder cb1 = new SqlConnectionStringBuilder();
cb1.DataSource = "192.168.2.10";
cb1.IntegratedSecurity = false;
cb1.UserID = "Joe";
cb1.Password = "Joe";
cb1.InitialCatalog = "Test";
using (SqlConnection con = new SqlConnection(cb1.ConnectionString))
{
con.Open();
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessa
ge);
new SqlCommand(@."delete from LoadTable", con).ExecuteNonQuery();
new SqlCommand(@."bulk insert LoadTable from
'c:\data\foo.txt'",con).ExecuteNonQuery();
using (SqlDataReader rdr = new SqlCommand("select * from
LoadTable",con).ExecuteReader())
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
Console.ReadLine();
}
static void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}
}
}
--end Program.cs---
Monday, March 19, 2012
bulk insert not working
i MADE A USER AS bULK aDMIN BUT HE STILL CAN'T BULK iNSERT TO A TABLE . He has dd_writer and dd_reader roles assigned in database .
What should I do to fix this . Here is the error
The current user is not the database or object owner of table 'Temp_load'. Cannot perform SET operation.does the database bulk insert flag is on?|||Keep in mind only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
What should I do to fix this . Here is the error
The current user is not the database or object owner of table 'Temp_load'. Cannot perform SET operation.does the database bulk insert flag is on?|||Keep in mind only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
Saturday, February 25, 2012
Bulk Insert - Cannot perform SET operation.
I am trying to use Bulk Insert for a user that is not sysadmin.
I have already set up the user as a member of "bulkadmin".
I have already set up the user as a member of "bulkadmin".
When I run the following script:
DECLARE @.SQLVARCHAR(1000)
CREATE TABLE amdbo.#temp (
[id] [varchar] (10) NULL
,[fld2] [varchar] (10) NULL
,[fld3] [varchar] (10) NULL
)
set @.SQL =
'BULK INSERT amdbo.#temp
FROM ''F:\test.txt''
WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
= ''\n'')'
EXEC (@.SQL)
select * from #temp
I still get the message ...
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table '#temp'.
Cannot perform SET operation.
Anyone have an idea what I am doing wrong?
Drew.Drew (cladre@.hotmail.com) writes:
> I am trying to use Bulk Insert for a user that is not sysadmin.
> I have already set up the user as a member of "bulkadmin".
> When I run the following script:
> DECLARE @.SQL VARCHAR(1000)
> CREATE TABLE amdbo.#temp (
> [id] [varchar] (10) NULL
> ,[fld2] [varchar] (10) NULL
> ,[fld3] [varchar] (10) NULL
> )
> set @.SQL =
> 'BULK INSERT amdbo.#temp
> FROM ''F:\test.txt''
> WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
>= ''\n'')'
> EXEC (@.SQL)
> select * from #temp
> I still get the message ...
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table '#temp'.
> Cannot perform SET operation.
As I recall you cannot bulk load to temp tables if you are not sysadmin.
Or, this is somewhat weird: the user is the dbo of tempdb.
It's probably better to create a permanent table that this user is
the owner of and use that table as the target.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The solution I've done is to create a physical table, populate it, and
when the procedure ends, drop the table. Which is pretty much what
you suggested.
Thanks,
Drew
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402F24BBF061Yazorman@.127.0.0.1>...
> Drew (cladre@.hotmail.com) writes:
> > I am trying to use Bulk Insert for a user that is not sysadmin.
> > I have already set up the user as a member of "bulkadmin".
> > When I run the following script:
> > DECLARE @.SQL VARCHAR(1000)
> > CREATE TABLE amdbo.#temp (
> > [id] [varchar] (10) NULL
> > ,[fld2] [varchar] (10) NULL
> > ,[fld3] [varchar] (10) NULL
> > )
> > set @.SQL =
> > 'BULK INSERT amdbo.#temp
> > FROM ''F:\test.txt''
> > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> >= ''\n'')'
> > EXEC (@.SQL)
> > select * from #temp
> > I still get the message ...
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table '#temp'.
> > Cannot perform SET operation.
> As I recall you cannot bulk load to temp tables if you are not sysadmin.
> Or, this is somewhat weird: the user is the dbo of tempdb.
> It's probably better to create a permanent table that this user is
> the owner of and use that table as the target.|||I have the similiar issues encountered. Please refer to MS
knowledgebase 302621. Microsoft has confirmed this is an software
issue.
cladre@.hotmail.com (Drew) wrote in message news:<d00212d6.0310200700.3b2bca9f@.posting.google.com>...
> The solution I've done is to create a physical table, populate it, and
> when the procedure ends, drop the table. Which is pretty much what
> you suggested.
> Thanks,
> Drew
>
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402F24BBF061Yazorman@.127.0.0.1>...
> > Drew (cladre@.hotmail.com) writes:
> > > I am trying to use Bulk Insert for a user that is not sysadmin.
> > > I have already set up the user as a member of "bulkadmin".
> > > > When I run the following script:
> > > DECLARE @.SQL VARCHAR(1000)
> > > CREATE TABLE amdbo.#temp (
> > > [id] [varchar] (10) NULL
> > > ,[fld2] [varchar] (10) NULL
> > > ,[fld3] [varchar] (10) NULL
> > > )
> > > set @.SQL =
> > > 'BULK INSERT amdbo.#temp
> > > FROM ''F:\test.txt''
> > > WITH (DATAFILETYPE = ''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR
> > >= ''\n'')'
> > > EXEC (@.SQL)
> > > select * from #temp
> > > > I still get the message ...
> > > Server: Msg 8104, Level 16, State 2, Line 1
> > > The current user is not the database or object owner of table '#temp'.
> > > Cannot perform SET operation.
> > As I recall you cannot bulk load to temp tables if you are not sysadmin.
> > Or, this is somewhat weird: the user is the dbo of tempdb.
> > It's probably better to create a permanent table that this user is
> > the owner of and use that table as the target.
Bulk Insert
I am trying to Bulk Insert into Database from a program in VC++ on to sql2000 database.My code was successful if i use the credentials of SA user.
but i need to execute the same process with different user which has DBO privileges on the database in which i have the table.Can anyone tell me what privileges need to be granted for this dbo user to make bulk insert work.
thanks a lot for your help.
annaYou need insert privleges on the table, which are included for dbo. You should be "good to go" for everything you've described so far.
-PatP
but i need to execute the same process with different user which has DBO privileges on the database in which i have the table.Can anyone tell me what privileges need to be granted for this dbo user to make bulk insert work.
thanks a lot for your help.
annaYou need insert privleges on the table, which are included for dbo. You should be "good to go" for everything you've described so far.
-PatP
Friday, February 24, 2012
BULK INSERT
Question: All of my developers (2000) use DTS to load data. However, I jus
t
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
t
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
BULK INSERT
Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
BULK INSERT
Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...
Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...
Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...
Sunday, February 19, 2012
bulk delete
I have data in the User table with these rows UserID(PK),Usernumber,Username where userid is the primarykey
1 101 Tom
2 101 Tom
3 102 Dick
4 102 Dick
5 103 Harry
6 103 Harry
Now I want to get out put with UerId(PK),UserNumber and UserName, deleting the repeated(second occurance) row, like this...
1 101 Tom
3 102 Dick
5 103 Harry
Can any one let me know.
Thanks in adv
Hello,
Try this to get the output you request:
select min(UserID) as UserID, Usernumber, Username
from TableName
group by Usernumber, Username
order by UserID
Then, to delete the other occurances, do this:
delete from TableName
where UserID not in (select min(UserID) from TableName group by Usernumber, Username)
Hope this helps.
Jarret
|||Thanks jarret,
It worked.
Thursday, February 16, 2012
Buisnes Managment System
i am desighning a database that looks after all expences of a buisnes. please any ideas would be welcome with regards to the user interface and requirements that you would need inorder for the product to function for your buisnes or needs
Hope to here from you soon
Thank you in advance
Simon Burgess
Dmop123@.hotmail.com
Email and msnThe obvious question is why don't you just use one of the dozens of off-the-shelf accounting packages? You will definitely be reinventing the wheel here.|||all If that is the case then how has the wheel transformed form wooden spocked cart wheels to the qulity of pirelli (whitch would you put on your car).
Within every thing there is always room for inprovement as nothing is perfect, but things can only get beter if constructive critisism is given.
chears|||One person may carve a wagon wheel but it takes a team of experts in many fields to create a Pirelli, plus one more to improve on it.|||thank you.
Hope to here from you soon
Thank you in advance
Simon Burgess
Dmop123@.hotmail.com
Email and msnThe obvious question is why don't you just use one of the dozens of off-the-shelf accounting packages? You will definitely be reinventing the wheel here.|||all If that is the case then how has the wheel transformed form wooden spocked cart wheels to the qulity of pirelli (whitch would you put on your car).
Within every thing there is always room for inprovement as nothing is perfect, but things can only get beter if constructive critisism is given.
chears|||One person may carve a wagon wheel but it takes a team of experts in many fields to create a Pirelli, plus one more to improve on it.|||thank you.
BUILTIN\Administrators
Dear All,
Can someone tell me what this actually is and what it,
more importantly where I would get it changed.
The reason is that a user (an ex developer) can give
themselves SA rights although through there NT
Authorization however they are not an administrator and
should not be allowed to access any of the server security
properies.
The only theory I have is that the BUILTIN\Administrators
is allowing SA access as its the only group I currently
have.
Thanks for reading
Peter
There are several ways to work around this:
1. Remove the exdevelopers from the NT administrators group and change the
password of local administrator account on the server machine.
2. An extreme solution, which I have used on my local server is to delete
this group from SQL Server. You can then add individaully or by another
group that you can control who has sa access on the server. I would
recommend you test this solution well before trying it in production.
Amol.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:b57301c43762$44127700$a101280a@.phx.gbl...
> Dear All,
> Can someone tell me what this actually is and what it,
> more importantly where I would get it changed.
> The reason is that a user (an ex developer) can give
> themselves SA rights although through there NT
> Authorization however they are not an administrator and
> should not be allowed to access any of the server security
> properies.
> The only theory I have is that the BUILTIN\Administrators
> is allowing SA access as its the only group I currently
> have.
> Thanks for reading
> Peter
>
|||Thanks Amol
Peter
>--Original Message--
>There are several ways to work around this:
>1. Remove the exdevelopers from the NT administrators
group and change the
>password of local administrator account on the server
machine.
>2. An extreme solution, which I have used on my local
server is to delete
>this group from SQL Server. You can then add individaully
or by another
>group that you can control who has sa access on the
server. I would
>recommend you test this solution well before trying it in
production.
>Amol.
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:b57301c43762$44127700$a101280a@.phx.gbl...
security[vbcol=seagreen]
BUILTIN\Administrators
>
>.
>
|||The second solution is potentially dangerous. Services, such as the Full
Text engine require the BUILTIN\Administrators group to be given access to
SQL Server...
Cheers,
James Goodman
"Amol Kasbekar" <apk@.nospam.com> wrote in message
news:%23vclye2NEHA.3944@.tk2msftngp13.phx.gbl...
> There are several ways to work around this:
> 1. Remove the exdevelopers from the NT administrators group and change the
> password of local administrator account on the server machine.
> 2. An extreme solution, which I have used on my local server is to delete
> this group from SQL Server. You can then add individaully or by another
> group that you can control who has sa access on the server. I would
> recommend you test this solution well before trying it in production.
> Amol.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:b57301c43762$44127700$a101280a@.phx.gbl...
>
Can someone tell me what this actually is and what it,
more importantly where I would get it changed.
The reason is that a user (an ex developer) can give
themselves SA rights although through there NT
Authorization however they are not an administrator and
should not be allowed to access any of the server security
properies.
The only theory I have is that the BUILTIN\Administrators
is allowing SA access as its the only group I currently
have.
Thanks for reading
Peter
There are several ways to work around this:
1. Remove the exdevelopers from the NT administrators group and change the
password of local administrator account on the server machine.
2. An extreme solution, which I have used on my local server is to delete
this group from SQL Server. You can then add individaully or by another
group that you can control who has sa access on the server. I would
recommend you test this solution well before trying it in production.
Amol.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:b57301c43762$44127700$a101280a@.phx.gbl...
> Dear All,
> Can someone tell me what this actually is and what it,
> more importantly where I would get it changed.
> The reason is that a user (an ex developer) can give
> themselves SA rights although through there NT
> Authorization however they are not an administrator and
> should not be allowed to access any of the server security
> properies.
> The only theory I have is that the BUILTIN\Administrators
> is allowing SA access as its the only group I currently
> have.
> Thanks for reading
> Peter
>
|||Thanks Amol
Peter
>--Original Message--
>There are several ways to work around this:
>1. Remove the exdevelopers from the NT administrators
group and change the
>password of local administrator account on the server
machine.
>2. An extreme solution, which I have used on my local
server is to delete
>this group from SQL Server. You can then add individaully
or by another
>group that you can control who has sa access on the
server. I would
>recommend you test this solution well before trying it in
production.
>Amol.
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:b57301c43762$44127700$a101280a@.phx.gbl...
security[vbcol=seagreen]
BUILTIN\Administrators
>
>.
>
|||The second solution is potentially dangerous. Services, such as the Full
Text engine require the BUILTIN\Administrators group to be given access to
SQL Server...
Cheers,
James Goodman
"Amol Kasbekar" <apk@.nospam.com> wrote in message
news:%23vclye2NEHA.3944@.tk2msftngp13.phx.gbl...
> There are several ways to work around this:
> 1. Remove the exdevelopers from the NT administrators group and change the
> password of local administrator account on the server machine.
> 2. An extreme solution, which I have used on my local server is to delete
> this group from SQL Server. You can then add individaully or by another
> group that you can control who has sa access on the server. I would
> recommend you test this solution well before trying it in production.
> Amol.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:b57301c43762$44127700$a101280a@.phx.gbl...
>
BUILTIN\Administrators
Dear All,
Can someone tell me what this actually is and what it,
more importantly where I would get it changed.
The reason is that a user (an ex developer) can give
themselves SA rights although through there NT
Authorization however they are not an administrator and
should not be allowed to access any of the server security
properies.
The only theory I have is that the BUILTIN\Administrators
is allowing SA access as its the only group I currently
have.
Thanks for reading
PeterThere are several ways to work around this:
1. Remove the exdevelopers from the NT administrators group and change the
password of local administrator account on the server machine.
2. An extreme solution, which I have used on my local server is to delete
this group from SQL Server. You can then add individaully or by another
group that you can control who has sa access on the server. I would
recommend you test this solution well before trying it in production.
Amol.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:b57301c43762$44127700$a101280a@.phx.gbl...
> Dear All,
> Can someone tell me what this actually is and what it,
> more importantly where I would get it changed.
> The reason is that a user (an ex developer) can give
> themselves SA rights although through there NT
> Authorization however they are not an administrator and
> should not be allowed to access any of the server security
> properies.
> The only theory I have is that the BUILTIN\Administrators
> is allowing SA access as its the only group I currently
> have.
> Thanks for reading
> Peter
>|||Thanks Amol
Peter
>--Original Message--
>There are several ways to work around this:
>1. Remove the exdevelopers from the NT administrators
group and change the
>password of local administrator account on the server
machine.
>2. An extreme solution, which I have used on my local
server is to delete
>this group from SQL Server. You can then add individaully
or by another
>group that you can control who has sa access on the
server. I would
>recommend you test this solution well before trying it in
production.
>Amol.
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b57301c43762$44127700$a101280a@.phx.gbl...
>> Dear All,
>> Can someone tell me what this actually is and what it,
>> more importantly where I would get it changed.
>> The reason is that a user (an ex developer) can give
>> themselves SA rights although through there NT
>> Authorization however they are not an administrator and
>> should not be allowed to access any of the server
security
>> properies.
>> The only theory I have is that the
BUILTIN\Administrators
>> is allowing SA access as its the only group I currently
>> have.
>> Thanks for reading
>> Peter
>
>.
>|||The second solution is potentially dangerous. Services, such as the Full
Text engine require the BUILTIN\Administrators group to be given access to
SQL Server...
--
Cheers,
James Goodman
"Amol Kasbekar" <apk@.nospam.com> wrote in message
news:%23vclye2NEHA.3944@.tk2msftngp13.phx.gbl...
> There are several ways to work around this:
> 1. Remove the exdevelopers from the NT administrators group and change the
> password of local administrator account on the server machine.
> 2. An extreme solution, which I have used on my local server is to delete
> this group from SQL Server. You can then add individaully or by another
> group that you can control who has sa access on the server. I would
> recommend you test this solution well before trying it in production.
> Amol.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:b57301c43762$44127700$a101280a@.phx.gbl...
> > Dear All,
> >
> > Can someone tell me what this actually is and what it,
> > more importantly where I would get it changed.
> >
> > The reason is that a user (an ex developer) can give
> > themselves SA rights although through there NT
> > Authorization however they are not an administrator and
> > should not be allowed to access any of the server security
> > properies.
> >
> > The only theory I have is that the BUILTIN\Administrators
> > is allowing SA access as its the only group I currently
> > have.
> >
> > Thanks for reading
> > Peter
> >
>
Can someone tell me what this actually is and what it,
more importantly where I would get it changed.
The reason is that a user (an ex developer) can give
themselves SA rights although through there NT
Authorization however they are not an administrator and
should not be allowed to access any of the server security
properies.
The only theory I have is that the BUILTIN\Administrators
is allowing SA access as its the only group I currently
have.
Thanks for reading
PeterThere are several ways to work around this:
1. Remove the exdevelopers from the NT administrators group and change the
password of local administrator account on the server machine.
2. An extreme solution, which I have used on my local server is to delete
this group from SQL Server. You can then add individaully or by another
group that you can control who has sa access on the server. I would
recommend you test this solution well before trying it in production.
Amol.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:b57301c43762$44127700$a101280a@.phx.gbl...
> Dear All,
> Can someone tell me what this actually is and what it,
> more importantly where I would get it changed.
> The reason is that a user (an ex developer) can give
> themselves SA rights although through there NT
> Authorization however they are not an administrator and
> should not be allowed to access any of the server security
> properies.
> The only theory I have is that the BUILTIN\Administrators
> is allowing SA access as its the only group I currently
> have.
> Thanks for reading
> Peter
>|||Thanks Amol
Peter
>--Original Message--
>There are several ways to work around this:
>1. Remove the exdevelopers from the NT administrators
group and change the
>password of local administrator account on the server
machine.
>2. An extreme solution, which I have used on my local
server is to delete
>this group from SQL Server. You can then add individaully
or by another
>group that you can control who has sa access on the
server. I would
>recommend you test this solution well before trying it in
production.
>Amol.
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b57301c43762$44127700$a101280a@.phx.gbl...
>> Dear All,
>> Can someone tell me what this actually is and what it,
>> more importantly where I would get it changed.
>> The reason is that a user (an ex developer) can give
>> themselves SA rights although through there NT
>> Authorization however they are not an administrator and
>> should not be allowed to access any of the server
security
>> properies.
>> The only theory I have is that the
BUILTIN\Administrators
>> is allowing SA access as its the only group I currently
>> have.
>> Thanks for reading
>> Peter
>
>.
>|||The second solution is potentially dangerous. Services, such as the Full
Text engine require the BUILTIN\Administrators group to be given access to
SQL Server...
--
Cheers,
James Goodman
"Amol Kasbekar" <apk@.nospam.com> wrote in message
news:%23vclye2NEHA.3944@.tk2msftngp13.phx.gbl...
> There are several ways to work around this:
> 1. Remove the exdevelopers from the NT administrators group and change the
> password of local administrator account on the server machine.
> 2. An extreme solution, which I have used on my local server is to delete
> this group from SQL Server. You can then add individaully or by another
> group that you can control who has sa access on the server. I would
> recommend you test this solution well before trying it in production.
> Amol.
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:b57301c43762$44127700$a101280a@.phx.gbl...
> > Dear All,
> >
> > Can someone tell me what this actually is and what it,
> > more importantly where I would get it changed.
> >
> > The reason is that a user (an ex developer) can give
> > themselves SA rights although through there NT
> > Authorization however they are not an administrator and
> > should not be allowed to access any of the server security
> > properies.
> >
> > The only theory I have is that the BUILTIN\Administrators
> > is allowing SA access as its the only group I currently
> > have.
> >
> > Thanks for reading
> > Peter
> >
>
Tuesday, February 14, 2012
Building multiple "temp" tables in SQL....
Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Mark
Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>
|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>
|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place?
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Mark
Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>
|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>
|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place?
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
Building multiple "temp" tables in SQL....
Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that woul
d
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.c...ived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.c...ived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exa
ct
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
>
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that woul
d
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.c...ived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.c...ived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exa
ct
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
>
Building multiple "temp" tables in SQL....
Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.com/jg_derived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> Hello,
>> Using SQL 2000 and I want to write a query that creates a different
>> "temp"
>> table every time the query is run so that more than one user cannot be
>> accessing the same temp table at the same time.
>> I see two possible ways of doing this.
>> 1.) My app can take the user ID as an input field, and I suppose that I
>> could use this in the query (as a variable) to generate a separate "temp
>> "
>> table for each user based on their ID.
>> For example, I'm Joe and my user ID is 1.
>> In the app I select my user ID as an input field and I click a button.
>> The
>> button runs the query which needs to take that User ID and create a temp
>> table with the ID as part of the table name so that it is different for
>> each
>> user.
>> 2.) If I can just tell the query to create a differentiated "temp" table
>> every time it is run (say with an increment function or something) that
>> would
>> work as well.
>> My goal is simply to make sure that when the query is run that not more
>> than
>> one person is accessing the same temp table at the same time.
>> The table is then truncated or dropped by another process.
>> Any idea if of how this can be done?
>> Thanks much,
>> Mark
>>
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> > hi
> >
> > may be you should spend some time to understand what temp tables are,
> > read BOL and
> >
> > http://www.sqlteam.com/article/temporary-tables
> > http://www.sql-server-performance.com/jg_derived_tables.asp
> >
> >
> >
> > regards
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> >
> >
> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> >> Hello,
> >>
> >> Using SQL 2000 and I want to write a query that creates a different
> >> "temp"
> >> table every time the query is run so that more than one user cannot be
> >> accessing the same temp table at the same time.
> >>
> >> I see two possible ways of doing this.
> >>
> >> 1.) My app can take the user ID as an input field, and I suppose that I
> >> could use this in the query (as a variable) to generate a separate "temp
> >> "
> >> table for each user based on their ID.
> >>
> >> For example, I'm Joe and my user ID is 1.
> >>
> >> In the app I select my user ID as an input field and I click a button.
> >> The
> >> button runs the query which needs to take that User ID and create a temp
> >> table with the ID as part of the table name so that it is different for
> >> each
> >> user.
> >>
> >> 2.) If I can just tell the query to create a differentiated "temp" table
> >> every time it is run (say with an increment function or something) that
> >> would
> >> work as well.
> >>
> >> My goal is simply to make sure that when the query is run that not more
> >> than
> >> one person is accessing the same temp table at the same time.
> >>
> >> The table is then truncated or dropped by another process.
> >>
> >> Any idea if of how this can be done?
> >>
> >> Thanks much,
> >>
> >> Mark
> >>
> >>
> >
> >
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>
>
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.com/jg_derived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> Hello,
>> Using SQL 2000 and I want to write a query that creates a different
>> "temp"
>> table every time the query is run so that more than one user cannot be
>> accessing the same temp table at the same time.
>> I see two possible ways of doing this.
>> 1.) My app can take the user ID as an input field, and I suppose that I
>> could use this in the query (as a variable) to generate a separate "temp
>> "
>> table for each user based on their ID.
>> For example, I'm Joe and my user ID is 1.
>> In the app I select my user ID as an input field and I click a button.
>> The
>> button runs the query which needs to take that User ID and create a temp
>> table with the ID as part of the table name so that it is different for
>> each
>> user.
>> 2.) If I can just tell the query to create a differentiated "temp" table
>> every time it is run (say with an increment function or something) that
>> would
>> work as well.
>> My goal is simply to make sure that when the query is run that not more
>> than
>> one person is accessing the same temp table at the same time.
>> The table is then truncated or dropped by another process.
>> Any idea if of how this can be done?
>> Thanks much,
>> Mark
>>
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> > hi
> >
> > may be you should spend some time to understand what temp tables are,
> > read BOL and
> >
> > http://www.sqlteam.com/article/temporary-tables
> > http://www.sql-server-performance.com/jg_derived_tables.asp
> >
> >
> >
> > regards
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> >
> >
> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> >> Hello,
> >>
> >> Using SQL 2000 and I want to write a query that creates a different
> >> "temp"
> >> table every time the query is run so that more than one user cannot be
> >> accessing the same temp table at the same time.
> >>
> >> I see two possible ways of doing this.
> >>
> >> 1.) My app can take the user ID as an input field, and I suppose that I
> >> could use this in the query (as a variable) to generate a separate "temp
> >> "
> >> table for each user based on their ID.
> >>
> >> For example, I'm Joe and my user ID is 1.
> >>
> >> In the app I select my user ID as an input field and I click a button.
> >> The
> >> button runs the query which needs to take that User ID and create a temp
> >> table with the ID as part of the table name so that it is different for
> >> each
> >> user.
> >>
> >> 2.) If I can just tell the query to create a differentiated "temp" table
> >> every time it is run (say with an increment function or something) that
> >> would
> >> work as well.
> >>
> >> My goal is simply to make sure that when the query is run that not more
> >> than
> >> one person is accessing the same temp table at the same time.
> >>
> >> The table is then truncated or dropped by another process.
> >>
> >> Any idea if of how this can be done?
> >>
> >> Thanks much,
> >>
> >> Mark
> >>
> >>
> >
> >
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>
>
Sunday, February 12, 2012
building cube using AMO
hi,
i am developing an application which enables the user to create a cube on the fly. I know that i can use AMO to create a Analysis Services 2005 cube.
but my question is :
Is it possible to use AMO to create a Analysis Services 2000 cube ?
Cheers
Anil
The answer to your question is: No, you cannot use AMO to create cubes in Analysis Services 2000. There are too many differences betwee AS 2000 and AS2005. Analysis Services in version 2005 has been rebuilt almost from the groud up. The techologies and protocols used in AS2005 are new.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
I am in the process of converting a DSO and SQLServer 2000 process which programmatically creates data sources (now DS views), dimensions, and cubes to AMO and SQL Server 2005, and I've been struggling. Can anyone suggest a good book or documentation source which clearly describes the new AS object model and gives comprehesive, well explained AMO examples (preferably in VB)?
Thanks in advance.
|||how can we use AMO to build cubes on the fly?|||Thanks for your help edward.
Cheers
Anil
I suggest you guys start a new thread.
Just shorly:
To learn AMO it often better to take a look at number of AMO samples provided with SQL Server 2005 installation.
There is already abundance of information you can search for with people posting on how to use AMO.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)