I have recently had to optimise a site. The DMVs are useful in establihing which queries are taking the longest to run but the data is skewed as it takes into account all queries than have run ever. This is not as useful as profiling a site which in fact I used.
Is there a way to clear all the data returned by the DMVs. This would be useful as all the data could be cleared and then I could just check the DMV every week.
I got a situation in which I would like to create a Views which is using my Test database (Could use more then one test database), but when I test It I would like to create using my Production database (Which is different Database name). Is there any way so that I can pass a parameter at run time which hold a Database name and create those views using that parameter. Any help really appreciated
I am using dynamic sql in my report. I cannot see the sql the report is generating in sql profiler. Is there somewhere else I can view it? Thanks, Linda
I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?
EDIT: Changed the title to read Procedurally generating a large view.
I'm very new to SQL Server. Please help. I need to create a FUNCTION that creates a view. Then call this function in a SQL which is passed as a parameter to BCP. In Oracle, it would be something like:
create function CREATEVIEW
return number as
begin
create view SampleView as SELECT a,b,c from Mytable;
return 1;
when others then return 0; -- for exception handling
end:
create function DROPVIEW
return number as
begin
Drop view SampleView;
return 1;
when others then return 0; -- for exception handling
end:
Then my BCP will have something like:
BCP "select CREATEVIEW from dual"... QUERYOUT ..
then
BCP "select * from SampleView"... QUERYOUT ..
then drop the view again:
BCP "select DROPVIEW from dual"... QUERYOUT ..
I know there is no DUAL table in SQL SERVER. I just want to know how to code this in SQL Server.
The origin of my problem is that my SQL statement is too long to fit as BCP parameter, hence, am creating a view and reading there and dropping it again. If you can provide me with a better workaround, that would be great. Thanks in advance.
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT IF OBJECT_ID('#External_Referrals') IS NULL DROP TABLE #External_Referrals; GO DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
I have a table in my db that has an identity column as the primary key. I have deleted all the data and have tried to use truncate table on it to reset the identity column. The table has a number of foreign key constraints so it will not truncate. Is there any way to reset the indentity column without removing the constraints?
FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1)) RETURNS VARCHAR(100) AS BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') + CAST(quantity AS varchar(5)) FROM flexing_stock_transactions WHERE item = @item AND week = @week AND firm_or_commission = @type GROUP BY quantity ORDER BY quantity
RETURN @Output
END
gives the same output every time it is called, despite different parameters passed. @Output does not change as expected.
Hi, consider my table, s.no priority status 1 2 pending 2 3 pending 3 0 completed 4 1 pending 5 0 completed Now, if I insert a record as, 6 1 pending the priority must be reset as follows, s.no priority status 1 3 pending 2 4 pending 3 0 completed 4 2 pending 5 0 completed 6 1 pending Im using c# and sql server. How can i do it? Thanks, Jasmeeta.
I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes The tables have been used for testing while the application was developed. I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?
Hi Forum, I have a .mdf that I have used to test SQL data app. I want to reuse the .mdf; delete all added data and reset Customer_ID primary colomn back to 1. All good info appreciated thanks Paul
I have a SQL data source triggered with this code: string searchstring = TextBox2.Text; SqlDataSource1.SelectCommand = "mod_search_all_sel"; SqlDataSource1.SelectParameters.Add("search_str", searchstring); SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; The second time this fires however, the SelectParameters.Add function creates another paramter and the stored procedure blows up. How do I do a reset like in old Classic ASP where you would set the DS = Nothing?
In my application , DB has a large table. I write a small program to clear the table whenever the size of table is over 50 MB. At that time , I want to reset identity as 1. How can I do that? Currently , my program delete old table and generate a new one with the same schema when the table is too large.But this is kind of ugly.
How do reset identity seed on table, for example I have table that has gap in the identity such as (1,2,3,5,6, etc..) and I want reset the entire table.
This morning I made a change to two tables in my live system. All I did was add a new column in each called 'suspense' with a data type of bit and a default value of '0'.
When the users started using the system they noticed that the record ID numbers had reset and were starting from 1 and going up 2, 3, 4 etc. The last good id number was about 20500. The id columns are set as primary key with identity and increment of 1.
This shouldn't happen should it? Is it a bug? I never touched the ID column, just added the new one.
I have a DTS package that deletes all the records in a table and repopulates from an ACCESS table. All's well except the primary key of the target table doesn't go back to 1.
What's the syntax in T-SQL to accomplish that?
I imagine it's dropping the column and adding it back - but since it's a primary key field, regular drop/add syntax isn't working for me.
I have build an application on top of SQL Server 2005 and i am using microsoft driver. My application stops periodically becoz of Database Connection Loss and I get the following exception
com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.transmit operation. Exception:Socket closed. Context:(5) [Thread[Thread-55,5,main], IO:7e2dc, Dbc:null]. 10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDriverError(Unknown Source) 10:06:14:703 AM at com.microsoft.sqlserver.jdbc.DBComms.transmit(Unkn own Source) 10:06:14:703 AM at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand( Unknown Source) 10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.se ndExecute(Unknown Source) 10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerStatement.do ExecuteQuery(Unknown Source) 10:06:14:703 AM at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeQuery(Unknown Source)
Unable to find the exact reason for the above problem. Pl advice
I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.
The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.
The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.
Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.
But when I tested it, nothing happened ...
ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL] AFTER INSERT AS BEGIN DECLARE @PartOnly varchar(16) -- Part Number DECLARE @Rev varchar(4) -- Part Revision DECLARE @PartCode as varchar(2) -- Part Classification DECLARE @Last_Checked as datetime -- Checked Date
this procedure resets the business day to one for every month. It works fine except for the month of October. any idea or suggestions?
DECLARE @i INT SET @i = 1 DECLARE @DateID INT, @DtTimeD DATETIME, @LASTDAY DATETIME DECLARE c CURSOR FOR -- -- LASTDAY is the last day of the month -- the counter will reset to 1 on the first of each month -- SELECT DateID, DtTimeD, DATEADD(dd, -DAY(DATEADD(m,1,DtTimeD)), DATEADD(m,1,DtTimeD)) 'LASTDAY' FROM D_DATE WHERE WkDayIn = 'Yes' AND HolidIn = 'No' OPEN c FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY WHILE @@FETCH_STATUS = 0 -- -- update the business day in D_DATE -- BEGIN UPDATE D_DATE SET BusDay = @i WHERE DateID = @DateID -- -- reset the counter to 1 if it's the last day of the month -- IF @DtTimeD = @LASTDAY SET @i = 1 ELSE SET @i = @i + 1 -- IF @@ROWCOUNT = 500 COMMIT -- FETCH NEXT FROM c INTO @DateID, @DtTimeD, @LASTDAY END CLOSE c DEALLOCATE c GO
I use Microsoft SQL Server and also I have several tables. One table has ID like PK, and name. ID use autoincrement number mode, and now when I want start form one but not from 156 or like that. I don't know how? And also when I delete all data from table an run my apllication and add something in table, it starts form last number, not from one.
We are about the deploy our project. We made many testing such as Random data insertion etc. Some tables have autoincrement column, and their numbers reached 3,000,000. After testing, we delete all data in database. Now question is how can we truncate all tables, and make fresh start. We may use TRUNCATE query but it is boring and giving errors some time. Is there way to reset whole database, and make a fresh start, like no data entered before.????
Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @@Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?
