I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL] CREATE TABLE [dbo].[PARAMEXAMPLE]( [Entity] [varchar](16) NULL,
SSIS 2008 when I develop and debug in BIDS sometimes ignores debug break point.
The script component is in the main control flow and at some point the breakpoint did work.
If, for example, I create a new project and copy my script component there the debug breakpoint will work.So it's absolutely *random* when it works and when it does not.
Below is my BIDS detail:
Microsoft Visual Studio 2008 Version 9.0.30729.4462 QFE Microsoft .NET Framework Version 3.5 SP1 Installed Edition: IDE Standard Enterprise Library v5 Configuration Editor 4.0
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
Kimberly Tripp describes a recipe for switching partitions in and out, thru the use of staging tables, when it comes time to "slide the window" on a partitioned table. She says that the clustered index (on staging) must be the same as that chosen for the partitioned table itself but she doesnt discuss whether or not all of the non clustered indexes need to be the same too once the ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersOctober2002 and ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24 run. For the data being switched out, I wouldnt want to do anything extra. For the data being switched in, I'd like to understand if she is implying that all other indexes would be built automatically as a result of the 2nd ALTER statement?
Kimberly's article is at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...
You can see that the indexes are taking up more space than data there are a total of four indexes on the table is shouldn't the data(MB) be higher than the indexes(MB) ?
--Exec Database.Employees --Use Database --Go --Create PROCEDURE AEM.TempTable --AS --BEGIN --Select * into #emptemp From Database.Employees --End --Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked. 2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
I have a table which gets updated with the usage figure every week. Any similar t-sql which returns the increase in usage percentage of all the columns.
We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20)) FROM sys.dm_exec_sessions s INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id ORDER BY g.name GO
Following is the code to create pool, group,classifier function:
USE master GO; -- Create a resource pool pool_login. CREATE RESOURCE POOL pool_login WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin USING pool_login; GO CREATE WORKLOAD GROUP GroupDBNAME USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1); GO
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK] AS DECLARE @sql NVARCHAR(max) DECLARE @DB VARCHAR(max) DECLARE databasecursor CURSOR FOR
We have an Access application using Jet. I added some new indexes yesterday and now they are being blamed for poor Access application performance. I then dropped the new indexes. The poor performance continued until the Access application was re-linked to the SS2000 database. Then things returned to normal.
Question, does Access/Jet persist SqlServer schema info in its MDB (or elsewhere?) I am told that the MDB is copied from a share to the local PC where it grows during its use. Some people are saying the MDB persists schema info about the SS2000 schema which influences how Jet accesses the SS2000 database. Is that true? Is there a link where I can read about this? I am a dba and am not an Access developer . . .
I am having a problem restricting write access to tables in my database.In my database I have a table called, for the sake of argument, 'TableX'.In my SQL Server Logins, I have set up a login for 'Domain Users' using NTauthentication, and a login called 'FullTableX', using SQL Serverauthentication.I have added two users to my database relating to the above logins.I have added a role to my database called 'ReadTableX' with 'Domain Users'as a member of this role. 'ReadTableX' has SELECT permission only on arestricted set of tables.The only other role that 'Domain Users' is a member of is 'public', and'public' has no permissions on any of my tables.The user 'FullTableX' is a member of 'public', 'db_datareader' and'db_datawriter'With the above settings, I would expect user 'FullTableX' to have fullaccess (Select, Insert, Update and Delete) on all my database tables (so farso good), but any user connecting to the database with NT authentication(via ODBC System DSN) should only have read access to the limited set oftables. However, what is happening is that NT authenticated users also havefull access to all of the tables.What am I missing here??
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
