Wednesday, August 7, 2013

[how to] Choosing A PostgreSQL Authentication Method For A Large Course

[how to] Choosing A PostgreSQL Authentication Method For A Large Course

Choosing A PostgreSQL Authentication Method For A Large Course

Posted: 07 Aug 2013 08:22 PM PDT

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone.

I would like students to be able to do the following, and (of course) have their accounts reasonably secure from attack:

  • Use psql on the same machine where the database server runs.
  • Use pgAdmin on their local (probably Windows-based) machine.
  • Write JDBC applications that run from their local machine.
  • Write JDBC webapps that run through apache on the same machine where the database server runs.

There are many authentication methods available (see http://www.postgresql.org/docs/9.2/static/auth-methods.html), but none seem to meet all of my requirements well.

  • Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the peer mechanism.
  • Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
  • Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option A is what I have always used myself, and it would be my preference. But I believe that it would rule out accessing the database from any client other than psql running on the same machine.

Option B seems to be the most flexible. But it also seems terribly ugly for students to need to set and maintain passwords in two disparate systems.

Option C would only allow connecting from clients on remote machines, which is not really acceptable.

I am fairly unfamiliar with GSSAPI / Kerberos, but it does not really sound like what I want either. My ideal connection method would have PostgreSQL ask the OS on which it is running to ask for a username and password, no matter where the client software is running. Is there some better option for my requirements than B above?

Conversion failed when converting date and/or time from character string

Posted: 07 Aug 2013 09:08 PM PDT

I am getting this error:

Conversion failed when converting date and/or time from character string.

DECLARE @fdate DATE         ,@tdate DATE    SET @fdate = CONVERT(DATETIME, @fromdate, 103);    SET @tdate = CONVERT(DATETIME, @todate, 103);    WITH cSequence  AS (      SELECT @fdate AS StartRange          ,@fdate AS EndRange          ,users.userid      FROM Users      -- WHERE Users.UserId = 54         UNION ALL      SELECT StartRange          ,DATEADD(dd, 1, EndRange)          ,UserId      FROM cSequence cq      WHERE EndRange < @tdate      )  SELECT DISTINCT EndRange      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN s.Description                  --when datepart(DW,c.EndRange) =  1 then 'Sunday'           ELSE 'Not Entered'          END AS DailyUpdate      ,CASE           WHEN CONVERT(DATE, s.reportdate, 103) = CONVERT(DATE, c.endrange, 103)              THEN convert(TIME, s.FromTime, 103)          ELSE convert(TIME, '00:00:00')          END AS FromTime              ,FullName      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN convert(TIME, s.ToTime, 103)          ELSE convert(TIME, '00:00:00')          END AS Totime  FROM cSequence c  LEFT JOIN UserDailyReportDtl s ON c.UserId = s.UserID  --AND CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)      LEFT JOIN users u ON s.UserID = u.UserId  WHERE NOT EXISTS (          SELECT 1          FROM UserDailyReportDtl          WHERE UserDailyReportDtl.UserId = c.UserId              AND CONVERT(DATETIME, UserDailyReportDtl.reportdate, 103) = CONVERT(DATETIME, c.EndRange, 103)          )      AND CONVERT(DATE, s.reportdate, 103) BETWEEN @fdate          AND @tdate      AND @Username = FullName  UNION ALL  SELECT DISTINCT EndRange      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN s.Description                  -- when datepart(DW,c.EndRange) = 1 then 'Sunday'           ELSE 'Not Entered'          END AS DailyUpdate      ,CASE           WHEN CONVERT(DATE, s.reportdate, 103) = CONVERT(DATE, c.endrange, 103)              THEN convert(TIME, s.FromTime, 103)          ELSE convert(TIME, '00:00:00')          END AS FromTime      ,FullName      ,CASE           WHEN CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)              THEN convert(TIME, s.ToTime, 103)          ELSE convert(TIME, '00:00:00')          END AS Totime  FROM cSequence c  LEFT JOIN UserDailyReportDtl s ON c.UserId = s.UserID  AND CONVERT(DATETIME, s.reportdate, 103) = CONVERT(DATETIME, c.endrange, 103)  LEFT JOIN users u ON s.UserID = u.UserId  WHERE CONVERT(DATE, s.reportdate, 103) BETWEEN @fdate          AND @tdate              --AND CONVERT(DATETIME, s.reportdate, 103)   is not null        AND @Username = FullName    ORDER BY c.endrange DESC

Administering PostgreSQL For Database Course [on hold]

Posted: 07 Aug 2013 06:13 PM PDT

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen to use PostgreSQL (running on a GNU/Linux-based VPS), since I am familiar with it from my own personal projects. But I have never needed to administer a server with more than one user, so I want to make sure that I am making wise decisions before setting things into stone. There are a few different aspects of administration that I know I need to think about (but am also interested in others I may be overlooking). Apologies if this would have been better as five distinct questions, but they all seem interrelated.

Aspect #1: Organization of databases

I can see several different ways to possibly partition our data:

  • Option A): Each student gets their own cluster to work in and creates a database per project.
  • Option B): Each student gets their own database within a shared cluster, and creates a schema per project.
  • Option C): Each students gets their own schema in a shared database.

Option A seems like it would be a management headache and require running many instances of the server, while option C would cause namespace clashes between projects. Option B has no downsides that I am aware of, so that's what I believe I should go with. Agreed?

Aspect #2: Client software

There are several ways that students could access the server:

  • Option A): SSH to the same machine where the server is running and run their own instances of psql.
  • Option B): Run pgAdmin on their local (likely Windows) machine.

Option A is the only one I have any personal experience with. Option B might be easier because I will not have to worry about teaching them to use UNIX at all. But the pgAdmin interface is enormously complex, exposing details that I would not like students to worry about before they have mastered the basics, and would allow them to things with point-and-click rather than mastering SQL. I think I would like to use option A initially and introduce option B later in the course as an alternative. Reasonable?

Aspect #3: Users, roles, & authentication

There are several ways the system could authenticate users:

  • Option A): Each student has their own OS-level user account and a password-less database role to match. Connections are allowed only through the ident mechanism.
  • Option B): Each student has their own OS-level user account and a database role to match that has a password. Connections are allowed only through the md5 mechanism.
  • Option C): Students do not have OS-level user accounts, only database roles with passwords. Connections are allowed only through the md5 mechanism.

Option C does not seem like a good choice, because I would like students to be able to write database-driven webapps in ~/public_html on the same machine that runs the database server, and that would seem to require user accounts. Option B seems ugly, because students would need to set passwords in two different systems. Option A seems cleanest (and is most familiar to me), but it seems to me like it would be incompatible with Aspect #2 Option B. And I am not sure this would allow the writing of webapps either, since apache would not be running as the file's owner. So option B looks like the least bad alternative. Agreed?

Aspect #4: Querying student-owned objects

Once students have done some work, I need to be able to view it (hopefully in an automated way, since I will have 40+ students in the course). I could log in as the superuser role to do this, but being a superuser unnecessarily seems like a bad idea. I can grant myself CONNECT privileges to the students' databases after I create them (for aspect #1 option B, adjusted as necessary for other options). But once students create tables I will not have SELECT privileges unless the student specifically gives them to me. Is there some way that I can get that privilege (and only that privilege) automatically granted to my non-superuser role on all objects that will later be created? If not, are there any other, better alternatives to logging is as a superuser.

Aspect #5: Protecting my disk space

