|
I presume there is a good reason why you are using dynamic SQL, the select statement does not require it!
This will work without dynamic SQL
Set @Surname = @Surname + '%'
SELECT *
FROM tbl_Employee
WHERE tbl_Employee.Department_ID = @Department_ID
AND tbl_Employee.Surname LIKE @Surname
As Shameel suggested print the @SQL, copy the result back and try to run it, then fix it so it runs and make the changes to your dynamic sql construct.
|
|
|
|
|
Thanks guys. The reason for dynamic sql is because this is part of a much larger section of code for custom paging. Again thanks
ASP all the way
|
|
|
|
|
Hi all,
I want to start to enter my records from specific number.
let's say I would like to start entry in database table from 1000 instead of 1.
means right now my records are entered in database from 1.
Instead of 1 I would like it to start from 1001. Is it possible? if yes then how?
Believe Yourself™
|
|
|
|
|
Try This :
DBCC CHECKIDENT (tableName, RESEED, 1000)
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
That worked!!
Thanks Abhijit 
Believe Yourself™
|
|
|
|
|
Great ! 
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
Make your question as clear as possible, so others can understand it properly. What you are referring to is probably start an identity column in a table.
When creating the column, you can specify the seed and the increment.
CREATE TABLE Tab1
(
Col1 int IDENTITY (1000, 1)
)
If you want to change the seed of an existing column that may already have data, use this:
DBCC CHECKIDENT ("Tab1", RESEED, 1000);
|
|
|
|
|
Both worked. Thanks. 
Actually I am going to start it from 300000.
Does this huge starting number could create any issue when the records increases??
If there would be any limitation for increasing the amount of the records in future then also please mention.
Believe Yourself™
|
|
|
|
|
There is no known problem with using IDENTITY columns. It works well. If you want to really have 'big' numbers, use bigint datatype instead of int. The syntax remains the same.
|
|
|
|
|
Hey All,
I find myself here asking a question around SSIS which is somewhat out my comfort zone of C# but I have been tasked with some work, and I have came across a problem which I can't seem to over come.
I have an SSIS Package which I am calling from the command line so its in the file system. Its encrypted with password etc. When I am logged in under my own domain user name the script executes perfectly. However; there is a network service user our support department has set up to run this job. When I run this package under this new user, the pacakge fails.
My package has several tasks, however; the task which appears not to run in the Script task. Again, it runs under my own logon, just no with the service user logon.
As a test, I added the service user to the administrator group on the computer the package is running, and the script task worked perfectly. (I think this might be important).
I'm guessing I have a permissions issue with my new user. My thoughts are that it cannot execute vb scripts which I believe are internal to SSIS?
If anyone has any thoughts, could you please help.
Thanks in advance
TF
|
|
|
|
|
You need a Configuration File to store the credentials to the Server. To make sure if what you are saying 9is what am assuming. can you please send us the error ?
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
|
Even though the structure might be similar, the order of columns is not the same. When you use *, SQL Server takes the column names in the order they were created, therefore causing a data type mismatch. When you specify the columns, SQL matches the column names exactly. This can even happen with a normal INSERT INTO ... SELECT statement, it has got nothing to do with Linked servers.
|
|
|
|
|
Please don't delete the post once someone has replied to it. It might help others.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Just throwing the question out there incase anyone has tried this. When you're using SQL Server Management Studio, you can right click on most objects and manually generate the create/drop/alter scripts.
Is it possible to generate the SQL scripts (or send the script text to a parameter) from within a stored procedure?
Thanks for any pointers!
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
I'm not aware of anything "built in" that would generate the create/alter/drop script for you, but the syntax of these commands is pretty straight forward.
For example:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FSACTIONSET]') AND type in (N'U'))
DROP TABLE [dbo].[FSACTIONSET]
So you could build your own SPROC to generate the script syntax.
What are you trying to do ? Maybe there is a different way to attack your problem.
david
|
|
|
|
|
Right, that would be from a drop script.
I need to dynamically rebuild a large set of tables. There could be changes to the table on a daily basis, so I was hoping there would be a way to programmatically get at the create scripts and exec it as dynamic sql.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Jon_Boy wrote: There could be changes to the table on a daily basis
This tells me your design sucks. If you are changing table structure daily then there must be something disastrously wrong with your data design. I shudder to consider working in such an environment, it sounds like a nightmare.
|
|
|
|
|
I think I found a way to accomplish what I need. Thanks anyways.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Jon_Boy wrote: I think I found a way to accomplish what I need
Care to share? It might help someone else.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Sure Ashfield (thanks for reminding me to do so).
I found this thread on another site. It got me pretty close to what I'm trying to do, just modifying to my needs.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
i have the Following Query
select sa1 as[sa1],sa2 as [sa2],count(sa1) as [count] into #ttemp from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2
and my final query is this
select c.sa1,c.sa2,c.dur1,c.dur2 from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1
and c.sa2 = t.sa2
as you can see there are Duplicates in the sa1 and sa2. So i want to delete any of record but one of each. so that means at last there should be 5 Records.
that bring me these Duiplicate
sa1 sa2 Dur1 der2
============================
6 7 3 2
6 7 3 3
354 867 1 2
354 867 1 3
354 872 1 2
354 872 1 3
356 867 1 2
356 867 1 3
356 872 1 2
356 872 1 3
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Just 1 small doubt..
For every record, the
der2 field is different.
So how can u say that entire row is a duplicate(though the first 3 are same!)?
Please clarify this.. and what is the output u are expecting please put it clearly
Niladri Biswas
|
|
|
|
|
i catch the Duplicate from two Records. i was able to do it like this
select sa1,sa2,count(sa1) from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2
SELECT ACTV as ACTV_ID--, count([VENU]) as , sum([stud])
into #temp
FROM [dbo].SOL_ACTV_VENU
group by ACTV
having count(venu) > 1
order by sum(stud)
and i was deleting my Duplicates like this
SET ROWCOUNT 1
DELETE EXP_REL_CLSH_CONT
from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1
and c.sa2 = t.sa2
SET ROWCOUNT 0
but now in SQL Rowcount = 1 will stop everytime a duplicates is deleted so that it cannot delete the second record. Its Good. But how can i make it to loop and skip one record and delete one in this approach ?
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Try this
declare @t table(sal1 int,sal2 int,dur1 int,dur2 int)
insert into @t
select 6,7,3,2 union all select 6,7,3,3 union all
select 354,867,1,2 union all select 354,867,1,3 union all
select 354,872,1,2 union all select 354,872,1,3 union all
select 356,867,1,2 union all select 356,867,1,3 union all
select 356,872,1,2 union all select 356,872,1,3
select * from @t
I am taking this RecordSet
sal1 sal2 dur1 dur2
6 7 3 2
6 7 3 3
354 867 1 2
354 867 1 3
354 872 1 2
354 872 1 3
356 867 1 2
356 867 1 3
356 872 1 2
356 872 1 3
Since you want any one of the record set so I can use either
sal1 sal2 dur1 dur2
6 7 3 2
or
sal1 sal2 dur1 dur2
6 7 3 3
If this assumption of mine is correct, the here is the answer
select sal1,sal2,dur1,dur2 from(
select row_number() over(partition by sal1,sal2 order by sal1,sal2) rn, sal1,sal2,dur1,dur2 from @t) X
where rn = 1
Output:
sal1 sal2 dur1 dur2
6 7 3 2
354 867 1 2
354 872 1 2
356 867 1 2
356 872 1 2
Here I am considering only the first one for every duplicate entries
What next you can do is put this record set in some temp table , delete the original one and then insert this record back into the table.
Please let me know in case of any concern.
Note - This code will work for Sql server 2005+
Niladri Biswas
modified on Tuesday, November 24, 2009 9:31 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
