Replication Autogenerated Procs - Interesting ;2 Notation

Jun 23, 2006

I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure "sp_MSins_dboMEETING" @c1 int,@c2 int,@c3 int,@c4 varchar(250),@c5 datetime,@c6 datetime,@c7 bit,@c8 char(1),@c9 datetime,@c10 bit,@c11 bit,@c12 bit,@c13 datetime,@c14 smallint,@c15 datetime,@c16 smallint,@c17 binary(8),@c18 bit,@c19 bit,@c20 bit,@c21 varchar(1000)

AS
BEGIN


insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)

values (
@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @c20, @c21
)


END

GO

create procedure "sp_MSins_dboMEETING";2 @c1 int,@c2 int,@c3 int,@c4 varchar(250),@c5 datetime,@c6 datetime,@c7 bit,@c8 char(1),@c9 datetime,@c10 bit,@c11 bit,@c12 bit,@c13 datetime,@c14 smallint,@c15 datetime,@c16 smallint,@c17 binary(8),@c18 bit,@c19 bit,@c20 bit,@c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @c2,"MEETING_STATUS_ID" = @c3,"TITLE" = @c4,"START_DATE" = @c5,"END_DATE" = @c6,"PUBLISH_IND" = @c7,"GROUP_IND" = @c8,"PUBLISH_DATE" = @c9,"MY_ADVISORS" = @c10,"SUBMITTED_IND" = @c11,"ACTIVE_IND" = @c12,"CREATE_DATE" = @c13,"CREATED_BY" = @c14,"LAST_UPDATE_DATE" = @c15,"LAST_UPDATED_BY" = @c16,"DATE_INDEXED" = @c17,"ON_DEMAND_IND" = @c18,"NOT_REPORTED_IND" = @c19,"MAJOR_PROJECT_IND" = @c20,"MAJOR_PROJECT_COMMENT" = @c21
where "MEETING_ID" = @c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12 ,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21 )
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?

View 1 Replies


ADVERTISEMENT

Handling Autogenerated PK/FK Conflicts In Merge Replication

Aug 14, 2007

We use autogenerated primary keys in most of our tables. Some of these keys are also foreign keys in other tables. Right now there is only 1 database sever at a central location. But now there is a need to have multiple database servers at different locations. Data from these remote sites needs to be replicated to the central server. Some data would also distribute from central server to selected remote sites.

If I could resdesign, I would have choosen something like GUIDs for the primary keys or combination of something like ServerName and AutoGenerated number as a combined key. But that's not possible right now. How do I handle merge replication conflicts in this case?

I am looking for some pointers as to how to handle this case. If it were just simple table with 1 primary key, that would be easy as I can throw the primary key on remote server and let the central server create a new key when data is inserted. But in my case, a single table can be related to 5 or more other tables through these autogenerated keys. Any help is much appreciated.

View 2 Replies View Related

Interesting Transactional Replication Issue

Jun 12, 2006

Hello,

We have moved from SQL 2000 to SQL 2005 for our main server, and our reporting server, which uses transactional replication.

Now, in SQL 2000 when I originally setup replication, it replicated all of the table indexes.

I have recreated the publications in SQL 2005, but they are no longer there. Do you have any idea what would cause some of our table indexes to be missing?

What can be done to ensure this doesn't happen?

Thank you.

View 4 Replies View Related

Custom Procs For Replication

Mar 20, 2006

SQL Server 2000 Enterprise

Hello,

I have a test publication of two tables. Both tables are supposed to use a custom update stored procedure
(you know, "Replace UPDATE commands with this stored procedure call:")
When updating Table1 the system calls my custom stored stored procedure.

Here is a small excerpt captured by the Profiler when updating Table1:

exec usp_FNLC_MSupd_Table1 'Jun 24 2005 12:00:00:000AM', NULL, 100081142, 0x01

Looks good. I see that my custom update proc was used (usp_FNLC_MSupd_Table1).


