I am creating an index on a table wit 35 million records but I get the error
'TT_ObjPerformance' table
- Unable to create index 'IX_TT_ObjPerformance_CACode'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
How can I get the index created?
Thanks
SQL Server newbie
From where are you creating the index? via query analyzer/job/some front end( hopefully not)?
Hello,
I am having an error when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work.
I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.
Here is an example (or as close as I can remember at this point) of some code I tried:
myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1, 2"
myCommand.ExecuteNonQuery()
myCommand.Close()
Any help would be appreciated.
Thanks,
StuporMan
You are correct, but it can be made correct by changing the statement to:
"Create View TestView as Select 1 as test1, 2 as test2"
(you can verify this by running in SQL Server management studio).
Andthis does not change the ASP.net error message.
You can do thefollowing test to show what I mean.
Create an ASP.net page with 2buttons.
One button click runs this code:
myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()
(I have left the definitions and SQL connection string up to you)
On the other button click run this:
myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()
And you should produce the same error message as I am getting.
Thanks,
StuporMan
|||
StuporMan:
I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.
I've tried to run your command in SQL Server Management Studio:
Create View TestView as Select 1, 2
and it generates this error message:
Msg 4511, Level 16, State 1, Procedure TestView, Line 1
Create View or Function failed because no column name was specified for column 1.
|||
Ok, I had mistyped the SQL I was using as I had to do it from memoryat the time. Here is a valid Create View statement that produces thesame error in ASP.net:
"Create View TestView as Select 1 as test1, 2 as test2"
This should run in SQL Management studio. If it does not try"Create View TestView as Select * from <insert table name here>",which should also be valid. In either case, ASP.net should produce thesame error message.
Thanks,
StuporMan
I am still having problems even after correcting the SQL string.Tryany valid CREATE VIEW statement from ASP.net and see if it will work. Ihave not been able to get any to work to date.
|||Presumably whatever context your web application is under doesn't have the needed security. Are you sure you elevated the permissions of the correct account? (Just guessing here, as I've never tried creating views from a web application myself.)|||
Thanks for the response, you prompted me to again check mypermissions. The user I was logging into the SQL server with DIDhave the correct permission set. The ASPNET user did not. Giving ASPNETthe permission did the trick.
Thanks,
StuporMan
I'm trying to test merge replication, but am unable to create the publication, the snapshot agent keeps failing. The error I get from replication monitor is "Script failed for Table <tablename>"
Our database has quite a few XML datatype columns, when I take out the xml columns the snapshot succeeds OK, but fails with the xml column.
I created a simple table containing a varchar column and xml column. I created this table in our database, and in Adventure Works. Was able to create a publication with this test table in the adventureworks database, but was unable to create publication with this table in our database. The snapshot failed with error "Script failed for table testtable". I removed the xml column on the test table in our database and was able to create the publication successfully. Creating a publication in the adventureworks database with an xml column has no problems, but does in our database.
Anyone have any ideas?
Hi|||Hi
What is the name of the database you are creating the publication in?
Can you post a script for the xml schema collection you are trying to publish?
Thanks
|||
Hi Katie,
I suspect that the compatibility level of your database is set to <90, and you can find out whether this is the case by executing sp_dbcmptlevel with the name of your database. If the compatibility level of your database is indeed set to < 90, you can use sp_dbcmptlevel again to change it to 90 and see if the snapshot problem goes away.
Hope that helps.
-Raymond
Hi All,
Is there a way to create a report in SQL2005 reporting services to call a stored procedure that has parameters? I keep getting an error saying i am not supplying the params, I can't seem to find where to put my parameters for the report to use.
Thanks, Joe
You would be better posting this on the reporting services forum, as this is mainly for T-SQL issuesIs there a way to create a report in SQL2005 reporting services to call a stored procedure that has parameters? I keep getting an error saying i am not supplying the params, I can't seem to find where to put my parameters for the report to use.
Thanks,
Joe
How about something like
exec uspTest @.parm1, @.parm2
If I remember right it will provide a couple of boxes for you to type the parm values when you view the report in Report Manager
|||the parameters (Report Parameters) you have in RS should have the same name as the parameters you declare in stored procedure.sqlI am very new to SQL Server and to S.S. 2005. I am trying to create a relationship using a database diagram. I keep getting this error:
'tblCASEMAST' table saved successfully
'tblCASECHRG' table
- Unable to create relationship 'FK_tblCASECHRG_tblCASEMAST'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblCASECHRG_tblCASEMAST". The conflict occurred in database "Criminal Records", table "dbo.tblCASEMAST", column 'CASENO'.
In tblCASEMAST, CASENO is nvarchar, length of 12 and a primary key
In tblCASECHRG, the column I am trying to link to is also nvarchar, length of 12
Any help is appreciated.
I found the solution. I had to delete the data, create the relationship and then import the data again.I am trying to run SSIS packages under SQL Server Agent 2005 and I keep getting a package failed error in the event viewer.
I've heard that I need to set up a proxy account. I have found the following code and need a little explanation on what all the parts mean since I am very new to this:
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'
Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'
Let's say for the sake of argument my domain is called CompanyInc and I log into windows with my name Philip_Jaques and my password is badpassw0rd. Would I modify the above code this way to create my proxy?
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'CompanyInc\Philip_Jaques', secret = 'badpassw0rd'
Use msdb
Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'
Sp_grant_login_to_proxy @.login_name='Philip_Jaques', @.proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'
Also, when I create this proxy account where in SQL Server 2005 can I go to view it and its properties? And assuming I get the proxy account set up correctly, how do I get my current jobs to start using it so they will successfully run?
Thanks in advance for your help and advice!
I've never heard of having to create a proxy to get SqlAgent to run SSIS accounts. Sql Agent should be setup to run under a service account already, and will run SSIS packages no problem. Where did you find this information?|||This article pretty much explains my problem:
http://support.microsoft.com/default.aspx/kb/918760
what is wrong and how can i fix it?
i have updated mssql/win 2003 with the lates updates
Rgds
BobbyHi Bobby
This usually has something to do with user permissions. Please ensure
that your database login has permissions to create new databases. Also
ensure that you have sufficient Windows user permissions to the
directory where SQL Server is trying to create the .mdf and the .ldf
file.
Cheers
Louis
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConStr"))
Dim Cm As SqlCommand
Dim dr As SqlDataReader
Dim FileName As String = Guid.NewGuid.ToString & ".csv"
Dim FilePath As String = Server.MapPath("") & "\CSVFiles\" & FileName
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
Dim Line As String
Dim lItem As System.Xml.XmlElement
Dim i As Int16
If ValidatePage() Then
Cm = New SqlCommand(BuildQuery, Conn)
Conn.Open()
dr = Cm.ExecuteReader()
Dim lXmlDoc As New System.Xml.XmlDocument()
lXmlDoc.LoadXml(txtHXml.InnerText)
For Each lItem In lXmlDoc.DocumentElement.SelectSingleNode("SELECT").ChildNodes
Select Case lItem.InnerText
Case "chkPRId"
Line &= "PR Id,"
Case "chkDateIssued"
Line &= "Date Issued,"
Case "chkOriginator"
Line &= "Originator,"
Case "chkBuyer"
Line &= "Buyer,"
Case "chkVendor"
Line &= "Vendor,"
Case "chkCostCode"
Line &= "Cost Code,"
Case "chkOracleRef"
Line &= "Oracle Reference,"
Case "chkTotal"
Line &= "Total,"
End Select
Next
Line = Line.Substring(0, Line.Length - 1)
sw.WriteLine(Line)
Line = ""
While dr.Read
For i = 0 To dr.FieldCount - 1
Line = Line & dr(i) & ","
Next
sw.WriteLine(Line)
Line = ""
End While
dr.Close()
Conn.Close()
sw.Close()
fs.Close()
Response.Redirect(FilePath)
End If
ResetPage()
PopulateListBoxFromXML()Have you checked that the file path resolves as expected. At least in C# backslashes in a file path get escaped to some other charcter. Try either:
or
Dim FilePath As String = Server.MapPath("") & "\\CSVFiles\\" & FileName
Dim FilePath As String = Server.MapPath("") & @."\CSVFiles\" & FileName
Line 4: Dim oODBCConnection As OdbcConnection
Line 5: Dim sConnString As String = _
Line 6: "Driver={SQL Server};" &_
Line 7: "Server=209.151.130.8;" &_
Line 8: "Database=blah;" &_
...(Error in line 6)
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30203: Identifier expected.First off - I'd say you're going in the wrong direction, since it looks as if you're trying to connect with a SQL server - -
ASP.Net has a namespace that talks directly to SQL Server...
it should look more like the info in :
Database Connect and Display Samples
Which tutorial are you reading?|||I've been using Classic ASP untill now, I'm moving on up to .NET...
I'm looking for a very simple way to retreave some data from a database and display it using of of the data web controls..
The part that I'm having trouble with is comunicating with the server (creating a connection)..
On The Side:
I've read about placing the connection string in the AppSettings section in web.config, but this isn't going to work in my case because I am only working with a single aspx file... Am I correct in saying that this way won't work because I'm using only one aspx file..|||Ps: I changed the ip, and user name for security reasons...
<% @.Import Namespace="System.Data.SqlClient" %>
<% @.Import Namespace="System.Data" %>
<script language="vb" runat="server" debug="true"
Sub Page_Load(Source as Object, E as EventArgs)
Dim strConn as string = "server=555.555.11.11;uid=test;pwd=test1;database=webland"
Dim MySQL as string = "Select pl_username From Users"
Dim MyConn as New SQLConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"Customers")
myDataList.Datasource=ds.Tables("users").DefaultView
myDataList.DataBind()
End Sub
</script
<asp:DataList runat="server" id="myDataList"
Width="85%" Border="1"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="4"
Font-Size="10pt"
RepeatColumns="5" >
<ItemTemplate>
Question:<br />
<%# DataBinder.Eval(Container.DataItem, "pl_username") %>
</ItemTemplate>
</asp:DataList
ERROR:
Cannot open database requested in login 'webland'. Login fails. Login failed for user 'test'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'webland'. Login fails. Login failed for user 'test'.
BUT:
I bleave that my user name information that I provided is correct..I think taht something else is causing the error, but i'm not getting the correct error message?|||It is very possible that wherever this database resides, that they are not using the standard sql port 1433. A lot of places are doing this now for security by obscurity reasons. Anyway, if you don't know for sure, I would contact the people who host your website and make sure that you have the right port. If it is a different port just add a comma and the port number after the server address. For example if you find out that the port number is 1435, then instead of this:
Dim strConn as string="server=555.555.11.11;uid=test;pwd=test1;database=webland"
try this:
Dim strConn as string="server=555.555.11.11,1435;uid=test;pwd=test1;database=webland"
Hi,
I want to add html as body of my mail message. I added CreateMHTMLBody method in my SQL code.
Syntax I used is
Exec @.hr=sp_oamethod @.imsg,'CreateMHTMLBody',NULL,@.MsgFile,0,'',''
This syntax is working fine on my system but whem I implement this on production it gives error.
Needed ASAP as I have to implmenet this on my production server today.
Thanks
GAurav
What is the error message you are getting|||are you running the same version of cdosys.dll on dev and prod?|||Why use CDO when you have sql database mail.
CreateMHTMLBody expects 4 parameters (url, cdomhtmlflags, user, pwd) so check your @.MsgFile.
|||Check the required COM DLLs are registered properly on your server.|||Using SQL database mail you can't send the HTML mails where message body is created from the HTML file
And my syntax is correct as it is working perfectly fine on my development env. but it is not working on production
|||Yes COM dll is properly registerde and it is sending Textbody mail. But when I use this CreateMHTMLBoby it gives me above error. And my code and syntax is also correct as it is working fine on development env.
Thanks
Hi all,
I not sure whether is there a way to create/alter a view with variable declare in the same statement. I encounter some error when I run the below statement.
Error:
Server: Msg 156, Level 15, State 1, Procedure Employee_Details , Line 2
Incorrect syntax near the keyword 'DECLARE'.
Server: Msg 170, Level 15, State 1, Procedure Employee_Details , Line 16
Line 16: Incorrect syntax near ')'.
My SQL Statement
ALTER VIEW Employee_Details as (
DECLARE @.usr nvarchar(250)
SET @.usr = user
SELECT
E.ID
,E.NAME
,E.DEPARTMENT
,E.JOB_TITLE
,E.JOIN_DATE
,E.RESIGN_DATE
FROM
EMPLOYEE AS E
WHERE
E.DEPARTMENT = (SELECT *
FROM GETCURRENTUSER(@.usr))
)
The GetCurrentUser is a function I have create to get the department that the user can view.
Please advice. Thanks in advance.
Hello,
You cannot declare variables in view defiunitions. You can either create your logic in a procedure or modify your view to make use of the in-built user_name() function in order to return filtered results.
Cheers,
Rob
Hi Rob,
Yes, I have try to use another way of doing and I think I have solve the problem.
Really thanks for your advice, it does give me another idea of doing this. 
