SQL Server 2008 :: Transmission Table Structure In Database Is Inconsistent

Jun 1, 2015

I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0

I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “

So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?

View 9 Replies


ADVERTISEMENT

SQL Server 2008 :: Compare Data Between Identical Table Structure?

Jun 18, 2015

I have two identical table structure with at least 60 or more columns!!

Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....

I need to compare Data between two table and provide result like this!

RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0

So condition like. If table A Column Data does not Equal Table B Column data then

If datatype is Date then display Difference of Days.

if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)

if Datatype is Varchar then display No Match word.

View 0 Replies View Related

How Can I Get The 'entire' Database, Structure And Data, From SQL Server 2008 To SQL Server 2005?

Jun 20, 2008

1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.

View 2 Replies View Related

SQL Server 2008 :: Replicating Table A Into Table B Within The Same Database

Jun 9, 2015

I am trying to replicated table A into table B withing the same server and the same database.

I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.

Is there any workaround for this?

I am using SQL Server 2008 R2.

View 2 Replies View Related

Database Automatically Creates Xxx_Temp Table While Modifying / Updating Table Structure .

Dec 16, 2007

Hello friends,

I am new to the SQL Server 2005 development.

From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.

Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,

"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".

where xxx is the table name.

I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.

Thanks in advance. Any help will be appreciated.

View 5 Replies View Related

SQL Server 2012 :: Adding RowID To Existing Table - Inconsistent Results

May 6, 2015

I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.

We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:

AAAA.1234 /* (account number)*/
1/1/2015 $150 First Transaction
1/3/2015 $24.233 Second Transaction
BBBB.5678
1/1/2015 $350 Third Transaction
1/3/2015 $24.233 Fourth Transaction

My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.

Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.

I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:

AAAA.1234 1/1/2015 $150 First Purchase
AAAA.1234 1/3/2015 $24.233 Second Purchase
BBBB.5678 1/1/2015 $350 Third Purchase
BBBB.5678 1/3/2015 $24.233 Fourth Purchase

My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:

SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericTable
WHERE RowHeader LIKE '____.____%'

Results look like this:

But every time I run the routine, I get different numbers!

Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.

TRUNCATE TABLE GenericImportTable;
ALTER TABLE GenericImportTable DROP COLUMN RowID;
BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6)
ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericImportTable
WHERE RowHeader LIKE '____.____%'

View 3 Replies View Related

SQL Server Admin 2014 :: Inconsistent Data In Database And Master Files?

Apr 21, 2015

USE <database>

select * from sys.database_files

and

select * from sys.master_files where database_id= <db id>

give me different size of memory optimized file in <database>

Microsoft SQL Server 2014 - 12.0.2456.0 (X64)

View 1 Replies View Related

Database Table Structure

Jan 29, 2008

Hi i have a student table and also an societies table,  and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society.
My tables so far look like this;
STUDENTS; student_id, student_name
SOCIETIES;  society_id, society_name
 
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.

View 7 Replies View Related

SQL Server 2008 :: Table Migration From One Database To Another

Jul 7, 2015

I'm trying to migrate tables from one database to another. The tables in the source database (MY_SOURCE_DB) have a schema of "dbo". I want to create the tables in the target database (MY_TARGET_DB) with a schema of "xyz". I want to migrate all of the tables with "contract" in the table name:

select table_name from information_schema.tables
where table_name like '%contract%'

I want to migrate the full table definitions, table data and dependent objects. This needs to be done through a sql script as opposed to SSIS.

View 2 Replies View Related

SQL Server 2008 :: Inner Join Database Table With XML Data

Jan 25, 2011

I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method

Here is an example of what i am doing

DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'

SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)

the above query produces the following result

EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh

How do I join the data coming out from the above query with another table in the database, for example Employee in this case

i.e. somewhat like this (note the following query does not perform the join correctly)

SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID

My desired output after the join would be

EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin

View 8 Replies View Related

SQL Server 2008 :: Queries Running Against To A Table In Database

Jun 15, 2015

How can we get most frequent queries that are running against to a table in our database?

View 3 Replies View Related

SRS2000 - Same Database Structure, Different Table Owner,qualifier

Dec 14, 2006

Hi,

I am creating reports for an application, that when installed can have various different table owners/qualifiers depending on how client created the DB. How can I create standard reports across all the DB without hardcoding the tablenames qualifier/owner in the dataset query? Again the table structure remain the same just the qualifiers may be different. Any help would be great.

--DCB

View 1 Replies View Related

Something Wrong With Table Structure Change On Mirrored Database

Mar 6, 2008

I have set up mirroring in high availability mode (principal, mirror, and witness).

After setting up mirroring, I needed to adjust the size of a column in one of my tables. I did so with the following command

ALTER TABLE OrderOptimizer.dbo.rCodes ALTER COLUMN CodeDescription VARCHAR(8000).

The column was initially VARCHAR(128)

When I look at the table structure it shows that the column is now VARCHAR(8000), however I can only enter 128 characters into the field.

I forced a failover to look at the table on the mirrored version. It too showed that the column was VARCHAR(8000).

If I open the table I can enter more than 128 characters into the field, however when I refresh, it is truncated to 128 characters,

Any help would be appreciated

View 4 Replies View Related

SQL Server 2008 :: Recover Data In A Table - Restoring A Database Alongside Existing DB

Apr 17, 2015