Now, when updating Table2 (Table2 is pretty identical to Table1) Profiler sends this back and the update on the subscriber fails:

exec sp_executesql N'delete from "Table2" where "Dates3ID" = @P1 insert into "Table2" values (@P2, @P3) ', N'@P1 int,@P2 int,@P3 datetime', 8, 8, 'Mar 24 2006 12:00:00:000AM'

Then..

exec sp_executesql N'insert into "Table1" values (@P1, @P2) ', N'@P1 int,@P2 datetime', 8, 'Mar 24 2006 12:00:00:000AM'
Go

This is what the distribution agent throws back:

Insert Error: Column name or number of supplied values does not match table definition.
(Source: DFBPBSO (Data source); Error number: 213)

So, why isn't the article Table2 using my custom update proc? All I do is updates, no deletes or inserts. No matter what I do for this article, it will not use the custom update proc.

I need help, please. Thank you in advance,
L

View 3 Replies View Related

Replication Failing While Applying Snapshot As The Stored Procs Are Out Of Sync

Aug 28, 2007

Replication is failing while applying the snapshot as the stored procs are out of sync ( the objects referenced in the stored procedures are no more exists in the database) . Is there any easy way to identify the out of sync procedures so that I can exclude these stored procedures from the articles list. I am having around 1000 procs and is not possible to test them by executing.

Thanks in advance.

View 1 Replies View Related

Autogenerated Id Help

Sep 13, 2005

can anybody tell me how to have an autogenerated id in sql server? pls?

View 2 Replies View Related

Linq To Sql Autogenerated Value

Mar 20, 2008

Hello,
How can I get autogenerated value of inserted item with linq to sql?
Thanks

View 2 Replies View Related

Handling Autogenerated ID

Jun 5, 2007

Hi,
I am using SQL Server 2000 and the clients would like to archive and remove some of the old records from certain tables in the database. I would like to know the methods of setting ID as autogenerated and if the ID will be initialized to 0 again if records are removed, and if autogenerated IDs reuse missing IDs (e.g. I have ID 1-10, remove ID 2, will the next new record be ID 2?)

We need the ID to keep incrementing and not reuse missing IDs, nor reinitialized once we've archive the old records, and I was thinking of storing all the IDs in a table. Just asking for the possibility of SQL Server autogenerated ID does this.

View 5 Replies View Related

Math Notation In Db?

Dec 1, 2007

I'm not a professional dba or dbd, but I'm proficient in the basics ofdatabase design and sql. I want to create a database of mathdefinitions, and I'm wondering how one would go about creating adatabase that contains mathematical notation (and I'm not just talkingabout basic symbols where I could get away with ascii code). I needto be able to insert a wide variety of mathematical expressions, fromfractions to integrals, into fields (just like you can enter in-linemath symbols in MS Word using equation editor). I have no clue how togo about this. Is it a matter of developing certain programmingskills/languages? Would such a capabliltiy be proprietary (dbms-specific)? Is it possible at all? Any help would be appreciated.Thank you.

View 3 Replies View Related

Convert From Scientific Notation

Jan 22, 2008

I've got some values stored in nvachar(255) field stored by mistake as scientific notation (eg 7.5013e+006 instead of 7501301) and I need to convert and update the field with normal entry, not scientific notation. Is there a way to do that?

View 6 Replies View Related

How Truncate Scientific Notation From Real?

Jul 23, 2005

HiWe've got some numbers stored as Reals which are returning values inscientific notation that we need rounded down to 3 digits to the rightof the decimal.ie 8.7499999E-2 needs to return 8.75.Round, cast, convert, formatnumber in the dts package all fail.Help!Thanks Moe

View 2 Replies View Related

Reporting Services Engineering Notation

Jun 1, 2007

In Reporting Services, is it possible to display a field in Engineering Notation or Scientific notation? I guess can do it by adding a format code, can somebody tell me the format for those notations.

