SQL Server 2008 :: Notify Operator Only Works Some Of The Time?

May 25, 2010

I've been working with Database Mail for some time but I haven't seen this one before. I have a maintenance plan that does the following:

1. Check database integrity
On Success:
2. Perform backups
On Success:
3. Perform a maintenance cleanup
On Success:
4. Notify operator of success

Steps 1 - 3 are also linked (via On Failure arrows) to a singular Notify Operator of Failure task.

The maintenance plan does exactly what I want it to, and if everything goes correctly, it successfully sends an email to an operator.

If steps 1 - 3 fail, the job ends in an error state, but does not trigger the Notify Operator of Failure task.

I was able to recreate the problem by creating another maintenance plan on the same instance with the same steps. Its "Notify Operator" on failure task also doesn't work.

It occurs to me that maybe I'm missing something, so here's some of the details of my SQL server:

SQL 2008 + SP1 (10.0.2531) x64 on Windows 2008 R2
DB Mail profile is public and default

I do have a slightly unusual profile, in that it uses two accounts:

1. A connection to a SharePoint SMTP service (where it catches emails directed at document libraries)

2. A connection to a UNIX-based smtp server (which routes mail to regular mailboxes and my SQL DBA mailing list)

The Profile will attempt to send to the SharePoint server first. The SharePoint server does not relay. If the document library email address doesn't exist, SQL will raise a warning (in the Database Mail log) and the profile will use the second account on the list, which is a real mail server and can relay the message to any mailbox.

It works really well, actually. When the maintenance plan completes successfully, the message is sent to the drop folder on the SharePoint server, and SharePoint routes the email to the correct library, and we have a central archive of all DB Mail notifications.

But if the job fails (for example, if the backup disk is out of space), none of this happens. According to the log, the job doesn't even try to send a notification. Looking at the DBMail log, the Mail service does not start. No email is delivered to the drop folder of the receiving SMTP server. So I don't think my Database Mail configuration is the problem here. It is apparently something to do with the way the job itself handles errors.

View 9 Replies


ADVERTISEMENT

Notify Operator

Dec 2, 2006

Hello,

I have a problem with the notification. I have enabled Database mail and i have created an Operator.

Under SQL Server AgentJobs and then in the job properties i have enabled notification but i don`t receive a mail. Test e-mail works fine but "real" e-mail not.

I have also tried with the Database Maintenance Wizard but even then it just not work.

I have noticed that others have experienced the same problem but i have seen no solution yet.

Could you please help me? What can cause this problem? What`s the best i can do to start searching for the solution? Thanks!

View 6 Replies View Related

Notify Operator Task Question

Nov 19, 2007



Hi,

can the Notify Operator Task send the output error message of its predecessor task(e.g Execute T-SQL Statement Task) to the operator? If yes, how?

Thanks,

Greg

View 11 Replies View Related

SQL Server 2008 :: Notify Only If There Are Certain Blocks By Certain User

Jun 12, 2015

I'm trying to set the query to send email ONLY when it returns records of blocks and i cant seem to get this going.

declare @blocks varchar(max)
set @blocks = (SELECT spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,

[Code] ....

View 1 Replies View Related

DB Mail And Notify Operator Through DB Mail Error Could Not Retrieve Item From The Queue

Feb 2, 2007

I've set up DB mail and sent a test e-mail and that comes through fine.

I set up an Operator with email Name: DWhelpton@k-and-s.com;MWeaver@k-and-s.com

I created a job and set up the notifications to e-mail the operator on failure.

When the job runs and fails, I do not get an e-mail and I get the following exception in the db mail log:

Date 2/2/2007 8:35:00 AM
Log Database Mail (Database Mail Log)

Log ID 402
Process ID 3936
Last Modified 2/2/2007 8:35:00 AM
Last Modified By NT AUTHORITYSYSTEM

Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)


What step am I missing?

View 1 Replies View Related

SQL Server 2008 :: How To Use Like Operator To Get Multiple Columns

Sep 12, 2015

how to use like operator select statement to retrieve multiple column names in sql server DB...for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'

View 2 Replies View Related

SQL Server 2008 :: Varchar Invalid For Sum Operator

Oct 29, 2015

Below doesn't work for varchar column?

select sum(cast(Enter_your_field_name as int)) from Table_name

View 5 Replies View Related

SQL Server 2008 :: Custom Alert Is Not Emailing Operator?

Apr 8, 2015

I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server.However, for now, I need to restart the SQL server service because users begin complaining they can't connect.

1) I created an operator - me.

2) I created a job which runs a query.

INSERT INTO Sessions_alert
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC

3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.

4) I've set the alert to email and delay is between responses 2 minutes.

It history tables says number of occurences is 18964. However, I don't receive an email.

Shouldn't the alert send an email? Do I need to include email code in the job?

View 6 Replies View Related

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works

Jun 4, 2007

Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430' 
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha

View 2 Replies View Related

SQL Server 2008 :: Replication Works From A Command Prompt But Not From SSMS?

Sep 30, 2015

SQL Server 2012 Replication. The command prompt instance of the replication is being run as the same user as the SSMS "version" using the runas option. With SSMS the error is that the client is missing a privilege. There is no error when it runs from the command prompt. All runs well.

We have reinstalled the replication feature.We have gone through several MSDN articles that indicate exactly what privileges (both within the DB and in Windows) that service account should have. None have worked Incidentally, when I create a new publication and indicate that the SQL Server Agent be used , the publication runs.

View 0 Replies View Related

SQL Server 2008 :: SSIS Package Fails Silently On Server But Works If Run Manually

Jul 7, 2015

I have two calls to stored procedures that in an SSIS package fails silently. They are simply not executed in production but works fine in test, nothing happens and the sql server agent reports that everything has gone just fine.

In test they have 1 server with db A and B. No issue here.

In prod they have 2 servers with db A and B. On server 1 sql server agent executes a job that includes an SSIS package that on server 2 runs a couple of sp's. That user is db owner on server 2 db B and yet nothing happens. The sp's are not executed.

If I in prod run the job manually then it works, but not when run with the sql server agent account that as said is even db owner.

View 2 Replies View Related

Access To SQL Server Via WCF Works Only Part Time

May 16, 2007

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.



In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.



When we access Guider we have no problems getting data.

When we access Talker we have a login failure:



Cannot open database 'Talker' requested by the login. The login failed.

Login failed for user 'AcornCommunicationServices'.



The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.



Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!



Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

View 1 Replies View Related

SQL Server 2008 :: Displaying Transaction Time Punch Data In A Time Card Form?

Oct 7, 2015

I have a table called employee_punch_record that we use to store employee time clock punches.

The columns are:

employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident

Here are some examples of a record:

bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0

We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.

I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?

View 0 Replies View Related

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP

Jul 26, 2004

I've got a popular problem so i get a message that server acces denied! ..

But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...

On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by

RETTO - name of my server

server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;

I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!


PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!

I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??

View 3 Replies View Related

SQL/CLR DML Error: Invalid Use Of Side-effecting Or Time-dependent Operator

Apr 18, 2008

I am attempting to use a CLR Function to perform an update operation, since a SQL UDF will not allow this within the function. For the POC, I hard-coded the db connection string and setup the assembly to use EXTERNAL_ACCESS. This worked fine and the update operation was running properly via the CLR Function. However, I now want to make the code run in the current db context without plugging in a connection string. So, I've applied "context connection=true" instead of the explicit db string. When I do this, I am receiving the following error:


A .NET Framework error occurred during execution of user defined routine or aggregate
'MyTestCLRUDF':

System.Data.SqlClient.SqlException: Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

System.Data.SqlClient.SqlException:

etc.


I don't understand why simply swapping the connection string would cause this issue. I assume this is somehow related to permissions, and I am missing something. I've tried using each of the three permission levels (external, safe, and unsafe), and no luck.

Any help is appreciated! Here's the code:


public static bool MyTestCLRUDF(Guid myID)

{

string connectionString = "context connection=true";

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand command = new SqlCommand("MySprocName", connection);

command.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = new SqlParameter("@ID", myID);

command.Parameters.Add(parameter);

connection.Open();

command.ExecuteNonQuery();

connection.Close();

}

