Does Cursor Convert Table To Read/write?
Hello,
Any help here much appreciated.
I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.
i suspect this is when i run a cursor through the table.
Is anyone able to confirm for me whether running a cursor changes a table's properties?
Many thanks.
Tim
Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
View Complete Forum Thread with Replies
Related Forum Messages:
Read/Write To Same Table
Hi, we're trying to read from a table and write back to the same table and are having a lot of trouble with blocking. What could we do to prevent our application from hanging due to blocking of this type?
View Replies !
Last Read/write Time Of Table
Is there a way to get the last read/write time of a table? I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.
View Replies !
Foreach Loop Read Table Data And Write To File
Hi, I want to do the following with a ssis package: INPUT: A table contains 2 columns with data i need. column A=Filename and column B=FileContent PROCESS: I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A. I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file. Is there any example similiar to that so i could learn how to start on that? Thanks Danny
View Replies !
Reset Database Files From Read-only To Read-write
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message: Server: Msg 3415, Level 16, State 2, Line 1 Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded. What command(s) are needed to make these files read_write? thanks
View Replies !
XML Read And Write
Pls help guys I need to know whether its possible to read or write in to XML documents from SQLServer directly using stored procedures. I basically need to created a number of XML documents on a nightly basis.,..........also i need to read a no of XML docs to SQLServer... Is it wise to use stored procedures for this? Or are there performance issues.........and do u suggest doing in in .NET Your comments are appreciated.. Regards Benny
View Replies !
Different Cpu Read Write
I have a query that runs for 10 sec on one database( A) and 5 min on another database(B) even though two database have identical scheam, tables, index and statistics.. I ran a profiler and got the below information CPU READ Write Database A: 92051 711956 8774 Database B: 91812 7621589 315822 A query runs on database has a significant larger read and write.. I don;t understand why this is happening? even though these two database have the same structure?? it has the same execution plan as well.. How can i solve this issue?
View Replies !
How To Write SQL Query And Not Cursor ?
Please help me to get the required result: For each IDS in table1 - change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3) - in table2, find the maxVal for each number - disply the table1..ids, number, table2..maxVal & table1..idsDesc, order by table1..ids, table2..maxVal & IntValue I have 2 tables, over milin records each. The Simplified versions of the tables looks like that: create table table1 (ids varchar(100), idsDesc varchar(100)) go insert table1 select '1,2,3', 'Description 1' union all select '2,3,4', 'Description 2' union all select '1,7', 'Description 3' union all select '16,3,8', 'Description 4' union all select '2,5,6,1', 'Description 5' go create table table2 (ids int, maxVal int) go insert table2 select 1, 10 union all select 2, 6 union all select 3, 12 union all select 4, 11 union all select 5, 66 union all select 6, 4 union all select 7, 3 -- union all select 8, 5 -- no value for 8 union all select 9, 6 union all select 16, 12 go I have also function that returns table variable of numbers delivered from given string: create function dbo.fn_StrToIntValues ( @str varchar(1000) ) returns @numsTbl table (IntValue int not null) The command select * from dbo.fn_StrToIntValues('1,2,33') Returns --> intValue 1 2 33 Can I use SQL query and not cursor to get the following result ? Required Output : ids IntValue maxVal idsDesc --------- -------- ------- --------------- '2,5,6,1' 5 66 'Description 5' '16,3,8' 16 12 'Description 4' '2,3,4,9' 3 12 'Description 2' '1,2,3' 3 12 'Description 1' '1,7' 1 10 'Description 3'
View Replies !
How To Write Set-based SQL Instead Of Cursor
Guys Here's the scenario create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT) insert data1 (dealid , datex , Tn ) values ('12345' , '31-12-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '30-11-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '31-10-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('98765' , '31-12-2005' , 2) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 1) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 0) select * from data1 I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way Any ideas
View Replies !
Read And Write Access
My database is uploaded at my host, but when im trying to create an account i get partly this message. Im sure its an easy thing to do, if i know how, to permit user to create an account. How do i configure the database to "read and write access"??? Rolf
View Replies !
Write In And Read From My_File.txt
Hi .. I want to Write in files or read from files for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt . and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ?? thanks
View Replies !
Read/Write For Variables
Hi, I am using a Script Component and I have a Read/Write Variable varStatusCase (as assigned in the Custom Properties of my Script Component). I used this inside my script to get a specific value. However, when I ran it I get this error: The collection of variables locked for read and write access is not available outside of PostExecute. How do I repair this? Thanks, cherriesh
View Replies !
Read/Write Performance
Hello, We currently run sql 2005 server and also sql express in our dev environments. We use sql express as an offline store (smart client). We have a similar/exact schema on the sql 2005 server and also the express. We use the auto attach feature to connect to the express version of the database. Both the developer machines and the one that is running the sql 2005 server have exactly the same hardware configuration. The only difference may be that the server box is not running the VS.Net environment. The disk space etc is pretty much the same. Actually we run another database server(DB2) on the 2005 server machine. We have observed that sql express is much slower and queries execute much slower aswell. For example, this may not be a totally scientific way of checking but a long running query on the server took only 2 minutes while on express it took longer than 9 minutes. The schema and data etc are the same. Is there something we need to look into as far as read write speed/performance goes ? TIA, Avinash
View Replies !
CURSOR READ ONLY
select * from new_iba_3 DECLARE new_cur2 CURSOR FOR select * from new_iba_3 order by rid, attr, val for update of rid; open new_cur2 DECLARE @tracepoint NUMERIC, @ibaidNUMERIC, @ridNUMERIC, @attrNUMERIC, @valNVARCHAR(1024), @src_ibaNUMERIC, @src_linkNUMERIC, @rowidNUMERIC, @new_rec_ibaidNUMERIC, @new_rec_ridNUMERIC, @new_rec_attrNUMERIC, @new_rec_valNVARCHAR(1024), @new_rec_orig_ridNUMERIC FETCH NEXT FROM new_cur2 INTO @new_rec_ibaid,@new_rec_rid,@new_rec_attr,@new_rec_val,@new_rec_orig_rid delete from new_iba_3 where current of new_cur2; --HERE close new_cur2; deallocate new_cur2; select * from new_iba_3 WHEN I TRY TO DELETE FROM new_iba_3 IT GIVES CURSOR IS READ ONLY BUT UPDATE WORKS ON THIS TABLE I HAVE NOT DECLARED THIS CURSOUR AS READ ONLY THEN WHY IT IS GIVING THIS ERROR
View Replies !
Read And Write A Constraint Or Default Value
Okay, maybe I'm getting ahead of myself. Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money. I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.
View Replies !
Implementing Read-write Locks
Hi,I would like to use database locking mechanism to control access to anexternal resource (like file system).What I need is1. an exclusive (write) lock conflicting with any access to theresource (both for read and write)2. non-exlusive (read) lock conflicting with writes onlyHow this could be done?I'd appreciate any reply.Vadim
View Replies !
Implementing Read-write Locks
Hi, I woild like to use database locking mechanisms in order to control access to an external resource (like file system). What I need is 1. an exclusive (write) lock conflicting with any access to the resource (both for read and write) 2. non-exlusive (read) lock conflicting with writes only How this could be done? I'd appreciate any reply. Vadim
View Replies !
Use Variables As A Storage [read/write] ?
Hello Guys, in SSIS I want to get a set of data and do some modifications on it before I insert it into the destinatipn. So far so good. Some of the modifications will include comparisions between two columns and if certain field is NULL then I want to get the value from the other one I was comparing to. When using conditional splits, I only get the rows to be redirected so that I can do whatever next. However, I want like use the variables as a storage so that I can put the value of one of the two columns in this variable which will be actually loaded into the destination finally. Any help? Thanks
View Replies !
Password To Read/write My Query
I use excel as an interface to write query to retrieve data from a database in network drive. My problem is everyone can open and edit my query. Of course, the univeral database access user name and password will ask but everyone know this. How can I put password to prevent any query modification? Thanks Daniel
View Replies !
Denormalization, 2 Databases , 1 Read 1 Write Db
Hi, I was reading that many of these high traffic websites actually have 2 databases, 1 database is used ONLY for reads, while the other is for writes. How does one go about creating such a setup? How does the database where writes are allowed replicated the data to the read only database server?
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
Looking For A Db Schema Read/write/synch Tool
Hello,I'm relatively new to the database world so please forgive me inadvance for my ignorance. I have recently been tasked at my job withfinding a tool that will perform the following tasks: 1) extractschemas from a db, 2) produce a difference report between schemas fromtwo databases, and 3) synchronize two schemas. The purpose is forproduct upgrades during which an existing database schema will need tobe synchronized with a baseline schema.The tool must support the following database vendors (versions givenin parenthesis): Oracle (8i/9i), Sybase (12.x), Informix (9.x), MSS2000, and IBM DB2 (7/8).I have spent the last several days downloading and researching avariety of tools such as Aqua Data Studio, DbVisualizer, DBExplorer,DBDiff, and AdeptSQL. I thought it would be a good idea to post amessage to a few database newsgroups and ask for any recommendationsdatabase developers may have.So if anyone has any recommendations on such a tool or softwarepackage, I would greatly appreciate any information.Thanks!Chad Smith
View Replies !
Stored Procedure To Read And Write Between XML And SQLServer
Hi All, I need some help from you experts. I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml. I should be able to give this as a job to exectue daily ,etc. Can we do this using stored procedures in SQLServer. Pls paste some sample code, if any or direct me to any url with better info. Thanks in advance
View Replies !
Read Write Image Data With Sqlceserver
I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox. Here is the code I have been trying out: Dim Img As Image ' Dim conn As New SqlCeConnection("Data Source = My Documents est2.sdf") conn.Open() Dim sql As String = "SELECT * FROM Dater" Dim cmd As New SqlCeCommand(sql, conn) Dim reader As SqlCeDataReader = _ cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) While reader.Read() TextBox1.Text = reader.Item("name") Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte reader.GetBytes(1, 0, b, 0, b.Length) Dim ms As New System.IO.MemoryStream(b) Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range Img = bmp End While PictureBox2.Image = Img I get an error ,Value does not fall within expected range. Does this mean the image was not save correctly in the database? Thanks for any help
View Replies !
Copy Of Standby/readonly DB Into A Read/write DB
Any idea to copy a standby/readonly database. We a have a standby/readonly DB from logshipping for hot standby and we would like to make a copy of this standby/readonly DB into a read/write DB in the same server for daily testing. Thanks in advance!
View Replies !
Read Write Same Variable In Script Task
I want to write a variable say testVar in PostExecute of my script. I also want to read the same variable at the start of my script block. How to specify this variable in the script transformation editor ReadOnlyVariables = ???? WriteOnlyVariables = ??? If i just define WriteOnlyVariables = testVar will it work . I mean i cannot use testVar in both read & write Any suggestions... Thanks
View Replies !
Cursor Is READ ONLY.----HELP!!!--error 16929.
CREATE PROCEDURE RemoveDuplicates AS Declare c_Work Cursor For SELECT storeid, stocknumber, invnumber FROM inventorytest ORDER BY storeid, stocknumber, invnumber -- declare variables for the columns -- data types made up Declare @storeid char(20) Declare @stocknumber char(10) Declare @invnumber char(12) -- declare a duplicate set of variables now for working with Declare @storeid_new char(20) Declare @stocknumber_new char(10) Declare @invnumber_new char(12) -- declare a counter variable for Transaction Commits Declare @Count int Open c_Work Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber BEGIN TRANSACTION While @@FETCH_STATUS = 0 BEGIN If @storeid = @storeid_new BEGIN If @stocknumber = @stocknumber_new BEGIN If @invnumber = @invnumber_new BEGIN DELETE FROM inventorytest Where Current Of c_Work SET @Count = @Count + 1 IF @Count = 1000 BEGIN Set @Count = 0 COMMIT TRANSACTION BEGIN TRANSACTION END End ELSE BEGIN SET @invnumber_new = @invnumber END END ELSE BEGIN SET @invnumber_new = @invnumber SET @stocknumber_new = @stocknumber END END ELSE BEGIN SET @invnumber_new = @invnumber SET @stocknumber_new = @stocknumber SET @storeid_new = @storeid END Fetch Next from c_Work INTO @storeid, @stocknumber, @invnumber END Close c_Work Deallocate c_Work COMMIT TRANSACTION ----When i execute this Sp i get "error 16929 cursor is READ ONLY" . It had worked a week ago and now it doesn't work. Can anyone help me out and its urgent. Thanks in advance.
View Replies !
Read The Csv In A Cursor Instead Of Bulk Update
Hello, I am trying to read in from a csv file which works like this: DECLARE @doesExist INT DECLARE @fileName VARCHAR(200) SET @fileName = 'c:file.csv' SET NOCOUNT ON EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT SET NOCOUNT OFF IF @doesExist = 1 BEGIN BULK INSERT OrdersBulk FROM "' + @fileName + '" WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) END ELSE print('Error cant find file') What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE. I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor. So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?
View Replies !
How To Read The Rows In A Cursor Variable
Hi, I have a dynamic query that returns its values in a cursor variable. How do I read each row from this cursor in a loop ? Eg.: use AdventureWorks go DECLARE @sqlnvarchar(4000), @paramsnvarchar(4000), @tables_cursorcursor, @db_namenvarchar(50), @table_namenvarchar(4000), @schema_namenvarchar(50); set @db_name = 'AdventureWorks'; set @schema_name = 'Production'; set @table_name = 'BillOfMaterials, Product'; set @sql = ' select a.name table_name ' + ' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' + ' on (a.schema_id = b.schema_id) ' + ' where b.name= @schema_name1 ' + ' and @table_name1 is null ' + ' order by 1; ' SELECT @params = N' @table_name1 nvarchar(3000) ,' + N' @schema_name1 nvarchar(100) ,' + N' @cursor cursor output' EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT
View Replies !
Data Read / Write Problem With Concurrent Users On With Dll
Hi,I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder. We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack: 06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:My PathValidatorValidator.NETDataDatabaseEventsDatabaseEventManager.cs:line 92 at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:My PathProjectsValidatorValidator.NETDataTranslinkCATCard.cs:line 94 at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:My PathProjectsValidatorValidator.NETDataTranslinkExistingSchemeEntitlement.cs:line 215 at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkExistingSchemeEntitlement.cs:line 147 at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:My PathProjectsValidatorValidator.NETDataTranslinkTranslinkApplicant.cs:line 369 at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkTranslinkApplicant.cs:line 353 at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:My PathProjectsValidatorValidator.NETDataTranslinkPrePrintedLetter.cs:line 282 at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:My PathProjectsValidatorValidator.NETValidationProcessor.cs:line 82 at clear_applications_scan_applications.ProcessValidation() It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!! Any ideas/suggestions would be greatly appreciated. Thanks
View Replies !
How To Encrypt And Decypt As A IUSR With Read And Write Only Rights
How to decrypt or encrypt without making user a db_owner. It is for a web application and I do not want make the web user a db_owner. Is there a way to make this work without making the user a db_owner. Currently the user is a db_datareader and db_datawriter. I created an asymmetric key for encryption by password. I am not using a master key because I want to keep the password seperately on the web server, so a hacker cannot get access to both if database gets hacked. These are the steps I took when I logged in to SQL server management studio using windows authentication: CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512 ENCRYPTION BY PASSWORD = 'password'; INSERT INTO Payments (CreditCardNumber,enc_CreditCardNumber) values( '458724124', EncryptByAsymKey(AsymKey_ID('ccnumber'), '458724124') ) SELECT CONVERT(varchar(50), DecryptByAsymKey( AsymKey_Id('ccnumber'), enc_CreditCardNumber, N'password' )) AS Creditcardnumber , Creditcardnumber FROM payments where Creditcardnumber = '458724124' When I use the above select statement it works if I make the user a db_owner but I get null if the user is just db_reader and db_writer. Is there a way to do encryption without making the user a db_owner?
View Replies !
Error - Attempted To Read Or Write Protected Memory
Hi, I am back with one more problem.. I have created few reports using SSRS 2005. I am using Oracle database in Data Source to fetch my data. It is working fine and showing me report correctly. But after running the report 8 to 10 times, it starts giving me Memory error. To get rid of that, I need to recycle (stop-start) ReportingService from IIS. I am exactly getting following error... Attempted to read or write protected memory. This is often an indication that other memory is corrupt. I am not getting the actual problem, why is it giving memory error only after running few times? Please let me know if anyone facing same problem or knowing the solution for the problem. Thanks, HMaheta
View Replies !
Is Ssis The Only Technology That Can Read/write Raw Data Type Files?
I'm contemplating a sql server archive strategy that rolls really old data off any sort of dbms and onto low cost media like dvds in a non relational archive format. I dont want to ever worry about these archives spanning different versions of sql when i go to retrieve a range of data that happens to span sql versions (eg one disc was sourced from 2005 another by 2008 but my report needs a union of both). So I'm thinking about neutral/efficient formats for these archives and a live homegrown catalog that can determine exactly what disc(s) need to be mounted based on passed from and to date parameters...all so that the data that might span discs (and versions and maybe even schemas) can be merged and loaded into my sql version d'jour's "throw away" archive database for a one time report or other unplanned activity. I remember raw data types being very convenient as an ETL format for our customers who have ssis, but wouldn't want our sqlexpress customers to be left without the archiving capability. Do the "things" that read and write raw data files really originate in some special T-SQL command that all sql editions can use, or is it strictly an ssis thing?
View Replies !
' Attempted To Read Or Write Protected Memory' While Generating A Snapshot
Hi all, The shotshot agent is generating a snapshot, I got the error ' Attempted to read or write protected memory. This is often an indication that other memory is corrupt'. Then, the agent failed. I tried to restart the agent and generate a snapshot again. This time, it run normally. So far, I got this error twice since the replication launched. Kindly advise. Thanks a lot.
View Replies !
Need To Convert Cursor
I am new to SQL and have created a stored procedure for a .net webapplication. Unfortunately I had to use a cursor in the storedprocedure, so it is taking several minutes to execute because it has toread through about 15,000 records. There must be another way to dowhat I'm trying to do without a cursor. I've tried temp tables andcase statements, but I can't seem to get them to work. I've beentrying to figure this out for over a week and I am just running into awall. Some expert advise would be much appreciated. My code is below.Thank you in advance.--Insert records into first temp tableDECLARE @tempA TABLE(lnkey varchar(10),AuditorIDvarchar(7))INSERT INTO @tempASELECTLNKEY,AuditorIDFROMdbo.tblALPSLoansWHERE AuditDate BETWEEN @BegDate AND @EndDate --parameters from myapplicationAND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE ='ADDED')AND AuditType = @AuditType --parameter from my application--Insert percentage value of Pre-Funding completes for each auditorinto temp table BDECLARE @tempB TABLE(LnkeyCount int,AuditorIDvarchar(7))INSERT INTO @tempBSELECTROUND(COUNT(LNKEY) * @Percent/100, 0) AS 'LnkeyCount',AuditorIDFROM dbo.tblALPSLoansWHERE AuditDate BETWEEN @BegDate AND @EndDateAND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE ='ADDED')GROUP BY AuditorID/*Create cursor to loop through records and add a loan number totblinjectloans if the number of loans in tblinjectloans for eachauditor is less than the percentage value for each auditor from@tempB*/DECLARE @lnkey varchar(10)DECLARE @AuditorID varchar(7)DECLARE @var1intDECLARE @var2intDECLARE @sqlvarchar(4000)DECLARE c1 CURSOR FORSELECT lnkey, auditoridFROM @TempAOPEN c1FETCH NEXT FROM c1INTO @LNKEY, @AuditorIDWHILE @@FETCH_STATUS = 0BEGINSelect @var1 = COUNT(Lnkey) from dbo.tblInjectLoans whereAuditorID=@AuditorIDSelect @var2 = LnkeyCount from @tempB where AuditorID=@AuditorIDIF @var1 < @var2Insert into dbo.tblInjectLoans(lnkey, AuditorID)Values (@LNKEY, @AuditorID)FETCH NEXT FROM c1INTO @LNKEY, @AuditorIDENDCLOSE c1DEALLOCATE c1
View Replies !
HowTo: Read/write Package Level Variables In Custom Task
Hello all, I have been struggling trying to read and/or write package level variables from within my custom task. I'd like to be able to get and set values from within the Execute method of my custom task. I have searched this forum and the books online and can't seem to find the answer. I thought maybe I could use an expression on my task (mapping the package variable to a custom task public property) but that doesn't seem to be working for me. I also would have thought I could use the VariableDispenser object from within my task but the collection is empty. I have 3 package level variables configured and can't seem to find a way to access them (with intentions of getting/setting). Could someone point me to a good doc or provide an example that may accomplish this? Thanks! (I'm using package level variables as a means of passing simple information between tasks that are not using a DB, if there is a better way I'm open to suggestions.) Jay_G
View Replies !
Concurrency Issue On A Single Database User For An Online Read/write Application
Hi to all DBAs, I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded i.e Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control. Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application? to further illustrate my query: Setup 1 would be: ###################Table: Users user a@ipadd --> db1 --> userid mary --> myOnlineApp user a@ipadd --> db1 --> userid john --> myOnlineApp user a@ipadd --> db1 --> userid paul --> myOnlineApp and Setup 2 would be: #####################Table: Users user mary@ipadd --> db1 --> userid mary --> myOnlineApp user john@ipadd --> db1 --> userid john --> myOnlineApp user paul@ipadd --> db1 --> userid paul --> myOnlineApp will Setup 1 be enough if I will use SQL Server as database? or Setup 2 is better user/database architecture? This application will be online enrollment for a school with 16,000 students very much appreciated for the feedbacks and suggestions ^_^x
View Replies !
Error While Trying To Assign A Value To A Read Write Variable In SSIS Package Script Component
Hi, I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute". What should be done to over come the problem please help me on this regard Thanks Madhavan.M
View Replies !
Save Me From A CURSOR. Flag Values That Won't Convert.
Hey all: Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation. Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime. I have another table that houses the primary key and the field of the record that cannot convert. Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion. I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated. --Thanks--
View Replies !
BUG? Optimistic Concurrency Check Don't Work When BLOB Field Read After Row Modification Outside Of The Cursor.
Hi, here is the problem. SYSTEMS INVOLVED: Sql Server 2000, VB 6, ADODB SYMPTOM: Recordset opend with settings: tb.LockType = adLockOptimistic tb.CursorLocation = adUseServer tb.CursorType = adOpenKeyset When I open recordset using above settings (just 1 record), then change that record in other application(eg. using Management Studio), then get back to vb and just read any blob field (text, ntext) and modify any field in the same record then at the very end execute tb.Update operation is succesful which is wrong, because we should get an error: "Optimistic concurrency check failed. The row was modified outside of this cursor." If you pass over the "read any blob field" step it works as it should (operation fails with above error). CODE TO REPRODUCE: Sub Test() Dim SqlConn As New ADODB.Connection Dim tb As New ADODB.Recordset tb.LockType = adLockOptimistic tb.CursorLocation = adUseServer tb.CursorType = adOpenKeyset On Error GoTo Error SqlConn.ConnectionString = "Provider=SQLOLEDB.1;User Id=sa;Password=<password>;Initial Catalog=Northwind;Data Source=<server>" SqlConn.Open tb.Open "SELECT * FROM Categories WHERE CategoryId = 1", SqlConn tb.Fields("CategoryName") = "A" MsgBox "Now modify and commit changes to this record in separate application, when done click OK." res = MsgBox("Read blob field (Yes, concurency check doesn't work), No (does work)", vbYesNo) If res = vbYes Then tmp = tb.Fields("Description") End If tb.Update MsgBox "Update successful!" SqlConn.Close Exit Sub Error: MsgBox Err.Description + vbCrLf + Err.Source, 16 End Sub I'm aware that blob fileld is handled in a special way, it is not stored in record's data page it belongs to (unles we specify in row option). If anyone had this problem or found confirmation that it is the bug, please let me know or maybe it's not the bug and there is some justification of such behaviore .... Thank you for any reply ! Tomek
View Replies !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
&&"Attempted To Read Or Write Protected Memory Error&&" In SSIS
I'm trying to import data from a Sybase ASE 12.0 database called "OurTestDatabase" into MS SQL Server 2005. I started SSIS Wizard and indicated "Sybase ASE OLEDB Provider" as a source and SQL Native Client as the target. I'm gettign the following error message: ----------------------------------------------------------------------------------------------------------------- Cannot get supported data types from the database connection "Provider=Sybase.ASEOLDEDBProvider;Password=;Persist Security Info=True;User ID=sa;Data Source=sybase;Initial Catalog=OurTestDatabase" Additional information |_ Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Data) ----------------------------------------------------------------------------------------------------------------- The same data source worked with DTS when we thought we'd convert to MS SQL Server 2000. Is this a bug in SSIS? What can be done? Using ".Net Framework Provider for ODBC" is not a good option because this doesn't allow me to choose any tables from the Sybase source. Any help is greatly appreciated.
View Replies !
Attempted To Read Or Write Protected Memory. This Is Often An Indication That Other Memory Is Corrupt. (Microsoft Visual Studio)
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated! **EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also. Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio) ------------------------------ Program Location: at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets() at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font) at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate) at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate) at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate() at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
View Replies !
SqlServer Changes Cursor To &"read Only&"
I am trying to write a cursor to update certain rows in a particularorder as follows: (I need the cursor version, not SQL, as the updatelogic depends on the order of rows and some other conditions. Iremoved the Order-By clause from the statement to simplify it; itgives the same error message with or without it.)DECLARE prod_cursor CURSORFORWARD_ONLYKEYSETFOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATEThis gives the following error message: "FOR UPDATE cannot bespecified on a READ ONLY cursor."I have tried a few different combinations of cursor types (like SCROLLinstead of FORWARD_ONLY) but they all give this error, although thestatement seems identical to what I have seen in the books and inbooks online.Any ideas on how to convert this into an updating cursor?
View Replies !
|
TRACKER
