MS SQL Server :: Delaying Compilation Real Time

How To Convert Time From Int To Real-time
Hi all.

I have posted this question in another forum but no one has so far been able to provide a solution to the problem. Since I know Database Journal always has very informative and enlightening posts, I figured I'd post the question here in hopes that some guru can provide an answer.

If you're running SQL 2000 and have any jobs that have been executed, you could perform a query as such:

select last_run_time from msdb.dbo.sysjobsteps

and receive returned values that contain the last time a job was executed "stored in integer datatype" columns. See ->
sp_help sysjobsteps.

In SQL 2005 I believe the concept is the same. I think the intent of Microsoft had it mind for doing this was to store the date separate from the time values which won't work using the datetime datatype and I have read this in documentation in the past.

The challenge is to convert that data into a humanly legible 12 or 24 hour time format like 11:00 AM or 02:45:39.

Does anyone have any suggestions or clues to assist in resolving this problem??? :(

Thanks.

View Replies !

Real Time Data
I currently have an application which retrieves stats from a SQL database that are updated throughout the day. My application updates every 3 seconds to retrieve these stats but I found that it's quite expensive to the server's CPU when there are several clients running my application. Are there any other methods for extracting the data that won't require so much CPU. The fact that the database is being hit so much isn't good either. I have already written a webservice hoping that this would improve things but I'm not sure it has.

View Replies !

Real Time Synchronisation
Hello,

Can anyone guide me. which is the best method for real time synchronisation of my production server. Is it Transactional Replication or Stand By Server?

Thanks In Advance

Sejal

View Replies !

SQL Real Time Replication
We are using SQL 7 at 2 different locations, on 2 different Domains that are connected via a T1 line.

We need to have our 2 SQL servers be identical at all times, meaning when a change is made on one server, it is immediately replicated to the other. The problem is that both servers are always being used, so there is no primary/backup server scenario.

Does anyone know if there is a third party software available, or if this is even possible to do, given the fact that the databases are always open ?

Thanks.

View Replies !

Real Time Progress Bar
The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

View Replies !

Real Time Replication With Ms Sql
hello,

I want to create real time replication of my databaser server.I have two database srevers,one is master which having real time data and another slave,where i want to replicate data from master .

plz help me to configure solution

Thanks,

Chetan S. Raut.

View Replies !

SQL Server For Real Time
Hi,

We currently have a propriety in memory DB that is used to store the latest transactions in the system and we have a service that copies the data to a SQL Server every couple of seconds - For historical reporting purpose.

We would like to move into a more standard DB as our real time DB, since we have scalability and availability issues. We taught about using SQL Server since this is the DB we know, but I'm not sure it's built to handle real time data.

Does someone has any experience in using SQL Server for "Real Time" applications?

Does someone has any experience in storing the data files on RAM?

Does MS has a solution similar to Oracle's TimesTen, which is their real time DB?

Thanks,

Avi G..

View Replies !

Real-time Databinding
Does anybody know a good way to get a (near) live view of a sqlce database that doesn't involve ResultSets? I tried to use a resultset with the Sensitive option set, but it doesn't like the joins that I have to do in order to have the data make sense to the user.

Thanks

View Replies !

Is There Anyone Wih Real Time Exp In Sql2005
hi guyz ...i m new to sql server so is there anyone who could guide me abt sql server 2005 ....i need some info abt ssis ,ssas ...i have got a proj n have sme ques regd tat so plz let me know ur no or mail id so that i could contact u ..plz its urgent

raghu

View Replies !

Synchronize DB In Real Time
i have 2 DB in 2 different servers.
db1 and db2

db1 gets 150k records every hour. db2 is like the backup of db1. db2 is used to retrieve records to create reports using reporting services.
what is the best way to synchronize db2 with db1?
is it to create a dts package and set up a job that runs every hour?

off topic question.
what kind of language does reporting services use to create the reports?

View Replies !

Real Time Replication
I have two database, one is for production and another for reporting database, in this Scenario we need to transfer the real time data to reporting database, which replication we need to use and what at the bottelnecks in this..can any one give some suggesstions or give some links...

Thanks in advance

View Replies !

Table Data To A TXT In Real-time
Hi
I´m a SysAdmin and I´m implementing ASSP (anti-spam) to use with an email server.
But the ASSP requires a txt file with all domains hosted in the server that are in a SQL Table of the server.
My questions is: Is there anything to make a real time TXT file with all the domains ?
maybe use trigger I dont know..

View Replies !

How To Protect A Frequently Real Time DB
Hi Everybody:

We have a table which needs to be updated 2 million times per day. It hosts all real time transaction. There are 200K records in this table. Would you please to share your experience with me about how to protect/save such table in SQL 2000 from any possible damage?

We plan to use point-in-time backup (every 5 minutes). It still takes at half an hour to recover the whole database. Any new technology from Microsoft or SQL 2000 you can recommend?

Thank you very much.

Joan

View Replies !

Real Time Anomaly Detection

Hi,
I'm would like know if the analysis service data mining enables to detect anomalies from "normal" behavior/patterns of data (1), and alert about such anomalies when detected (2). both above sql server relational DB (3).

Thanks,
Eyal

View Replies !

Real-time Data Minig
We have completed the implementation of data mining algorithms.Now we have to build an API for real time data mining.Is that possible in SQL Server?

View Replies !

Real-time Feedback From Scripts
I'm sure just about everyone uses the PRINT command to give feedback as to what their lengthy and involved scripts are doing, as sort of a record.

I cannot figure out how to make the stuff I use in PRINT commands come out in real-time like SELECTs seem to. Does anyone have an answer to this? These are long-running scripts, and I'd rather nip a problem in the bud before the entire script completes if there's a problem I can capture.

___________________________
Geek At Large

View Replies !

Setting Up A Real-Time Data Warehouse
Hi,I am currently working on building a real-time data mart. Am using DTSas the ETL tool and MS SQL 2000 as the database. The currentinfrastructure includes 2 4 Proc (1GHz) Xeon based servers, with 4 GBof RAM. One server is used for ETL while the other is used as theprimary database.Currently we are looking forward to a SAN implementation to improvethe speed of the database. The current size of the database is 80 GBs,we are estimating it to grow near to 0.5 TB in the next 5 years. Formy Real time project I am planning to use MSMQ to stack and send theprimary keys to the DTS package which will then extract, transform andload the data onto the warehouse.I don't have much insight into the hardware requirements for buildingand sustaining a real-time warehouse. If someone can give me theindustrial standards for real-time warehouses in both hardware, ETLtools and the backend databases.I have heard that Terradata and Informatica are good together forlarge databases, also Oracle is one of the options but don't know howmuch difference can it make when compared to MS SQL 2000 or the highlyanticipated 'YUKON'.Regards,Aashish

View Replies !

Logging Real Time DB Connections To A Table
When someone connects to a database, I'd like to log the time
connected to a custom table.

Originally, I had planned on creating a trigger on sysprocesses
to insert into this table. Unfortunately, the BOL state
that you cannot create a trigger on a system table.

Does anyone know of way to accomplish the logging?

Currently, I have a job that runs every hour and checks
sysprocesses to see if the db is active.

View Replies !

Integration Of Real Time Data In Datawarehouse
Hello,

I am looking for some ideas about how to implement real time data in an OLAP user interface. We have a sales cube that displayes the sales history over time (product by time). But we would like to add 1 column at the end with the current qty of those items or categories. we do not want to be calculated from the cube (receiving- sales). We have a field in our database that gives us that data.

Any idea how to integrate something like that?

Raphael

View Replies !

Real Time Quantity Update Using Triggers

View Replies !

Real Time Quantity Update Using Triggers
Hi
There are 2 databases db1 and db2 on SQL server 7 with tables 1 and 2 respectively.
Both these tables have a field called Quantity. IF quantity gets updated in table 1 then that change should be immediately reflected in table2.
I though an update on table1 would serve the purpose but this trigger doesn't seem to be working however there are no errors during the syntax check.
The code for the trigger that i have used on table 1 is as below
CREATE TRIGGER [Qty_UPDATE] ON [Table1]
FOR UPDATE
AS
IF UPDATE (Qty)
BEGIN
UPDATE db2.dbo.table2
SET db2.dbo.table2.qty = qty
END
Please help
Thanks in advance

View Replies !

Real-time Data Imports. How Stable Is It?
Hi there,

I was wondering about the stability of SSIS when it comes to importing data on a real-time basis. Let's say you have a scenario where flat files, for instance, will be dropped at random intervals ranging from 1 second to 10 seconds apart and the importer has to import these files immediately.

I would imagine that this is done with a package which runs a loop sniffing the directory forever but I stand corrected on the best ways of doing it.

I'm not too sure whether SSIS is a good idea for this as lots of people have had bad comments on SSIS in real-time in my company but they cannot elaborate on why enough to convince me. I have done some pretty cool stuff and must admit that I am a fan of the technology but dont want to defend it into a corner

Please give me your thoughts on this

Regards
Michael

View Replies !

Synchronize And Compare Two Database In Real Time
how to synchronize and compare two database in sql server 2000 without using any others software in real time.

my purpose is to check the two database in two different server if there has any different. And synchronize these two database continusly.

if one database had change, the other will also change

View Replies !

Real-Time Data Mining Discussion
I am about to prepare a paper concerning the field of real-time data mining. Real-time here means the process of incremental training of an existing model as soon as the data arrives.

There is a number of papers introducing algorithms for incremental association analysis, incremental clustering etc. Stream mining Ãs a field which is closely related to that. The main reason for the implementation of incremental algorithms is a) the large amount of data to be mined and b) the high rate of new data that is evolving every day.

Using classical batch mining algorithms, models that are outdated for some reason, would have to be re-trained, which could be very time consuming for billions of records. And once the training is completed, the training would have to be restarted once again because a bulk of new data has been arrived.

The question that I would like to discuss now is: For what real world applications would it be a meaningful or even essential to use real-time training of models?

Two main reasons could determine the answer to that question:

You just want to incorporate new data into existing models in order to increase the prediction accuracy of your model or
Your underlying data is subject to more or less massive changes (also refered to as concept drift) and you want to adapt your mining model continuously to that reality.

I'm looking for some examples or ideas where one of these cases apply and it would be a good idea to have incremental mining algorithms involved.

I'm looking forward to inspiring some discussion on that issue.

View Replies !

Viewing Commands Being Executed On Sql Server In Real Time
I saw a presentation last week where the speaker created some sorta sql server "watch window" (in Sql Server Management Studio I think) where he could watch all the commands being executed on his sql server database in real-time. For example he could navigate to web pages (that hit the database) and as he pressed buttons you could see the sql commands execute in this "watch window." If other users hit the database at the same time you could see those sql queries execute as well. I didn't think at the time to ask how he did it - does anyone know how to set this up? I have a problem with my sql server right now and it would be useful to see which sql queries (etc) are being executed when. Thanks in advance,J. Shane Kunklejkunkle@vt.edu

View Replies !

What Is The Best Way To Implement An On-line Near Real-time Archiving Method?
Hi to you all and specially to who will solve my problem!

That is what I want:
I need a database that will serve as an archive db in read-only for day-to-day queries
and a production db that will be kept small by deleting un-needed records

A replication setup would be fine but I didn't find a way to avoid the deletes to keep the production database small, to replicate in the archive database where transaction are replicated.

Is there someting i am missing?

Andre Vigneau

View Replies !

Rolap Cubes - Cannot Enable Real Time Updates
I am trying to create a ROLAP cube with 'Enable real-time updates' .

When I go to process the cube it fails stating, Creating indexes for Aggregation XXXX

CANNOT enable real time updates on this partition without indexed views: <date>

Im running SQL Sever 2000 Developer Edition - SP4 on Windows Server 2003

My cube don't use DCount measures ,only simple calculation
(sum, count)

Can anyone help me debug this? Is there any way to get a more descriptive
error from Analysis Services?

Thank you

View Replies !

Blocking With RECEIVE And SqlDataReader For Real-time Updates
I have this scenario which is working fine, but would like to know if others have tried it or can recommend a better approach. Below is a brief description, but code should fully explain:

A process updates a SQL Server table via a stored proc, which in turn writes information to a service broker target queue. In my client/server architecture, I would like clients to see this new information as soon as it gets written to the target queue. To do this, I have a WCF service that calls a stored procedure; the stored proc contains an infinite loop which has a RECEIVE statement with an infinite timeout. Once RECEIVE reads data from the SB queue, data is retrieved via a SqlDataReader and results are sent to clients via a pub/sub. I then wait for more rows until RECEIVE unblocks and so on.

Stroed Proc

Code Snippet

-- ...
WHILE 1 = 1
BEGIN
-- Param declarations

...

BEGIN TRANSACTION;
  WAITFOR
(
   -- Blocks while TargetQueue is empty
   RECEIVE
   TOP(1)
     @conversation_handle = conversation_handle,
     @message_type_name = message_type_name,
     @conversation_group_id = conversation_group_id,
     @message_body =
    CASE
      WHEN validation = 'X' THEN CAST(message_body AS XML)
      ELSE CAST(N'' AS XML)
    END
   FROM [dbo].[TargetQueue] -- No time out!
)

-- Handle errors
...

-- Return received information. After this statement is executed,
-- reader.NextResult unblocks and reader.Read() can read these
-- new values
SELECT 'Conversation Group Id' = @conversation_group_id,
        'Conversation Handle' = @conversation_handle,
       'Message Type Name' = @message_type_name,
        'Message Body' = @message_body ;

COMMIT TRANSACTION

END -- WHILE

C# Code

Code Snippet

// Create a SqlCommand, and initialize to execute above stored proc
// (set command time to zero!)
...

// ExecuteReader blocks until RECEIVE reads data from the target queue
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Begin an infinite loop
while (true)
{
    // Process data retrieved by RECEIVE
    while (reader.Read())
    {
        Trace.WriteLine("Conversation Group Id :" + reader.GetGuid(0) +
           "Conversation Handle: " + reader.GetGuid(1) +
            "Message Type Name : " + reader.GetString(2) +
            "Message Body : " + reader.GetString(3));

        // Send data via pub/sub
        ...
    }

  // Blocks until stored procedure returns another select statement
   // i.e., blobks until RECEIVE unblocks and retrieves more data from queue
   reader.NextResult();
} // while
}

View Replies !

Best Way To Retrieve Oracle Data Into SS2000 Near Real-time?
I have no idea where to post this kind of question, so here it is!

I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )

I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.

Any suggestions?

thanks!

View Replies !

Delaying The Execution Of A Component
Hi All,

Is there a way wherein if I have three components in my package, A,B,C and I want the DFT C to be executed only after A and B is executed ?

I have now connected A and B to C and made the two arrows from A to B to blue, but i guess that is not good enough. Will setting the DelayValidation property to true help here?

Because that the DFT C is totally dependent on A and B I should somehow ensure that there is a constraint set.

Can someone guide me here ?

Thanks in advance

Mithun

View Replies !

SSMS Replacement So I Can Read A SSCE3.5 Databse In Real Time
Has anyone come accorss such a beast or will I need to write one!

View Replies !

SSAS Data Mining: Using The Time Series Model In A Real World Scenario.

I probably posted my question in the wrong forum so please check here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3009605&SiteID=17

thanks

View Replies !

Real Time &"live&" Data On A Windows Application .NET 2.0
I want to build a windows application in Visual Studio 2005 that grabs some data from a SQL DB and displays it in a gridview. That is easy and I already have it done, but I also want to know how to show this data in real-time. For example: right now we have a application that pulls some login information for all the users and displays their phone extension, their name, and their status. They can change their status by clicking on some radios below the datagrid and on change it updates the data and then posts back. However, I dont know how the guy who did it made it so that these local changes are automatically shown on everyone elses computers around the office. In other words, they are shown the live data as it changes, without having to refresh it or anything. How would I go about doing this or does anyone have any places/resources that could help me accomplish this task. Thanks.
Oh and the guy who wrote this is no longer working here and he deleted the source code so i cant look at it.

View Replies !

Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:

DBConn= New OledbConnection("Provider=sqloledb;" _

DBInsert.Commandtext = "Insert Into GuestInfo" _

DBInsert.Connection =DBConn

DBInsert.Connection.Open

DBInsert ExecuteNonQuery()

What I'm trying to do is connect to the SQL database and input new information to the database.

This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.

Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()

View Replies !

Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

View Replies !

Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny

View Replies !

Compilation / Re-build Issue

Hi,
We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we do modification in the code it take longer time to build approax it takes around 2 min to display the default page (login page).

Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster.

Regards
K.Karthik Doss

View Replies !

SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does.

In oracle, system will let you know whether the the procedure is successfully complied or not?

Thanks/

View Replies !

SQL Compilation And Execution Plan
Hi all,

I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2).

Is SQL server working the way expected or am I missing something?

View Replies !

Compilation Of Stored Procs
Hi,

I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs?

The thing is when I am running the Stored procs in 2005, its performing really slow in first run.

Any help in his regard is highly appreciated.

Thanks,
Ritesh

View Replies !

Compilation Error On Store Procedure
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN SET @message = 'The account type is already exist' RETURN @message END
BEGIN TRANSACTION INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType) --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction IF @@error <> 0 OR @@rowcount <> 1 BEGIN ROLLBACK TRANSACTION SET @message = 'Insertion failure on AcctTypeCatalog table.' RETURN @message END ELSE BEGIN COMMIT TRANSACTION END
RETURN @@ROWCOUNTENDGO
--execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.

View Replies !

C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike

View Replies !

Function Returning Error During Compilation.....
Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @t1_sys char(10),@t1_all varchar(11)
declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@cg1,1,2)='Q$')
set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @temp_qcard values(@cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
WHILE @@FETCH_STATUS = 0
BEGIN

insert into @temp_qcard values(@t1_all)

declare @t2_sys char(10),@t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t2_all)

declare @t3_sys char(10),@t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t3_all)
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ....
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.

View Replies !

SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run?

What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this?

Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job.

Thanks in advance for any help you can give me.

Wayne E. Pfeffer
Sr. Systems Analyst
Hutchinson Technolgy Inc.

View Replies !

Newbie With An Easy Compilation Error Question.
I've been looking over this and can't see anything wrong. Can anyone shed some light on this for me?
------------------

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0117: 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader'

Source Error:

Line 16: SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn);
Line 17: myConn.Open();
Line 18: SqlDataReader myReader = myConn.ExecuteReader();
Line 19: do
Line 20: {

Source File: D:Inetpubhoteladvisor estLogin.aspx Line: 18

void Login(string username, string password)
{
SqlConnection myConn = new SqlConnection ("server = client1; uid = dbadmin; pwd = dbadmin; database = hotels");
SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn);
myConn.Open();
SqlDataReader myReader = myConn.ExecuteReader();
do
{
while (reader.Read())
{
if (username == myReader.GetString(1) && password == myReader.GetString(2))
{
messages.Text = "Your login was successful!";
}
else
{
messages.Text = " Your login was unsuccessful!";
}
}
}
while (reader.NextResult());
myReader.Close();
myConn.Close();
}
void Submit_Click(Object sender, EventArgs e)
{
Login(username.Text, password.Text);
}

Edit by moderator - NetProfit: Added < code>< /code> tags.

View Replies !

Ignore Compilation Errors For Creation Of Stored Procedures
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine

My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:

Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.

In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option.

My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure?

Here's a code snippet stripped down to the bare minimum to generate the error:

CREATE PROCEDURE [My_FullTextSearch]

@Term VarChar(1000)

AS

BEGIN

SET NOCOUNT ON;

IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1

BEGIN

Select [DocumentID]

from [DocumentText]

where [Term] like '%' + LTRIM(@Term) + '%'

END

ELSE

BEGIN

Select [key] from FREETEXTTABLE(Documents, Contents, @Term)

END

END

View Replies !

Delaying Compilation Real Time

Hi, I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please? Thanks

Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?

Thanks