return true;

}

View 6 Replies View Related

SQL Server 2008 :: How To Convert Char To Time

May 6, 2011

How do I convert (n)(var)char to time? I tried several ways including a simple:

SELECT CONVERT(time,'143825',108)But it always gives me this error:

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

View 9 Replies View Related

SQL Server 2008 :: How To Calculate Date Time

Apr 9, 2015

My data is looks like this,

Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1

I want data looks like this

Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8

View 7 Replies View Related

SQL Server 2008 :: Time Differences Between Different Intervals?

Apr 30, 2015

how can I get the time differences between them.Let's say , a person who click on break @ 12:00 PM and he is back and select I am back option @ 12:15 the total break time is 15 minutes. However, can I display this difference of break time.

View 4 Replies View Related

SQL Server 2008 :: Getting UTC Date And Fixed Time

May 27, 2015

I need to change one of the column to run utctime 2am. Whenever I declared and set the time, it has to run and set up to the UTC 2 am time.

How to declare the variable like that?

If I run that now, it has to set up next utc 2 am time, If I run after 2 hrs also or tomorrow whenever, it has to set the next utc 2am time

View 1 Replies View Related

SQL Server 2008 :: How To Add Time From One Field To Date From Another

Nov 2, 2015

If I have a datetime field, RequestDate, and a time field, DecisionTime, how do I add just the date portion of RequestDate to DecisionTime?

View 6 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

SQL Server 2008 :: Cannot Handle Multiple Threads Same Time

Jul 24, 2012

we are queirying an stored procedure multiple times same time,from our application. In this case, few processes executing successfully and few getting failed with error "50000 error executing the stored procedure" and if we run thesame process again its getting executed sucessfully.Does the MySQL cannot handle multiple threads same time?

View 9 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

SQL Server 2008 :: Restore Database To A Point Of Time

Sep 1, 2015

Can I use a full and differential backup to restore to a point of time?

Or I have to use full and transaction log backups in order to do a point of time restore?

I found today when I tried to restore a db from another database at the point of time for example 3:10 pm,
SSMS automatically select the full backup + the transaction backup that is done at 3:00 pm, but not select full + the differential backup I did at 3:12pm.

So I lost those records entered after 3:00pm.

I supposed it should use the differential backup and restore to 3:10. but it didn't.

View 8 Replies View Related

Report Subscription Only Works 80% Of The Time.

Aug 23, 2007

Hi,

I have setup report subcription with stored credential using a valid domain account and "Use as Window Credentials..." checked. Everything tested fine and works properly when the report is viewed in a web application. The problem is this report renders fine in the browser 80% of the time and occasionally it gives me Logon failure error:

An error has occurred during report processing (rsProcessingAborted)
Cannot impersonate user for data source 'Pinnacle'. (rsErrorImpersonatingUser)
Logon failed. (rsLoginFailed)
Logon failed: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)


Since the report is working 80% of the time and the user domain account hasn't changed. I am confused why I am getting bad user account error. Please help!

Thanks.

-SouBee

View 1 Replies View Related

SQL Server 2008 :: Restore To Point In Time DURING Differential Backup

Feb 25, 2015

We have a 1TB Database. Our backup strategy looks like this.

Weekly Full backups - Saturday 10pm. (Takes anywhere between 5 - 9 hours)
twice Nightly Diffs (8:30p and 2:30am - Mid week this takes roughly 1.5 hours)
Hourly Log backups starting at 4:00am until 11pm.

I have an issue where I need to restore to 3:00 today. If my (2:30a)Diff is still running at that recovery point.

I am getting an error when trying to restore Full/2:30a Diff/4a Log with a stopat 3:00a.

Error telling me my log backup is incorrectly formed.

Can I, in fact, restore to this point at all? Do I need to go back to my previous Diff (8:30p) and restore the logs, if so, which ones?

My nighttime Diff ran from 8:30 - 10p.

I have logs at 9p, 10p, 11p, 4a

View 8 Replies View Related

SQL Server 2008 :: Replication Articles Read-only AND Updateable At Same Time

Apr 21, 2015

We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.

We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.

Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.

We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.

It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.

I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)

So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?

View 9 Replies View Related

SQL Server 2008 :: Get Time Difference Of Char Datatype Column Value

May 29, 2015

How can I get time difference of the following record :

STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM

I have tried it by below query,

SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER

but it gives following error message

[color=red]Operand data type time is invalid for subtract operator.[/color]

View 6 Replies View Related

SQL Server 2008 :: How To Split Time Column Values Into Rows

Jun 6, 2015

I have the table as

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I want the output as : -

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I am trying the below mentioned code but it is not giving me the desired output..

with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn

[Code] .....

I am getting wrong output as -

| start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00

View 1 Replies View Related

SQL Server 2008 :: How To Find Time Difference Between Two Rows Of Record

Nov 6, 2015

I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .

LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR

View 6 Replies View Related

ODBC Connect Times Out, But 2nd Time Works

Oct 19, 2000

When I begin Enterprise Manager and open a connection to a Server, it fails with a timeout error. But if I do a refresh and then connect again, it immediately works.
I have had similar symptoms setting up a data source for ODBC. When you initially logon to SQL Server to define the connection it times out. But then if you immediately try again it succeeds.
Furthermore one of my application programs fails to work (due to the timeout) on systems that have this behavior.
I have had these symptoms occur on 2 diferent workstations, but other workstations access the SQL Server with no problem.
All systems are WinNT 4.0; the engine is SQL Server 7 with SP2. The timeout is defined as 15 seconds so should be long enough.

Does anyone know what is going on, or how to fix it?

View 1 Replies View Related

Error Invalid Use Of Side-effecting Or Time-dependent Operator In 'EXECUTE STRING' Within A Function

May 8, 2008



Hi all, mister
I want create a function but I get this error: Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function

I think in a function, cannot use temp tables, or calling exec or store procedures.

Which is the best solution for my issue ? develop store procedure ??

thanks.

CREATE FUNCTION fnObtenerTablaMaestra ()

RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )

AS

BEGIN

DECLARE @cmd nvarchar(max)

DECLARE @sql nvarchar(max)

DECLARE @nexoUNION NVARCHAR(max)

DECLARE @params nvarchar(max)

DECLARE @NombreTabla VARCHAR(MAX)

DECLARE @Descripcion VARCHAR(MAX)

DECLARE @CIF VARCHAR(MAX)

-- Cannot access temporary tables from within a function.

-- IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')

-- DROP TABLE #tmpTable

-- CREATE TABLE #tmpTable ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )



SET @nexoUNION = NULL



DECLARE c1 CURSOR for

SELECT [CD_NOMBRE_TABLA], [DS_CAMPO_DESCRIPCION], [DS_CAMPO_CIF] FROM [TABLA_MAESTRA]



OPEN c1

FETCH c1 INTO @NombreTabla, @Descripcion, @CIF

--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF

WHILE @@FETCH_STATUS >= 0

--WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @sql =

'INSERT INTO #tmpTable

N'''+ @NombreTabla + '''

N'''+ @Descripcion + '''

N'''+ @CIF + ''''



SELECT @sql =

'SELECT ' + @Descripcion + ', '+ @CIF + ' FROM ' + @NombreTabla

IF @nexoUNION IS NULL

BEGIN

SET @nexoUNION = 'UNION'

END

ELSE

SET @sql = @nexoUNION + ' ' + @sql



-- EXECUTE (@sql)

--Exec(@sql)

FETCH c1 INTO @NombreTabla, @Descripcion, @CIF

--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF



END

CLOSE c1

DEALLOCATE c1

--SET @sql = 'SELECT Descripcion, CIF FROM #tmpTable'

-- Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function

EXECUTE(@sql)

RETURN

END

GO

View 1 Replies View Related

SQL Server 2008 :: Calculated Time Difference Column In Create Table

Jul 14, 2015

How to include a time difference column using 2 other date columns during creation of a table ?

The requirement is to create a table and include the following columns:

1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved