|
Oh the joys of dealing with people who use Excel as a data source  .
The only reliable way will be to maintain a mapping table for each source, Load your data into staging tables (I use bulkcopy, creating a new table based on the source), then test the structure against your map, process or spit the dummy based on the structure check.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ravikiran72p wrote: Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.
The only solution I know of is to load the data excel data into raw tables - stage this data then qc it with queries etc.
Once you are happy with the integrity of the data you can then load it into your database.
Basically this amounts to a datawarehousing system - you will need human input between the staging and load to warehouse. This is where the qc comes into effect.
All data that comes from a client needs to be qc'd with a human eye as invariably there will be data that breaks your business logic.
Don't trust client data, especially if it is in Excel format!
If any data is incorrect then the standard procedure is to ask the client to resend the corrected data - it should never be the software or receivers job to 'correct' raw data, since as soon as you 'correct ' the data you then become responsible for its contents.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Ravikiran72p wrote: Is there any solution in Sqlserver or ssis or .NET solution to identify Generic Columns of excel(xls) and map to Sqlserver Table correctly.
Create an "adaptor" (concept rather than a specific type of implementation) specific to each vendor. I suggest STRONGLY that you use extensive error checking on such an adaptor because that is the only way you will detect when they change the format.
The vendor files are delivered to a vendor specific folder and that is used as a key to determine which adaptor is used to process them.
The adaptor could be written in either SQL or C#. I would choose C# because it provides more flexibility for this sort of operation. Note that a C# solution need not go directly to the database. In can just output a file, with a fixed known format, which is then used as an import file to SQL Server.
The adaptors should also have error reporting, logging is good, to report on failed conversion attempts, as well as when successful ones ran.
|
|
|
|
|
How can I backup my db through the terminal, what is the command?
|
|
|
|
|
Hi All,
I am trying to run this query on my database and I am getting an error Incorrect syntax near the keyword 'JOIN'.
USE IVRCallCenterTest
Go
SELECT *
FROM dbo.OutputPromptItem WHERE SegmentId=7198
JOIN dbo.OutputPrompt
ON dbo.OutputPromptItem.PromptId=dbo.OutputPrompt.PromptID
Go
what is wrong with my code ?
Thanks in advance
|
|
|
|
|
Your Where clause needs to come after From.
USE IVRCallCenterTest
Go
SELECT *
FROM dbo.OutputPromptItem JOIN
dbo.OutputPrompt ON OutputPromptItem.PromptId = OutputPrompt.PromptID
WHERE SegmentId = 7198
Go
|
|
|
|
|
You should use Where Clause at End of Statement
Like
SELECT *
FROM dbo.OutputPromptItem
JOIN dbo.OutputPrompt
ON dbo.OutputPromptItem.PromptId=dbo.OutputPrompt.Promp WHERE SegmentId=7198
|
|
|
|
|
I went through security and added. Checked the owners and added sa and admin account. I just can't seem to figure out what I have missed! DBs detached, copied, and moved from test server to actual network server seems to have not included a "setting" or "configuration". Any ideas? Using Windows Authentication.
The odd is that I can connect and see the Master, but not the attached databases that were added. And yes, they were attached and brought online. The fact that I can see the master tells me that I have the router rules set correctly. I am open to any suggestions. Throw it at me. I have to have missed something obvious.
FIXED!
The problem exists in policy, whether group or user I did not explore further. Installed SQL Server as a local machine with local admin. I completely took the server offline for the initial setup. Reconnected using the suggested username/password as well.
Not a problem at all.
modified 25-May-13 11:05am.
|
|
|
|
|
What UI are you using to manage the databases?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
The following are the only possibilities.
- The database has not been attached,
- You are not connecting to the database server that you think you are.
- The user permissions are wrong.
PrissySC wrote: <layer> The fact that I can see the master tells me that I have the router rules set correctly
If you couldn't route to the database server then you couldn't connect nor log in. It has nothing to do with whether you can see databases.
|
|
|
|
|
See, that is where I am puzzled!
I specifically went in and gave public access as well as set the Owner to the account.
So then, thinking aloud, I should look at the owner.
Should I look at builtin\users or default admin account?
I already set the NT. From there, I assume, properties, user mapping. Checked the DB in question and provided membership to owner and public.
I will check the server roles and grant status for the user too. Hmmm .... at least you agree with me!
Thanks, and if you think of something else, I will check back and keep this thread updated. 
|
|
|
|
|
If you have restored/attached the database from a different server you may need to drop and reapply the user permissions, while the name is the same the id values are different.
While I use windows authentication in SSMS I have all my apps have a SQL identity and uses that, so a connection string ALWAYS has a userid and password, never integrated.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would have agreed, but I created a new database on the SQL server. I went to a client machine to connect, and once again, the new database does not show in the list. I get the SQL list with master and such.
Truly, this is really bothersome. It is almost as if there is a "scope" problem. Could the GPO affect this? It is an Admin of the domain, but what was given to me was that this admin account has full access. I would think to differ only because ... SQL installs with a local machine admin yes?
Creator/Owner does have special permissions by default on 2008 ... would any portion of this effect the DATA folder where the database has been relocated for attachment?
Lastly, I will try using nonintegrated methods and consider troubleshooting ID values as well per Mycroft Holmes' post.
|
|
|
|
|
I have set up a second instance of SSRS 2005 on a Microsoft 2003 server using the custom security extension sample. I have the report server login working with the custom security extension, but cannot seem to get the report manager working with this.
The error I am getting is "The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version."
Now what I thought was that the logon form would display, the admin would login and then be able to add roles, etc. Is this wrong? If so, how is it supposed to work and how to I configure it to work that way?
Below is how I have my report manager config's configured.
ReportManager folder - RSMGRPOLICY.CONFIG
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="$CodeGen$/*"
/>
</CodeGroup>
ReportManager folder - RSWEBAPPLICATION.CONFIG
<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>False</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>http:/servername/ReportsExternal</ReportServerUrl>
</UI>
ReportManager folder - WEB.CONFIG
<identity impersonate="false"/>
|
|
|
|
|
Dear friends,
For days now, I have been trying to restore a backup of the database (with command prompt) I had made with mysqldump. This is my line of code.
"C:\Program Files\MySQL\MySQL Server 5.5\mysql --user=root --password=mypassword --host= localhost --port= 3306 --database=dbname < C:/Backup/dbname.sql"
I get "The filename, directory name, or volume lable syntax is incorrect" error messsage. I have googled for this error to no avail as to do with mysql.
- My mysql server is up and running with no problem. (Even the mysqldump command works)
-Problem is the same even if I open the command prompt from C:\Program Files\MySQL\MySQL Server 5.5\mysql
-I have tried to remove "", did not help.
- I tried to change / with \ but no success.
For this one, I will really appreciate any help.
|
|
|
|
|
Get the MySQL GUI Tools and try with them. That's the easiest way.
My impression is that you do something wrong with spaces and quotation marks. What about
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe" --user=root --password=mypassword --host=localhost --port=3306 --database=dbname < "C:\Backup\dbname.sql"
In case of the GUI tools, you need not worry about that.
|
|
|
|
|
Thank you. I got through it, and yes, the trick was to remove " before the first --user...
|
|
|
|
|
Hi all,I have a problem with back up command!
I use this code but it doesnt work:
string constr = @"DataSource=.\SQLEXPRESS;AttachDbFilename=C:\Users\EHSAN\Documents\Visual Studio 2010\Projects\hokm\hokm\App_Data\Database1.mdf;Integrated Security=True;User Instance=True;";
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection ();
con.ConnectionString = constr;
con.Open();
System.Data.SqlClient.SqlCommand comm=newSystem.Data.SqlClient.SqlCommand();
comm.CommandText = @"backup database Database1 to disk ='c:\Users\EHSAN\Documents\Visual Studio 2010\Projects\hokm\hokm\DBbackup.bak' with init,stats=10";
comm.Connection = con;
comm.ExecuteNonQuery();
con.Close();
and when I run it, I get this error:
Database 'Database1' does not exist. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally.
but I use the database1 and I connect it successfully!
what is the problem?
|
|
|
|
|
Uhm you are naming your backup file with the .aspx extension, pretty sure that is going to bite you later, normal extension is .bak
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ohhh,it was a typing mistake ,
I still have this error !
and another note is that the DBBackup.bak file doesn't exsit for the first time and I wanted to use the directory.createdirectory,file.exist or directory.exist method but I dont know what name space(s) should I use!
any suggession for my tow questions????
plz help me!
|
|
|
|
|
sara-setare wrote: but I use the database1 and I connect it successfully!
Are the connectionstrings the same? Sql Server databases are usually attached to that server, with the server assigning a database-alias to that attached file.
You are attaching a file "on the fly", without specifying an alias. I doubt that Sql Server will use the filename as the database-alias. Add a "Database=database1;" part to your connectionstring, similar to the example given below;
Server=.\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;Database=dbname;
Trusted_Connection=Yes;
I'd recommend attaching it permanently to the server to avoid collisions in alias-names when attaching.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you so much,it works!
but maybe a primary question!!:
how can I attach my database permanently to the server?
|
|
|
|
|
You're welcome. Easiest is using the Sql Management Studio, but it could also be done using TSQL;
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]Source[^]
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am using a windows forms application to write data to a SQL 2008 database over a wireless network. (Scanning serial numbers)
While stepping through the code in debug mode, the Connection.Open seems to take relatively long.
Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?
Will the connection stay open, or will SQL server drop it if it is unused for some time?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
