I have two replicated databases i.e.
1. Database 1 is a live database where all live applications point
2. Database 2 is a replica of Database 1 and all reporting / BI applications point to this db. This is read-only.
Is it possible for reporting applications, pointing to Database 2, to create temporary tables in the read-only database?
Thanks
Hi,
We have a SQL server database on remote server. We are using ODBC connection to read and write into the table. But we have another user wants to retrieve(readonly access) our data on his pages. How can we create a user on our database and grant readonly access to that user?
Thanks..
Dear All,
I need experienced advice on Security in SQL Server 2005. All I need specific advice on restrcting users to access SQL Server database to have only READ access for certain tables.
Thanks,
Hi,I have a prod database (main bread & bread DB) and have a small accessdatabase that sales team is using... I created a login inside SQL 2000and gave db_read permission and SELECT permissions... and public.Role of public is EXEC store procs and view some systables...I linked those tables that I gave permissions through ODBC link intoaccess db with the user that I created inside SQL as readonly user...but to my surprise when I ran a insert query from access on SQLtables... I was able to update and insert data... if my ODBC link isonly for readonly.. why was I able to unpdate & insert data into SQLtable?I don't want to give write/update/insert permissions for SQL tables tosales team... or anyone outside SQL 2000 server DB.Is there any easy way to create viewonly users inside SQL?I created users like this: security -logins -new login...select none serverrole.. db access (bread & butter DB)Thank you,hj******Pardon my English...
View 1 Replies View RelatedFirstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View 2 Replies View RelatedIs there any query to find out all the tables without a Primary key or without a Unique index ?
View 3 Replies View RelatedI need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.
Thank you,
Kameron
I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Thanks a bunch.
I'm in the process of migrating over nearly 900 reports to a replicated server.
I have moved over 100 reports, stored procedures and their dependent objects so far.
I have two tables that are not being applied to the subscriber.
Ok, so I must have screwed something up.
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €śIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Thanks a bunch!
md
Can you create a temporary table using an ad-hoc query?
What I am trying to do is a type of filter search (the user has this and this or this) were i will not know how may items the user is going to select until they submit....that is why i can't use a stored procedure(i think)....any help on how to do this?
Thanks,
Trey
Hi could anyone give me a hint what does term "temporary table" mean regarding sql server?
View 1 Replies View RelatedHi all,
how can i execute this query without errors??
create table #luca(c int)
drop table #luca
select * into #luca
from anagrafica_clienti
The error lanched is:
Server: Msg 2714, Level 16, State 1, Line 6
There is already an object named '#luca' in the database.
Why if i drop the table?How can i do?Thanks guy
Afternoon.
I'm having trouble with a query and ASP. The query itself is easy. I need a temporary table to be filled with the contents of a select and then i need to select out of the temporary table and return the results to the ASP.
So:
Code:
DECLARE @RESULTS TABLE (
ItemID int,
ItemDescription char(50),
ItemType int,
ItemRequestedBy char(50),
ItemStatus int,
ItemQuantity int,
ItemCostPer money,
ItemOrderNumber int,
DateAdded smalldatetime,
DateLastEdited smallDateTime
)
INSERT into @results (ItemID, ItemDescription, ItemType, ItemRequestedBy, ItemStatus, ItemQuantity, ItemCostPer, ItemOrderNumber, DateAdded, DateLastEdited)
SELECT * from cr_EquipmentData
SELECT * from @results
When I run this in Query Analyser, I get exactly the results I need... But when I try and run the ASP script, I get an error about the recordset not being open. (It's not the ASP checking 'cos when I run a simple select statement it works)
I appreciate there is no use for the query as it stands, but eventually I want to be able to perform not destructive statements on the @results table before returning the data to ASP. This is more 'testing' than anything at the mo'
Has anyone got any ideas?
Thanks...
I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.
I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.
I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.
The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.
Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?
How can I view logs of local (to a session) temporary that are created/dropped?
View 6 Replies View RelatedHi,
I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output
Select temp2.Value/temp1.Value as val
from
(
(
select count(*)as Value from [Master] m
inner join [Spares]s on s.SId=m.SID
where m.Valid_From between '2007-06-01' and '2007-06-30'
)temp1
Union
(
select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value
from [Order] h
inner join [Spares] s on s.Number = s.Number
where h.Valid_Date between '2007-06-01' and '2007-06-30'
))temp2
as t
I could not find the output..
Plz help me..
Thanks..
Hello,
Our java application is running on oracle and now we would like to port it to sql server 2000. In oracle we have a global temporary tables that has an option on commit to delete rows.
Now I am looking for the same option in sql server but as far as I can see sql server's local temporary tables are visible per connection. Since the connection are shared in the pool it seems I can not rely on local temporary tables since the connection does not get closed at the end of the user's session. I need something that is visible per transaction not per connection.
Is it a better approach just to create a regular table and basically have a trigger to delete all data on commit?
If a stored procedure invokes another stored procedure that creates atemporary table why can't the calling procedure see the temporary table?CREATE PROCEDURE dbo.GetTempASCREATE TABLE #Test([id] int not null identity,[name] as char(4))INSERT INTO #Test ([name]) VALUES ('Test')CREATE PROCEDURE dbo.TestASEXEC dbo.GetTempSELECT * FROM #Test -- Invalid object name '#Test'.Thanks,TP
View 4 Replies View RelatedHi, just a quick question regarding performance and speed.Q. Run a complicated query three times or create a TEMPORARY table andaccess it as needed?I have a page where it will be accessed 10,000+ a day.In that page I have an SQL query where it involves 3 different tables(approximate table sizes T1) 200,000 T2) 900,000 T3) 20 records)I'll be running that query 3 times in that page...One to retrieve the content and display it on the page.Second to count the number of records (using COUNT(*) function)And third to retrieve the content regions. (using DISTINCT function)What would be the best way of doing...Running the SQL query 3 timesOrCreate a temporary table and access it as many time as I needRegards,
View 4 Replies View RelatedHello,
I would like to know from other members whether they are successful in using temporary tables within a stored procedure and use that stored procedure in a report.
My scenario is like this:
I have a stored procedure A which fetches the data from different tables based on the orderno passed as input parameter.
I have built another stored procedure B with a temporary table created and inserting the rows in to this temporary table based on vendor related specifc orders by calling the above procedure A.
I have used this stored procedure in the dataset created and getting this error:
Could not generate a list of fields for the query. Check the query syntax, or click the Refresh Fields on the query toolbar.
Does anybody encountered this and have a resolution.
Thanks in advance.
Hello,
View 6 Replies View RelatedHi All
declare @temp table
([EVENT_TYPE_ID] [int],
[Desc] [nchar](50) NOT NULL,
[Lead_Time] [smallint] NULL)
INSERT @temp
SELECT *
FROM TBL_EVENT_DEFINiTION
The table definition for @temp is the same as TBL_EVENT_DEFINiTION. The problem is I need to add a field to the @temp table and define the values using an update statement. If I include the additional field in the @temp table declaration then I get an error saying something to the effect of the number of fields is different. Is there a way of altering the temporary table to add this field?
Many thanks in advance
Alex
Hello!
I'm creating a temporary table in a procedure to help me with some calculations. I would like the name of the temporary table to be 'dynamic', I mean, to have the hour it was created, something like this: create table #myTempTable15May11:10:00, so if someone access the procedure while it's running, he won't have problems in creating his 'own' temporary table (create table #myTempTable15May11:10:02). Is there anyway I can do this?
Thank you!
Hello,
I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.
I read that in this case I can´t use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.
My Store is likely to his. Please help me.
INSERT ##Resultados (Planta, Etapa,GrupoEquipo,Equipo,Concepto,Fecha,Guardia,
Valor,idConcepto)
EXEC CalculosDiarios @Area,@Reporte,@FechaIni,@FechaFin,0
SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2)
+ LEFT(D.Fecha,8) + D.Valor as Dato
FROM
ReportesPlantilla RP
LEFT JOIN
##Resultados D
ON
RP.Planta = D.Planta
AND RP.Etapa = D.Etapa
AND RP.GrupoEquipo = D.GrupoEquipo
AND RP.Equipo = D.Equipo
AND RP.Concepto = D.Concepto
AND D.Fecha BETWEEN @FechaIni AND @FechaFin
Can some one tell me how to display the fields of temporary table in the report? In the query a global temp table is created and in the end i am displaying the fields of that table. How do i do that?
View 1 Replies View RelatedHi,
I have some questions regarding Temporary tables.
I need to use a temporary table within a stored procedure (which is a transaction), should I use local temporary table or global temporary table?
If I use a temporary table in my transaction, do I destroy the temporary table at the end of the transaction, or just leave it, and let the sytem clean it up? If I destroy the temporary table at the of the transaction, what happens if another user session is accessing the temporary at the very moment?
Thanks.
Cathie
I am searching for information on paging large datasets, and have found
some that involve creating temporary tables in the database.
Before I head off and implement something, I have a number of issues
I'd like to bounce around here.
1. An example I found on MSDN involves creating a temporary table,
copying relevant columns to the row in the temp table. Why do
this, rather add the source tables primary keys into the temp table,
and do a join? Example; browsing Products Catalog which is
categorised into hierarchies. The MSDN version would have a temp
table created with a incrementing field which is used for the paging,
and then a number of fields are also copied from the products table to
the temp table - my question is why not simply copy the product primary
key into the temp table, and then join?
2. In real life, do people allow each user to create their own
temporary tables? If I have 1000 concurrent users, all wishing to
perform a page-based browse, I would be creating 1000 new temporary
tables. Do people consider default temp tables, that is, creating
a default temporary table for browsing each category in the products
table, for example?
3. Do you have any advice/tips for this type of problem?
Thanks!
JR.
In Access 97/2000, a local table can be created in the program database, while the data is stored in a linked database. This is useful for me to do a sort-of iterative drill-down on linked tables, e.g. filter table1 into table2, then further filter table2 into table3 and so on.....
If I do this I leave a trail that I can navigate in reverse (back up a step).
This is very important in my app.
So now my problem is....I'm trying to duplicate this in an ADP file using only ADO 2.5. I have no local tables, only server tables. If I create a temporary table, it only exists until the procedure ends.
Is there any way to create temporary tables that are specific to a user session and do not automatically delete themselves, or is there a better way to do this in SQL7?
on sql-server-performance.com i read :
Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000
I have a question does this negative effect also include simple SQL commands apart from stored procedures.
For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?
Thank you very much
I have created a local temporary table (#Temp) and placed data in it.When I do:SELECT * #TempThe result is a set of rows with no columns.The SELECT statement is in the same procedure as the creation of #Temp; so Idon't believe this is a scoping issue. Does that make sense?Thank you in advance,Eric
View 3 Replies View Related
hi,
I'm going through some stored procedures that have been written previously.
In every sp, they are checking for existance of temporary table in sysobjects.
So, i made a simple script and checked if #TempTable has a entry in sysobjects.
But i did not find it.
On internet many ppl are talking about checking if temp table already exists.
So my question is...
1. What are circumstances under which temporary table doesn't get deleted
automatically.
2. Does temporary table have a entry in sysobjects?
Thanks
Sandeep