Saturday, March 30, 2013

[how to] Executing sys.dm_fts_parser without sysadmin server role

[how to] Executing sys.dm_fts_parser without sysadmin server role

Executing sys.dm_fts_parser without sysadmin server role

Posted: 30 Mar 2013 08:41 PM PDT

A web application needs to execute sys.dm_fts_parser to discover which words SQL Server Fulltext Search used to match documents for the purpose of syntax highlighting. That stored procedure can only be executed if the caller is in the sysadmin server role.

Permissions

Requires membership in the sysadmin fixed server role and access rights to the specified stoplist.

http://msdn.microsoft.com/en-us/library/cc280463%28v=sql.105%29.aspx

Since it would be most unwise to grant that server role to the web server's user, I attempted to create a stored procedure that runs as a separate user that is in that role.

create procedure usp_fts_parser      @query nvarchar(max)  with execute as owner  as  select display_term from sys.dm_fts_parser(@query, 1033, 0, 0);  go

I then created a user login_sign_fts_parser, made it the owner of the stored procedure

alter authorization on usp_fts_parser to login_sign_fts_parser

and attempt to allow the web server's DB user rights to impersonate that user:

GRANT IMPERSONATE ON USER::[IIS APPPOOL\Strategic Window] TO login_sign_fts_parser

When I attempt to execute usp_fts_parser, I receive the error:

Cannot execute as the database principal because the principal "login_sign_fts_parser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Why do I get this error? How can I accomplish my goal of executing sys.dm_fts_parser without granting undue permissions to the web server's DB user?

Cannot create perlplu function

Posted: 30 Mar 2013 05:01 PM PDT

Running PostgreSQL 9.1.8 on Xubuntu 12.04, installed from the repos.

From the shell, I have executed:

createlang plperl db_name;  createlang plperlu db_name;

As the superuser running psql, I have executed:

GRANT ALL ON LANGUAGE plperl TO account_name;  GRANT ALL ON LANGUAGE plperlu TO account_name;

The pg_language table reveals:

select lanname,lanpltrusted from pg_language where lanname like 'plperl%';    "plperl";t  "plperlu";t

When I create the following function:

CREATE OR REPLACE FUNCTION get_hostname()    RETURNS text AS  $BODY$    use Sys::Hostname;    return hostname;  $BODY$    LANGUAGE plperlu IMMUTABLE    COST 1;  ALTER FUNCTION get_hostname()    OWNER TO account_name;

I receive the following error:

ERROR:  Unable to load Sys/Hostname.pm into plperl at line 2.  BEGIN failed--compilation aborted at line 2.  CONTEXT:  compilation of PL/Perl function "get_hostname"

Yet the following works:

CREATE OR REPLACE FUNCTION get_hostname()    RETURNS text AS  $BODY$    return '127.0.0.1';  $BODY$    LANGUAGE plperlu IMMUTABLE    COST 1;  ALTER FUNCTION get_hostname()    OWNER TO account_name;

The following Perl script works as expected from the shell:

use Sys::Hostname;  print hostname;

I tried to run the function as an anonymous block:

DO $$    use Sys::Hostname;    print hostname;  $$ LANGUAGE plperlu;

This returned the same error as before, with this additional information:

ERROR: Unable to load Sys/Hostname.pm into plperl at line 3.  BEGIN failed--compilation aborted at line 3.  SQL state: 42601  Context: PL/Perl anonymous code block

From the documentation, error 42601 is a syntax error.

I see no syntax error.

Furthermore, the function loaded fine with the superuser account when I imported the database:

psql -d db_name -U account_name -W -f db-dump.sql > import.log 2> error.log

I tried to simplify the code to no avail:

CREATE OR REPLACE FUNCTION get_hostname() RETURNS text AS  $$    use Sys::Hostname;    return hostname;  $$  LANGUAGE plperlu;

Same error as before.

What I don't understand is why, according to the error message, PostgreSQL is trying to load the code into plperl instead of plperlu.

Any ideas?

Update #1

Note that the postgres user is a Superuser:

                             List of roles   Role name |                   Attributes                   | Member of   -----------+------------------------------------------------+-----------   postgres  | Superuser, Create role, Create DB, Replication | {}

How do I design a database for a resource scheduling and allocation application?

Posted: 30 Mar 2013 02:39 PM PDT

I have two entities: Resource and Project. A Project runs for a certain amount of time (in calendar days), and I need to allocate several Resources to it (allocation interval - 1 work day, not an hourly basis).

Now if I create two tables (resource and project), how do I achieve this? How should the tables be designed? Do I create a new entry for every day and resource?

Any help is much appreciated!

Retrieving Alternating Values

Posted: 30 Mar 2013 07:39 PM PDT

I have a column with two values in my database, I want to retrieve the two values alternately, for example I have:

name   itemcode  ----------   A       1   B       2   C       2   D       2   E       1   F       1

I want to display them as:

name itemcode  ----------  A      1  B      2  E      1  C      2  F      1  D      2

Getting next and previous document

Posted: 30 Mar 2013 09:53 AM PDT

In a mongo collection I have a list of words (millions words).

{word:'a'}  {word:'b'}  {word:'x'}  {word:'y'}  {word:'z'}

I need to get lexical adjacent of a word. I am looking for most efficient method.

Database design - do I need another table?

Posted: 30 Mar 2013 06:18 PM PDT

I am trying to make a database that follows a form that the company uses. When a client walks in the membes of staff have to fill in a form and the data is recorded. The form on paper is 10 pages long. The first time a client is seen the entire form is filled in and the client gets a clientID.

I have split the form into sections that make sense like accommodation and employment. I know I can link these tables together with the clientsID. Thats the simple part.

Now when a client returns the form comes out again but this time only certain parts are filled in, what ever the clients needs are. The records for most parts don't need updating but a new record needs inserting. what would be the best way around this.

So at the moment I have for example a table called client with an id and name another table called accommodation with clientid and address and another table employment with clientid and employer.

But how do I go about it when a client comes in to let us know he has a new employer. I cant update the current one as that is needed but I would need to add new record for the client.

Would this mean I would have to add a look up table for all my current tables?

mysqlworkbench after cmd mysql startup went wrong

