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

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. 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

Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]

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

Help With Tsql Needed!!!

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

3 TSQL Questions.....Help Needed...

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

SQL Server 2005 SP1 Installs Successfully But Microsoft Update Still Showing It As Needed?

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

TSQL To Update From One SQL Database To Another

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

TSQL Developers - Save And Update SP's As DBO

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

Quick Update/Insert TSQL?

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

Help Needed For Update

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

Help Needed With SQL UPDATE Statement

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

Update Query Help Needed! Anyone?

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

Update Statement Help Needed

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

Help Needed ...to Update The Datatable

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

Update Table Assistance Needed

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

OPENQUERY UPDATE Syntax Help Needed

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

Help Needed To Implent Automatically Update Table And More....

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

The Fastest Way To Perform An Update ... Advice Needed :)

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

Help Needed In Merge Replication, SQL Server 2005 Mobile Edition And VC# 2005

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

Help Needed On How To Update Individual Fields When Importing Data Into Tables

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

SQL Server 2005 Security - TSQL

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

Sql 2005 SP1 Needed For Client Side Sql 2005 Install?

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

Is There A Build In FTP In MSSQL 2005 That Can Be Invoked With Tsql?

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

Help Needed!! Error Connecting To SQL 2005 EE Via VB 2005 EE

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

SQLAgent - TSQL Jobs Hangs (SQL Server 2005)

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

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?

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

Execution Time Gap Between Simple Tsql And Stored Procedure In SQl Server 2005

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

Subquery Help Needed. SQL 2005

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

Proxy 2005 - A Little Help Needed...

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&amp;cy_start_date=1%2f1%2f2006&amp;cy_end_date=3%2f1%2f2006&amp;region=RG20&amp;entity_num=nothing&amp;proc_ctr=nothing&amp;office_num=nothing&amp;render_format=htm&amp;view_name=standard&amp;group_id=0&amp;server_name=http%3a%2f%2flocalhost%2fJHnet%2f&amp;user_is_office=False&amp;rs%3aParameterLanguage=&amp;rc%3aParameters=Collapsed&amp;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

SQL 2005 Replication Help Needed

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

2005 Walkthrough Database Needed.

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

CASE Statement Help Needed - SQL 2005

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

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

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







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