I need to recover some data in a table but i'm not 100% sure the right way to do this safely.

I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?

If I restore, I assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?

The SQL server is 2008 R2 running as a VM.

View 9 Replies View Related

SQL Server 2008 :: How To Delete Tables In Database Whose Table Names Match A Criteria

Jul 22, 2015

The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".

What is the correct way to delete a group of tables whose name match a pattern from within SSMS?

View 9 Replies View Related

How Do You Describe A Table's Structure In SQL Server?

Apr 16, 2004

Oracle has a similar function, DESC, to diplay the table structure. Can't figure it out in SQL Server.

ddave

View 2 Replies View Related

Table Structure Of Linked Server

Aug 2, 2005

Hello!Does anybody know how to get tables structure of linked server (DBF tablesvia ODBC connection). I know that table structure of "normal" (not linked)server can get from systables and syscolumns tables, but now I need astructure of linked server tables.Thanks!

View 1 Replies View Related

Inconsistent Database Performance

Jul 23, 2005

Hi,I am facing a peculiar problem while looking ahead in a live Databasecurrently under operation in one of my clients Project. AnApplication that is updating 3 - tables in the Database is missing toupdate a certain number of Fields in one of the Tables. The fact isnot frequent and I have checked through the Server Performance Monitorthat there is no performance slag of the Server during any point oftime.The Tables are indexed with common Key fields. Can anybody help me inthis regard ?Thanks & Regards.--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict193836.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=655931

View 2 Replies View Related

Copy SQL Express Table Structure To SQL Server

Dec 16, 2005

How does one generate Transact-SQL for table structure in SQL server express 2005?
I have very limited access to an SQL server database (using rudimentary web based interface) for my hosted website.  I am ready to deploy an application that was developed with Visual Studio 2005 using SQL server express as the database.  I have four tables that I want to copy (structure only) from "express" to the hosted SQL server.  I think I need to generate Transact-SQL statements to "create" the table structure, then run these statements as a stored procedure on the web based SQL server.  I'm having trouble trying to figure out how to generate the code from SQL express 2005.  Anyone had any experience with this?
thanks.

View 1 Replies View Related

Import Directory Structure Into SQL Server Database

Jan 23, 2008

Hi,

As stated in the subject, I need to import a complete directory structure, from a root folder that I specify into a SQL Server database. To add further complexity, I also need to see what files exist in each directory.

At the end of the day what I am trying to achieve is to populate a table that will essentially have two columns, the filename and the directory that the file resides in. I am running SQL Server 2005.


Has anybody ever done anything like this before using T-SQL?

Thanks in advance.

View 5 Replies View Related

Does Anyone Know How To Modify Table Structure In Sql Server Management Console(2005)? Thanks

Jun 16, 2005

does anyone know how to modify table structure in sql server management console(2005)? thanks

View 1 Replies View Related

SQL Server 2008 :: Insert From Table 1 To Table 2 Only If Record Doesn't Exist In Table 2?

Jul 24, 2015

I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query

INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)

[Code] ....

View 4 Replies View Related

Any Structure On Sql Server Like WITH ... SELECT Structure On DB2

Jul 20, 2005

Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh

View 3 Replies View Related

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

Apr 10, 2015

I created am inventory table with few columns say, Servername, version, patching details, etc

I want a tracking of the table.

Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.

View 1 Replies View Related

SQL Server 2008 :: Inserting Data From Staging Table To Main Table

Feb 23, 2015

I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.

View 2 Replies View Related

SQL Server 2008 :: Create A Table Valued Function That Fetch Through The Table?

Apr 24, 2015

I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique

EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value

View 9 Replies View Related

SQL Server 2008 :: Non Blank / Non Null Value From Table To Update Same Table

Mar 13, 2015

Here is the sample data for table.

create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())

insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())

[Code] ...

I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.

It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.

So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.

Before the update :

empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

After the update:

empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

View 4 Replies View Related

SQL Server 2008 :: Alter Table Triggers Recreate Table?

May 26, 2015

which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?

View 2 Replies View Related

SQL Server 2008 :: Update A Table With Date When There Are New Records In Another Table

Sep 16, 2015

I have a scenario where I have to Update a table with date when there are new records in another table

For example:

I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.

Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?

View 8 Replies View Related

SQL Server 2008 :: How To Update The Database Physical File Location In Master Database

Mar 8, 2015

I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).

ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO

View 1 Replies View Related

SQL Server 2008 :: Script To List All Database Level Permissions In A Database?

Aug 4, 2015

I found this script online..

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

but the results are this: 2 columns - User and Permission

User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT

IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?

View 7 Replies View Related

Inconsistent Metadata From Linked 6.5 Server

Aug 16, 1999

I have a problem retrieving data from a linked SQL Server 6.5 server in version 7. I have set-up the server as a linked server and most operations occur with no problem. With some tables, though, I get the following error when I attempt to query any column of data:

Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

This occurs consistently for a subset of tables. I cannot see any pattern to the configuration of these tables that would make them any different to those where a query succeeds.

Any ideas?

Regards

Philippe

View 1 Replies View Related

SQL Server 2008 :: How To Get Column From Another Table And Insert Into Other Table

May 18, 2015

I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.

I am using below insert statment to insert data in RPT_Invoice_Shipped table.

insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code

while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).

But I am getting difficulty to arrange this in above query. how to achieve this?

View 1 Replies View Related







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