Does anyone know how I can get just one record returned from a select statement - not by doing set rowcount, but saying I want, for example the third record returned.
I'm trying to do a stored procedure that does a select of current records that will reduce the output to roughly 24 records and then get a random record from this - I need to do it all in a stored procedure.
What I have so far is :
BEGIN
DECLARE @Count int
DECLARE @RowCount int
DECLARE @IDint
/* Randomise the Data */
SELECT @Count = Count(*) FROM MyTable WHERE fRotation = 1
SELECT @ID = Rand() * @Count
END
As you can see, @ID will hold a record no.
I cant use batch cursors as they are giving me problems - work for a short while and then they mess up.
I create the cube and KPI successfully.Now want to define the "trend" in MDX.the problem is the trend is not linear, but a quadradic function, which means the kpi target is the highest point in the function.in order to get the "trend expression", to get the "absolute value" is needed (as to measure how near to the target point).but i browsed the operators available, there is no ||.
I'm almost embarassed to ask such a simple question but I'm pretty new to SQL and want to the do the following SQL select. I have two tables:
WORK ORDER HEADER DETAILS (WORKH) WorkOrderNumber | CustomerNumber | StartDate | EndDate
WORK ORDER LINES (WORKD) WorkOrderNo | Service
I want to select all customer numbers that have a work order that includes a line with the service code XXXXX and a start date of YYYYYY and end date of ZZZZZZ. Note that the first field in each table contains the same data but is named differently. It's this that's causing me a headache.
I just downloaded and installed Visual Basic Express Edition and SQL Server Express Edition. All I have in the Start menu is SQL Configuration Tools. My absolutely stupid question is, how do I get started with this?
I know a little bit about SQL from using SQL command line in Oracle, and I've built small databases before using Access. Where does this program fit into all of that? And what do I use and how can I get started with this program?
Seems to me like microsoft mentions learning, but it's never for beginners. I haven't found a single tutorial that says how to make a simple database step by step.
Does anybody know about a tutorial focused on teaching the very basics (a kind of "hello world" application) from setting up the new database with a few sample fields to "deploying" it (making it ready to be used)?
Please let me know.
Once I'm through with these first steps there seems to be a lot of helpful learning resources meant for experienced and expert users, but I'm not there yet...
I'm a user interface designer working for a small development shop in a government agency. My boss feels it would help our team out if I learned a bit more programming (I'm on that already in VS2k8, VB.NET), and some general database theory and practical knowledge. The more I learn about programming, the more my utter ignorance of databases proves to be an obstacle. We use SQL2k5, so I've decided to cut my teeth on that; I don't have the free time to learn something simpler and then graduate to SQL, so it is what it is.
So, I'm looking for a "Dumb Even for a Dummy" guide to database fundamentals, with a strong emphasis on SQL in particular. I slogged through the first 30 pages of this forum and found a couple of MSDN resources but nothing stuck me as being basic enough. I'm hoping someone can provide me with some "Go here, newb" resources to get me started.
Can my program with SQL database run correctly on another com using win xp home edition, with only C# 2005 EE installed? Is it compulsory for every table in a database to have a primary key? Can I extract a data from a specific cell in a database table(click button get data)? How do I go about doing it (the simplest n straight forward method)?
I have a table that expands and pushes down objects under it, i don't want this to happen i want my objects to use an absolute position so i can jiggle things how i want them - this doesn't seem to be an option in SSRS 2005?
when i put a table beside 2 graphs on my report, as the table expands it pushs the bottom table down ONLY when when it's displayed on the final webpage. the preview features shows it as being correct.
how do i prevent the expanding table from pushing the tables BESIDE it down? i could understand if it was a table below it, but it's not.
Hello there... Pending receiving the full version from the higher powers at my work, I have downloaded the Evaluation version of SQL 2000, and set up an instance on my desktop machine. This was set up on an administrator account, and works fine. My main aim with SQL 2000 is to upsize all the companies piddling little Access 97 databases into an SQL backend and keep the 97 frontends. I have migrated some of the tablesets into SQL, and linked to them in Access 97 and they work fine. However, the problems are when I try and set up ODBC. When I enter ODBC as a different user on the server itself, the ODBC list of available SQL Servers shows me the correct server information - my PC ID no, followed by the server name. However, when I try and connect with another machine on the network, it only displays the PC ID no of the server, and not the server name. When I attempt to add the server with just the PC name, it doesnt find the server - same when I add the name of the database to the end of the PC ID no. I'm sure there is something really basic I have not done, but I'm totally stumped now. I am using Windows Auth, and have set up the login of the other PC I am trying to connect, as an authorised user. Please help me - I am just a doofus, please feel free to laugh at me...
Hi all, I have a strange situation. I have a field in the database that has to be a string type field of around 4000 characters.
So naturally I setup the field as type: varchar length: 4000
However when I try to put any text in this field I find that I can put no more than 1023 characters of ascii text in there.
To check if this was a max record length prob I setup a test table with only 2 fields: ID: int, PK, Identity longVarchar: varchar, 4000
and tried to put some ascii text into the field called longVarchar. Again the most I could put in was 1023 characters!
Thinking that it could just be that SQL svr box that was wacky, I tried it on another one with the same result.
I have tried using other field types (nvarchar, char) and have found that they all could only hold 1023 characters max, no matter what how high I defined the size of the field.
Try it out yourselves and see if you get the same result. Any useful suggestions would really be appreciated.
Have a bit of an SQL challenge in that I need to create a well performing view in SQL2008R2 which calculates hours remaining at any given point in time. This is not a simple sum but has some absolute values included.
--------------------------------------------------------------------------------------------------- -- Basic simplified structure of maintenance and usage tables --------------------------------------------------------------------------------------------------- CREATE TABLE #Maintenance (Id int NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Maintenance PRIMARY KEY, MaintenanceDate smalldatetime NOT NULL, -- When maintenance hours are reset HoursAvailable numeric(5, 1) NULL -- Available hours until next maintenance )
[Code] ....
--------------------------------------------------------------------------------------------------- -- Required output view so that at any given point in time I can determine hours remaining --------------------------------------------------------------------------------------------------- -- AtDate HoursAvailable -- '01-Jan-2015 15:00' NULL -- before first maintenance hence unknown -- '10-Jan-2015 10:00' 10.1 -- maintenance hours reset -- '11-Jan-2015 07:30' 8.9 -- subtract 1.2 -- '11-Jan-2015 11:10' 2.9 -- subtract 6.0 -- '15-Jan-2015 00:00' -0.1 -- subtract 3.0 -- '01-Feb-2015 00:00' 12.2 -- maintenance hours reset -- '02-Feb-2015 13:00' 10.0 -- subtract 2.2
i am learning SQL basic commands ( like , where, having, group by, order by , join ,and simple sub-query) and I got one exersice I cannot solve
table RECIPE has 3 columns , ( pizza ,ingredient,amount) each pizza could use 1 / many ingredient, each ingredient could be used by 0 / 1/ many pizza, amount means the quantity of that ingredient used in certain pizza
question: (List the ingredients, and for each ingredient, also list the pizza that contains the largest amount of this ingredient.)
============================ Raw data :
recipe ====== pizza ingredient amount -------------------------------- margarita spice 5 margarita cheese 120 ham ham 150 ham spice 5 napolitana anchovies 100 napolitana olives 75 napolitana spice 10 hawaiian ham 100 hawaiian pineapple 100 hawaiian spice 5 cabanossi cabanossi 150 cabanossi spice 10 siciliano onion 50 siciliano capsicum 75 siciliano olives 50 siciliano anchovies 50 siciliano spice 15 americano salami 120 americano pepperoni 75 americano spice 10 mexicano onion 75 mexicano capsicum 75 mexicano mushroom 50 mexicano chilli 25 mexicano spice 20 seafood seafood 200 seafood spice 5 garlic garlic 25 garlic spice 10 vegetarian onion 50 vegetarian capsicum 50 vegetarian mushroom 50 vegetarian peas 50 vegetarian tomato 50 vegetarian spice 5 mushroom mushroom 100 mushroom spice 5 special cheese 25 special tomato 25 special ham 25 special anchovies 25 special olives 25 special mushroom 25 special bacon 25 special egg 25 special pineapple 25 special cabanossi 25 special salami 25 special capsicum 25 special onion 25 special peas 25 special seafood 25 special spice 10 stagiony ham 75 stagiony mushroom 50 stagiony olives 50 stagiony anchovies 25 stagiony spice 10
I have a report that prints cards for customers. The body of the report contains an address box, letter body, followed by a table that contains all the people to be printed on the cards. If the number exceeds 6 people, another page is to be printed, with no address or letter body, but the table with the remainder of the people on it.
I have been able to get everything to work except for the location of the table on the subsequent pages. They do not appear at the bottom as they do on the first page, but at the top of the page. I've tried using a rectangle as a container for the table, with no luck.
I seem to remember doing this in the past, but as projects get shelved for an extended period of time, the technical knowledge tends to fade.
Any suggestions would be greatly appreciated. Thanks
We are facing some issues with SSIS and thought if you could be of some help.
Actually, instead of Deploying our SSIS projects we copy the SSIS files to a different server from where we execute the SSIS Packages. The reason being, we have a Core Version of the application and this is deployed to different markets and subsequent customisations are performed in the market specific version of the Packages. The problem is although we have a Package Configuration Xml File for our Connection object, the first time when we copy the Packages, we have to manually open each of the Package and change the URL of the Xml File for Configuration. I read through some of the articles on the internet and came to know that the Package Configuration does not support relative path and only absolute path is possible.
Can you please share some of your suggestions on the above problem.
I have a table with a BLOB column, and I need to populate this table including the BLOB column (image type in the database).
What I have done is:
1. use a flat file transform to read a .csv file which specifies the names of the files that store the binary contents for the BLOB column for each row.
2. use an Import Column Transform to read the binary files.
3. use an OLE DB Dest transform to dump the data into my destination table.
I got the error saying:
Error: 0xC02090BB at XXXX, Import Column [1]: Opening the file ".diagram1.bin" for reading failed. The file was not found.
I guess this is because my file "diagram1.bin" is not in the current path? (The current path can be found by "System.IO.Directory.GetCurrentDirectory() call, in my case it is "c:program filesmicrosoft visual studio 0common7IDE".)
My question is: how to determine the directory path information of the package I am running?
I want to install SQLExp2K5 with Advanced Services on a standalone basis on my Dell Inspiron XPS laptop (Win XP sp2, .NET 2.0; 2gb RAM; 8mb broadband; hardware firewall/router) so that I can learn SQL/VB/VWD, and want to use SQL E 2K5 without internet connectivity (at least until I know what the hell I'm doing!)
Really confused as to what component(s), and services I should/should not install - so I've listed my questions below:
1. Service Account: As I'm not on / dont have a domain, i presume using a domain user account is not an option for me. Should I use the Local Service account ? If so, what are/can be the issues in doing so?
2. Authentication Mode: Is Windows Authentication the one to use for a standalone install?; how would I enable the "sa" account and set an "sa" password? In fact, do I even need an"sa" account / password for a standalone, non internet connected install?
3. Components at install: Is it OK to install all components first off?
4. IIS: Is it possible to install IIS and not allow it access to the internet (or is this a stupid question -bearing in mind I dont really know what IIS is/does)
5: Business Intelligence Development Suite: How do I add BIDS once (IF) I manage to install SQLExp2K5-Adv.Ser,?
6. SQLExp2K5 Books on-line: Am I right in thinking that I should install this after installing SQLExp2K5?
7: Visual Basic Express/Visual Web Developer Express: Do these need to be configured seperately to work with SQLExp2K5?
Sorry about the length of this post, but have tried to be as clear as possible. Also apologise in advance for silly/stupid questions :)
We have the customer requirement to display the footer of a SSRS Report fixed at the absolute bottom of a DIN A4 format page. The footer contains information like company address and stuff.
I searched quite a while on this topic and only found workarounds for SQL Server 2005 with Custom Code in the SSRS Report to calculate the size of the body content and then insert some empty lines to get the space needed to push the footer to the bottom of the page. But this won't work in SQL Server 2012. And I wasn't able to figure out how to achieve this yet.
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
i have a matrix report and i want to switch the record background color with each record in the value column in that matrix report e.g 1st record background color is gray and next record background color is white and then the next record background color is gray ... and so on
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category AlbumCategoryID (PK, int, not null) Caption (nvarchar(max), not null) IsPublic (bit, not null)
Albums AlbumID (PK, int, not null) AlbumCategoryID (int, null) Caption (nvarchar(max), not null) IsPublic (bit, not null)
I need to return: -[Album_Category].[AlbumCategoryID] -[Album_Category].[Caption] -[Albums].[Single AlubmID for each AlbumCategoryID] -[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.
I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.
