Dynamic Filter Not Updating Correctly
Apr 14, 2006
I have several SQL Mobile devices (WM5) syncronizing with a SQL 2005 database through merge replication. We are using the HOST_NAME parameter to filter several tables. A few of these filters use a sub-query to filter the data because of the complexity of the filter (need to do more than join to one table). The subquery will use the HOST_NAME parameter. For example, I want to get a list of all users in the current user's company I need to use host_name to get the current user, find their company ID, then filter the user table on that company ID.
Everything works fine until a user is removed from the table. In that case, the delete statement is not replicated to the device until they reinitialize the mobile database.
Thanks for the help!!
Mike
View 5 Replies
ADVERTISEMENT
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Aug 30, 2007
Here is what I am trying to do in SSRS 2005.
Setting up filters based on parameters with an expression like this:
=Iif(Parameters!Company.Value = "", "", Fields!company.Value)
In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter??
Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.
Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.
How can I do this?
Thanks in advance for your help.
View 3 Replies
View Related
Aug 23, 2006
Hi!
Is there any way to use datetime value as dynamic filter in the Merge Replication?
(Yeah, I use HOST_NAME in order to pass datetime value)
I used all possible approaches, but or I got the error
Conversion failed when converting datetime from character string. (Microsoft SQL Server, Error: 241)
or I got 0 rows on the subscriber after synchronization.
Any ideas how it can be solved?
Thanks
Paul
View 11 Replies
View Related
Jun 14, 2006
Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database.
I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc.
I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great.
Thanks in advance.
View 8 Replies
View Related
Feb 2, 2007
Hi!
I'm a merge newbie and have a couple of questions. I'm about to setup a merge replication with Sql Server 2005 and Sql Server CE as a subscriber. Situation is like this, we have 10 service technicians using pda.
I want to each pda user have their own data. What I understand I need to use dynamic filter and SUSER_NAME()?? Do I need to create a "translation" table to map my system's UserId against SUSER_NAME? How have you solved this problem?
/Magnus
View 1 Replies
View Related
Feb 28, 2007
I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.
Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null.
Add a filter to the dataset that contains the field to be searched. Set its Expression to:
=IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "",
Fields!LastName.Value, Nothing) ,
its operator to "="
and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")
voila!
Helen
View 2 Replies
View Related
Aug 12, 2006
Hi ...
I am working on a project where the server version of application has vouchers from different entities. I have created a publication manually. My next step was to create a client subscription using rmo and to execute a pull. This part works fine. Code samples from http://msdn2.microsoft.com/en-us/library/ms147314.aspx
My next step would be to implement dynamic filtering using the guid of the entity as a parameter.
I dont want to use suser_sname() or host_name() as I want to use a fixed login for the replication for all users, and a client could have several host dbs (sql express, sql mobile)
My goal would be to pass a guid-value to the HostName Property of the MergePullSubscription class and convert it to an uniquidentifier and use it as a filter as I have not found any other way to pass a guid as a filter.
RMO-Code:
subscription.HostName = "4bb0e468-c68a-4253-ba82-f71c3a6e302d"
Filter:
SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] = dbo.fx_ConvertHostToEntity()
Function:
create function fx_ConvertHostToEntity()
returns uniqueidentifier
as
Begin
declare @host nvarchar(50)
set @host = host_name()
declare @entity uniqueidentifier
set @entity = cast( @host as uniqueidentifier)
return @entity
End
When trying to set the filter sql server complains that a character string cannot be casted to a uniqueidentifier - so i can not set this filter. Is there a way to pass a parameter other then the username or the hostname as a filter?
SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] =@entity, where @entity is a guid
Thanks for your support
Alex
View 6 Replies
View Related
Oct 22, 2007
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
View 4 Replies
View Related
Sep 5, 2007
Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select €œselect all€? option from the parameter list all 20 parameters value should be false.
I will be very thankful.
Regards,
Faisal Saleem
View 5 Replies
View Related
Jun 29, 2007
Hello, I have a datagrid which is populated with data from an MS SQL server database. When I run an update query it always throws an exception - what is the most likely cause for this given that I am using the code below: 1 public void DataGrid_Update(Object sender, DataGridCommandEventArgs e)
2 {
3 String update = "UPDATE Fruit SET Product = @ID, Quantity = @Q, Price = @P, Total = @T where Product = @Id";
4
5 SqlCommand command = new SqlCommand(update, conn);
6
7 command.Parameters.Add(new SqlParameter("@ID", SqlDbType.NVarChar, 50));
8 command.Parameters.Add(new SqlParameter("@Q", SqlDbType.NVarChar, 50));
9 command.Parameters.Add(new SqlParameter("@P", SqlDbType.NVarChar, 50));
10 command.Parameters.Add(new SqlParameter("@T", SqlDbType.NVarChar, 50));
11 command.Parameters["@ID"].Value = DataGrid.DataKeys[(int)e.Item.ItemIndex];
12 command.Connection.Open();
13
14 try
15 {
16 command.ExecuteNonQuery();
17 Message.InnerHtml = "Update complete!" + update;
18 DataGrid.EditItemIndex = -1;
19 }
20 catch (SqlException exc)
21 {
22 Message.InnerHtml = "Update error.";
23 }
24
25 command.Connection.Close();
26
27 BindGrid();
28 }
All of the row types in MS SQL server are set to nvarchar(50) - as I thought this would eliminate any inconsistencies in types. Thanks anyone
View 5 Replies
View Related
Jul 8, 2015
updating a recordset contained in an System.Object variable during runtime.
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?
View 5 Replies
View Related
Apr 15, 2008
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
View 5 Replies
View Related
Jan 26, 2006
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
View 14 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
Mar 30, 2004
and (jobs.ApplicationsClosed > getdate() OR jobs.ApplicationsClosed = getdate())
The above statement does not pick jobs.ApplicationsClosed= 3/30/2004(that is today'sdate)
If I change 3/30/2004 to 3/31/2004 it picks it up. What is wrong with my syntax?
Thanks!
View 2 Replies
View Related
Dec 9, 2007
Hi guys,
I'm trying to use the bcp utility to transfer data from an SQL Server table (CUSTOMERS) to a text file (test.txt).
Here is what I have:
DECLARE @FileName varchar(50)
SET @FileName = 'C: est.txt'
EXEC bcp BAirwaysDB..CUSTOMERS out @FileName -S<servername> -T
only I keep getting the error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
and when running:
bcp BAirwaysDB..CUSTOMERS out C: est.txt -S<servername> -T
on the command line I get:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'RIVERAMelanie'.
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database "BAirways" requested by the login. The login failed.
Can you see where I'm going wrong with this? Any feedback would be really appreciated.
Thank you
Butterfly82
View 7 Replies
View Related
Oct 12, 2007
Hi,
I have the following simple SP..
There is a field which I'm doing a sUM() on to get a total for a QtyRecieved from one of the tbls in the SP..
However, the QtyRecieved field is set to be a two decimal place numeric field.
The sp works fine without issues until on the of the qty received is not a round number.
If it's a number with a decimal in it (i.e 5.5) then this is show as a seperate result and not SUM'd with the others.
Any ideas why?
SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID,
dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
PDS_Login.Supplier_Products.UOM, PDS_Login.Supplier_Products.Vatable, dbo.Suppliers_POs_Details.ProductID,
dbo.Suppliers_POs_Details.PODetailsStatusReason, SUM(dbo.Suppliers_POs_Details_Received.Qty) AS QtyReceived
FROM PDS_Login.Supplier_Products INNER JOIN
dbo.Suppliers_POs_Details ON PDS_Login.Supplier_Products.ProductID = dbo.Suppliers_POs_Details.ProductID RIGHT OUTER JOIN
dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID
GROUP BY dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, PDS_Login.Supplier_Products.ProductName,
PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
PDS_Login.Supplier_Products.UOM, PDS_Login.Supplier_Products.Vatable, dbo.Suppliers_POs_Details.ProductID,
dbo.Suppliers_POs_Details.PODetailsStatusReason, dbo.Suppliers_POs_Details_Received.Qty
HAVING (dbo.Suppliers_POs_Details.POID = @POID)
View 10 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Aug 31, 2006
This is a subset question of one of my other threads, the gist is while using forms authentication, I am trying to enter additional information into another table on the same db as the aspnet_membership tables. I think I need an <asp:SqlDataSource> to do this because it is about the only thing left I haven't tried to get this code to work so any help is appreciated. Environment: sql server 2000, windows server 2003, asp.net 2.0, vb, no GUI<ASP:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlServerConnection %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (@username,@email,@company,@firstname,@lastname,@phone)" runat="server" />looking over some of my books and online it says in the connectionStrings that it is supposed to be the database name that goes next to the : but when I try that I get an error that it is looking for the name of the connection string, so I enter the name of the connection string (as above) and nothing happens, which is basically the ongoing event with this whole problem, the form fields (username, email, password) that go into the aspnet_membership tables just fine, I don't have any problems with those, it is getting the additional information into another table that has been stalling me the whole time.So does this sqldatasource look right? Any help is appreciated.
View 12 Replies
View Related
May 16, 2008
Hi everyone,
I'm having some problems importing a CSV to a db in SQL Server
Consider the following csv input
"1102","Spiderman, New York City",
"1103","Superman, Metropolis",
"1104","Batman, Gotham City",
When I select comma as delimiter, the columns will automatically
be offset since the location includes commas as well. How do I correctly
set the delimiter?
I tried using the following as delimiter → ","
but that'll strip the quotes which are required in this case.
Does anyone have an idea how to resolve this?
View 8 Replies
View Related
May 22, 2005
Call me an idiot as I'm quite new to asp.net. I'm working through a
book called 'ASP.NET Unleashed' where the chapter on how to create a
database connection to an sql database on MSDE called 'pubs' is
shown. Iv'e installed MSDE. When I try to run the example it says that
the login or authentication has failed. Iv'e tried different things but
I'm out of ideas. Another thing is that when I try to install sql
server 2000 on my Win XP it first says something about this being the
wrong OS or something like that. I can get past that but when I'm
presented with 3 installation options 1.client 2. client and
connectivity 3.connectivity I installed client because that's supposed
to create a stand alone installation.
However when I'm done installing the only new entry in the start->programs is
client connectivity. Where is the Enterprise services that I'm supposed
to be able to access according to the book? Is sql server 2000
compatible with XP and not just win 2000?
View 2 Replies
View Related
Dec 13, 2005
can somebody tell me whats wrong with this sp..................when i pass lname only or fname only it does not return any data, but when i passs both lname and fname it returns the correct data.....when i rearrange the structure to where it checks the lname only it works also but fname doesn't .........any help is greatlly appreciated
CREATE PROCEDURE [dbo].[temp_search] ( @lname varchar(50) = null, @fname varchar(50) = null, @date1 datetime = null, @date2 datetime = null)
/*set @lname = null -- 'aaa'set @fname = null --'ddd'set @date1 = '2/25/2003'set @date2 = '9/30/2004'*/
as
set nocount on
if not @lname is null or @lname = '' and not @fname is null or @fname = ''begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and temp_user_info.lname like @lname and temp_user_info.fname like @fnameendelse if not @lname is null or @lname = '' begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON cir_temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and temp_user_info.lname like @lname end else if not @fname is null or @fname = '' begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and temp_user_info.fname like @fname end else if not @date1 = null and not @date2 = null begin select cir_temp_user_info.*, dateTime1 FROM cir_temp_user_info INNER JOIN temp_seq ON cir_temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and dateTime1 between @date1 and @date2 end else if not @date1 = null begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON temp_user_info.temp_seq = cir_temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and dateTime1 like @date1 endGO
View 5 Replies
View Related
Dec 31, 2005
I am very new to SQL server and I'm using stored procedures for my program. So far I wrote one tonight, that works just fine.
I haven't really written one before, but its kind of similar syntax since I know C++/C# and VB.
My question is, even though this works for what I need it do, is it written correctly? Can you see any problems with it, or would you have done it differently?
I want to make sure its done correctly, and it runs as fast as possible.
Thanks!
[pre]
CREATE PROCEDURE CreateNewUser
@UserID int out, @LoginID nvarchar(30), @Password nvarchar(30), @RegisterDate smalldatetime, @LoginIDExists nvarchar(30)AS /* Check to see if the loginID is already in use before attempting to save it. We MUST have a unique loginID for each user */ SELECT @LoginIDExists = loginID FROM users WHERE loginID = @LoginID
/* If we pulled a value from the database, then the loginID already exists, return with error code 1 */ IF (@LoginIDExists = @LoginID) BEGIN SELECT 1 RETURN END
ELSE BEGIN /* The loginID does not already exist, attemp to add the new user to the database. */ INSERT INTO users ( loginID, loginpassword, registerDate ) VALUES ( @LoginID, @Password, @RegisterDate )
/* Grab the UserID for the new user. */ SELECT @UserID = @@identity
/* return with error code 0 */ SELECT 0 RETURN ENDGO
[/pre]
View 6 Replies
View Related
Sep 14, 2001
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). What's going on here? Am I losing records somewhere?
Also, this is a cold fusion application. Thanks!!
View 1 Replies
View Related
Sep 17, 2001
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). There is absolutely no deletion of any kind going on. (i.e. My application (cold fusion) has no code whatsoever that is deleting records.) What's going on here? Am I losing records somewhere?
View 1 Replies
View Related
Apr 14, 2008
I've recently just got started using sub queries. The sub query that I'm running is supposed to select distinct values out of a table. It doesn't seem to be working though.
SELECT id
FROM data
WHERE (issueID IN
(SELECT DISTINCT issueID
FROM data))
When I execute this query, it returns a list of id's, but they don't each have a unique issueID. In the data table, issueID can appear more than once (hence, I need to use distinct). However, id is the primary key, and is unique.
Any ideas?
View 7 Replies
View Related
Feb 26, 2008
I am new to SQL Server 2005. I have tried to be a good student and learn on my own about designing the database. However, I am facing a blockage of getting data into the database. I want to verify I have setup the relationships correctly. Here is the tables.
1. A Manufacture has an Address.
2. A Manufacturer has one or more Distribution Points.
3. A Distribution Point has an Address.
The Manufacturer --> Address is a one-to-one relationship.
The Distribution Point --> Address is a one-to-one relationship.
The Manufacturer --> Distribution Point is a one-to-many relationship.
The manufacturer table has a FK to the address id (PK).
The distribution table has a FK to the address id (PK).
The distribution table has a FK to the Manufacturer id (PK).
Address has several other FK, but not used in this scenario.
The INSERT and UPDATE Specification for the Delete Rule and Update Rule is set to "No Action" for the Manufacturer and Address tables. In the Distribution Point table, the relationship for the update rule involving the relationship with the Address and Manufacturer tables is set to CASCADE. What this tells me is SQL Server 2005 will update the table should changes occur in Address and Manufacturer table.
Is there anything else I can check to be sure the issue I am facing is NOT the deisgn of the database?
Thanks for taking the time to read my post.
Russ
View 1 Replies
View Related
Mar 14, 2006
Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.
information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1
this post about the database does not exist has received no replies:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1
Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.
Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.
View 12 Replies
View Related
Mar 12, 2008
Hi
Basicaly this is isituation: Software that our company developing (in Visual Basic 2005 .NET) is supposed to deliver files to sever and get them from it over Internet. In database those files are stored as image files. But the problem is that when Internet connection is slow or unstable the data transion seems to srew things up. Therefore file that is saved in database is corrupted and when i get it back from db it's not possible to open it.
Question is this: How to ensure safer delivery of large data (large meaning up to 4 MB). Is there any strategy that is recommended or proven to be best there is to this point.
Our first guess was to make MP5 based checksum in software and send it with data and then on serverside make a new checksum wiht MP5 and check if those two match. Since I've never done this I wanted to know (in case you dont know any better way)
*) what would be the code to use in order to make a MP5 checksum form Image and *) what the best best type of data for MP5 checksums (in tables and stuff)
View 7 Replies
View Related
