|
just hit the return key on the typewriter instead of space key.
|
|
|
|
|
Another option is to insert enough spaces if word wrapping is on... 
|
|
|
|
|
Hi all,
I wrote a StoredProcedure which returns a dataset.
But when i drag that SP into .dbml file, the return type of SP is becomes int, not ISingleResultType<>. I dont know reason for this.
When i call that SP i got Error / Return vale 0
Plz Help me.
Here my SP:
ALTER PROCEDURE [dbo].[USP_GetAdNames](
@user varchar(50),
@specialization smallint = null,
@country smallint = null,
@state tinyint = null,
@city varchar(50)= null,
@from datetime = null,
@to datetime = null
)
AS
CREATE TABLE #TEMPADS
(
int_ID int,
str_Name varchar(50)
)
DECLARE @query nvarchar(1000)
BEGIN
SET @query = N'INSERT INTO #TEMPADS SELECT transAds.int_AdID,transAds.str_Name
FROM TRANS_ADS AS transAds
WHERE transAds.str_UserID = '''+@user+''''
if (@specialization != null OR @specialization != 0)
SET @query = @query + ' AND tansAds.smallint_Specialization = '+cast(@specialization as varchar(10))
if (@country != null OR @country != 0)
SET @query = @query + ' AND transAds.smallint_CountryCode = '+cast(@country as varchar(10))
if(@state != null OR @state != 0)
SET @query = @query + ' AND transAds.tinyint_FocusState = '+cast(@state as varchar(10))
if(Ltrim(Rtrim(@city)) != '' OR Ltrim(Rtrim(@city)) != null)
SET @query = @query + ' AND transAds.str_FocusCity = '''+@city +''''
if(@from IS NOT NULL)
SET @query = @query + ' AND transAds.dt_Modifiedon >='''+convert(varchar(25),@from,101)+''''
if(@to IS NOT NULL)
SET @query = @query + ' AND transAds.dt_Modifiedon <='''+convert(varchar(25),@to,101)+''''
print @query
EXECUTE sp_sqlexec @query
SELECT int_ID,str_Name FROM #TEMPADS
END
///// When i execute in SQl Management Studio iam getting records as dataset. But not in my program.
I am calling that sp like this:
var query = from temp in DataContext1.USP_GetAdNames("kvs",null,null,null,null,null,null) select temp;
.....................................................
I also tried creating a partial class in DataContextMapping.Designer.cs file but no use.
Please suggest me, what went wrong in my SP.
|
|
|
|
|
Try using table variables instead of temporary tables since the result set of a stored procedure with temporary tables can not be inferred.
Regards,
Syed Mehroz Alam
|
|
|
|
|
The database engine for SQL Server 2008 Express is downloadable from: http://www.microsoft.com/express/sql/download/[^]
Management Studio Basic and Advanced Services (Full-Text Search and Reporting Services) are not yet released, but based on blogs the target is at the end of August.
In order to install SQL Server 2008 Express, you need to install first:
- Microsoft .Net Framework 3.5 SP1
- Windows Installer 4.5
Standalone version of Books Online can be downloaded from Microsoft SQL Server 2008 Books Online[^]
[Edit]
Also tools and advanced services are now released for download
[/Edit]
modified on Saturday, August 23, 2008 1:51 AM
|
|
|
|
|
|
No, just thought that this could be useful info. That's why I selected News for message type.
|
|
|
|
|
hi Guys
i have a Stored procedure
Create Procedure [prcLoginv]
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
IF EXISTS (Select * from Login
where Username = @User_name and U_Password = @U_Password)
return 1
else
return 0
i get the following Error
Msg 156, Level 15, State 1, Procedure prcLoginv, Line 8
Incorrect syntax near the keyword 'IF'.
what is wrong
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
Take the SELECT statement to a separate statement and take the return values (for example username or COUNT) to a local variable and then test the variable value in IF statement.
Mika
|
|
|
|
|
Given that he has a syntax error (code won't even compile) how will checking the logic of the stored procedure help?
|
|
|
|
|
That's true, didn't notice the missing AS keyword  .
|
|
|
|
|
You are missing the keyword AS
CREATE PROCEDURE Name
@parameters parameter_types
AS
BEGIN
-- Code goes here.
END
|
|
|
|
|
Thank you guys,
But how can i forget such small thing.
Thanks a lot 
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
It's pretty obvious that password hashes for SQL Server 8.0 and 9.0 are different. So when migrating a server from SQL server 2000 to SQL Server 2005, everyone's password pretty much gets jacked.
Is there a way to login to a SQL Server 2005 instance using SQL Server 200 credentials?
I've tried specifying the provider and driver in my connection string for my ADO objects but get an error thrown back at me saying that the "provider" keyword is not recognized.
|
|
|
|
|
So here's the story. I was wrong about the hashes.
But, 2005 uses case sensitive passwords by default. So when our vendor's application converts all passwords to upper before connecting, of course it'll do the same when it creates the users.
So I have the misfortune of being an idiot and writing supplementary applications for badly designed software. Yay for me, and all I have to do now is edit a few libraries.
|
|
|
|
|
Hello
How does one go about debugging Transact SQL code in MS SQL 2005.
The instance on which I wish to debug the tSQL, is running under the LocalSystem account.
Thanking the replier in advance
ChristiaanS
|
|
|
|
|
You can use Visual Studio for debugging. Select the procedure from Server Explorer and "Step Into Stored Procedure"
Mika
|
|
|
|
|
|
You're welcome 
|
|
|
|
|
iam passing a string example "hi this is john" then how i can i check whether the string john is there in the string or not in given string .
|
|
|
|
|
Just a sample how can you implement in your real data.
DECLARE @val AS VARCHAR(255)<br />
SET @val='hi this is john'<br />
<br />
IF(SELECT CHARINDEX('john',@val))>0<br />
BEGIN<br />
PRINT 'exists' <br />
END<br />
ELSE<br />
PRINT 'doesnt exists'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi
I really need some help here. I’m about to introduce SSIS packages where I work. Basically this package needs to import data into SQL tables. I have created all the necessary connections – from a flat file as a data source to a ole db connection. The flat files names stays the same but the suffixes changes on a daily basis.
Eg: imp.1 for day 1 then imp.2 for day 2 and I can’t be changing file names on a daily basis.
How may I tackle this one?
Kicza
|
|
|
|
|
Is there not a fornext object in SSIS that you can read each file in. Test the file pattern using a script and process or dump the file.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sometimes, a low tech approach is the way to go ...
How about this:
You will need 2 directories,
1 for where the files will be placed on a daily basis
2 a work area where you can rename "today's" file to be processed.
You can use a .bat file or .vbs script to check directory 1 for a new file, take that new file and copy/rename it to the work area with the desired standard file name convention and kick off the SSIS import. After the processing you could go back to directory 1 and rename the file to something like "myfile.day1.done".
You have alot of flexibility with writing .vbs scrips and scheduling them for execution.
Just a thought.
David
|
|
|
|
|
Ok, this isn't so much a "I need help" question as it is a "what do you think of this" question. I'm building a .net desktop app that accesses it's database through a web service. I need to implement a way for my users to upload large data files into the database on demand. The data files are generated by third parties and their format changes quite frequently. When these files are uploaded, only some columns from the files are uploaded into the DB tables. Now, I've looked at several options and here's what I've decided on. I'm going to create a DTS package and job for each import file and I'm going to fire it off using sp_start_job. I'm then going to poll the job's status using sp_help_job. Does this sound reasonable or can anybody think of a simpler way? It's gotta be flexible and easy to update without having to recompile/redistribute code, which is why I'm going the DTS package route.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