Thanks

View 1 Replies View Related

Stupid Text Box/notation Question

Mar 11, 2008



I'm in Visual Studios working on a data flow. I want to add some notes. I open a text box (by typing on the screen).

Why can I only get ONE line of text???? The return key doesn't work.

View 5 Replies View Related

Float Column Not Show Scientific Notation

Oct 16, 2013

We sometimes have small values stored in a column with datatype of float like 0.000644470739403048 which is being converted to -5.8E-05. Perhaps that is OK to be stored in the database however I need the value in decimal format to use. (I'm using longitude values in google maps).

is there anything I can do at the database level. I was looking at the properties which is 53 numeric precision and 8 length.

View 8 Replies View Related

Formatting A Float In Varchar But NOT In Scientific Notation

Jul 20, 2005

I'm trying to find a way to format a FLOAT variable into a varchar inSQL Server 2000 but using CAST/CONVERT I can only get scientificnotation i.e. 1e+006 instead of 1000000 which isn't really what Iwanted.Preferably the varchar would display the number to 2 decimal placesbut I'd settle for integers only as this conversion isn't businesscritical and is a nice to have for background information.Casting to MONEY or NUMERIC before converting to a varchar works finefor most cases but of course runs the risk of arithmetic overflow ifthe FLOAT value is too precise for MONEY/NUMERIC to handle. If anyoneknows of an easy way to test whether overflow will occur and thereforeto know not to convert it then that would be an option.I appreciate SQL Server isn't great at formatting and it would be fareasier in the client code but code this is being performed as adescription of a very simple calculation in a trigger, all stored tothe database on the server side so there's no opportunity for clientintervention.Example code:declare @testFloat floatselect @testFloat = 1000000.12select convert(varchar(100),@testFloat) -- gives 1e+006select cast(@testFloat as varchar(100)) -- gives 1e+006select convert(varchar(100),cast(@testFloat as money)) -- gives1000000.12select @testFloat = 12345678905345633453453624453453524.123select convert(varchar(100),cast(@testFloat as money)) -- givesarithmetic overflow errorselect convert(varchar(100),cast(@testFloat as numeric)) -- givesarithmetic overflow errorAny suggestions welcome...CheersDave

View 3 Replies View Related

Float Data Types And Scientific Notation

Jan 10, 2008



Hi All,

Am I missing something or is there something odd with float data types. i know that float is not the most precise definition but i came across something really odd today.

first let me define the scenario.

this is sql server 2005, standard edition build 3042.

I have a table defined as


CREATE TABLE [dbo].[ASSET](

[Property_Num] [numeric](10, 0) NOT NULL,

[Accrual_Factor_Val] [float] NULL

)



the accrual_factor_val was updated to a value of 0.00005 then the web service failed because the proc returned 5E-05!

i opened the table, and discovered this is the stored value. is this correct?



thoughts and ideas?

thanks for your input!

View 5 Replies View Related

Format Output As Scientific Notation (was Sql 2000 Question)

Jan 29, 2005

SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval

how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.

View 1 Replies View Related

Scientific Notation While Taking Data From EXCEL Into SSIS

Apr 25, 2008

Hi ,

I have one problem regarding format of mixed data columns in excel. I am using this query to retieve data from my excel spreadsheet

Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;IMEX=1;IHDR=yes;Database=E: est.xls;HDR=YES',

'SELECT * FROM [original$]')

to retrieve data from excel sheet.


I am able to retreive all data (numaric and text) , but for few cells I am getting wrong values.

like this
15106749310-> 1.51067e+010
70400672657-> 7.04007e+010
48110753143-> 4.81108e+010




registry setting are €œtext and 0€³. I am also using IMEX=1 in connection string.

I have numeric and text data both in same column,
eg:
70400503549
70400672657
KF1080420908
KF1260370908
KF1260880908
KF1260960908

