is there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.
The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.
Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option.
Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again.
Any ideas on how to break the chain here are welcome.
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber
group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id
Having sr.spec_checklist_id = 30 and count(*)>1
order by sr.linked_to_worknumber
Sample of query results:
idlinked_to_worknumberspec_checklist_id
18323IP-DN-03377430
24229IP-DN-03377430
18294IP-DN-03380830
28027IP-DN-03380830
18295IP-DN-03381030
19924IP-DN-03381030
In the 3 cases, I would want to retain only records with ids 24229,28027 and 19924.
Does anyone know how to go back 1 record?
I know how to go forward 1 record:
Code:
SELECT TOP 1 *
FROM MyTable
WHERE [id] > 27
ORDER BY [id]
This will select the record with the id 28, if id 28 does not exist, then it will select the next available id.
however, then I try:
Code:
SELECT TOP 1 *
FROM MyTable
WHERE [id] < 27
ORDER BY [id]
It goes to the very first record in the table, becuase it selecting the TOP 1 record, How do I only go back 1 record, eg. id 26.
Hi,
I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID
This is my stored procedure:ALTER PROCEDURE dbo.quote
(@Business_Name nvarchar(50),
@Business_Type nvarchar(50),@Contact_Title nchar(10),
@Contact_First_Name nvarchar(50),@Contact_Last_Name nvarchar(50),
@Address_Line_1 nvarchar(MAX),@Address_Line_2 nvarchar(MAX),
@City nvarchar(MAX),@Postcode nchar(7),
@Tel_No nchar(11),@E_mail_Address nvarchar(50),
@Distributor_ID int,@Profile_Class int,
@Meter_Time_Code int,@Line_Loss_Factor int,
@Unique_Identifier1 int,@Unique_Identifier2 int,
@Check_Digit int,@Tariff nchar(20),
@UnitRate1AnnualUsage nchar(10),@UnitRate2AnnualUsage nchar(10),
@UnitRate1SubTotal money,@UnitRate2SubTotal money,
@QuoteTotal money
)
ASINSERT INTO client_details (Business_Name, Business_Type, Contact_Title, Contact_First_Name, Contact_Last_Name, Address_Line_1, Address_Line_2, City, Postcode, Tel_No, email_Address)VALUES (@Business_Name, @Business_Type,@Contact_Title, @Contact_First_Name, @Contact_Last_Name, @Address_Line_1, @Address_Line_2, @City, @Postcode, @Tel_No, @E_mail_Address)
DECLARE @Client_ID INTSET @Client_ID = scope_identity()
INSERT INTO meter_quote (Client_ID, Tariff, Meter_Distributor_ID, Meter_Profile_Class, Meter_Time_Code, Meter_Line_Loss_Factor, Unique_Identifier1, Unique_Identifier2, Check_Digit, UnitRate1AnnualUsage, UnitRate2AnnualUsage, UnitRate1SubTotal, UnitRate2SubTotal, QuoteTotal)VALUES (@Client_ID, @Tariff, @Distributor_ID, @Profile_Class, @Meter_Time_Code, @Line_Loss_Factor, @Unique_Identifier1, @Unique_Identifier2, @Check_Digit, @UnitRate1AnnualUsage, @UnitRate2AnnualUsage, @UnitRate1SubTotal, @UnitRate2SubTotal, @QuoteTotal)
RETURN
And this is the code I have in my asp page:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="quote" InsertCommandType="StoredProcedure">
<InsertParameters><asp:ControlParameter ControlID="TextBoxBusinessName" DefaultValue=""
Name="Business_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="DropDownBusinessType" Name="Business_Type"
PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="DropDownListTitle" Name="Contact_Title"
PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="TextBoxFirstName" Name="Contact_First_Name"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLastName" Name="Contact_Last_Name"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine1" Name="Address_Line_1"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine2" Name="Address_Line_2"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine3" Name="City"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxPostcode" Name="Postcode"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxTelNo" Name="Tel_No"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxEmail" Name="E_mail_Address"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxDistributorID" Name="Distributor_ID"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxProfileClass" Name="Profile_Class"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxMeterTimeCode" Name="Meter_Time_Code"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLineLossFactor" Name="Line_Loss_Factor"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxUniqueIdentifier1" Name="Unique_Identifier1"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUniqueIdentifier2" Name="Unique_Identifier2"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxCheckDigit" Name="Check_Digit"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="LabelTariff2" Name="Tariff"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUnitRate1Usage" Name="UnitRate1AnnualUsage"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate2Usage" Name="UnitRate2AnnualUsage"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate1Total" Name="UnitRate1SubTotal"
PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelUnitRate2Total" Name="UnitRate2SubTotal"
PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelQuoteTotal" Name="QuoteTotal"
PropertyName="Text" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>
And the following in the C# code:
try
{
SqlDataSource3.Insert();//Insert quote details into the database using a stored procedure
}catch (Exception ex)
{LabelInsertException.Text = "Failed" + ex.Message;
}
Any help would be much appreciated
Thanks, Hayley
I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.
however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)
Here's my code
SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID
WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID
[Code] ....
Assuming that I have created relationships (PKs and FKs) on my tables already, does the following statement permanently remove a Foreign Key constraint, or does it mearly disable it?ALTER TABLE myTable NOCHECK CONSTRAINT FK_myForeignKeyGO Also, I can't seem to find out how to temporarily remove the Identity qualifier of a field, and then reset it back as Identity later. Any help?Finally, will a failure of ALTER TABLE affect the @@ERROR variable? Can I check @@ERROR after each ALTER TABLE table statement to see if @@ERROR <> 0?The reason for both of these issues is that I am redesigning an unnormalized database, and I need to write a large script to drop all constraints on all tables, transform the data as normalized into the new table structure, and then re-enable constraints, Identity fields, etc. Thanks.
View 3 Replies View RelatedIs there a way to temporarily disable replication? My problem is that I have pull subscribers that I do not want accessing the database at certain times, so that data updates and backups can occur. Is there a way to make that happen?
View 4 Replies View Related
I and my backup admin have full server, database, and SRS admin rights. We both have several subscription reports active. I need to temporarily disable all subscriptions for today. Of course, I can see my subscriptions, get into them, and stop them. However, from SQL Server Management Studio / Reporting Services, I can see her subscriptions but can not find a way to disable them.
Any ideas?
Guys,
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
Thanks a lot
I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in oracle, but looking for equivalent query in sql server?
In oracle query is like this:
select *
from tablename
where deptnumber = &department_number;
Enter Value for department_number
I am looking this query in sql serevr2008.
I have synchronous mirroring. Some times I loose connection to witness and mirror servers. These times primary server is down. Is there any way I can change mirroring to asynchronous when primary server is down due to communication break down between witness and mirrored servers? I can break mirroring but to re-establish mirroring, I have to backup and restore on the other side. So if I can change mirroring to asynchronous when primary server is down due to connection breakdown between witness and mirrored server, then when witness and mirror servers come back, I don't need to restore the entire database. Ofcourse I could use asynchronous always but that does not failover automatically. I am thankful to all answers and suggestions. Thanks.
View 3 Replies View RelatedI am doing a data conversion in which I drop constraints before inserting data and then reapply at the end. I would like to also drop the not null constraints for certain columns, transfer the nulls and then fill them in later before reapplying the constraints. Is there a relatively simple way to drop and reapply the not null constraints?
View 4 Replies View RelatedI have an application that dumps massive amounts of data into a database during the installation. My log file always ends up being 30-40GB+ at the end of the install. Can I turn off logging while I do the install and enable it after? What are my options.
View 6 Replies View RelatedPoor Performance Temporarily Cured by Re-booting SQL Server
Please can you help?
SQL Server 2000.
Accessed by VB.Net 2003.
Server set up by SQL consultant (who is no longer available).
We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).
The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.
Additional information:
Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it?
Newbie
I need to run a script in production that adds primary key to a table. Because table is large, I can't run it in one shot, the log file is not so large to accommodate it. Instead, I created a new table with same structure plus new surrogate primary key, and I populate it in a cursor loop.
I already ran it many times in test server, and no problems with that. But the problem will be in production when applications will be accessing this table and try to insert/update it while I am running my loop.
So I am looking for a solution how to block users to access this table, it's OK if they receive an error. Setting database to single user mode will not work because I don't want to block them from all the rest tables in this database.
I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedNew to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on theĀ roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View RelatedHello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View RelatedHow I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies View RelatedHi,
I'm pretty new to SQL and I've got a bit of a sticky problem. I've looked around on the net for a solution but possibly I just don't know what I should be searching on.
I'm trying to join tables together where there is a one to many releationship - but I'm trying to put the results for each relationship on one row (which kind of results in dynamic columns).
table one:
Number
1111
2222
3333
table two:
Number Code
1111 15
2222 18
2222 13
3333 22
2222 26
I want the resulting table to look like:
Number Code1 Code2 Code3
1111 15
2222 18 13 26
3333 22
Is this possible? There is no limit on how many rows in table 2 can be related to table 1.
Can anyone point me in the right direction for what I should be looking at?
The reason I'm trying this is for SQL reporting services if that makes a difference?
Thanks,
Kelvin.
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isnt completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
Hi all,
I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000.
The application is in production and everytime I want to do some changes, i do the changes on my
development machine then I copy the application dll on the server.
The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements.
These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.
How can I do modifications without affecting the production server and the users ???
thanks.
I just want to get the sum of a table's column into a variable, in a stored procedure. The best I can do is
SET @TotalBalance = SELECT SUM(Balance) FROM AccountDetails
Not good enough, of course.
I'm trying to put a date into a SQL Server table. The database field type is "smalldatetime". The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#). When I run the following code the date in the database is always ends up being: 1/1/1900.
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _ "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery()
Any other field types work fine, it's just dates that aren't working ???? Can someone please provide a code snippet showing me what I'm doing wrong??
The two queries:
set dateformat 'dmy'
select count(c.id), e.name
from call c left outer join employee e
on c.req_id = e.id
where c.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(c.id) desc
set dateformat 'dmy'
select count(ch.id), e.name
from call_hist ch left outer join employee e
on ch.req_id = e.id
where ch.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(ch.id) desc
the results:
42NULL
34Dirk Deloof
13Annick Leirman
11Ronny Loosen
9Geert Benoot
9Nicole Ferrari
8FLOCK
8Mosselmans Christoph
7Geert Pets
7Mireille Dutrieue
6johan
6Laurent De Schrijver
5Jeanette De SChrijve
5Marc De Vlieger
5minerva
5Pascal Saesen
5Rik Haghebaert
5Sonja Van Kerckhove
4Bcatron
4Luc Willems
3Brigit Brocken
3euroadmin
3Francine Kopp
3Luc Steyaert
3Marie-Rose Buysse
3Marnix Van Steirtege
3Mattias Denys
3Pieter Frooninckx
3Reserve
3Rik De Scheemaecker
3Thierry Linard
2Carlos Van Alboom
2Dorine Sierens
2Els Poelman
2Jean Claude Vermeir
2Katrien Colman
2Kim Impens
2Kris Lejeune
2MEDreserve01
2Roger De Wilde
1Agnes Lebon
1Carla Van Den Broeck
1Eric Vlaeminck
and
118NULL
58Marie-Rose Buysse
47Dirk Deloof
45Ronny Loosen
43Annick Leirman
41Geert Pets
38FLOCK
38Pascal Saesen
28Teamleiders afwerkin
24Kim Impens
22Ilse Soetens
22Rik Haghebaert
22Severine Balduck
21Teamleiders print
20Mosselmans Christoph
20Jeanette De SChrijve
19Geert Benoot
19Francine Kopp
18Geert Meuleman
17Rik De Scheemaecker
16johan
16Katrien Colman
15Gaby Eloot
14Kris Lejeune
14Gilbert Callebaut
14Laurent De Schrijver
13Els Poelman
13Luc Steyaert
11Marnix Van Steirtege
10Frans Hoogewijs
10Sonja Van Kerckhove
10Dorine Sierens
9Eric Vlaeminck
9Thierry Linard
7Frederic Denis
7Michel Poppe
6Carla Van Den Broeck
6Pieter Frooninckx
5Katlijn Poleyn
5MEDreserve01
5Mireille Dutrieue
5Agnes Lebon
4Guido Antoin
4Onderhoud
4minerva
4Jeanette Van Brussel
3Roger De Wilde
3Sofie Gabriels
3Verf2
3euroadmin
3Marc De Vlieger
2Luc Willems
2MEDRESERVE07
2Regina Decoster
2Monique Kohl
2MEDRESERVE04
2Portier
2Bcatron
2Pierre Hanet
2Tgabriels
2Isabelle Torrelle
2Nicole Ferrari
1Robert Zwaak
1Carlos Van Alboom
1testuser
1Brigit Brocken
1Reserve
1Opleiding
1Verf
How do I put these results in one? I need not two but one Query.
Please help me. Thanks
I've got 2 tables :
TABLE DateSqlServer
Date as DateTime
TABLE DateDb2
Date as VarChar(26)
I run these queries :
INSERT INTO DateSqlServer (Date) VALUES (GetDate())
INSERT INTO DateDb2 SELECT Date FROM DateSqlServer
Then, when I run :
SELECT Date FROM DateDb2
I get
"march 8 2004 3:45 PM"
instead of
"2004-03-08 03:45:12:000"
How can I transfer the date as I see it in table DateSQLServer
WITHOUT doing FORMATs on the Date column ?
Why does the INSERT transform the date format ?
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block
ALTER PROC csp_ICASTransaction_AskDoc
@ClientID INT
AS
DECLARE @ClientID INT
SET @ClientID = 1
DECLARE @cClientID INT
DECLARE @cMonth VARCHAR(20)
DECLARE @cOccurance INT
DECLARE @Counter INT
DECLARE @AskDoc CURSOR
SET @AskDoc = Cursor FOR
select @ClientID, Month_Year, SUM(AskDocs)
FROM zzenrolled$
WHERE ICASClientID = @ClientID
GROUP BY Month_Year
OPEN @AskDoc
FETCH NEXT FROM @AskDoc
INTO @cClientID, @cMonth, @cOccurance
/*
SELECT @@Cursor_Rows
PRINT @cClientID
PRINT @cMonth
PRINT @cOccurance
*/
DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT)
WHILE (@@FETCH_Status = 0)
BEGIN
SET @Counter = 0
INSERT INTO @MyTable
VALUES (@cClientID, CASE @cMonth
WHEN 'Jan-03' THEN '20030101'
WHEN 'Feb-03' THEN '20030201'
WHEN 'Mar-03' THEN '20030301'
WHEN 'Apr-03' THEN '20030401'
WHEN 'May-03' THEN '20030501'
WHEN 'Jun-03' THEN '20030601'
WHEN 'Jul-03' THEN '20030701'
WHEN 'Aug-03' THEN '20030801'
WHEN 'Sep-03' THEN '20030901'
WHEN 'Oct-03' THEN '20031001'
WHEN 'Nov-03' THEN '20031101'
WHEN 'Dec-03' THEN '20031201'
WHEN 'Jan-04' THEN '20040101'
WHEN 'Feb-04' THEN '20040201'
WHEN 'Mar-04' THEN '20040301'
WHEN 'Apr-04' THEN '20040401'
WHEN 'May-04' THEN '20040501'
WHEN 'Jun-04' THEN '20040601'
WHEN 'Jul-04' THEN '20040701'
WHEN 'Aug-04' THEN '20040801'
WHEN 'Sep-04' THEN '20040901'
WHEN 'Oct-04' THEN '20041001'
WHEN 'Nov-04' THEN '20041101'
WHEN 'Dec-04' THEN '20041201'
WHEN 'Jan-05' THEN '20050101'
WHEN 'Feb-05' THEN '20050201'
WHEN 'Mar-05' THEN '20050301'
WHEN 'Apr-05' THEN '20050401'
WHEN 'May-05' THEN '20050501'
WHEN 'Jun-05' THEN '20050601'
WHEN 'Jul-05' THEN '20050701'
WHEN 'Aug-05' THEN '20050801'
WHEN 'Sep-05' THEN '20050901'
WHEN 'Oct-05' THEN '20051001'
WHEN 'Nov-05' THEN '20051101'
WHEN 'Dec-05' THEN '20051201'
WHEN 'Jan-06' THEN '20060101'
WHEN 'Feb-06' THEN '20060201'
WHEN 'Mar-06' THEN '20060301'
WHEN 'Apr-06' THEN '20060401'
WHEN 'May-06' THEN '20060501'
WHEN 'Jun-06' THEN '20060601'
WHEN 'Jul-06' THEN '20060701'
WHEN 'Aug-06' THEN '20060801'
WHEN 'Sep-06' THEN '20060901'
WHEN 'Oct-06' THEN '20061001'
WHEN 'Nov-06' THEN '20061101'
WHEN 'Dec-06' THEN '20061201'
WHEN 'Jan-07' THEN '20070101'
WHEN 'Feb-07' THEN '20070201'
WHEN 'Mar-07' THEN '20070301'
WHEN 'Apr-07' THEN '20070401'
WHEN 'May-07' THEN '20070501'
WHEN 'Jun-07' THEN '20070601'
WHEN 'Jul-07' THEN '20070701'
WHEN 'Aug-07' THEN '20070801'
WHEN 'Sep-07' THEN '20070901'
WHEN 'Oct-07' THEN '20071001'
WHEN 'Nov-07' THEN '20071101'
WHEN 'Dec-07' THEN '20071201'
END ,'3' )
SET @Counter = @Counter + 1
IF @cOccurance > @Counter
FETCH NEXT FROM @AskDoc
INTO @cClientID, @cMonth, @cOccurance
END
CLOSE @AskDoc
--INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance])
SELECT * FROM @MyTable
DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
Any help will be greatly appreaciated.
Kind Regards
Carel Greaves
Atm this is my Test SQL String
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT 'Test' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTESTarchived est.pdf', SINGLE_BLOB) AS BLOB"
When I try to put in a variable as follws
"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT '" + @[User::MyFileValue] + "' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTestarchived est.pdf', SINGLE_BLOB) AS BLOB"
I keep getting errors Saying it contains an illegal escape sequence of w any ideas ?
Btw the above is used as an expression
I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please take a look at the statement and see if its well formed,
String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";