Im looking to create a Stock Levels table which goes through each record of my Part table updating the corresponding Part in the Stock Level table.
I know how to perform all the Count calculations etc, if someone could just give me an example of a suitable loop that would be great
cheers.
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedI have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
I'm not looking for details or code, just general direction I should go. Here is my goal:
I have a SQL stored procedure that returns data which I will need to add spaces to, then end up with a space delimited text file.
This txt file will then be needed to be saved and emailed.
Also, I would like to be able to schedule this in the Task Scheduler for once a month execution.
Is this all just one big crazy idea?
Thanks for any help.
Andrew
i want to take 70-028 test.
but i still have some questions about administration.
can anybody help me?
Question:
(NY1-----T1-----NY2)-----56kbps-----(Seattle-----T1-----SanFrancisco-----T1-----Los Angeles)
NY1=NewYork1,NY2=NewYork2,SF=SanFrancisco,LA=Los Angeles
A. NY1=pub/dist; NY2,Seattle,SF,LA as subscriber
B.NY1=pubs; NY2=dist;Seattle,SF,LA as subscriber
C.NY1=pubs; SF=subs/dist/pubs; NY2,Seattle,LA as subscriber
D.NY1=pubs; LA=dist; something not workable
which one is correct answer, and why?
How to do the questions like this?
In the company that I work there are two offices, one is outside the city and the other is in the city. They are connected trough a DS0 using frame relay. The thing is that the comunication is very slow (the SQL server is in the city) and we want to add a new server at the office outside the city and we want at the end of the day replicate the data between the two servers so every server has the actual data. We didn't use primary keys, instead of that we used identities (autoincremental value administered by the server). What kind of replication should I use and why? (I'm new with SQL Server). There are 3 kinds: Snapshot, Transactional and Merge.
View 4 Replies View RelatedI have 20 tables (the same schema)
I use view to search it with union operator.
now i have indexes on all of the tables on 1 kolumn (nvarchar)
what kind of index i should use to make it fastest
index on view ?
original data type is string ,format like MM/dd/YYYY,also there exists null or "" value
I need to convert them into datetime,format like YYYY--MM-dd
any idea about this?
thanks in advance
Hi everyone,
Is there any way to know how is stored a package before do loadpackage, loadfromsql... bla,bla??
I mean, is there any property for Package class where you can see how it is stored?
Thanks in advance for your comments,
Hello, I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database? . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access Unlimited Microsoft SQL Server 2000 1 x MySQL 1 x PostgreSQL 1 xDSN/ODBC Unlimited SQL Database Users Unlimited Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you, Beezgetz
View 11 Replies View RelatedHi,
I have a problem in a Stored Procedure SQL query im writing, basically what I want is something that works like this
WHERE intField IN (@strCommaSepList_Of_Integers)
First I tried this:
SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (@strListUserGroupsAllowed))
But if I passed a 25,26 value to the last paramenet is gave an error saying:
Syntax error converting the varchar value '25,36' to a cllumn of data type int.
How can I get this to work??
If for example I manually put the WHERE IN values it works, ike this I mean:
SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (25, 36))
But thats no use as I have to pass the list of values dynamically...
Please help!!
- Carl
Hi,
I am unable to understand how can I create a new filegroup?
I know how to move certain text/ntext/images column to a new file group - thru design table but my problem is how to create a new filegroup?
Do I have to use Alter Table/Alter Database for this or there is any other better way?
TIA
Jai.
Here's a basic overview of my Table
ID identity int
ParentID int
Name varchar
Now, the root level items will have a ParentID of NULL, and the child nodes will have it's parent's ID in it's ParentID column (a simple self-referencing relationship).
However, when I select it, I would like to Order by ID, but then have all it's children right after it. For example, if this is the unsorted view:
Code:
ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
3 <NULL> Test 2
4 1 Test Child 2
5 3 Test 2 Child 1
I would like the sorted to look like:
Code:
ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
4 1 Test Child 2
3 <NULL> Test 2
5 3 Test 2 Child 1
Is this possible with my current structure? If not, how could I change it?
Hey all!
This may sound like a weird one but i couldn't think of a better audience to ask!
If you are a SQL Server DBA (or were, or aspire to be, or play one on T.V., etc.) and the CIO of your 2.something billion $ a year company has offered you an 1 hour forum to sit down and ask him anything you want with the pretense of getting a straight answer, what would you ask?
I know this offers an excellent opportunity to cut up a bit...and do so if you must (keeping us entertained around here is important too!)...but i'm looking for work related, SQL related, direction, strategy, etc, etc type stuff.
Not: 'Whats it like to drive a $250k 'benz to work everyday?' OR 'Can i have .5% of your $2,000,000 bonus this year?'
While i genuinely have those questions in my mind...i'm not looking to waste his $5,000 an hour ass's time - and I only have 1 hour to chew his ear off.
What do ya'll think?
Hi,
What kind of functions i cannot use within a trigger?
Thanks for all your replys for my previous posts.
If we don t have the right to create an SP, can we create a dynamic in memory kind of SP
what s the syntax pls?
Thanks a lot
Hi all,
Just can't figure this out. I'll try not to give a long-winded explanation (I hope), let's say this is the table, for example:
[Table1]
RowNumber | Value
1 | 4
2 | 6
3 | 3
4 |10
5 | 6
6 | 5
7 | 8
8 | 8
9 | 2
10 | 6
I want to write a query that creates a column whose values are similar to (ROW_NUMBER -1), and resets each time a value is present.
Using the above example, 6 will be the test value. So, I want to know how many rows it takes till 6 is repeated.
Output would be:
RowNumber | Value | Spread
1 | 4 | 1
2 | 6 | 0
3 | 3 | 1
4 |10 | 2
5 | 6 | 0
6 | 5 | 1
7 | 8 | 2
8 | 8 | 3
9 | 2 | 4
10 | 6 | 0
I think PARTITION BY can be used somewhere, with the ORDER BY on the [RowNumber] - but I'm just not sure on which ranking function to use, and how to reset the Ranking on the number 6 (above example).
Any help would be appreciated. This has been a mind-teaser for me, and I give
Thanks,
Denvas
I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.
I have downloaded and installed the software, and would like to know how to connect and create a test database.
There will be a small test in the interview and the questions are:
1. SQL Management and Data Extraction
For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx
2. XSLT
For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.
3. Database Design
You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.
These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.
This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.
In document for encryption in SQLServer with application XP_Crypt, i see file with extension .pem when using private key or public key. How to create file .pem. Please explain me private key or public key.
View 1 Replies View RelatedWas able to solve my connection issues with my local (test) machine. However, now I've got a much bigger issue on my intranet machine (W2K Server that acts as a domain controller).
When I use essentially the same set up for connecting as I did for the other machine (adjusted for machine name, etc), I get the following message:
Login failed for user '[username]'. The user is not associated with a trusted SQL Server connection.
I went into the SQL Server Express Config, and discovered under "SQL Server 2005 Services>SQL Server(SQLEXPRESS)>Properties" that this service is set up to login as the Local System built-in account. When I try to change this to Network Service, I get the following popup error message:
Cannot perform this operation on built-in accounts. [0x8007055b]
1) This happens regardless of wheter I stop the service and try to reset, apply and restart, or simply try to change it while it's running
2) I can find nothing on the error code above anywhere on the Web (Google came up EMPTY).
Anyone have any ideas about this one?
Hi,
I have a query which gives me the following results:
lLedgerCode sGLCode bDebit TotalSum sGLDesc
61 6843000701 0 600ALPS Holding
33 8345000701 0 1116ALPS Premium Due
56 1000000701 0 1116Regular Premium Income
63 6836000701 1 516ALPS Group Holding
61 6843000701 1 600ALPS Holding
30 6842000701 1 600ALPS Policy Clearing
33 8345000701 1 1116ALPS Premium Due
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is:
SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc
FROM dbo.tbGLTransactions
INNER JOIN dbo.tbLedgerCode
on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode
WHERE dbo.tbGLTransactions.lGLExtractRun = '452'
Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
Thanks!
We have a database that we have designed so all the data dependances are managed by the front end code. However the company we are writing it for has asked us to add around 50 constraints. I was just a little worried what kind of effect on performance this would have. Cheers Ed
View 1 Replies View RelatedI am having a problem getting into SQL 2005.
My connection string is as follows:
using System.Data.OleDb;
OleDbConnection conn = new OleDbConnection(@"Provider=SqlOleDb;Data Source=Orange;Initial Catalog=Test;");
//tried with taking out comma after Test as well.
I have tried a lot of different permutations here and I cannot get this to work.
Ive started using try/catch in my t-sql code now and I rather like it, since im a C# developer. I read that some errors with an error code below 10 will not cause the catch block to be entered. What kind of errors does this include ?
View 2 Replies View Relatedhi help me as soon , i need know ,i get for my teacher
View 1 Replies View RelatedI need to save MicroStation "elements" along with other general information. Is that possible to do with SQL Server 2005?
View 1 Replies View RelatedHello
I have the following table, and i need to know, what kind of Constraint-restriction and how to apply using management studio for the next case
Table ITEMSProperties
===============================================================================
ItemID (PK-FK) ValueType (PK-FK) PropertyValue (NVARCHAR)
-----------------------------------------------------------------
1 1 (1=Color) Red
1 2 (2=Date) 01/01/2001
2 1 Blue
2 2 021/badStr/08badstr
Well, so ..
If I don€™t want bad Dates-Strings stored in this table, for the ValueType = 2, .. What kind of constraint-restriction or maybe a trigger, I need to use and please tell me how to aplly using management studio ?
Hi,
I am not expert on SQL server, i need to be able to access different server from withing sproc,
1. Is Linked Server is the best aproach.
2. If yes then how i set it up that it take all authenticated users of this server to remote, i tried with impersonate but it gives error that NT authority dont hae access etc etc (when i try run a sql that accesses remote server)
3. The reason i need to access remote server is ... one of the that table in my db (logTable) is getting to big it is 10 times the size of rest of the db altogether so i though i move it out not just from same db actully put it on a seprate server. so that all the tasks about main db would become easy etc.
any idea help
Sajjad
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
I know this is an easy one, but for some reason i keep gettin the wrong results.
This displays something like this :
Question_description Visit_Activity_Id SR_Name
Vacation 5 Judy Smith
Sick 2 Judy Smith
Visit 1 Tom Mathews
Training 3 Karen Williams
But i want it to show all the SR_Name's ..like this:
Question_description Visit_Activity_Id SR_Name
Vacation 5 Judy Smith
Sick 2 Judy Smith
Visit 1 Tom Mathews
Training 3 Karen Williams
NULL null Tom Jones
NULL null Kim Jones
NULL null Jon Travis
Any help will be grately appreciated! thanks!
Code Block
ALTER PROCEDURE [dbo].[PROC_RPT_SR_DAILY_ACTIVITIES]
(@Region_Key int=null, @Daily_activity_statistics_datetime datetime )
AS
BEGIN
SELECT Customer.Name as Store_Name,
Tbl_Daily_Activity_Statistics.SR_Code,
Tbl_Daily_Activity_Statistics.Territory_Code,
Tbl_Daily_Activity_Statistics.Customer_code,
Tbl_Daily_Activity_Statistics.in_progress_time,
Tbl_Daily_Activity_Statistics.completed_time,
Tbl_Daily_Activity_Statistics.Visit_Activity_Id,
Tbl_Daily_Activity_Statistics.dial_in_datetime,
Tbl_Daily_Activity_Statistics.question_code,
tbl_Questions_to_RC_Question.RC_Question_description,
Qry_Sales_Group.SR_Name, dbo.Qry_Sales_Group.Region,
Qry_Sales_Group.Region_Key,
Tbl_Daily_Activity_Statistics.Daily_activity_statistics_datetime
FROM Tbl_Daily_Activity_Statistics
INNER JOIN tbl_Questions_to_RC_Question
ON Tbl_Daily_Activity_Statistics.question_code = tbl_Questions_to_RC_Question.question_code
INNER JOIN Qry_Sales_Group
ON Tbl_Daily_Activity_Statistics.SR_Code = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
LEFT OUTER JOIN customer
ON dbo.Tbl_Daily_Activity_Statistics.Customer_code = dbo.customer.customer_code
WHERE Region_key=@Region_key AND Daily_activity_statistics_datetime = @Daily_activity_statistics_datetime
hai,
I Want to send SMS all kind of phones arround the world,so what can i do for that pls help me...............
A application using Sql Server 7 is developed under WinNT(Win2000, WinXp) . Now want to transfer this application into Vista OS. When install MSDEx86.exe, "SQL Server7 is not compatible with Vista" was warned. When do such kind of transfer, what kind of software related to Sql Server is required? And is it necessary to modify the code of application?