Could you please help me to get the correct value from excel .

thanks in advance ,

View 7 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Very Interesting !

Jun 12, 2004

Below is the statement given in MicroSoft SQL Server migration documentation :

"When a view is defined with an outer join and is queried with a qualification on a column from the inner table of the outer join, the results from SQL Server and Oracle can differ. In most cases, Oracle views are easily translated into SQL Server views"

Please can anyone explain the above with some examples. I don't find such a 'CREATE VIEW' statement existsing in two DBs, which results in different result set. Am I wrong ?

Thanks,
Sam

View 2 Replies View Related

Interesting

Aug 26, 2004

Another tech was having the same problem with his sp which he also made into a macro. He was having the same problem as I was, he thought since he made the user the dbo owner that he woud have no problems...but since hes the one who created the db and the stored procedures when the user tried to access it he got that same error message. It went away after he went into the Enterprise manager and in the properties of the stored procedure under permission he gave the user EXEC permissions...And WAAALLLAAA problem solved :):):):)

View 10 Replies View Related

Interesting SQL...

Oct 22, 2004

Anyone else experience this? A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second. SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co, dbo.v_contract_co_statusWHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id ) AND contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )SELECT dbo.contract_co.producer_num_id, contract_co_statusFROM dbo.contract_co INNER JOIN dbo.v_contract_co_status ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_idWHERE contract_co_status = 'Pending' OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

View 3 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

Interesting ID Issues...

Feb 1, 2007

Hi all,

I have a system in place currently that both a  local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID'sFor example;Table "Sales"ID - IntRemoteID - IntVarious Data fieldsThe 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.So i have been considering two possibilities to address this problem,1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.2. Having a controlling ID table which provides the next IDBoth options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem?  Thanks in advance! 

View 2 Replies View Related

An Interesting Problem

Jan 3, 2001

We've had an interesting server issue here since about the start of the year, where the dual processor Netfinity server we've been running SQL 7 on has been holding steady at almost exactly 50% CPU utilization, give or take about 5%-10%. Disk utilization in all areas is relatively low. In fact, the server will do this right after a fresh reboot, with no users accessing. The really interesting part is that a graph of the two processors mirror each other. When the utilization of one goes up, the other goes down, almost perfectly. The two processors are the same speed, but their firmware build is different. IBM says it's not a problem. What say ye?

View 1 Replies View Related

Interesting Question!

Nov 28, 2001

If Query1 gives 1 row output as:

customer_id
1234
-------------------
Query2 gives 1 row output as:

product_id
8970
-------------------
How do we get the above results as a single result set of 2 columns side by side? as follows:

customer_id product_id
1234 8970


Is that possible in a single select statement??
Query1 and Query2 both are select statements on the same table with different where clauses.

Sheila.

View 2 Replies View Related

Interesting Problem

Jul 14, 1999

Hi ! I am facing an interesting problem for one of my databases. When I double click on a particular table in Enterprise Manager, the Manage Tables window shows me only one or two fields from the table. (it does not show me all the fields). When I run sp_help <tablename> in isql, all the information is displayed. Why is there such inconsistency. It happens only on one particular database. I have run DBCC commands but still no help. Does anyone have any ideas on this. Thankx,

View 3 Replies View Related

Interesting Challenge

Jul 12, 2005

We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.

This Db has pretty much 0 normalization present.

IOW, your worst nightmare.

Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.

View 9 Replies View Related

Folks ! Very Interesting !

Jun 8, 2004

Hi Folks,

This is a create table statement in DB2 z/ODS and OS/390 ( mainframe versions ) :

create table test(sno int) IN DATABASE database1 AUDIT NONE DATA CAPTURE NONE

This will create a table in the named database 'database1'.

1) In SQL Server, is there any equivalent for this 'IN DATABASE' syntax in DB2 ?

2) 'Data capture none' will not store any extra info about the data replication in the log files. Is there any eqiuivalent for this in SQL Server ?


Thanks,
Sam

View 1 Replies View Related

Interesting Error

Jan 10, 2008

In this SQL query I get en error like this:Incorrect syntax near ','.SELECT SUM(mCount * mPrice), CONVERT(DATEPART(yyyy, mDate), varchar) + '.' + CONVERT(DATEPART(mm, mDate), varchar) AS DateSoldFROM salesWHERE mDate BETWEEN '9/10/2007 0:0:0' AND '1/10/2008 23:59:59'I think you it isnt necessary to show values in the table because it is a syntax error but I dont undertand how it can be incorrect...Thanks for help...Saren Taşçıyan

View 14 Replies View Related

Interesting Problem

Jul 23, 2005

Imagine a table in Microsoft Sql Server 2000 named Pictures with threefields. A primary key ID(int), a Name(nvarchar) and a Picture(image)field.Lets put some records into the table. The first two fields as expectedwould take an int and a string. The third field is of type image.Instead of putting a bitmap in this field lets place an xml documentthat has been streamed into a byte array. The xml document woulddescribe the Picture using say lines. If the picture was that of asquare we would have four lines in the xml document. You could think ofthe xml document as something similar to vector graphics but thedetails are not relevant. The important fact is that the contents ofthe image field is NOT a bitmap but a binary stream of an xml document.Now imagine we have a reporting tool like Crystal Reports that can beused to report on this database table. Imagine we create a report byusing the three fields mentioned above. As far as Crystal is concernedthe first field is an int, the second a string and the third an image.If our table had ten records and we preview the report we like to see10 entries each consisting of an ID, Name and a Picture.This can only happen if the image field contains a Bitmap, but asmentioned above the field contains an xml document.Now my question...Can we write something in SQL Server 2000 (not 2005) to sit between thetable and Crystal Reports so to convert the XML document to a bitmap.The restriction is that we cannot use anything but sql server itself.The client in the above case has been Crystal Reports but it could beanything.I know SQL Server 2005 supports C# with access to the .NET frameworkwithin the database. Unfortunately, I am not using SQL server 2005.Some people have suggested the use of User Defined Functions and TSQL.I like to know from the more experienced SQL Server people if what I amtrying to achieve is possible. Maybe it has not been done but is itpossible?Any suggestions would be greatly appreciated...Many Regards

View 5 Replies View Related

Really Interesting Issue!

Sep 20, 2007



Hi All,

When I wanted to see ONLY today€™s records on the sql server, it took 5 minutes.
When I wanted to see records including today€™s records on the sql server, no problem. (19 Sep. is a past date which is working properly)
It is very interesting it takes 5 minutes to sort the lastday there is nothing wrong with the other days.
I ran DBCC INDEXDEFRAG method but no change anything.

I am wondering about something, everyday at 22:00 pm we are getting backup. Is this process creating a difference between before getting the back up and after got the backups?
It is really interesting issue. I do not know what I can say. It seems there is an issue with today€™s records. May be there are some locks on the today€™s records. I do not know. Why does not cause any issue for old dates but for current day ?



SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration
FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration



FROM CallInfo AS MAINTBL

JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration


FROM CallInfo

GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID
) as CallInfo

WHERE CallIDChar = CallIDChar


AND (CallStartTimeStamp >= '2007-09-20T00:00:00')

AND (CallStartTimeStamp <= '2007-09-20T23:59:59')

What can be reason for this issue and how can I solve it ?

Thank you in advance,

Melih

View 15 Replies View Related

An Interesting Bug For SSIS

Apr 12, 2007

I have a very simple SSIS package that imports data from ODBC source. There are two "Time" columns in source, but When this table is created in SQL2005, it give me "bigint"!!!



How can this happen?



I tried to import data to SQL2000, those two columns are created as "smalldatetime" properply.



Thanks.

View 11 Replies View Related







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