Since many students will be sharing one system with constrained resources, I need a way to protect it from malicious or accidental havoc. There does not appear to be any way to limit database size within PostgreSQL. I thought about creating a tablespace per user in their home directories (this rules out aspect #3 option C) and using the OS's disk quotas. But it looks like this would not work because the files would still be owned by postgres rather than the individual user. Is there any good solution for this?

Oracle won't start

Posted: 07 Aug 2013 03:28 PM PDT

While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error:

SQL> Connected to an idle instance.  SQL> ORA-01081: cannot start already-running ORACLE - shut it down first  SQL> Disconnected

Also when trying to start SQL Plus manually AS SYSDBA I get:

ERROR:  ORA-12162: TNS:net service name is incorrectly specified

Using SQL Plus with other users, I get:

ERROR:  ORA-12537: TNS:connection closed

Any help appreciated ...

Best practice for upgrading mysql on a master-master setup

Posted: 07 Aug 2013 02:03 PM PDT

Anyone have experience upgrading a master-master setup of Mysql 5.1 to a release version of Mysql while trying to keep at least one server online (in my case we are upgrading to MariaDB 5.5).

Mysql ON DUPLICATE KEY UPDATE does not work with specific key

Posted: 07 Aug 2013 01:09 PM PDT

While, my query works on almost all entries, it does not work with one particular key.

"INSERT INTO highscores (uuid, name, score) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE score = VALUES(score)"

If I remove the ON DUPLICATE KEY part, both keys throw error that they already exist.

"SELECT * FROM highscores"  a0c0abfe-e859-33d2-8075-a5532417a09d Tomas 408  a0c0abfe-e859-33d2-ffdf-591e2e27509d John 48

Before and after query:

Saving score: a0c0abfe-e859-33d2-8075-a5532417a09d, Tomas, 423  Saved score:  a0c0abfe-e859-33d2-8075-a5532417a09d, Tomas, 408    Saving score: a0c0abfe-e859-33d2-ffdf-591e2e27509d, John, 75  Saved score:  a0c0abfe-e859-33d2-ffdf-591e2e27509d, John, 75

Table layout:

CREATE TABLE highscores (UUID BINARY(16) NOT NULL,                           NAME VARCHAR(64),                           SCORE INT,                           PRIMARY KEY (UUID));

Do you have an idea what could cause this ?

Engine:mysql(5.6.12)

Why set up static data in views vs. using tables in mysql?

Posted: 07 Aug 2013 12:45 PM PDT

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields.

I have the following too:

LDAPIMPORTANT - view that stores all 75K but only 15 fields

LDAPSHORT - view that stores all 75k but 5 fields

LDAPAB - view that only stores 9k employees based on two groups (field lookup)

Each of these are used a lot and for different apps and also there are a lot of views written against these views. But there is no updates to them. We do not update employee data. It is just LDAPALL update once a night.

In this circumstance should I create tables from the LDAPALL table instead of views? I could set up jobs to create these tables once a night. What is best practice behind this? Speak in layman's terms because I am a PHP developer made to do all DB admin stuff.

How to delete duplicate records

Posted: 07 Aug 2013 10:58 AM PDT

I'm able to find duplicate records using the following query, however, I'm not sure how to delete duplicates records, and ignoring any records that starts with 0 in starttime field.

SELECT `StartTime`, COUNT(`CallDetailRecordID`) AS cnt FROM `CallDetailRecord`  GROUP BY `StartTime` HAVING cnt > 1

Sample output 

 StartTime     cnt    -------------  --------          0        198  1340511506737         2  1340511958364         2  1340512141687         2  1340512191631         2  1340512244925         2   1340512670902         2

How to query a database for empty tables

Posted: 07 Aug 2013 05:40 PM PDT

Due to some 'developers' we had working on our system we have had issues with empty tables. We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't.

I would like to run a query the system tables to find what user tables are empty. We are using MS SQL 2008 R2.

Thanks for the help.

How to execute this procedure in PL/SQL?

Posted: 07 Aug 2013 12:49 PM PDT

I have this table in the below format: 

Persnbr | Userfieldcd | Value  01      | Port | Funds     01      | Vip1 | Systems    02      | Port | Bank    02      | Vip1 | Authority

This is how I want it: 

Persnbr | Port  | Vip1  01      | Funds | Systems     02      | Bank  | Authority

As I dont know the all the fields in the userfieldcd column, I am trying to dynamically pivot the table. So I am using this procedure but I dont know how to call it in PL/SQL developer. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production 

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)  as  sql_query varchar2(1000) := 'select persnbr ';    begin      for x in (select distinct userfieldcd from persuserfield order by 1)      loop          sql_query := sql_query ||              ' , min(case when userfieldcd = '''||x.userfieldcd||''' then value else null end) as '||x.userfieldcd;                dbms_output.put_line(sql_query);      end loop;        sql_query := sql_query || ' from persuserfield group by persnbr order by persnbr';      dbms_output.put_line(sql_query);        open p_cursor for sql_query;  end;  /

When I call the procedure using:

VARIABLE x REFCURSOR    BEGIN           dynamic_pivot_po(:x)        END        /

it gives me

ORA-00900: Invalid SQL statement.

Execution plan vs STATISTICS IO order

Posted: 07 Aug 2013 06:11 PM PDT

SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON?

The following query:

SET STATISTICS IO ON;    SELECT  *  FROM    Sales.SalesOrderHeader AS soh          JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID          JOIN Production.Product AS p ON sod.ProductID = p.ProductID;

Generates this plan:

Graphical execution plan

And this STATISTICS IO output:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 3, read-ahead reads 1277, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 1, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, I reiterate: what gives? Is there a meaningful ordering to STATISTICS IO output or is some arbitrary order used?

Indexing for query containing xml column

Posted: 07 Aug 2013 03:21 PM PDT

I have below queries which performs little slow.So i am planning to create index.I created primay xml index and now performance improved little bit(by looking at execution plan) .Queries are shown below. 

Update account set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Balance/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Balance)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;    Update account  set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Transaction/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Transaction)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

The table has below structure

CREATE TABLE [dbo].[account ](      [ID] [int] NULL,      [Type] [char](10) NULL,      [Date] [date] NULL,      [Balance] [decimal](15, 2) NULL,      [TRansaction] [decimal](15, 2) NULL,      [mybal] [decimal](15, 2) NULL,      [dailybalance] [xml] NULL,      [AutoIndex] [int] IDENTITY(1,1) NOT NULL,   CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED   (      [AutoIndex] ASC  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am using sql server 2008 r2 express.So i think i cannot use full text search. So please advice me how the index should be created so that above query performance is improved.

ROW wise SUM VS COLUMN wise SUM in MySQL

Posted: 07 Aug 2013 09:34 PM PDT

I have a table tableA with the following structure:

enter image description here

I modified this structure into tableB to reduce number of rows and the category is fixed length:

enter image description here

Assume I have 210k rows in tableA. After the transfer to the new structure, tableB contains only 70k rows.

In some cases I want to sum all the values in the table:

SELECT SUM(val) AS total FROM tableA;               -- 1  -- or  SELECT SUM(cate1+cate2+cate3) AS total FROM tableB; -- 2

Query 1 is executing faster than query 2. tableB contains less rows than tableA. I was expecting query 2 to be faster.

Why is query 2 slower than query 1?

SSIS package blocks itself if uses TRUNCATE

Posted: 07 Aug 2013 10:09 AM PDT

There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data:

enter image description here

Here the Delete block deletes everything from six tables, and the Parse block loads six files to the six tables.

If the Delete uses delete from dbo.table, then everything works fine, but is slow.
But if Delete uses truncate table dbo.table instead, the deletion takes no time, but the package becomes blocked when it comes to the Parse task. Would just sit around doing nothing.

At this moment on the server side I can see an SSIS spid being blocked by spid -2, which is "Orphaned distributed transaction." It would appear that used to represent the truncating connection. This connection hangs around, and the package is blocked by the locks it placed.

If I switch the only used SQL Server Connection manager to RetainSameConnection = True, then the package fails as soon as it enters the Parse task. The task asks the manager for a connection, and the manager responds with

[SQL Server Destination [471]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection Manager" failed with error code 0xC001A004. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Is there a way to set up the package so that it uses truncate and does not block itself?

How to build a database that contain only the delta from yesterday

Posted: 07 Aug 2013 06:32 PM PDT

I need to know what has been changed on my database since last night. Is it possible to extract this data from the LDF file and to build a new Database that contains the delta?

For example, let say I have a table for users and now, a new user was added and one of the users update his home address. I need to be able to build a new database that users table will contain two records 1. The new user (and to add a new column to know if it's new or update field) 2. The user that update his record (it will be nice to know which record has been update)?

BTW, I have to SQL servers that I can use (2008 and 2012)

Thanks In Advance

Simple XML .query SELECT statement times out on 1 SQL SERVER instance

Posted: 07 Aug 2013 07:44 PM PDT

I dropped and recreated a stored procedure on a DB on a SQL Server 2012 instance (SERVER1/INSTANCE1), after which the sproc started hanging on a line. Using profiler I have reduced a recreateable scenario on this SQL Server instance to the following code:

DECLARE @RequestXML xml = '<Foo>      <Node1>Hello</Node1>      <Node2>World</Node2>      <Node3>Today</Node3>  </Foo>'    SELECT      @RequestXML.query('/Foo/Node1[1]')      ,@RequestXML.query('/Foo/Node2[1]')      ,@RequestXML.query('/Foo/Node3[1]') -- comment out any of these lines so only one or two items are returned and the query works fine!

This statement hangs. If you comment out one of the returned columns in the SELECT statement, then the statement runs fine. So one of two returned columns is fine, but anything over is bad. If you take out the first node selector (i.e. the [1]) it also runs fine.

Here's the rub though:

  • the statement fails on any DB in this SQL Server 2012 instance (SERVER1/INSTANCE1).
  • the statement succeeds on any other SQL Server instance I have tried, including other instances running from the same SQL Server machine as the failing instance (e.g. SERVER1/INSTANCE2, SERVER1/INSTANCE3 etc.) - all of which are 2012 instances.

It seems to me that SERVER1/INSTANCE1 has borked the way it runs this statement. I have heard that SQL Server does things with optimising execution of statements, but my knowledge stops there. I'm sure there must be some way to get it to behave again, but how?

UPDATE

The following adjustment (getting the singleton of the Xpath result-set rather than potentially multiple first elements) solves the issue above for the SERVER1/INSTANCE1 issue. I would put this as the answer, except that I don't believe this is identifying the underyling problem (please correct me if I'm wrong!). Given that our codebase deployed on other servers implements the above statement (or derivatives of it) I don't want to update our entire shredding XML approach without good reason (or at least I would have to justify it to my peers).

DECLARE @RequestXML xml = '<Foo>      <Node1>Hello</Node1>      <Node2>World</Node2>      <Node3>Today</Node3>  </Foo>'    SELECT      @RequestXML.query('(/Foo/Node1)[1]')      ,@RequestXML.query('(/Foo/Node2)[1]')      ,@RequestXML.query('(/Foo/Node3)[1]')

Any help gratefully received.

Thanks,

Ali

Totals Column - Blank if Zero or NULL

Posted: 07 Aug 2013 08:48 PM PDT

A report currently computes a "totals" column like so:

,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +             ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0                         THEN ''        ELSE CONVERT(VARCHAR(15),              ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) +             ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0))         END AS 'MealsTotal'

How can I get a blank cell when a total equals zero, instead of NULL or "0", and avoid doing the computation twice?

Can I force a user to use WITH NOLOCK?

Posted: 07 Aug 2013 10:37 AM PDT

Can I force a user's queries to always run with the hint NOLOCK? e.g. they type

select * from customer

But what is executed on the server is

select * from customer with (nolock)

This question is not:
About the various pros and cons of NOLOCK, respectfully. I know what they are, this is not the place to discuss them.

How to select from a table without including repeated column values?

Posted: 07 Aug 2013 10:36 AM PDT

In a previous question How to merge data sets without including redundant rows? I asked about filtering redundant historical data during import, but @DavidSpillett correctly replied that I couldn't do what I was trying to do.

Instead of filtering the table during import, I now want to create a view on the table that returns only records where the price has changed.

Here's the original scenario rephrased to suite this question:

We have a table of historical prices for items. The table contains rows where the same price is recorded for multiple dates. I want to create a view on this data which only shows price changes over time, so if a price changes from A to B I want to see it, but if it "changes" from B to B then I don't want to see it.

Example: if the price yesterday was $1, and the price today is $1, and there were no other price changes, then the price today can be inferred from the price yesterday so I only need the record from yesterday.

Example (http://sqlfiddle.com/#!3/c95ff/1):

Table data:    Effective            Product  Kind  Price  2013-04-23T00:23:00  1234     1     1.00  2013-04-24T00:24:00  1234     1     1.00 -- redundant, implied by record 1  2013-04-25T00:25:00  1234     1     1.50  2013-04-26T00:26:00  1234     1     2.00  2013-04-27T00:27:00  1234     1     2.00 -- redundant, implied by record 4  2013-04-28T00:28:00  1234     1     1.00 -- not redundant, price changed back to 1.00    Expected view data:    Effective            Product  Kind  Price  2013-04-23T00:23:00  1234     1     1.00  2013-04-25T00:25:00  1234     1     1.50  2013-04-26T00:26:00  1234     1     2.00  2013-04-28T00:28:00  1234     1     1.00

My initial attempt used ROW_NUMBER:

SELECT      Effective,      Product,      Kind,      Price  FROM  (      SELECT          History.*,          ROW_NUMBER() OVER          (              PARTITION BY                  Product,                  Kind,                  Price              ORDER BY                  Effective ASC          ) AS RowNumber      FROM History  ) H  WHERE RowNumber = 1  ORDER BY Effective

Which returned:

Effective               Product  Kind  Price  2013-04-23T00:23:00     1234     1     1.00                                               -- not 2013-04-24, good  2013-04-25T00:25:00     1234     1     1.50  2013-04-26T00:26:00     1234     1     2.00                                               -- not 2013-04-27, good                                               -- not 2013-04-28, bad

I tried searching for a similar question/answer but it's hard to work out how to phrase the search, an example is worth a lot of words.

Any suggestions appreciated. Thanks

Why does Log Shipping .TRN file copy just stop

Posted: 07 Aug 2013 01:59 PM PDT

I apologize in advance for a long post but I have had it up to here with this error of having to delete LS configuration and starting it over for any DB thats got this error.

I have LS setup on 3 win2k8r2 servers(pri,sec,monitor) with 100 databases transactions backed up and shipped from the primary to secondary and monitored by monitor. Back ups and copies are run every 15min and then the ones older than 24hrs are deleted. Some DBs are very active and some not so much but shipped regardless for uniformity sake(basically to make secondary server identical to primary). Some DBs are for SP2010 and majority for inhouse app.

The issue is that after all LS configs are setup, all works well for about 3 to 4 days then i go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status because the time since last copy is over 45min so no restore has occured. This seems random and the only errors i see is from an SP2010 DB(WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a reports db that gets created weekly and LS cannot just figure which the last copy to backup or to restore is. I posted here regarding that and i have yet to find a permanent solution. For my main error(time since last copy) i have not seen anything that could have caused that and i dont get any messages(even though some alert statuses have been ignored for 3 days). Anyway, I would really appreciate any input on understanding whats causing this and how i could fix it. Thanks.

oracle alter table move taking long time

Posted: 07 Aug 2013 10:55 AM PDT

I'm currently trying to compress a table in Oracle with the following statements

ALTER TABLE MYTABLE COMPRESS FOR OLTP;  ALTER TABLE MOVE;

My question is: the MOVE operation is taking forever and unfortunately I forgot to state the ONLINE clause.

If I cancel it, will I lose data? Or is there any way to find out how long it will take?

How to determine Oracle LOB storage footprint?

Posted: 07 Aug 2013 10:36 AM PDT

With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.

Timing is fairly easy to profile but what's the easiest way to get a reasonably accurate measurement of how much space a specific LOB column takes up?

Oracle schema import is not importing all the tables present in the schema dump file

Posted: 07 Aug 2013 09:55 AM PDT

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported.

Here are the export and import commands i have used.

Export Command:  ---------------  exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log     Import Command:  ---------------  imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y

The Oracle we are using is 10g EE.

What could be going wrong ? Can you please suggest a solution to this issue.

SQL Server update query on linked server causing remote scan

Posted: 07 Aug 2013 04:54 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?

Difference between idx_tup_read and idx_tup_fetch on Postgres

Posted: 07 Aug 2013 08:35 PM PDT

On Postgres 8.4 when you do:

select * from pg_stat_all_indexes where relname = 'table_name';

It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?

User login error when trying to access secured SQL Server database

Posted: 07 Aug 2013 01:55 PM PDT

We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past".

One vendor package we use uses the built-in MS SQL Server security. Each module has three groups:

  • xxView = View Only
  • xxUser = Add, Update rows (cannot delete)
  • xxAdmin = Delete rows

So we can add a person to one of these groups an they get the appropriate access. I don't have the actual error message in front of me anymore, but it said that they are not authorized to table CriticalVendorTable. It worked before the rename. The admin removed the person from each group and re-added them. Still no go. I even restarted the server and it still doesn't work. My best guess is that there is UUID (or unique id) somewhere that is causing problems.

The vendor's response is to delete the user and then re-add them. I have only had time to do some brief searching, but I found this page; AD User SID Mis-mapping. Would this be worth trying? Would it be better to just delete the user and recreate them?

hstore for versioning fields in postgresql

Posted: 07 Aug 2013 08:55 PM PDT

I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table.

In order to track the changes done on each field I am thinking on create a table defined by:

CREATE TABLE fieldhistory(tableid int, fieldid int, id bigint,value hstore);  CREATE INDEX fieldhistory_index ON fieldhistory(tableid, fieldid, id);

I would expect the table to grow and grow, and retrieve from fieldhistory data only by tableid, fieldid and id.

When the tables are modified I would add a new record with something like:

SELECT upserthistory(1,1,1,'first update','115435','3');  SELECT upserthistory(1,1,1,'second update','115435','3');

where upserthistory is defined by :

CREATE OR REPLACE FUNCTION upserthistory(key1 INT, key2 INT, key3 BIGINT, data TEXT, theuser TEXT, whend TEXT) RETURNS VOID AS  $$  BEGIN      LOOP          -- first try to update the key          UPDATE fieldhistory SET value = value || ((whend||'.'||theuser) => data) WHERE tableid = key1 AND fieldid = key2 AND id = key3;          IF found THEN              RETURN;          END IF;          -- not there, so try to insert the key          -- if someone else inserts the same key concurrently,          -- we could get a unique-key failure          BEGIN              INSERT INTO fieldhistory(tableid,fieldid,id,value) VALUES (key1, key2, key3, hstore(array[whend||'.'||theuser,data]) );              RETURN;          EXCEPTION WHEN unique_violation THEN              -- Do nothing, and loop to try the UPDATE again.          END;      END LOOP;  END;  $$  LANGUAGE plpgsql;

Are there better approaches to accomplish this? the table would grow max to 225 millions of rows, or would it be better to have 50 tables of 4.5 millions of rows at max each? noting that the actual trace of each field will go to the hstore.
Posted by at No comments:

[SQL Server] Scheduled SSIS package not pulling all data

[SQL Server] Scheduled SSIS package not pulling all data

Scheduled SSIS package not pulling all data

Posted: 07 Aug 2013 06:16 AM PDT

I have a scheduled SSIS package that is not including all information every morning into the flat file it generates. It's a really simple SSIS package. It does a data extract to a comma delimited file, renames it with the current date then it uploads the file. After that it moves it to an archive folder.It seems that 2/3's of the time it gets all the data it needs to but the other part of the time, it skips records. When I run the same script in SSMS after the fact, the results (within SSMS) are spot on but the file it created might be 5 to 15 records short. Normally there are anywhere from 20-40 records every day and it's a really simple SQL script. When running the script in SSMS it takes less than 1 second.What would be causing the script to skip over records? The filter criteria is such that it pulls on information that is never changed once it's entered into the system so I could go back and do day by day screen shots if I wanted to and running my SQL script matches perfectly with what the application shows. I'm thinking this has something to do with a bad connection or something along those lines.I went through all 15 packages that run on this server and none of them overlap. The AV scan takes place 11 hours before this script fires off. The backups take place 7 hours before this job fires off. I'm not sure what could be interfering with this SSIS package. I don't have any issues with any of the other SSIS packages on that server. The packages that pull data from our database pull complete data.Does anyone have any troubleshooting tips to suggest?TIA,John
Posted by at No comments:

[MS SQL Server] master database corrupted after upgraded?

[MS SQL Server] master database corrupted after upgraded?

master database corrupted after upgraded?

Posted: 06 Aug 2013 07:52 PM PDT

Hi,Master database was corrupted after upgraded to enterprise edition and updated SP3.I had done IN-PLACE UP-gradation method as below steps..Old Edition1. Windows server 2008 enterprise edition with SP1(32 bit)2. SQL SERVER 2008 standard edition with SP1. (32 bit)new edition1. Windows server 2008 enterprise edition with SP1(32 bit)2. In place upgrade edition thru SQL 2K8 setup (which is enterprise edition), it was successfully completed then connected SSMS and checked Enterprise edition updated and all the SQL related services were running.3. Updated service pack 3, it was also successfully updated after that I took restart the server.4.After restart the server SQL Service not started due to master database corrupted and error message appear in the event viewer.5.Tried to rebuild that master database but it was not successful due to user SA existing password not accepted while rebuilding database.6.Finally I restored system image FULL backup and started working with old standard edition..(by taking 3rd party tool)I noticed that master database corrupted due to applying SQL SP3… also tried to uninstall SP3 that was not happed due to asking setup CD for removing SP3.All above steps done at Development server, it was working fine and there was no issues and errors.I am facing issues in production server.. how come issues was happened? Could you suggested me, how to resolve in feature?what are the steps need to be followed the Troubleshooting?1. I have verified startup parameter files location in configuration manager.2. Service account need to be check3. Rebuild the database thru command promptThanks & RegardsAnanda
Posted by at No comments:

[Articles] The Standard Limitation

[Articles] The Standard Limitation

The Standard Limitation

Posted: 06 Aug 2013 11:00 PM PDT

SQL Server Standard Edition only supports 64GB of RAM and it's our fault. Or is it, and is this a reasonable number? Steve Jones has a few thoughts today.
Posted by at No comments:

[SQL 2012] Condition positioning in where clause

[SQL 2012] Condition positioning in where clause

Condition positioning in where clause

Posted: 07 Aug 2013 02:47 AM PDT

Hi.Can anyone please explain me this.When running a query on a table with 3 conditions in the where clause, if I change the position of the conditions in the where clause it could take 2 minutes while's if i leave the conditions positions it could take 2 seconds.For example: Table 1 contains a list of contacts.Table 2 contains a list of phone calls.Table 3 contains a list of email messages.When searching for a contact in table1 where not is (table2) and is in(table3) it could take 1 second for results.But when searching for the same a contact, where is in (table3) and is not in (table2) (the same query as above just now we search with table3 first) it could take 2 minutes.Please advice.

Issue with Like Statement

Posted: 06 Aug 2013 09:54 AM PDT

Hello,I have a textbox in my application which allows the user to type any criteria in this will then be fired to the DB to return matching records that are LIKE the criteria i've passed in.Now im my database table there are two records which differ these are as followsKipling Road Alexandra ParkWhen I type in the letter k both of them are returned yet only one has the letter k in?below is my stored procedure[code="sql"]ALTER PROCEDURE [dbo].[sp_GetMapLocations] @SearchText varchar (20)ASBEGIN SET NOCOUNT ON; Select LocationName, Latitude, Longitude from Locations where ((@SearchText is null) or ( LocationName like '%' + @SearchText + '%' ))END[/code]This is the part thats going wrong (which im sure you new anyway)[code="sql"]( LocationName like '%' + @SearchText + '%' )[/code]Any help would be appreciated.

Problem with agent service accounts?

Posted: 06 Aug 2013 07:33 AM PDT

Hey guys. Ive never had this problem with SQL2008 /R2. But I cant get the SQL Agent to log in with anything but local system or a local user that has admin rights on the server. Generally, I would make a SQL_svc_agent_user and grant no rights and and using the sql installer it would grant any permissions including log on a service and add NT SERVICE\SQLSERVERAGENT as SA. Now even with all that in place this user cannot log on in 2012. Even if I granted SQL_svc_agent_user SA rights in Management Studio this still does not work. Seems like something changed in 2012? What am i missing here? The service account for SQL runs just fine but not the agent. This is a local account, but the machine is connected to a domain. Its driving me nuts!:hehe:

SQL 2012 AlwaysOn with SQL 2008 Failover Cluster?

Posted: 07 Aug 2013 01:31 AM PDT

Does anyone know if you can have an availability group that has databases on a SQL 2008 R2 failover clustered instance and a 2012 standalone SQL server?Here is the scenario we want as most of our old applications will not be supported on 2012. We still have some on 2005. [u]Site 1 (Primary Site):[b][/b][/u]Failover Cluster 1: Two nodes each running Windows SQL Server 2008 R2 Enterprise Edition This is the traditional cluster with shared storage (active/passive) - we can only afford one replica on the site in terms of storageThis will be automatic failoverFailover Cluster 2: Two nodes each running Windows SQL Server 2012 Enterprise Edition This is the traditional cluster with shared storage (active/passive) - we can only afford one replica on the site in terms of storageThis will be automatic failover [u]Site 2 (Remote Site):[b][/b][/u] - Planned for DR scenario using alwaysON availability groupsStandalone SQL Server (which of course will be made part of the Windows FC indicated above): Single server running Windows SQL 2012 Enterprise EditionWe want to use alwaysON to create availability groups for manual failover between sites. Now, manual failover for Cluster 2 from site 1 to site 2 I believe is feasible. However, my concern is whether or not 2008 R2 FC is supported with 2012 alwaysOn. Is it possible to create availability groups so I can manually fail from my 2008 clustered instance to site 2?Thanks You.

SQL Cluster goes to sleep?

Posted: 06 Aug 2013 11:21 PM PDT

I have two Windows 2012/SQL 2012 failover clusters that seem to go to sleep at night. I can connect to then with no issues all day long, but when I try in the morning I can not connect to them remotely. If I RDP to the server and run SSMS, they are fine, but from my desktop I get a "did not respond in a timely manor" I simply fail the instances to another node and then they are fine. There is nothing in the system logs that indicate any issues. We have the firewalls turned off. Any ideas?

SQL server 2012 CDC operation code for update are not correct

Posted: 06 Aug 2013 05:51 PM PDT

HiHas anyone tried CDC on SQL server 2012The operation code for update is created as 1 and 2 but it should be 3 and 4Any guessAshish Shevale

Best practice for SQL UPDATE / INSERT for huge amount of data

Posted: 06 Aug 2013 03:29 AM PDT

Hi Experts -I am using SQL MERGE for Update / Insert in my script for a table which has around 40 Millions of data and everyday, can expect up to 10 - 15 Lacs of data. Please suggest if MERGE is the best way to update / insert large volume of data. If not, please suggest.Thank You!
Posted by at No comments:

[T-SQL] Adding special characters

[T-SQL] Adding special characters

Adding special characters

Posted: 07 Aug 2013 12:48 AM PDT

Hi,I have a scenario in which I have to find a column with varchar or nvarchar datatype in all tables of a database and update the value of those columns with special characters like '!@#$%^&*()'.Here is the DDL script : [code="sql"]CREATE TABLE table1( ID INT IDENTITY, Name1 VARCHAR(50))GOCREATE TABLE table2( ID INT IDENTITY, Name2 VARCHAR(50))GOCREATE TABLE table3( ID INT IDENTITY, Name3 VARCHAR(50))INSERT INTO table1SELECT 'a'UNION ALLSELECT 'b'GOINSERT INTO table2SELECT 'c'UNION ALLSELECT 'd'GOINSERT INTO TABLE3SELECT 'e'UNION ALLSELECT 'f'[/code]Expected Output:table1 '!@#$%^&*()a'table1 '!@#$%^&*()b'table2 '!@#$%^&*()c'table2 '!@#$%^&*()d'table3 '!@#$%^&*()e'table3 '!@#$%^&*()f'I just have to add these special characters in front of columnvalues whose datatype is varchar or nvarchar..

Splitting Comma Separated Values into Rows

Posted: 06 Aug 2013 04:25 PM PDT

[code="sql"]DECLARE @t Table( AreaID int, AreaName nvarchar(100), Responsible nvarchar(100))Insert Into @tSelect 1, 'Finance',NullUnion AllSelect 2, 'IT','Internal, External'Union AllSelect 3, 'Audit, Security', 'Internal'Union AllSelect 4, 'Health, Safety, Compliance', 'Internal, External' Select * From @t/* I want the results like the following without having to use any splitter udf AreaID AreaName Responsible ------ ------------- --------------- 1 Finance Null 2 IT Internal 2 IT External 3 Audit Internal 3 Security Internal 4 Health Internal 4 Health External 4 Safety Internal 4 Safety External 4 Compliance Internal 4 Compliance External*/[/code]

Need help with TSQL

Posted: 06 Aug 2013 11:37 PM PDT

Hi,I have table structure as shown in the below image. I have attached script for the table with sample data.There is parent child relation between Time_Id and Parent_Id columns. [img]http://www.sqlservercentral.com/Forums/Attachment14139.aspx[/img]I want output rows in the following order. Basically all the root nodes(with NULL parent_id) should be in the order of SortOrder column. But at the same time if root has childs, all the childs should immediately appear below the root in the sorted order.[img]http://www.sqlservercentral.com/Forums/Attachment14140.aspx[/img]Can you please help ?Thanks.

Splitting Comma Separated Values into Rows

Posted: 06 Aug 2013 04:28 PM PDT

I want to split the Comma Separated Values into rows[code="sql"]DECLARE @t Table( AreaID int, AreaName nvarchar(100), Responsible nvarchar(100))Insert Into @tSelect 1, 'Finance',NullUnion AllSelect 2, 'IT','Internal, External'Union AllSelect 3, 'Audit, Security', 'Internal'Union AllSelect 4, 'Health, Safety, Compliance', 'Internal, External' Select * From @t/* I want the results like the following without having to use any splitter udf AreaID AreaName Responsible ------ ------------- --------------- 1 Finance Null 2 IT Internal 2 IT External 3 Audit Internal 3 Security Internal 4 Health Internal 4 Health External 4 Safety Internal 4 Safety External 4 Compliance Internal 4 Compliance External*/[/code][b]Reason: Not allowed to build custom function on a proprietary database[/b]Any help please?

Trying to select the last 3 months of data

Posted: 06 Aug 2013 06:50 AM PDT

I am trying to create a date, that I can put in a where statement to filter for the last 3 months of data... but not use today's date but the latest date in the table.I was thinking along these lines.. but SQL errors out:;with base as(select post_dm from ztb_forecastable_metrics_hist group by Post_DM)set @lastdate = (select MAX(post_dm) as Max_DM from base)

DML with linked servers is so slow!!!

Posted: 06 Aug 2013 08:19 AM PDT

Grettings everybody, I looking for the one who can solve this issue!I need to save data from Server1 to Server2, this data is created from a stored procedure because It is the result from other tables and this data must to travel to a Publicator for future Replication.The problem is, when Server1 updates the table on Server2, is too slow. I do something like this: insert into [Server1].[Base].dbo.table select * from #tablebut if I do -> insert into table select * from #table. It is so fast! I testing this process on my laptop but I need use two servers.I read this is why SQL Server scans the table from linked server. Now, I need to know if exists a way to do this without this scan!Thanks!PD: Sorry if my english was bad! :(

Using MERGE to perform the INSERT?

Posted: 06 Aug 2013 02:06 AM PDT

Hi,Please see the below sample data:[code="sql"]create table TargetTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)select 906900, 1935, 121067, '2010-04-01', NULL UNION ALLselect 946003, 5249, 121041, '2012-08-06', NULLcreate table SourceTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into SourceTable (Uarn, BACode, VO_Ref, From_Date, To_Date)select 906900, 1935, 112067, '2013-05-12', NULL UNION ALLselect 946003, 5249, 199041, '2013-01-01', NULLcreate table ResultTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into ResultTable (Uarn, BACode, VO_Ref, From_Date, To_Date)select 906900, 1935, 121067, '2010-04-01', '2013-05-11' UNION ALLselect 946003, 5249, 121041, '2012-08-06', '2012-12-31' UNION ALLselect 906900, 1935, 112067, '2013-05-12', NULL UNION ALLselect 946003, 5249, 199041, '2013-01-01', NULLselect * from TargetTableselect * from sourcetableselect * from ResultTable order by 1[/code]Is it possible to achieve this with the MERGE statement? So far my efforts have failed!Thanks in advance.

Is possible create numbered sequences

Posted: 06 Aug 2013 03:18 AM PDT

ROW_ID12233344445555566666677777778888888899999999910101010101010101010111212131313Up to 10000Is possible create numbered sequences like the ROW_ID above, where the row numbers repeat themselves up to 10000 into an empty column? In SQL serverThanks

Touchdowns & Missing Threads

Posted: 06 Aug 2013 08:44 AM PDT

It seems that a thread that requested help in determining the name of a quarterback, the max number of touchdowns thrown and year it happened was inadvertently deleted during a purge of lots of spam. I have part of the OP, DDL and data, and my reply. Steve Jones, SSC Editor, asked if I would repost so that the OP, and others, could see it, contribute, etc...[code]CREATE TABLE player_goals (Name varchar(50), Year int, Touchdowns int);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2007, 29);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2008, 25);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2009, 20);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2007, 19);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2007, 38);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2009, 44);[/code]My reply[quote]Try this and see if it works:[code];with cte as( select Year, Name, touchdowns, ROW_NUMBER() over(PARTITION by name order by touchdowns desc) RowNum from player_goals)select Year, Name, touchdowns from cte where RowNum = 1[/code][/quote]

Pivot on two columns

Posted: 06 Aug 2013 07:49 AM PDT

I have a dataset similar to the following:Account Date Amt1111 20090228 200.001111 20090328 175.001111 20090428 250.001111 20090528 210.002222 20120115 100.002222 20120213 150.003333 20110605 300.003333 20110705 300.003333 20110805 300.00I am trying to pivot on the date and the amount columns. The final output would look like this:Account Date1 Amt1 Date2 Amt2 Date3 Amt3 Date4 Amt41111 20090228 200.00 20090328 175.00 20090428 250.00 20090528 210.002222 20120115 100.00 20120213 150.003333 20110605 300.00 20110705 300.00 20110805 300.00 I've attempted doing this using a CTE to add row numbers and then pivoting on the row numbers, which works well when just pivoting one column, but I have not been able to do this to pivot both columns out.I think that it may be able to be done using two subsets or the original dataset and some sort of row numbering, but my mind is twisted in a knot thinking about it. Any help would be greatly appreciated.-Jeremy

Query Help - Embed a \ string in a string

Posted: 06 Aug 2013 06:50 AM PDT

I'm trying to modify an existing stored proc. This seems like of like a weird way to do it, but based on input parameters they are basically constructing a script string in the stored proc and then running it.[code]if @Interval = 'Monthly' Begin set @groupclause = ' Group by MONTH(ContractDate), YEAR(ContractDate) ' set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' + ' From Reporting.dbo.PrgSum ' set @orderclause = ' ORDER BY YEAR(ContractDate), MONTH(ContractDate)' End...declare @bigquery nvarchar(max)=@sql+@whereclause+@groupclause+@orderclause exec sp_executesql @bigquery[/code]I'm trying to figure out how to insert a "/" between the Month and Year values:[Code]set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' + ' From Reporting.dbo.PrgSum 'r[/CODE]

Execute a function from a query

Posted: 06 Aug 2013 06:09 AM PDT

Hi,I have this function that is used to fill a SQL table - which is then used for a data export.[code="sql"]ALTER FUNCTION [dbo].[itfAPVEND]( -- Add the parameters for the function here)RETURNS @APVEND TABLE ([CO] [char](5) NOT NULL,[A] [char](12) NOT NULL, [B] [char](60) NULL, [C] [char](30) NULL, [D] [char](30) NULL, [E] [char](30) NULL, [F] [char](30) NULL, [G] [char](20) NULL, [H] [char](20) NULL, [I] [char](20) NULL, [J] [char](20) NULL, [K] [text] NULL, [L] [char](15) NULL)ASBEGIN insert into @APVENDselect 'WSIAP', * fromopenquery(WSIAP, 'select VendorKey, Vendorname, ContactName, Vendoraddress1, Vendoraddress2, Vendoraddress3, VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND') insert into @APVENDselect 'ASSFD', * fromopenquery(ASSFD, 'select VendorKey, Vendorname, ContactName, Vendoraddress1, Vendoraddress2, Vendoraddress3, VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND') RETURN; END[/code]Problem is - I now need to execute this function every night. What is the best way to accomplish this? I was thinking of using a SQL job and call it from a query. BUt I can't seem to get the function to execute from a query. How would I execute this function from a query?
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog