how to reseed for selected basing on their last count.I have written a query to to reseed basing on last count.But for how to do 10 tables at a time .
declare @last int
select @last=max(empid) from Table_1
DBCC CHECKIDENT (Table_1, RESEED, @last)
but how to do for more than 10 tables or more tables...reseeding at one go basing on last count.
Hi,
I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.
This works only for the first ExitCoilID then I need to RESEED.
Here is my code:
DECLARE
@EntryCoilCnt AS INT,
@ExitCoilID AS INT,
@SubtractedFromEntyCoilCnt AS INT
DECLARE
@ExitCoilID_NotProcessed TABLE
(ExitCoilID int)
INSERT INTO @ExitCoilID_NotProcessed
SELECT DISTINCT ExitCoilID
FROM
dbo.TrendEXIT
where
ExitCoilID is not null and
ExitCnt is null
order by
ExitCoilID
DECLARE
@ExitCoilID_Cnt_Index TABLE
(ExitCoilID int, ExitCnt int IDENTITY (1,1))
IF @@ROWCOUNT > 0
BEGIN
DECLARE ExitCoilID_cursor CURSOR FOR
SELECT ExitCoilID FROM @ExitCoilID_NotProcessed
ORDER BY ExitCoilID
OPEN ExitCoilID_cursor
FETCH NEXT FROM ExitCoilID_cursor
INTO @ExitCoilID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @ExitCoilID_Cnt_Index
SELECT ExitCoilID
FROM dbo.TrendEXIT
WHERE
ExitCoilID = @ExitCoilID
ORDER BY
EntryCoilID, Cnt
select * from @ExitCoilID_Cnt_Index
--truncate @ExitCoilID_Cnt_Index
--DBCC CHECKIDENT ('@ExitCoilID_Cnt_Index', RESEED, 1)
FETCH NEXT FROM ExitCoilID_cursor
INTO @ExitCoilID
END
CLOSE ExitCoilID_cursor
DEALLOCATE ExitCoilID_cursor
select * from @ExitCoilID_Cnt_Index
END --IF @@ROWCOUNT <> 0
I have a stored procedure which gets data from several tables in database A and inserts them into the same tables in database B. Before the inserts are done, the data in database B is removed currently by using the TRUNCATE statement.
Unfortunately these tables are now being used for replication and you cannot TRUNCATE a table used for replication.
The issue is that these tables contain an Identity column each and using DELETE means the Identity columns will be incremented from the last value each time. I do not want this to happen.
Is there any way of reseeding an Identity column without using the DBCC CHECKIDENT statement because I do not want the procedures to run under the "sa" context if a DBCC statement was to be incorporated into the stored procedure with a DELETE statement?
many thanks for help
Hi All.
I'm writing a Test Suite up for a client and currently I'm working on some Tests that assert the state of the database before and after various persistence opertions. The client uses identity columns for their keys so they are involved in the asserts. To ensure the identity values are easy to assert, each test reseeds them to the required value prior to running the persistence operation.
This works well almost all of the time except just after running the DDL to create the tables for the test suite (Which our continuous integration server does every time it runs the suite).
What looks to be happening is if a reseed call occurs for a new table that has never had an insert then the the value given to the reseed function (rf) is used as the identity value for the first insert for that table. In all other cases it's rf + 1.
I.e.
Case A)
DDL run, table never had an insert:
DBCC CHECKIDENT ('table', RESEED, 0);Then the first insert into the table will have value identity value 0
Case B)
Normal run table has had > 0 inserts.
DBCC CHECKIDENT ('table', RESEED, 0);In this case the next insert will have identity value 1
Is this behaviour correct? If it is can anybody suggest a means of detecting the special first case?
Thanks for your help
Nick Jones.
Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.
View 2 Replies View RelatedHi,
Wondering if anyone can help with this..
I want to INSERT some data already SELECTED in a query and also some other variables.. Will the **dodgy code** make it easier to understand I wonder..
** DODGY CODE **
INSERT INTO tblTableOne(Column1, Column2, Column3, Column4)
VALUES( ( SELECT Column1, Column2 FROM tblTableTwo ), "Text for Column3", "And text for column4")
** END OF DODGY CODE **
I understand it may have something to do with TEMP TABLES perhaps??
Any help greatly appreciated..
KingRoon
Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.
Can this be done?
I have a procedure where I have Values and Selected Table items that have to be inserted into a nother table that require them to be stored in the same record to the data correctly placed?
I have seen some information on this but have not understood fully the answers. I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer. How do I get selected values from a LIstbox into a SQL statement using the IN command
First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End IfNextLabel5.Text = strPC The result in the lable looks something like: 100, 101, 102 , I get rid of the trailing blank and commar with:Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) I have created a parameter called @PROPCLASS that will use the values from the selected values in the listbox in a SQL query using an IN statement: Cmd.Parameters.Add(New OleDbParameter("@PROPCLASS", Label6.Text)) strSQL = "SELECT * FROM web_transfer WHERE ( PROP_CLASS IN (@PROPCLASS)) AND (ACRES >= @lowSize)AND (ACRES < @HighSize) AND (YEAR = @lowYear)AND (SALE_PRICE > @lowPrice) AND (SALE_PRICE < @highPrice) "
It is really the first bit of the WHERE clause I am interested in. The sql statement works if I either type in the actual values IN( 100, 101,102) or if I select only one value in the list box. If I select two then the sql statement does not work.
My question is: How do I get the values selected in the List box into my SQL statement Thank you in advance, s
I have the SQL query. If the user is selecting all the vendor Numbers available in the vendor number parameter drop down then, I will not include the vendor Number condition in the where portion of the sql query. For that I want to know whether the user has selected all the values available in the drop down. How to identify this?
View 3 Replies View Relatedi have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
Hello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex
Hi,
I have developed a report that uses oracle as the database. I have wrote my query by creating a dataset. In the query I am also calling a function.
query:
select EmpName, get_Client_Count(:cities), POS from ...
--function definition
create or replace function get_Client_Count(cities in varchar2) return number is
....
From the report I have multi selectable dropdown check box list for Cities.
the function returns count when I select only one city. Works fine with no problem. As soon as I select multiple cities I am getting the following error
ORA-06553: PLS-306: wrong number or types of arguments in call to 'get_client_Count'
I know this error was thrown since multiple values were selected.
Has anyone come across this situation and came up with workaround or solution to this? I really appreciate your inputs.
Thank you.
hi,
i am using ssrs 2005 and need to pass the multi selected parameter values to another report. how can i achieve this task? I use jump to report option.
How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where
(Expression)
=Fields!LOCATION_ID.Value
(Operator)
=
(Value)
=Parameters!Loc.Value(0)
This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.
Hi,
I am experiencing some problems calling reports in ReportServer using URL.
I have several multivalue parameters that are not being set through the URL. The values for this parameters should be the default ones that I have set in VS.
When I preview the report in VS, the default values are correctly set but, when using the URL call, I keep being prompt for this parameters and the report is not rendered until I set them manually.
Does anyone ever had this problem?
Thank you in advance
Catarina Ribeiro
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):
<asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False"
ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" />
<asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" />
<asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" />
<asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True"
SortExpression="loadid" />
<asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" />
<asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" />
<asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" />
</Columns>
</asp:GridView>
and the sqldatasource's info:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" >
<UpdateParameters>
<asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter>
<asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter>
<asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter>
<asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter>
<asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter>
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue"
Type="Int32" />
<asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
TIA,
John
I have a requirement for the following SSRS expression ( I am using Report Builder 3.0). The end user will input a selection via a mutli select parameter (financial month/s), Â if all month are inputted then sum(Field1.Month1.value...Field12.Month12.value )/12 * 100 else if parameter.value =1 then sum(field/s.values for selected month)/1Â *100 else if month.values = 1 AND 2 then sum(field/s.values for selected month)/2 *100 and so on for any combination of the selected inputs.
Put in simpler terms I would like to calculate a percentage from a field/s containing relevent values, based on month/s the user inputs.
Developing a measure which displays the difference of two values from the selected years.
An example : Show the difference of the sales amount from 2013 and 2015.
Since i am not really into mdx or calculated members.
Hi,
I have dropdown parameter with multi-values allowed.
In my report headed I want to show all the dropdown values that were checked by the user to run the report. But since there could be a couple of hundred values I want to show ALL when all the values are selected instead of listing them one by one.
How can I do that?
Thanks,
Igor
Hi All,
I have requirement where first I need to show only one report
parameter. Based on user selection I need to prompt or show the user
another report parameter.
Say suppose I have 3 parameters. User selects first value in first
parameter I should not show the other 2 parameters. If user selects
second value in first parameter I should show second parameter and
hide third parameter. There is no relationship between these 2
parameters except user selection. Similarly if user third value in
first parameter then I should show third parameter and hide second
parameter.
Is this possible? I can not see any Visible property for report
parameters.
If yes, how to achieve this functionality?
Appreciate your help.
Regards,
Raghu
I have a power pivot with 2 multi valued report filters  student_branch & blood_group. These report filters are used to fetch the data set that contain below result set
student_branch        blood_group      count
Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.
Hi all,
I have the following SQL script that works fine, but I like to view all the fields for the records that are not exists in “capdb.dbo.abc “ for “capdb2.dbo.abc� table instead of some fields :
select distinct cp2abc.subj_num , cp2abc.abc_age, cp2abc.ABC_LETHARGY, cp2abc.ABC_STEREOTYPY
, cp2abc.ABC_STEREOTYPY, cp2abc.ABC_HYPERACTIVITY, cp2abc.ABC_INAPPROPRIATE_SPEECH
from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc
where not exists
(select cp1abc.subj_num, cp1abc.abc_date from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num
and cp2abc.abc_DATE = cp1abc.abc_date)
I tried cp2abc.*, but the returns entire the records in the tables.
select cp2abc.*
from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc
where not exists
(select cp1abc.subj_num from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num
and cp2abc.abc_DATE = cp1abc.abc_date)
Is there a way to accomplish this
Thanks for any help.
Regards,
Abrahim
(moved from Script Library by Jeff)
Hi,
I am using windows authentication to access SQL Server 2005 objects. I have created a database role which grants select permission to only 2 tables in the database. I have added the domain user to this role. So the user should be able to select data from only those tables but when i try to select data from other tables also it displays the data which shouldnt happen. Could you please let me know whether there is any specific setting that needs to be done? Also is there anything to do with the schema level permission setting?
Hi,
I hope you can help me.
I've got a csv that I need to import. It's about 74 columns wide and 2500 rows long. What I need to do is select the first 3 columns from the columns from the csv and put them into one table and then put every 7 columns (plus the 1st column as the primary key) into a different table.
I've not explained that very well, basically it's like this...
A B C D E F G H I J K L M N O P
1
2
3
4
5
Table 1 Data
cs# A1
fe B1
cl C1
Table 2
cs# A1
desc D1
date E1
pay F1
amount G1
vat H1
total I1
cReq J1
I'm not really sure were to start. I want to import the data nightly into the tables. I've tried to use the import wizard but I can't seem to select the data I want and exclude the data I don't.
Steve
Hi ,
I am working on Sql server Reporting Services(Sql Server 2005),
i have designed a Report and deployed that report on Report Server, on this report i need to show the user selected values
in page header by using Parameters which i have already created in the Report.
There are 35 to 40 fields in the Front End(Asp.net2.0)
on passing all these values from front end Report is prompting for parameters which is not desired for my case even though i have made ShowParameterPrompts to false.
Thanks in advance
Srinivas Govada
There is a multi value parameter called  "include" in the report where "Allow Multiple Values" is checked and it has 4 Available values as shown in the attached screen shots and preview of the report is also shown .There is no data set for this parameter and the values  will get displayed on the report based on the visibility condition set in the report.Example : If first value  is selected  then 1 is passed and based on the visibility condition set in the report - the report output is displayed.None is default value and has value 4  and when the report is run with this option i.e. "None" then rest three parameter values are not applicable .
Requirement :
-When the end user selects (Select All) Check box then (None)
-check box must be disabled or must not appear for selection for the end user
-When the end user selects check boxes either of the first three except None then also None check box must be disabled or must not appear for -selection for the end user
-when the end user selects a combination of first three then also None check box must be disabled or must not appear for selection for the end user
-The None is set as default with a value as 4 and is applicable only when the user does not select either of the first three values and the report will run.
Hi,
I thought my problem was a LINQ problem but I got referred to this forum, because they didn't think so. I'm kind of new to all these different forums so I will just try it over here (don't know if I'm in the right place).
My application is suppose to delete all the rows in all the tables and after that it should reseed these tables. I'm using LINQ and I think that's where the problem occurs.
Firstly I execute queries like these for all 5 tables:
dataContext1.ExecuteCommand("DELETE FROM PRODUCER");
After that I immediately execute the following on all 5 tables:
dataContext1.ExecuteCommand("DBCC CHECKIDENT (PRODUCER, RESEED, 1)");
The problem here is that a newly inserted row will not start at 1 in any table. It just continues where it left of before the deletions. However when I execute the empty database function twice (without inserting any rows in between, so I execute a delete on every already empty table) it IS reseeded. I also tried to use different DataContext instance for the reseed queries, but no luck there either.
I think the TRUNCATE command is no solution to my problem because I have a couple of Foreign Key Constraints in my database.
Does someone know the answer to this problem?
Thanks,
Yoni
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedI have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values"  for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the  outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.
View 3 Replies View RelatedI have a database that has an Identity field in one table which has duplicate values. how can this be? and is there any way that this can be adjusted or corrected. (this field is used as the primary key).....UG!
View 1 Replies View Relatedi have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...
View 5 Replies View Related