Posted: 30 Mar 2013 05:18 PM PDT

i have problems with my mysql server. I have installed the newest mysqlserver but having problems after starting my server from command line on localhost. Before that i was running xammp. The problem is that when starting from command line and not from xammp, mysqlworkbench shows me tables from another datadir when connecting with database . I really don't know how to fix my problem.

Insert from one row to another using cases

Posted: 30 Mar 2013 01:38 PM PDT

My original table was(being used since 2005):

CREATE TABLE `request` (      `msg` VARCHAR(150) NOT NULL,      `id` VARCHAR(20) NOT NULL,      `ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',      `date` VARCHAR(25) NULL DEFAULT NULL,      `status` VARCHAR(10) NOT NULL DEFAULT 'empty',      `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,      PRIMARY KEY (`fid`),      UNIQUE INDEX `msg_id` (`msg`, `id`),      UNIQUE INDEX `msg_ctg` (`msg`, `ctg`)  )  COLLATE='utf8_general_ci'  ENGINE=MyISAM  CHECKSUM=1  AUTO_INCREMENT=18491;

Since, it was neither good management of table nor appreciable by me, I created a newer table:

CREATE TABLE `requests` (      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,      `ctg` VARCHAR(15) NOT NULL,      `msg` VARCHAR(250) NOT NULL,      `nick` VARCHAR(32) NOT NULL,      `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',      `dated` DATETIME NOT NULL,      `filldate` DATETIME NULL DEFAULT NULL,      `filledby` VARCHAR(32) NULL,      PRIMARY KEY (`id`),      UNIQUE INDEX `nick_msg` (`nick`, `msg`),      UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)  )  COMMENT='Requests from users in any of the categories.'  COLLATE='utf8_general_ci'  ENGINE=MyISAM;

What I want to do now is:

SELECT data from first table and insert as follows:

  • msg to msg, id to nick, ctg to ctg and date to dated are copied as is.
  • status in original table is either empty or filled. I want to use a CASE clause while insertion so that Y and N are inserted accordingly.

My query:

INSERT INTO `requests`( `id`, `ctg`, `msg`, `nick`, `dated`, `filled` )  SELECT `fid`,      `ctg`,       `msg`,       `id`,       `date`,      CASE          WHEN `status` = 'empty' THEN 'N'          WHEN `status` = 'filled' THEN 'Y'      END CASE  FROM `request`

My question is

Is the query above perfect? Also, one of the sample string from request table with 'filled' status is like:

.hack//SIGN (filled by hjpotter92 in 02/08/12 09:47:07 )

the format is the same: filled by <<nick>> in <<<date>> in <<mm/dd/yy H:I:S>> for all filled entries. Can I extract the date from there and use it with STR_TO_DATE() to pass it in filldate field too? I know it'd need me to create a procedure/function.

For now, I am thinking of using PHP for the task but I wanted a clean MySQL solution if possible.

Understanding MySQL Integer Types?

Posted: 30 Mar 2013 03:39 PM PDT

Please could someone explain to me how the MySQL integer types and lengths work as it is somewhat confusing.

Which different integer types and lengths should I use for each of the following ranges to keep them as efficient as possible?

0 - 1  0 - 700,000  0 - 9,999,999,999

What is the difference between TINYINT(5), SMALLINT(5) and INT(5), are they the same?

mysql trigger on update with insert statement

Posted: 30 Mar 2013 10:16 AM PDT

I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created.

DELIMITER ;;  CREATE TRIGGER trig  BEFORE UPDATE ON table  FOR EACH ROW  BEGIN  IF old is null then  INSERT INTO table(table.col1, table.col2) VALUES ('new.table.col1','new.table.col2');  END IF;  END;  ;;  DELIMITER ;

Why is it not adding a record if update is trying to update some non existing record?

How to Shrink Oracle SYSTEM Tablespace？

Posted: 30 Mar 2013 02:32 PM PDT

SYSTEM Tablespace grow out of control because of SYS.AUD$ table.

SYS.AUD$ truncated but datafile still very big= 30G

Resize doesn't work because

file contains used data beyond requested RESIZE value

What should I do here?

Oracle trigger to update columns daily

Posted: 30 Mar 2013 11:14 AM PDT

I'm trying to create a trigger that would run twice daily and do updates based on a specific condition.

To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

We have Table A and Table B with columns

  1. Primary Key
  2. Date 1
  3. Date 2
  4. Date 3
  5. Status

For a specified set of Primary Keys in Table A, until Table A.Status==Completed, I want the Date fields to be copied over from Table B to Table A twice daily. The same primary keys will be already setup in Table B. The number of records to update will decrease over time as more records in the primary keys list reach completed status.

The purpose is to maintain certain static values on the Date fields till status==completed. We are unable to control an external system that keeps over-writing them. As a stopgap we want to rewrite them at frequent intervals to have the notion of maintaining static values. Once all PKs in our list reach completed status, we want to turn off the trigger.

Run Multiple Postgres Server

Posted: 30 Mar 2013 02:58 PM PDT

Hello I am new to postgres, i wanted to run postgres v8.3 and v9.1 same at a time, but don't know how..

anyone help me how to do it simply.

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

Posted: 30 Mar 2013 05:04 PM PDT

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the system. I assumed that this was caused because the user was not in the system, but looking in the users database of MySQL the user is listed correct with the password.

The dump was from an earlier version of MySQL (5.0) into the latest one that comes with Xamp 5.527. At first I thought it could be that the latest version was incompatible with the existing one.

Looking on this forum, there are some existing answers covering this problem that refer to making changes to the database and then dumping it again.

However, my on line database works fine, so I dare not make changes to it in case I kill it. That is assuming that I understood what changes I should make and how to do it. I have PHPmyadmin. Can I modify it on the existing dumped database,or do I have to change it on the on line one and then export it?

And exactly what do I need to do to modify it. The original database was a Microsoft Access one, converted to MySQL, and it has worked without error since 2003.

SQL Server update query on linked server causing remote scan

Posted: 30 Mar 2013 08:01 PM PDT

I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.

The following queries executes in less than 1 second:

   SELECT kg.IdGarment     FROM Products p      INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID      INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID      INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID      INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment      INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID      WHERE log.ActionType = 'I'

t_ProcessIT_Garment contains 37,000 rows, the query returns two records, the IdGarment column is the Primary Key. No problem here.

However, if I run this query to do a remote update, it takes 24 seconds, and 2 rows is affected:

   UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment SET      IdGarment = IdGarment     FROM Products p      INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID      INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID      INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID      INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment      INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID      WHERE log.ActionType = 'I' ;

I tested using IdGarment = IdGarment to keep things simple. The execution plan shows it uses Remote Query for the first query, but Remote Scan for the second query, which has 100% of the cost.

The table joins are identical in both queries, why is it using Remote Scan for the second query, and how do I fix this?

Mysql - How to optimize retrival time in a table

Posted: 30 Mar 2013 06:04 PM PDT

I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table...

mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
+------------------------------+
| COUNT(DISTINCT id) |
+------------------------------+
| 8242063
+------------------------------+
1 row in set (3 min 23.53 sec)

I am not satisfy with this timing ..! how can I reduce the result time less than 30sec. Kindly give me any suggessions! It will be more helpful to me!

thanking you!

MySQL specific database configuration file

Posted: 30 Mar 2013 01:58 PM PDT

In MySQL's configuration file I've globally disabled autocommit as so.

[mysqld]  autocommit=0

I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the user or the database itself (doesn't matter). Thinking it would look something like this.

[mysqld]  autocommit=0  execute_sql="Custom SQL to set autocommit for a database"

SQL Server 2005 Replication

Posted: 30 Mar 2013 04:58 PM PDT

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription.

server 1 windows 2003 server 192.168.10.1 connected by vpn SQL Server 2005 domain1.local

server 1  windows 2003 server  192.168.10.1 connected by vpn  SQL Server 2005  domain1.local

server 2 windows 2003 server 192.168.10.6 connected by vpn SQL Server 2005 domain2.local

server 2  windows 2003 server  192.168.10.6 connected by vpn  SQL Server 2005  domain2.local

When I setup up Replication everything looked fine until I looked at the sync status and it said:

The Agent could not be started    An exception occurred while executing a transact-sql statement or batch    sqlserveragent error request to run job  server1-username blah blah blah

From user sa refused because the job is already running from a request by user sa changed database context to technical error 22022.

I have cleared jobs in the server agent as well as restarted the service.

Could this be something to do with authentication between two non trusted domains as I can browse and even control each sql server via SQL studio but just not setup replication?

Yes I can manage each SQL Server in SSMS and we are using merge with snapshot.

Mysqldump tables excluding some fields

Posted: 30 Mar 2013 12:58 PM PDT

Is there a way to mysqldump a table without some fields?

Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of table USER, so excluding some fields like mail, ip_login, etc.

How can I do this?

How to do something like UPDATE DELAYED in MySQL

Posted: 30 Mar 2013 02:58 PM PDT

I have an averages table that should keep track of an average value over time. I don't want to have a row for each value, just a single row that continuously updates the average. What I've come up with is this:

set @value=4;  set @name="myAverageValue";  UPDATE `timing` SET    `max` = greatest(`max`,@value),    `average` = `average` + ((@value - `average`) / (`count` + 1)),    `count` = `count` + 1  WHERE `name` = @name

Many clients may be doing this at the same time, and I don't want there to be any locking issues. I don't care what order the updates are run in, since in the end it will all end up the same. I just want to have a query that sends the UPDATE to the database, and it will process it eventually, similar to an INSERT DELAYED. Does UPDATE LOW_PRIORITY do this, or does that cause the client to wait until it is available?

SQL Server 2012 database backup successfully report but no backup file

Posted: 30 Mar 2013 07:58 PM PDT

enter image description here

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be stored. But SQL Server logs history about this maintenance plan are successful.

What is the problem?

I am using SQL Server 2012. The operating system is Windows Server 2008 R2.

Thank you for paying attention.

enter image description here

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

Posted: 30 Mar 2013 06:58 AM PDT

we are using a Sybase SQL Anywhere 12 db.

In the db there are indices, which are unique, but shouldn't be unique.

Therefore I search for a quick way to list all tables with unique primary keys.

I tried

SELECT z.name FROM sysobjects z JOIN sysindexes ON (z.id = i.id) WHERE type = 'U'

The result was an error message: Anweisung konnte nicht ausgeführt werden. Tabellenname 'sysindexes' ist mehrdeutig SQLCODE=-852, ODBC 3-Status="42S13" Zeile 1, Spalte 1

Roughly translated: sysindex is ambiguous.

I found on internet the query:

select 'Table name' = object_name(id),'column_name' = index_col(object_name(id),indid,1),  'index_description' = convert(varchar(210), case when (status & 16)<>0 then 'clustered' else 'nonclustered' end  + case when (status & 1)<>0 then ', '+'ignore duplicate keys' else '' end  + case when (status & 2)<>0 then ', '+'unique' else '' end  + case when (status & 4)<>0 then ', '+'ignore duplicate rows' else '' end  + case when (status & 64)<>0 then ', '+'statistics' else case when (status & 32)<>0 then ', '+'hypothetical' else '' end end  + case when (status & 2048)<>0 then ', '+'primary key' else '' end  + case when (status & 4096)<>0 then ', '+'unique key' else '' end  + case when (status & 8388608)<>0 then ', '+'auto create' else '' end  + case when (status & 16777216)<>0 then ', '+'stats no recompute' else '' end),  'index_name' = name  from sysindexes where (status & 64) = 0  order by id

Which looked what i wanted. But there was still the same result of ambigious sysindexes.

What dows ambigious indexes mean in this context? Will/Can this cause any error in future?

As workaround I used sybase central (which by the way opens always on first monitor, not on the one where it was closed - ugly behaviour), and found that a item indices showed what i searched for.

But I still want to know how a programmatically solution looks like.

Replication master binlog rotation when network is unavailable

Posted: 30 Mar 2013 03:58 PM PDT

I recently experienced an issue where the binlog file in master rotated because network connectivity between the slave and master was unavailable.

After solving the network issue, the slave was not able to follow the master as it was doing through previous binlog and position.

That was solved by purging the binlog to last binlog in master and pointing the slave to that last binlog and previous binlog's position which was following.

I am wondering if this issue is normal?

Issues installing SQL Server 2008 on Windows Server 2008 R2

Posted: 30 Mar 2013 09:58 AM PDT

I am trying to install SQL Server 2008 on Windows Server 2008 R2 Cluster. While installing the first node, I keep on getting the following error:

TITLE: Microsoft SQL Server 2008 Setup
The following error has occurred:
Attempted to perform an unauthorized operation.
Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

Has anyone seen this issue before? I have uninstalled it multiple times and tried re-installing it but same results.

Take individual MySQL database offline

Posted: 30 Mar 2013 06:58 PM PDT

MySQL does not have any SQL commands or internal mechanisms for

  • making an individual database unavailable / offline
  • moving an individual database

Thtis being the case, how can you take an individual database offline?

Choosing shard key and friendly URL Ids for my MongoDB

Posted: 30 Mar 2013 08:58 PM PDT

I have decided to use MongoDB as my Database for a web application. However, I have some difficulties to get started and I hope that you can help me out with a few questions.

I am developing my application in ASP.NET and with MongoDB as the back-end. I intend to start with a single server + 1 replication but wanted to built it right so I won't have problem sharding the database in the future if I have to.

One of my biggest problems is choosing the right shard key and friendly URLs for my website.

I have a folders collection and files as embedded collection inside the folders collection. Each user can create any number of folders and add files to it. Each folder belongs to one user. I wanted to know what is the best shard key for this type of collection? Many queries will query by the user, getting the folder and its items by querying the folders collection by its unique id. I will also use the id in the URL to get the folder and its filers: ex. mywebsite.com/folder/[the_id_of_the_folder]

I will also will use paging in my application, so I need to query the data (also in a sharded environment) and get for example: the last 10 records, page 2 with 10 records - all ordered by the last time the were inserted/updated

  • So my first question is what is the best shard key to use for a single machine, but considering that I will have to shard in the future
  • Does the shard key has to be the primary unique id of the document in MongoDB?
  • How can I generate more user friendly URLs - I prefer a numerical value instead of GUID (is there option to convert it?)

Help will be very appreciated, as I am stuck and can continue until I solve this.

Mongo connection failing with 'Transport endpoint is not connected'

Posted: 30 Mar 2013 08:58 AM PDT

I have a 2 server installation.

A web server with Apache and a DB server with MongoDB.

I am load testing it, and on ~300 RPS I am getting this error:

PHP Fatal error: Uncaught exception 'MongoConnectionException' with message 'Transport endpoint is not connected'.

The only thing I am noticing is that right before the fail, I am getting a lots of connections on Mongo:

insert  query update delete getmore command flushes mapped  vsize    res faults locked % idx miss %     qr|qw   ar|aw  netIn netOut  conn       time        0    659      0      0       0       1       0   208m  1.28g    40m      0        0          0       0|0     0|0    62k   217k   486   03:57:20

Almost 500 connections here... but never more than that!

Mongo is 2.0.3. PHP is 5.3.x (latest of Debian install...)

Help!

Modify DEFINER on Many Views

Posted: 30 Mar 2013 05:06 PM PDT

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

After some investigation it appears that the definer for these views is an old developer account that has been purged from the system. The databases and views with this problem are used very infrequently, and most being kept around for archival purposes.

There is about 40 views with a definer that no longer exists. Is there an easy way to change the definer to a different account on everything at once? Is there a way to get mysqldump to simply dump all the views out to a file so I could edit that file and recreate the views?

SQL: SELECT All columns except some

Posted: 30 Mar 2013 05:09 PM PDT

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.

Something like:

SELECT * -the_geom FROM segments;
  • I once heard that this functionality was deliberately excluded from the SQL standard because changing adding columns to the table will alter the query results. Is this true? Is the argument valid?
  • Is there a workaround, especially in PostgreSQL?
Posted by at No comments:

[SQL Server] monitoring a table

[SQL Server] monitoring a table

monitoring a table

Posted: 29 Mar 2013 10:33 PM PDT

I want to monitor a table continuously to see whether its updating or not if its updated i want too know how many rows it update and i wan to store them in a tabletime updated, no of rows
Posted by at No comments:

[MS SQL Server] Sql server logins

[MS SQL Server] Sql server logins

Sql server logins

Posted: 29 Mar 2013 05:31 AM PDT

Hi,can somebody help me with How to find out all user's permission, roles for all the databases?Thankstina

Version not chaning in Change Tracking in SQL Server 2008

Posted: 29 Mar 2013 06:07 AM PDT

Hi,When i Inserted few record in a table where CHange Tracking is enable, has generated all different Version Number for column SYS_CHANGE_VERSION and same for column SYS_CHANGE_CREATION_VERSION(For Insert SQL).Whereas in another table(In another DB), the 4,000,000 records only 50 distinct SYS_CHANGE_VERSION generated. So it was like a group of records have same version number.I was under impression that new version will be created each time if there is change is happening(insert,update,delete) in a table.Please guide..Anupam
Posted by at No comments:

[T-SQL] MERGE TSQL in SQL SERVER 2008

[T-SQL] MERGE TSQL in SQL SERVER 2008

MERGE TSQL in SQL SERVER 2008

Posted: 29 Mar 2013 06:34 PM PDT

Hi ALL,Need one help/suggestion in tsql using MERGE in sql server 2008.I'm using the below code to do upsert in one of our table. [code="sql"]MERGE [dbo].[Dly_Target_Comp] AS DSTUSING [dbo].[Dly_Target_Comp_Temp] AS SRCON (SRC.[DateKey] = DST.[DateKey]ANDSRC.[Wghtd_WI_Key] = DST.[Wghtd_WI_Key])WHEN NOT MATCHED THENINSERT ([DateKey], [Wghtd_WI_Key], [Item_Cd], [Metric_Desc], [Volume])VALUES (SRC.[DateKey], SRC.[Wghtd_WI_Key], SRC.[Item_Cd], SRC.[Metric_Desc], SRC.[Volume])WHEN MATCHED AND ( ISNULL(DST.[Item_Cd],'') <> ISNULL(SRC.[Item_Cd],'') OR ISNULL(DST.[Metric_Desc],'') <> ISNULL(SRC.[Metric_Desc],'') OR ISNULL(DST.[Volume],'') <> ISNULL(SRC.[Volume],'') )THEN UPDATE SET DST.[Item_Cd] = SRC.[Item_Cd] ,DST.[Metric_Desc] = SRC.[Metric_Desc] ,DST.[Volume] = SRC.[Volume];[/code]Now the requirement has changed, instead of doing update when there is a MATCH i need to do insert with the help of a new column called 'Projection_Date' . So there will same records but with different projection date.But while trying to insert when matched, it says insert not allowed in 'when matched' clause.Please advice.Thanks in advanceSam

i need the max length value of every column in every table in a database

Posted: 29 Mar 2013 07:34 AM PDT

I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will get the max length of each column in each table in a database. Then store it locally into a table like the below.create table TableColLengths(id int identity(1,1), tablename varchar(255), colName varchar(255), MaxColLength bigint)I'm going to write it myself however didnt know if somewone has already done it so i dont have to go though the hassle.

harnessing sp_msforeachdb to return one result set

Posted: 29 Mar 2013 06:16 AM PDT

I found a nifty but unsupported stored procedure that loops through all databases on your server and allows you do to things to each database. I would like it to just return a list of db_users for each database. I tried executing as follows but I get a seperate result set for each loop:[code="plain"]execute sp_msforeachdb 'use[?]; SELECT name, type, type_desc FROM sys.database_principals '[/code]Can someone show how to get only one result set listing all databases with corresponding users, type, and type_desc in one result set?

please help with cursor syntax

Posted: 29 Mar 2013 03:56 AM PDT

I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.For the cursor part I'm getting the following error:Msg 16915, Level 16, State 1, Line 4A cursor with the name 'getDatabaseName' already exists.Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 8Must declare the scalar variable "@DatabaseName".my cursor script[code="plain"]use MiguelsAppDECLARE @DatabaseName varchar(max)DECLARE getDatabaseName CURSOR FOR SELECT name, database_id, create_dateFROM sys.databases where name not in ('master', 'tempdb', 'model', 'msdb');GOOPEN getDatabaseNameFETCH NEXTFROM getDatabaseName INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGINprint @DatabaseNameFETCH NEXT FROM getDatabaseNameINTO @DatabaseNameENDclose getDatabaseNamedeallocate getDatabaseName[/code]Will someone please tell me where my syntax is wrong so it will run?

How to select data in a particular format

Posted: 29 Mar 2013 04:40 AM PDT

Greetings all.I have data in a view that when selected from looks like this:CertYear CertCode Quantity-------- --------- --------2008 HI-Master 22008 HI-Instr 12008 HI-Train 12008 HI-Master 32008 HI-Train 12009 HI-Master 32009 HI-Instr 4There are a few other fields that are in this view, but I left them out because they're not really relevant to this question (apart from simply knowing that there are more fields in this view, which is why the data that I have above looks redundant in nature, but isn't). I've also left out more rows (such as year 2010 and later). [b]What I need: [/b]Using a single select statement (I am not allowed to do this using a stored procedure), I would like to return a record set from this view that looks like the following:CertYear QtyMaster QtyInstr QtyTrain-------- ---------- -------- --------2008 7 2 42009 3 4 3I have a query that I've constructed which gets me part way there, but not quite. What I have is this:[code="sql"]select v.CertYear, v.CertCode , case when v.CertCode = 'HI-Master' then sum(v.Quantity) else 0 end as QtyMaster , case when v.CertCode = 'HI-Instr' then sum(v.Quantity) else 0 end as QtyInstr , case when v.CertCode = 'HI-Train' then sum(v.Quantity) else 0 end as QtyTrainfrom dbo.vCerts vgroup by v.CertYear, v.CertCodeorder by v.CertYear asc, v.CertCode asc[/code]But what this select yields (like I said, it's not quite what I want) is this:CertYear CertCode QtyMaster QtyInstr QtyTrain-------- --------- ---------- -------- --------2008 HI-Master 7 0 02008 HI-Instr 0 2 02008 HI-Train 0 0 42009 HI-Master 3 0 02009 HI-Instr 0 4 02009 HI-Train 0 0 3I tried remove the CertCode from the GroupBy and the select (except not from the case statement itself), because I thought that might get me to the record set that I want, but instead I get an error that says "Column 'dbo.vCerts.CertCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."Thanks in advance for any help on this, and sorry for the complexity involved!edit: Sorry for the data that is hard to read. I'm not sure how to get whitespacing to persist on this site (it appears to be mostly ripped out).

Alter Column with Identity

Posted: 18 Aug 2010 06:14 PM PDT

Hi All,The following one is the Table definition.After inserting some rows ...i want to add identity property to Owner_id.So please give me alter statement.CREATE TABLE [dbo].[Testowners]([owner_id] [int] NULL,[owname] [varchar](15) NULL)INSERT INTO Testowners Select 1,'asas'Select * from Testowners

Generate all possible number combinations for a provided list of numbers

Posted: 29 Mar 2013 01:17 AM PDT

Hi Experts,I want to get "all possible number combinations" for provided list of numbers.As an example, if I have 4 numbers: [b]1, 2, 3 and 4 [/b]For these numbers, I want to generate following numbers [which should be in order, so as to get unique list]:11,21,31,41,2,31,2,41,3,41,2,3,421,2 (for 2,1) [which is duplicated]2,32,41,2,3 (for 2,1,3) [which is duplicated]1,2,4 (for 2,1,4) [which is duplicated]1,2,3,4 (for 2,1,3,4 ) [which is duplicated]31,3 (for 3,1) [which is duplicated]2,3 (for 3,2) [which is duplicated]3,41,2,3 (for 3,1,2) [which is duplicated]1,3,4 (for 3,1,4) [which is duplicated]1,2,3,4 (for 3,1,2,4) [which is duplicated]41,4 (for 4,1) [which is duplicated]2,4 (for 4,2) [which is duplicated]3,4 (for 4,3) [which is duplicated]1,2,4 (for 4,1,2) [which is duplicated]1,3,4 (for 4,1,3) [which is duplicated]2,3,4 (for 4,2,3)1,2,3,4 (for 4,1,2,3) [which is duplicated]Therefore unique list of possible number combinations (which are ordered) for 1,2,3 & 4 is:1; 2; 3; 4;(1,2); (1,3); (1,4); (2,3); (2,4); (3,4);(1,2,3); (1,2,4); (1,3,4); (2,3,4)(1,2,3,4)Could this be achieved using sql? Thanks much for your help!Thanks
Posted by at No comments:

[SQL Server 2008 issues] APP-101 Exam Questions Free Download

[SQL Server 2008 issues] APP-101 Exam Questions Free Download

APP-101 Exam Questions Free Download

Posted: 29 Mar 2013 07:29 PM PDT

AppSense, the leading provider of user virtualization technology, has been elevated to a Managed Independent Software Vendor (ISV) Partner status by Microsoft Corp. The company was previously a High Potential Partner since 2009. Prior to joining exam1pass AppSense, Hoelzl spent four APP-101 exam years with Parallels as vice-president and general manager EMEA, helping the company achieve a market-leading position in desktop virtualisation.Most companies redesigning exam1pass their desktop architecture to take advantage of new, more efficient technologies such as Windows 7 and Microsoft APP-101 exam Application Virtualization (App-V) find that user-related challenges can become an obstacle to deployment.Hoelzl's goals are to communicate to the local market the benefits of user virtualisation and grow revenue in the region with a focus on the exam1pass largest 500 companies. We are pleased to see AppSense extend Microsoft's User State Virtualization to deliver APP-101 exam enhanced capabilities that help customers manage their employees' data and settings across physical and virtual environments.http://www.exam1pass.com/APP-101-exam.html

How to copy All data from sp_helpdb to some other table

Posted: 29 Mar 2013 03:00 PM PDT

Hi All,I am trying to copy All data from sp_helpdb to some other table. I am not finding any solution that how to do this.Can someone help on this.

Multiple order by in a result set

Posted: 28 Mar 2013 08:54 PM PDT

I have a specific requirment where a table contains a column called 'Empno' with the values 12, 34, 3, 78, 65, 109, 8. I want first three values(12, 34, 3) should print as it is. rest of the values should come in ascending order. expected out come should be as 12, 34, 3, 8,65,78,109. I tried using rownumber() with case statement in order by clause. but couldnt help?any ideas please?

SQL Query on Staging Table in SSIS 2008

Posted: 29 Mar 2013 02:34 PM PDT

Hi.I am doing ETL and after transformation (Multiple table joins etc) has been done, I got the table shown in the picture attached. Now finally I want to perform some simple queries on this transformed table to filter out records. How I can perform sql query on this table ? After performing queries I will load data to the destination table. Please help me.I will be very thankful to you.Regards

Assistance with this query

Posted: 25 Mar 2013 10:01 AM PDT

I'm a bit new to SQL, and I'm attempting my first real project. Currently, my setup is: SQL Server 2008 R2 with a linked SQL Server 2000.A couple points that I'm having issues comprehending:[li]Each record in the "employee" table has it's own ACTIONDATE, which allows more than one record for the same person, so getting one result for one person is appearing difficult. EMPLOYEEIDs are unique to the user, so how would I word the query to grab the most recent ACTIONDATE for each employeeid?[/li][li]Multiple joins are causing my queries to run for over 3 minutes. Each table has 100k records, minimum. How would I optimize a query with multiple joins with big tables? Or is there a better way to grab this data?[/li]I'm attempting to make a hierarchy from a SQL Server that houses are workforce software and place it into my SQL Server 2008 R2 to be utilized in another application. I want to grab all active employees (ENDDATE IS NULL) with the most recent ACTIONDATE of each active EMPLOYEEID.The query I'm attempting to run is:[code="sql"]SELECT DISTINCT employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEIDFROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org ON employee.employeeid = org.employeeid RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job ON employee.employeeid = job.employeeidWHERE employee.ENDDATE IS NULL[/code]

GO (batch separator)

Posted: 29 Mar 2013 10:38 AM PDT

If i copy/paste the output of the dynamic query and run it -then it works fine but when i run it using within dynamic sql using EXEC then it give me the following error:Incorrect syntax near 'GO'.Msg 111, Level 15, State 1, Line 8'CREATE VIEW' must be the first statement in a query batch.Msg 102, Level 15, State 1, Line 128What is the work around this?Thanks.

SQL2K8R2 Computed Column formula error

Posted: 29 Mar 2013 10:51 AM PDT

Hello,I was going to create a computed column based of course upon the values in three other columns. Of the four (the fourth being the computed column) involved 3 are defined as time(7). The fourth is tinyint. The formula is quite simple. (A*B)+C = Answer.SoColumnA time(7)ColumnB tinyintColumnC time(7)ColumnAnswer time(7)My formula is like this:(([ColumnA] * [ColumnB]) + [ColumnC])The error...time is incompatible with tinyint.So am I going to have to move this to a udf and even then not sure what I would CAST / CONVERT to accomplish the multiplication??Pointers would be greatly appreciatedJBSo to add to this I have written the following in SSMS:DECLARE @A time(0)DECLARE @B tinyintDECLARE @C time(0)SET @A = 0:1:30'--Well this won't do because I need everything in seconds to do the math sooo...DECLARE @Aseconds intSET @Aseconds = (DATEPART(HOUR, @A) * 3600) + (DATEPART(MINUTE, @A) * 60) + (DATEPART(SECOND, @A))SELECT @Aseconds * @B--then convert it back to time to add @CSELECT CONVERT(varchar, DATEADD(ms, @Aseconds * 1000, 0), 114) --Now dateadd each part really?? should I do this in .NET instead?

How to preserve global temporary table data

Posted: 29 Mar 2013 06:05 AM PDT

Hi guys,I would like to know if anyway to preserve global temporary table data till some one explicitly drop it. I have created one global ##tmp table and inserted data into it in one procedure (proc1) and selecting the data from another procedure (proc2). If i execute the proc2 immediately after proc1 finished.. there is no issue but when i execute the same proc2 after few min later (for say around 15/20 min) i found that the global tmp table has been dropped!!!! how to achieve the result without creating any physical table!!!Thanks for your help....

SSIS question on CSV Import

Posted: 29 Mar 2013 08:46 AM PDT

Many people may have faced this problem and I searched many solutions but somehow I could not hit the right one. Sorry for repeatative question.Here is my data in csv file and I am trying to import into sql server table but it does not import the way it should.field1,field2,field3,field4,field5,field6n1,n2,n3,stringWithout Quotes,"String in Quotes with ""words"" and coma , in the string",some moren1,n2,n3,"stringWith Quotes","String in Quotes with coma , in thestring",some moreI need output[u]Record 1:[/u]Field1: n1 Field2: n2Field3: n3 Field4: stringWithout QuotesField5: String in Quotes with "words" and coma , in the stringField6: some more[u]Record 2:[/u]Field1: n1Field2: n2Field3: n3Field4: stringWith QuotesField5: String in Quotes with coma , in thestringField6: some moreI tried to cleanup with Regex in script task ,mentioned in some other blog but it did not work and I am not expert to change RegEx to get desired output. Can anyone please help to find solution? Regex should work but any other solution is also welcome.Thanks.

Need help with this query... ER diagram attached

Posted: 29 Mar 2013 08:37 AM PDT

HiCan some one please help with this query:[b]List all Customers with multiple Meters.[/b]I'm a complete newb so forgive me if this is an easy problem to solve..Customers – minimum 20 recordsMeters – min. 30 recordsMeter Readings – min. 100 readingsInvoices – 1 per Meter ReadingI have tried the following query with no success... select *from CustomersLEFT OUTER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomerswhere Customers.idCustomers = Customers.idCustomers;I have also attempted SELECT CASE queriesER diagram is attachedThanks

Check Constraint Question - Situations Where a Child Record Should Be Limited to One Occurrance

Posted: 29 Mar 2013 05:01 AM PDT

One of my databases has a table for Project Dates. Some of the milestone dates can occur multiple times such as 'Project Review'. Others can only occur once, 'Project Handover'. Is there a way to create a check constraint whereby the dates that should only exist once, can be limited to existing only once while the others can exist multiple times?I do have an external table that manages the various dates and assigns a key to each date.Table: DateCategoriesId (Primary Key)Description[Other Fields]Table: Project DatesId (Primary Key)DateCategoryId (Foreign Key)Milestone Date

Parse CC in String

Posted: 29 Mar 2013 05:40 AM PDT

Hi Guys,I have a table with a column which is VARCHAR. This column has text and potentially Credit Card # and also has couple of dates. What I need to do is find all the records that may potentially have CC# in this column... I was thinking of using something like[code="sql"]WHERE patindex('%[0-9][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -][0-9 -]%',Memo) > 0[/code]Is there a better way to look for numbers in the string that may potentially be CC#. It is fine if some of them are not CC#. Maybe someone has already worked on a function like this where you find CC# from a string... Any help is appreciated. I am not looking for CLR function though as I have to do this using Query Analyzer.Thanks,Laura

Several Service Broker queues keep auto-disabling, not sure how to debug it

Posted: 29 Mar 2013 12:36 AM PDT

I have five queues that are automatically disabling themselves after being re-enabled. I've been running broker for about two years without such a problem, not sure where to begin troubleshooting. The only errors in sys.transmission_queue say "One or more messages could not be delivered to the local service targeted by this dialog."Can anyone help point me in the right direction?

How to avoid Deadlock while deleting data

Posted: 29 Mar 2013 04:18 AM PDT

The X_table has around 25K rocords (52MB in size), the application deletes records from this table, when it deletes the deadlock occurs, spid 1 hold X lock while deleting and spid 2 request for S lock until the spid 1 completes its delete and vice versa, which leads to deadlock.Below is the simple delete statement.exec sp_executesql N'delete from X_table where internal_name = @tableName',N'@tableName nvarchar(21)',@tableName=N'Tbl_Deleteme'I've attached deadlock graph and estimated plan for delete as well for reference. Could you please share your thoughts to mitigate this deadlock issue?Thanks in advance...

club to columns into one

Posted: 29 Mar 2013 02:25 AM PDT

The type and task column are combined into one table task keeping in mind the order they have to come in.current tableProc seq type task1 1 a1 this is 1 2 b2 supposed 2 1 a1 to 2 2 b2 be a 2 3 c3 test db2 4 c3 thank youdesired tableProc seq task1 1 a11 1 this is 1 2 b21 2 supposed 2 1 a12 1 to 2 2 b22 2 be a 2 3 c32 3 test db2 3 thank you

Joining two column to One

Posted: 29 Mar 2013 02:58 AM PDT

This current table has to be converted to the @temp_outOrder would be like this Proc --> Type --> Individual task for that typethe order of the @temp_out task col is defined like Order by type is base on the seq col then the each Type will have the list of task below it[code="sql"]/*Current Table*/Declare @temp Table ([Proc] INT,Seq INT,Type VARCHAR(2),Task VARCHAR(20))INSERT INTO @TEMPSELECT '1','1','a1','this is ' UNION ALLSELECT '1','2','b2','supposed ' UNION ALLSELECT '2','1','a1','to ' UNION ALLSELECT '2','2','b2','be a ' UNION ALLSELECT '2','3','c3','test db' UNION ALLSELECT '2','4','c3','thank you'SELECT * FROM @TEMP/*Desired Table */Declare @temp_out Table ([Proc] INT,Seq INT,Task VARCHAR(20))INSERT INTO @temp_outselect '1','1','a1' unionselect '1','1','this is ' unionselect '1','2','b2' unionselect '1','2','supposed ' unionselect '2','1','a1' unionselect '2','1','to ' unionselect '2','2','b2' unionselect '2','2','be a ' unionselect '2','3','c3' unionselect '2','3','test db' unionselect '2','3','thank you'SELECT * FROM @temp_out[/code]

SSIS Solution File Issue: Generate a new .database file and ask to overwrite?

Posted: 29 Mar 2013 02:18 AM PDT

Hello All,I have a trouble with my SSIS project solution file. I would appreciate it if someone could help me out.I'm doing things on our team foundation server. I mapped the latest project to my local path, but every time I clicked the project solution file, it gave me like: "[local mapping path]\ [b]projectname11.database[/b] already exists. Are you sure you want to overwrite it?" YES/NO?I have the [projectname].database in my mapping folder, the projectname11.database will be generated automately when I hit the .sln file in VS.If I choose to overwrite it, it will show:" The item [local mapping path]\[b]projectname11.database[/b] could not be found in your workspace" and " This file is under source code control. It must be checked out before it can be modified."If I choose not to overwrite it, " The '[local mapping path]\[b]projectname11.database[/b]' file already exists" This is really irritating. Please let me know if you have any thoughts. Thank you in advance. Best,Jing

Replication issue, blocked by another process and invalid article

Posted: 29 Mar 2013 02:22 AM PDT

Hello Allwell I have this weird issue, this is the layout3 servers, a publisher a distributor and a subscriptorall using sql server 2008 r2on the publisher there are two replications out of one databasethe first one always works fine, the second is failing while validating the snap view for two articles with these errors:[0%] The synchronization view for article 'table1' is invalid.[0%] The synchronization view for article 'table2' is invalid.Server MYSERVER, Level 13, State 51, Procedure sp_MSrepl_reinitsubscription, Line 603Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: MSSQLServer, Error number: 1205)now, on that server I do not see any blockingand the replication agent just keeps trying and tryin until it finish,if I restart the replication it will work just fine the first time and then the issue is backI removed the articles from that and create a new replication with only those two moving the data to a new database on the subscriber and the error continuesI already restarted all the services and nothing changes, no idea what to do nowmy issue is exactly the same that is happening here:http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/21206c41-a83e-4a40-805c-3825e4535595/but it was not solved....Please please help me, I really need this to start working

Curious: performance difference when using function in a where clause.

Posted: 21 Mar 2013 11:12 PM PDT

Hello all,This is just something I noticed and am [b]wondering[/b] about (I think this is an unexpected effect), there is [b]no problem[/b] to solve.I get a performance difference when I use a function in a where clause. See the code, the performance numbers are below the code.Sorry I was not able to isolate the problem and give definitions and data to reproduce this.Please read the code.[code="sql"]------------------------------------------------------------------------------------select * from client_order where -- ABS(ClientNr) = 12345 [b]ClientNr = 12345[/b] and startdttm = '2012-03-16'----(1 row(s) affected)----Table 'Encounter'. [b]Scan count 30, logical reads 62, physical reads 61[/b], read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.---- SQL Server Execution Times:---- CPU time = 0 ms, elapsed time = 765 ms.-- Estimated execution plan: Estimated Subtree Cost : [b]0.106408[/b]-- Actuel execution plan: Estimated Subtree Cost : [b]0.106408[/b]------------------------------------------------------------------------------------------------------------------------------select * from client_order where [b]ABS(ClientNr) = 12345[/b] -- ClientNr = 12345 and startdttm = '2012-03-16'----(1 row(s) affected)----Table 'client_order'. [b]Scan count 1, logical reads 4, physical reads 4[/b], read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.---- SQL Server Execution Times:---- CPU time = 0 ms, elapsed time = 49 ms.-- Estimated execution plan: Estimated Subtree Cost :[b] 0.0183334[/b]-- Actuel execution plan: Estimated Subtree Cost : [b]0.0183334[/b]------------------------------------------------------------------------------------[/code]Thanks for your time and attention,ben brugman

addinga Func to an SP?

Posted: 29 Mar 2013 12:33 AM PDT

HiFirst time doing this and not sure how(Even if its the proper way)I created the function GetforperiodALTER FUNCTION [dbo].[GetForPeriod] ( @StartDate datetime, @EndDate datetime) RETURNS TABLE RETURN SELECT Date FROM Auxiliary.Calendar WHERE date BETWEEN @StartDate AND @EndDatewhich returns the dates for the periodI want to add this function to an SP that looks at appointment dates , the reason is, I want to setup a matrix where if an appointment does not fall on a date I still want to display the date so its a clean.... say for January, 1 thru 31 I want to use @startdate and @Enddate as parameters....Make sense?ThanksJoe

ER diagram and related query!

Posted: 28 Mar 2013 11:04 PM PDT

HiI am getting 1000 rows returned from this Query: (see attached ER diagram)select *from Customers,Meters, MeterReadings, Invoice where Customers.idCustomers = 2;Obviously that's wrong!! so i'm thinking that maybe the ER diagram is set up wrong... for example, I should get two metersIDs back for customerID 2, with the associated readings and invoices but thats not the case, Where am i going wrong??Customers – minimum 20 recordsMeters – min. 30 recordsMeter Readings – min. 100 readingsInvoices – 1 per Meter ReadingThe query is:For any specific Customer list each of their Meters, Meter Readings and InvoicesIf you haven't guessed already I'm a complete newbHope you can help... even if it's just to tell me that the ER diagram is set up correctly!! If need be I can attach the data that I used to populate the tablesThanks

XML Index

Posted: 29 Mar 2013 12:35 AM PDT

We had a report that was using XML column and timing out. I added an XML Index and the report started to work. Last weekend we had a release and the size of the XML columns has been measured as as much as 10 times bigger. We now experience slowness and some blocking. Would the changes in that index that occur when the XML gets inserted, Updated or deleted be so determintal that I should remove the XML Index on that column?

Query running slow

Posted: 28 Mar 2013 07:47 PM PDT

Hi,Sql queries were running slow and these are triggering from They are supposed to run for 5-10 sec, but now they are taking 2 min.I can see SOS_scheduler in last wait column of sysprocesses.We have found fragmentation and rebuild the indexes. but after some time, the users were reporting the problem again.Can anyone advise how to proceed?

Display Every three rows has diffrent color repeatedly using tablex report in ssrs

Posted: 28 Mar 2013 09:58 PM PDT

Hi All,I have one requirement like report display 25000,client want to display row1=green,row2=blue,row3=red,row4=green,row5=blue,row6=red,row7=green.........can any one give me how to resolve the issue with details.Thanks,Vivekanand Kola

validating values

Posted: 28 Mar 2013 09:08 PM PDT

Hi,I have a screen from which some columns values like VoucherNo, Denomination, Quantity etc. I have to matched those values from my database tables that those values exists or not. If they exists then I have to stored those values in a separate table and if not matched then I have to store in a separate table.can you plz tell me how can I achieve this?How can I matched the value?
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog