Update SQL Help Needed - SQL 2005 TSQL
Apr 16, 2008
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC'. Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Description is being used after the first BEGIN. I know it needs to also go into my insert somehow, but I'm not sure how or if there is an easier way to do this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
View 1 Replies
ADVERTISEMENT
Apr 16, 2008
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC'. Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
View 2 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
Oct 31, 2001
RESTORE HEADERONLY.... return some result.
I need place part of this one into variable.
How I can do it?
Sorry for bad English.
Thanks before.
View 1 Replies
View Related
Oct 20, 2007
You are troubleshooting a T-SQL stored procedure with a developer using SQL Server 2005. There are several update transactions and calls to other stored procedures within the stored procedure, and you suspect one of them is failing. You don€™t know which one. You don€™t want to heavily modify the stored procedure with error detection. What can you do to easily trap the error?
A stored procedure is called from an outwardly facing web application. The stored procedure uses dynamic SQL to retrieve data from a table. Is this a €œsafe€? thing to do? Why or why not?
A developer writes a T-SQL view that joins data from two tables. The code is as follows€¦
SELECT
C.contactName,
C.contactTelNo,
F.fundManagerName,
F.fundManagerTelNo
FROM
Contacts C, FundManagers F
WHERE
C.fundManagerId *= F.fundManagerId
When the view is executed, the developer notices differing result sets on a daily basis. Why?
View 20 Replies
View Related
Jun 3, 2006
I have successfully installed SQL Server 2005 SP1 but when I go to the "Microsoft Update" site it is still shown as required. I have tried going back to an old image of the PC and installing SQL Server 2005 and SP1 again but still the "Microsoft Update" page is not picking up the fact that SP1 has installed successfully. Anyone had the same problem or know of a fix?
Steve
View 3 Replies
View Related
Mar 7, 2008
I need to update a column(X) in database A.tableA with a column(Y) from database B.tableB where the value in
another column(Z) in A.tableA = the value in a column(W) in B.tableB
Both databases A & B are on the same SQL server.
Any help with the syntax will be appreciated
Thanks
View 6 Replies
View Related
Aug 20, 2001
Hello -
I am looking for a way to allow users to create and edit their own stored procedures, saved under DBO (not under their own schema).
Any help would be greatly appreciated!
Paul
View 2 Replies
View Related
Aug 14, 2007
Hi all!
I have a quick question...
'UPLOAD / INSERT EXISTING CLIENT DATA INTO SQL SERVER FROM CLIENT
INSERT INTO ProductLocal
(tblID,SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date)
SELECT tblID, SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM Product
WHERE (Alias = 'me')
'DOWNLOAD / UPDATE-INSERT EXISTING LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE
What would be the best and least expensive way to UPDATE/INSERT the local client db from the SQL Server?
-- LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE
Any help would be appreciated.... thanks.
Kind regards,
billb
View 2 Replies
View Related
Mar 31, 2008
I have a table called emp
1eno enamedesgsal
1aaamanager20000
2bbbexecutive15000
3ccchr25000
4dddceo45000
using single update statement , how can i update the salary based on the desg .i have to give hike if its manager sal =sal+(sal*0.10) .like this
Thanks & Regards
Zakeer
View 2 Replies
View Related
Jan 13, 2006
I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement......
strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @IDParam"
Thanks!
Eugh
View 10 Replies
View Related
Jun 7, 2006
Hello, I'm new to the site, I've been trying to find help and i hope this is the right place.
In my MySQL database i have a table 'A' with a colum called 'Description'. Inside 'Description' there is some text and a link to a page. I have over 2500 Description fields and all have the same link inside but i need to change that link to a new one and i dont know how to update the description column with the new link. I know how to change just everything in it but its like this:
text text text text link
so how do i just change the link in the description column.
Anyone?
thanks in advance.
View 4 Replies
View Related
Nov 28, 2007
Below is my table structure:
CREATE TABLE [reports].[MDC_DRG](
[InsDrgMdc] [nvarchar](50) NULL,
[InsDrgMdcDesc] [nvarchar](50) NULL,
[MDC] [nvarchar](100) NULL,
[Drg] [nvarchar](50) NULL,
[DrgDesc] [nvarchar](255) NULL,
[AnyDx] [nvarchar](100) NULL,
[Cases] [decimal](18, 0) NULL,
[IntOOS] [int] NOT NULL,
[ChronicOther] [nvarchar](50) NULL
) ON [PRIMARY]
Here is a sample select from the AnyDx field:
AnyDx
,85225,85225,5304 ,5181 ,80501,E8190,3485,
,1983 ,1983 ,49121,1625 ,34550,79381,78079,30001,V1,
,2396 ,2396 ,78039,311,
,33390,33390,31401,49390,
,3410,3410,2768,3051,72293,V146,V4579,V1041,
,43491,43491,25000,4019 ,V1581,
,43491,43491,25000,4919 ,3569 ,4019 ,72252,78650,71,
,43491,43491,2768,30590,34290,4019,V146,7845,2724,2,
,43491,43491,34290,40290,25000,7843,7872,41401,4278,
,43491,43491,412 ,25000,41401,4019 ,2724 ,29680,V1,
I need to issue an update statement to the ChronicOther field. If the range below is in the AnyDx field, then I want the ChronicOther field to say "Chronic", if not then I want it to say "Other".
where AnyDx between '25000' and '25091'
or AnyDx between '41400' and '41406'
or AnyDx between '42800' and '42890'
or AnyDx between '40100' and '40291'
or AnyDx between '34200' and '34292'
or AnyDx between '14000' and '19910'
or AnyDx between '49600' and '49600'
or AnyDx between '27240' and '27240')
My problem is what is the best way to look for this range (below) in the AnyDx field without multiple like statements?
example:
Update reports.MDC_DRG
SET ChronicOther = CASE When AnyDx like ('%,25000,%') Then 'Chronic' Else 'Other'
etc., etc.....
View 12 Replies
View Related
Jan 8, 2007
hi,
I have my database stored in the sqlserver 2005. Using the table name i am retrieving the table and it is displayed to the user in the form of datagridview.I am allowing the user to modify the contents of the table, including the headers. Is it possible for me to update the table straightway rather than giving a sql update command for each and every row of the table .
Pls reply asap....
-Sweety
View 3 Replies
View Related
May 22, 2008
Hello,
I am trying to update a table based on values that I have imported into a temporary table. Basically I have a list of lab codes (EMR_temp_labtest_configupdate) and each lab has a zseg code tied to it. The definitions for zseg code are in a separate table called (EMR_zseg_code_descriptions)
I need to update the lab_test_add_conf to add in each lab code that does not have any configuration information (not exists in lab_test_add_conf) based on the zsegcode.
For example a zsegcode (defined in the emr_zseg_code_descriptions table) is ZBL and the lab code 003277 fits into the zseg category according to the temp table. For each lab code that first into the ZBL category a row needs to be inserted for
Example of table data:
emr_temp_labtest_config
labtestcode, zsegcode
003277, ZBL
emr_zseg_code_descriptions
zsegcode, valuecode
ZBL, PATRAC
ZBL, HERITG
I want to look at the data in the temp table and determine which category it is in and then insert into the lab_test_add_conf table a row for each lab test each zseg table value code that exists.
My Final Goal:
lab_test_add_conf:
lab test code, valuecode
003277, PATRAC
003277, HERITG
I know I need to do an update statement but I am not sure how to set up the SET statement or if there is anythign that I need to take into consideration. Here is my code so far.....any thoughts?
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists
(Select *
From lab_test_add_conf b
where a.labtestcode = b.labtest_key)
update table lab_test_add_conf
select a.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
View 3 Replies
View Related
Apr 25, 2007
Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
View 1 Replies
View Related
Sep 3, 2002
Hi guys,
I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:
1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time.
2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.
I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.
I appreciate any help you provide.
Dogli
View 1 Replies
View Related
Nov 12, 2006
Hi all,
I have a situation where my Visual C# application presents a number of fields. In order to update a student object, I wish to call a stored proc. 1 or more fields can be updated... And If one is left null, then I don't want to update it, but instead I want to keep the old value.
I am really wondering if I am approaching this the right way. The following stored proc does what I want it to do, however I'm thinking there may be a faster way...
Here it is:
-- Update a student, by ID.
DROP PROCEDURE p_UpdateStudent
CREATE PROCEDURE p_UpdateStudent
@ID INT,
@NewFName VARCHAR(25),
@NewOName VARCHAR(25),
@NewLName VARCHAR(25),
@NewDOB DATETIME,
@NewENumber VARCHAR(10),
@NewContactAID INT,
@NewContactBID INT
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE OLD VALUES
DECLARE @FName AS VARCHAR(25)
DECLARE @OName AS VARCHAR(25)
DECLARE @LName AS VARCHAR(25)
DECLARE @DOB AS DATETIME
DECLARE @ENumber AS VARCHAR(10)
DECLARE @ContactAID AS INT
DECLARE @ContactBID AS INT
-- Get all of the old values
SELECT @FName = FName FROM TBL_Student WHERE ID = 10000
SELECT @OName = OName FROM TBL_Student WHERE ID = 10000
SELECT @LName = LName FROM TBL_Student WHERE ID = 10000
SELECT @DOB = DOB FROM TBL_Student WHERE ID = 10000
SELECT @ENumber = ENumber FROM TBL_Student WHERE ID = 10000
SELECT @ContactAID = ContactAID FROM TBL_Student WHERE ID = 10000
SELECT @ContactBID = ContactBID FROM TBL_Student WHERE ID = 10000
-- USE ISNULL to set all of the new parameters to the provided values only if they are not null
-- Keep the old ones otherwise.
SET @NewFName = ISNULL(@NewFName, @FName)
SET @NewOName = ISNULL(@NewOName, @OName)
SET @NewLName = ISNULL(@NewLName, @LName)
SET @NewDOB = ISNULL(@NewDOB, @DOB)
SET @NewENumber = ISNULL(@NewENumber, @ENumber)
SET @NewContactAID = ISNULL(@NewContactAID, @ContactAID)
SET @NewContactBID = ISNULL(@NewContactBID, @ContactBID)
-- Do the update
UPDATE TBL_Student
SET FName = @NewFName,
OName = @NewOName,
LName = @NewLName,
DOB = @NewDOB,
ENumber = @NewENumber,
ContactAID = @NewContactAID,
ContactBID = @NewContactBID
WHERE
ID = @ID
END
GO
So yeah it works. But As you can see I wish to keep an old copy of the values to perform checks pre update....
Is there any faster way, or am I on the right track? I need a pro's advice :) (before i write all of my procs!!)
Thanks all.
Chris
View 7 Replies
View Related
Apr 10, 2008
I have written following code in my application
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program
Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition
Error: A request to send data to the computer running IIS has failed. For more information see HRESULT
Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider
Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace DeviceApplication1
{
public partial class Form1 : Form
{
string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1()
{
InitializeComponent();
}
private void DeleteDB()
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"DotNetCF";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"PubDotNetCF";
repl.Subscriber = @"SubDotNetCF";
repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(filename))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
// Display Same Data In Another DataGrid : dataGrid1
SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'");
SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn);
if (dsJobLists == null)
{
dsJobLists = new DataSet();
}
try
{
dsJobLists.Clear();
daJobLists.Fill(dsJobLists, "JobLists");
dataGrid1.DataSource = dsJobLists.Tables["JobLists"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
DeleteDB();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime())
{
// TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'.
this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists);
}
}
}
}
I have created a merge replication correctlly( I suppose, there were no errros)
Please help
Your help will be appriciated
View 1 Replies
View Related
Sep 12, 2001
Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb
View 2 Replies
View Related
Jan 28, 2006
Hi All,
I have been building a database in SQL Server Express for some months now using the Windows level authentication login that has given me full access to everything in the database.
The time has now come where I need to create user accounts and grant permissions to specific stored procedures and I'm having trouble doing this.
Can anyone give me a brief rundown of the required T-SQL commands I need to set up a user account that can do nothing but run stored procs (not the system procs which apparently are being discontinued: http://msdn2.microsoft.com/en-us/library/ms182795.aspx).
So far I have:-
CREATE LOGIN db_test_user WITH PASSWORD = 'eXaMpL3Pwd
USE db_new
CREATE USER db_test_user
The above code executes successfully but when I try and connect (using Management Studio) I get error message 'The user is not associated with a trusted SQL Server connection (error 18452)' which means little to me.
I also tried creating a 'WITHOUT LOGIN' user for the database but could not figure out how to give it a password.
I don't know (/understand) roles/schemas and don't know if I really need them as I only need user access to specific stored procs. I don't ever want them to see the tables for example so they only need the most restricted access.
Can anyone help?
Thanks,
DG
View 2 Replies
View Related
Jul 7, 2006
We have just updated our sql 2005 server with sql SP 1. Should we apply this service pack to the client boxes that access this server or is that unnecessary?
TIA,
barkingdog
View 1 Replies
View Related
Jan 16, 2008
Hi. I was wondering if there is such thing in MSSQL 2005? Not the one in SSIS.
Any feedback will be greatly appreciated.
Thanks,
Rick..
View 11 Replies
View Related
May 2, 2007
System.Data.SqlClient.SqlException was unhandled
Class=20
ErrorCode=-2146232060
LineNumber=0
Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Number=2
Server=""
Source=".Net SqlClient Data Provider"
State=0
Hi guys, I have just set up a database in SQL 2005 Express Edition. However, I have a problem accessing it with the application program created in VB 2005 EE. Before using SQL 2005, the application worked perfectly in SQL 2000.
Here is my connection string used in SQL 2000:
Dim sqlConn As New SqlConnection("Data Source=(local); Database='Inventory List'; Integrated Security=yes")
Is this valid in SQL 2005 Express Edition? Or should I change the connection string instead? Thank you very much.
View 2 Replies
View Related
Aug 15, 2007
I have two tables
t_DTM_DATA_STAGING around 2 million records
t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR]
,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME]
,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM])
SELECT
t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
View 4 Replies
View Related
Jan 18, 2008
Hello all.
I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.
I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table
Any help on this would be greatly appreciated.
View 3 Replies
View Related
Oct 16, 2007
Hi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
View 1 Replies
View Related
Jan 20, 2008
I am getting the following error in SQL2005. I need some assistance with adding in a Subquery.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'LastVisitDate'.
Msg 207, Level 16, State 1, Line 34
Invalid column name 'LastVisitDate'.
/* Patient List*/
SET NOCOUNT ON
DECLARE @Zip varchar(40)
SELECT @Zip = LTRIM(RTRIM('NULL')) + '%';
WITH cteMedlitsPatientStatus AS
(
SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus'
)
SELECT
PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0),
PatientName=CASE
WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
END,
PatientAddr1=pp.Address1, PatientAddr2=pp.Address2,
PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip,
PatientRespName=CASE
WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
END,
PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City,
PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'),
Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate,
Status = ml1.Description,
pp.BirthDate,
(select top 1 visit
from patientvisit pv
where
LastVisitDate >= ISNULL(NULL,'1/1/1900') and
LastVisitDate < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
pp.patientprofileid = pv.PatientProfileID
and datediff(day, getDate(), visit) < 0
order by visit desc) as LastVisitDate
FROM PatientProfile pp
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
WHERE ...... etc, etc, etc
View 7 Replies
View Related
Mar 8, 2006
As posted previously, thank you in advance...
--------------------------------------------
We have a reverse proxy for rs 2000 -> a client requests a report, it (the proxy) then goes out to the rs box, gets the report, encrypts any return urls and feeds the modified html to the requesting client. I understand this isn't necessary anymore with rs 2005 due to the architecture. Question is, when I use the ReportExecutionService.Render method it is still returning the parameters for the report, and not the ReportSession, ControlID, Controller, etc. parameters which hides the actual return values on the href links of the report.
Documentation is plentiful for rs 2005, but examples are not. Can someone please explain to me if using the new features in rs 2005 to hide the parameter values from the users is possible via web request? Making the parameter values completely and entirely (even via sniffer) is absolutely a must (which is why we are currently encrypting return URL's).
My code:
ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
ParameterValue[] pvValues = null;
DataSourceCredentials[] credCredentials = null;
ReportParameter[] rptparamRequiredParams = null;
ParameterValue[] pvReportParameters = null;
ExecutionHeader execHeader = null;
ExecutionInfo execInfo = null;
rptparamRequiredParams = null;
string sHistoryID = null;
{fill parameters block}
rs.SetExecutionParameters (pvReportParameters, "en-us");
try
{
string extension;
string encoding;
string mimeType;
Warning[] warnings = null;
string[] streamIDs = null;
byte[] result = rs.Render (sFormat, sDeviceSettings, out extension, out mimeType, out encoding, out warnings, out streamIDs);
string d = System.Text.Encoding.ASCII.GetString(result, 0, result.Length);
HttpContext.Current.Response.Write (d);
}
catch
{
// do stuff
}
My code is returning:
http://henneseyjm1/ReportServer$sql2005?%2fJH.RSReporting%2fBAG&cy_start_date=1%2f1%2f2006&cy_end_date=3%2f1%2f2006&region=RG20&entity_num=nothing&proc_ctr=nothing&office_num=nothing&render_format=htm&view_name=standard&group_id=0&server_name=http%3a%2f%2flocalhost%2fJHnet%2f&user_is_office=False&rs%3aParameterLanguage=&rc%3aParameters=Collapsed&rc%3aToolbar=False
Where I would like it to return:
http://localhost/Reports$sql2005/Reserved.ReportViewerWebControl.axd?ReportSession=iyvsxg45vhzwd2acii3jj4q4&ControlID=de367546-919a-4f67-be4d-cd2747166dca&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl161&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top
View 1 Replies
View Related
Apr 12, 2007
Hey all
I am in the process of implementing transactional replication.
Site 1 : Sql server with 3 Databases
lets just call them DB1 to 3
Site2 : Sql server with 1database
DB1 replicated from site 1
Site 3: Sql server with 1 database
DB2 replicated from site 1
Site 4: Sqlserver with 1 database
DB 3 replicated from site 1
site 5: Sql server with 1 database
DB1 replicated from Site2
I need to know how to go about this
Site 1 will be Publisher/Distributor
Site 2 will be subscriber from site1, but site 2 needs to replicate the same DB down to site5, can site 2 be subscriber and distributer
Site 3 and 4 will subscribe to site 1
The various sites must also be able to update the DB back up to Site 1
The things i need info on is the security settings
The servers are stand alone servers and dont have active directory
Need info on how to configure the Various service accounts, must they be windows accounts.
Is there any content i can read up on to help me on this
Hope all this info helps
Any help would be Great
Thanx a lot
View 7 Replies
View Related
Apr 5, 2006
How can I get Northwind into SQL Server 2005 Development?
In working through Visual Studio 2005 Pro Walkthroughs the next one needs Northwind. Is it straight forward to add in?
View 2 Replies
View Related
Jan 29, 2008
In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following:
(select top 1 visit from patientvisit pv
where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like:
Case
When dateadd(y,[LastVisitDate],getdate())<2 then 'Less Than 2'
When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more'
else 'No detected visit'
END
So basically, either your in 2 yrs or your not.
My Current Query:
/* Patient List*/
SET NOCOUNT ON
DECLARE @Zip varchar(40)
SELECT @Zip = LTRIM(RTRIM('NULL')) + '%';
WITH cteMedlitsPatientStatus AS
(
SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus'
)
SELECT
PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0),
PatientName=CASE
WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
END,
PatientAddr1=pp.Address1, PatientAddr2=pp.Address2,
PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip,
PatientRespName=CASE
WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
END,
PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City,
PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'),
Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate,
Status = ml1.Description,
pp.BirthDate,
(select top 1 visit
from patientvisit pv
where visit >= ISNULL(NULL,'1/1/1900') and
visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
pp.patientprofileid = pv.PatientProfileID
and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
FROM PatientProfile pp
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
WHERE etc ......
View 1 Replies
View Related
Oct 20, 2006
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
View 1 Replies
View Related